April 25, 2012 at 12:21 pm
SQLKnowItAll (4/25/2012)
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
You have many of these: [Admission ID] [varchar(255)] NULL . Change the [varchar(255)] to varchar(255) or [varchar](255). Also, no such thing as a bigvarchar.+1 Lynn... To the OP. I kind of see what you are trying to do, but I don't know why you are doing it. Changing all of these columns to VARCHAR(255) is, frankly, just silly. You are going to cause major problems if you are not clear on what you are doing and why you are doing it.
Let me explain to you what is going on, I want to insert data into SMARTadmissions table from another table called SMARTUpload and most of the fields in the SMARTUpload table has data type different from the one in the destination table. I have tried to use Format Function to convert the data type but all to no avail. So that is why I wanted to recreate the destination table to have the same data type as the source table.
Note: I wasn't the one that design the database, that was the way I met it.
Thanks for your concern.
The way I see it, is that you don't have a problem with the table structure; i.e. leave those as int's and whatever else they were. You have a problem with your data tranformation from source to destination. Why don't you give us the table definition for the source table and we can help you to convert the data properly.
And if there are errors, what are the error message(s) you are receiving.
By the way, what I see when comparing the two scripts for the table is that it appears that to create the second script a replace was done to replace int with varchar(255), which resulted in [int] being changed to [varchar(255)], which is why the new script doesn't work.
April 25, 2012 at 12:39 pm
SQLKnowItAll (4/25/2012)
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
You have many of these: [Admission ID] [varchar(255)] NULL . Change the [varchar(255)] to varchar(255) or [varchar](255). Also, no such thing as a bigvarchar.+1 Lynn... To the OP. I kind of see what you are trying to do, but I don't know why you are doing it. Changing all of these columns to VARCHAR(255) is, frankly, just silly. You are going to cause major problems if you are not clear on what you are doing and why you are doing it.
Let me explain to you what is going on, I want to insert data into SMARTadmissions table from another table called SMARTUpload and most of the fields in the SMARTUpload table has data type different from the one in the destination table. I have tried to use Format Function to convert the data type but all to no avail. So that is why I wanted to recreate the destination table to have the same data type as the source table.
Note: I wasn't the one that design the database, that was the way I met it.
Thanks for your concern.
The way I see it, is that you don't have a problem with the table structure; i.e. leave those as int's and whatever else they were. You have a problem with your data tranformation from source to destination. Why don't you give us the table definition for the source table and we can help you to convert the data properly.
HERE IS THE SOURCE TABLE:
CREATE TABLE [dbo].[SMARTAdmissionUpload](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Client Intake ID] [varchar](255) NOT NULL,
[Client ID] [varchar](255) NULL,
[Agency ID] [varchar](255) NULL,
[Clinic ID] [varchar](255) NULL,
[National Provider Identifier] [varchar](255) NULL,
[Admission ID] [varchar](255) NULL,
[Admission ID Integer] [int] NULL,
[Facility Identifier] [varchar](255) NULL,
[Unique Client ID] [varchar](255) NULL,
[Serial Number] [varchar](8) NULL,
[Admission Creation Date] [varchar](255) NULL,
[Agency Client ID] [varchar](255) NULL,
[Social Security Number] [varchar](255) NULL,
[Client Type] [varchar](255) NULL,
[Date of Admission] [varchar](255) NULL,
[Transaction Type] [varchar](255) NULL,
[Number of Prior Admissions] [varchar](255) NULL,
[Source of Referral] [varchar](255) NULL,
[Sex] [varchar](255) NULL,
[Race] [varchar](255) NULL,
[Ethnicity] [varchar](255) NULL,
[Date of Birth] [varchar](255) NULL,
[County of Residence] [varchar](255) NULL,
[Zip Code] [varchar](255) NULL,
[Marital Status] [varchar](255) NULL,
[Highest School Grade Completed] [varchar](255) NULL,
[Employment Status] [varchar](255) NULL,
[Family Income] [varchar](255) NULL,
[Primary Source of Income] [varchar](255) NULL,
[Family Primary Source of Income] [varchar](255) NULL,
[Living Arrangement] [varchar](255) NULL,
[Number of Dependant Children] [varchar](255) NULL,
[Health Coverage] [varchar](255) NULL,
[Currently Pregnant?] [varchar](255) NULL,
[Current Mental Health Problems?] [varchar](255) NULL,
[Tobacco use in the past 30 days?] [varchar](255) NULL,
[Number of days waiting to enter treatment] [varchar](255) NULL,
[ASI Medical Score] [varchar](255) NULL,
[ASI Employment Score] [varchar](255) NULL,
[ASI Alcohol Score] [varchar](255) NULL,
[ASI Drug Score] [varchar](255) NULL,
[ASI Legal Score] [varchar](255) NULL,
[ASI Family Score] [varchar](255) NULL,
[ASI Psychiatric Score] [varchar](255) NULL,
[In a Controlled Environment past 30 Days?] [varchar](255) NULL,
[POSIT Substance Abuse Score] [varchar](255) NULL,
[POSIT Physical Health Score] [varchar](255) NULL,
[POSIT Mental Health Score] [varchar](255) NULL,
[POSIT Family Score] [varchar](255) NULL,
[POSIT Peer Score] [varchar](255) NULL,
[POSIT Education Status Score] [varchar](255) NULL,
[POSIT Vocational Status Score] [varchar](255) NULL,
[POSIT Social Skill Score] [varchar](255) NULL,
[POSIT Leisure Recreatal Score] [varchar](255) NULL,
[POSIT Aggression Score] [varchar](255) NULL,
[POSIT STD HIV Risk Score] [varchar](255) NULL,
[Primary Substance] [varchar](255) NULL,
[Primary Severity] [varchar](255) NULL,
[Primary Frequency] [varchar](255) NULL,
[Primary Route] [varchar](255) NULL,
[Primary Age of First Use] [varchar](255) NULL,
[Secondary Substance] [varchar](255) NULL,
[Secondary Severity] [varchar](255) NULL,
[Secondary Frequency] [varchar](255) NULL,
[Secondary Route] [varchar](255) NULL,
[Secondary Age of First Use] [varchar](255) NULL,
[Tertiary Substance] [varchar](255) NULL,
[Tertiary Severity] [varchar](255) NULL,
[Tertiary Frequency] [varchar](255) NULL,
[Tertiary Route] [varchar](255) NULL,
[Tertiary Age of First Use] [varchar](255) NULL,
[Treatment Setting] [varchar](255) NULL,
[Attending Grades K-12] [varchar](255) NULL,
[Attending GED Program] [varchar](255) NULL,
[Attending Vocational Training] [varchar](255) NULL,
[Attending Higher Education] [varchar](255) NULL,
[Number of Arrests in the Past Year] [varchar](255) NULL,
[Number of Arrests in the Past 30 Days] [varchar](255) NULL,
[Special Funding 1] [varchar](255) NULL,
[Special Funding 2] [varchar](255) NULL,
[Special Funding 3] [varchar](255) NULL,
[Special Project 1] [varchar](255) NULL,
[Special Project 2] [varchar](255) NULL,
[Special Project 3] [varchar](255) NULL,
[Number of Days in Support Group in Last 30 Days] [varchar](255) NULL,
[Number of Days Attended AA/NA in Last 30 Days] [varchar](255) NULL,
[Treatment Services] [varchar](255) NULL,
[Additional Information] [varchar](255) NULL,
[Is Submit Without ASI Scores] [varchar](255) NULL,
[Intake Date] [varchar](255) NULL,
[Intake Creation Date] [varchar](255) NULL,
[HATSAdmissionID] [int] NULL,
[IFB] [bit] NULL,
[Ignore] [int] NULL,
[SrcFileName] [varchar](255) NULL,
[SrcFileDate] [varchar](50) NULL,
CONSTRAINT [PK_SMARTAdmissionUpload] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SMARTAdmissionUpload] ADD CONSTRAINT [DF_SMARTAdmissionUpload_Admission ID Integer] DEFAULT ((0)) FOR [Admission ID Integer]
GO
ALTER TABLE [dbo].[SMARTAdmissionUpload] ADD CONSTRAINT [DF_SMARTAdmissionUpload_Ignore] DEFAULT ((0)) FOR [Ignore]
GO
April 25, 2012 at 12:44 pm
Okay, it looks cleaner, but does it work? If so, is there another problem?
April 25, 2012 at 12:46 pm
Lynn Pettis (4/25/2012)
Okay, it looks cleaner, but does it work? If so, is there another problem?
I think the OP is saying that this is the source table for the data that needs to get into the other table. We need to change the VARCHAR(255) data to int for the proper columns. To the OP... Can you tell us how the data gets into the source table? Where does it come from? Why can't the source table be changed to match the destination (that would be the best solution)? What errors do you get converting the VARCHAR(255) to int?
Jared
CE - Microsoft
April 25, 2012 at 12:50 pm
One other thing, is this thread related back to these:
http://www.sqlservercentral.com/Forums/Topic1289178-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1289243-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1289489-391-1.aspx
If so, lets get everything consolidated into one thread so we aren't all over the place trying solve pieces parts.
April 25, 2012 at 1:04 pm
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
Okay, it looks cleaner, but does it work? If so, is there another problem?I think the OP is saying that this is the source table for the data that needs to get into the other table. We need to change the VARCHAR(255) data to int for the proper columns. To the OP... Can you tell us how the data gets into the source table? Where does it come from? Why can't the source table be changed to match the destination (that would be the best solution)? What errors do you get converting the VARCHAR(255) to int?
The data come from many excel files which are uploaded using asp.net and some stored procedures, once the data are uploaded into the SMARTUpload table they are then inserted into the SMARTAdmission table. But for some reason the data are not being inserted to SMARTAdmission table like it used to be before. I can't really give the reason why it's no more inserting data into the SMARTAdmission table.
Thanks so much.
April 25, 2012 at 1:08 pm
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
Okay, it looks cleaner, but does it work? If so, is there another problem?I think the OP is saying that this is the source table for the data that needs to get into the other table. We need to change the VARCHAR(255) data to int for the proper columns. To the OP... Can you tell us how the data gets into the source table? Where does it come from? Why can't the source table be changed to match the destination (that would be the best solution)? What errors do you get converting the VARCHAR(255) to int?
The data come from many excel files which are uploaded using asp.net and some stored procedures, once the data are uploaded into the SMARTUpload table they are then inserted into the SMARTAdmission table. But for some reason the data are not being inserted to SMARTAdmission table like it used to be before. I can't really give the reason why it's no more inserting data into the SMARTAdmission table.
Thanks so much.
Looking back at some of your other posts, it seems the data has changed. For example, you were having problems converting 'MD-101289' to an int. The question is... Why did this change from an INT to a different data type? You have to speak with the business to find out if the requirements changed, or if someone is making a mistake in the excel sheets. You can't force a round peg through a square hole... To fix it the question is, is the peg supposed to be square or is the hole supposed to be round?
Jared
CE - Microsoft
April 25, 2012 at 1:09 pm
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
Okay, it looks cleaner, but does it work? If so, is there another problem?I think the OP is saying that this is the source table for the data that needs to get into the other table. We need to change the VARCHAR(255) data to int for the proper columns. To the OP... Can you tell us how the data gets into the source table? Where does it come from? Why can't the source table be changed to match the destination (that would be the best solution)? What errors do you get converting the VARCHAR(255) to int?
The data come from many excel files which are uploaded using asp.net and some stored procedures, once the data are uploaded into the SMARTUpload table they are then inserted into the SMARTAdmission table. But for some reason the data are not being inserted to SMARTAdmission table like it used to be before. I can't really give the reason why it's no more inserting data into the SMARTAdmission table.
Thanks so much.
No errors during processing? The data just doesn't transfer?
What about my other question regarding the other threads you started? Everything seems to tie together somehow, and I am trying to figure out how.
April 25, 2012 at 1:13 pm
SQLKnowItAll (4/25/2012)
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
Okay, it looks cleaner, but does it work? If so, is there another problem?I think the OP is saying that this is the source table for the data that needs to get into the other table. We need to change the VARCHAR(255) data to int for the proper columns. To the OP... Can you tell us how the data gets into the source table? Where does it come from? Why can't the source table be changed to match the destination (that would be the best solution)? What errors do you get converting the VARCHAR(255) to int?
The data come from many excel files which are uploaded using asp.net and some stored procedures, once the data are uploaded into the SMARTUpload table they are then inserted into the SMARTAdmission table. But for some reason the data are not being inserted to SMARTAdmission table like it used to be before. I can't really give the reason why it's no more inserting data into the SMARTAdmission table.
Thanks so much.
Looking back at some of your other posts, it seems the data has changed. For example, you were having problems converting 'MD-101289' to an int. The question is... Why did this change from an INT to a different data type? You have to speak with the business to find out if the requirements changed, or if someone is making a mistake in the excel sheets. You can't force a round peg through a square hole... To fix it the question is, is the peg supposed to be square or is the hole supposed to be round?
I did not change anything since the last post, I decided to take this rout when I couldn't figure out away to get the data inserted to the destination table.
April 25, 2012 at 1:15 pm
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
Okay, it looks cleaner, but does it work? If so, is there another problem?I think the OP is saying that this is the source table for the data that needs to get into the other table. We need to change the VARCHAR(255) data to int for the proper columns. To the OP... Can you tell us how the data gets into the source table? Where does it come from? Why can't the source table be changed to match the destination (that would be the best solution)? What errors do you get converting the VARCHAR(255) to int?
The data come from many excel files which are uploaded using asp.net and some stored procedures, once the data are uploaded into the SMARTUpload table they are then inserted into the SMARTAdmission table. But for some reason the data are not being inserted to SMARTAdmission table like it used to be before. I can't really give the reason why it's no more inserting data into the SMARTAdmission table.
Thanks so much.
Looking back at some of your other posts, it seems the data has changed. For example, you were having problems converting 'MD-101289' to an int. The question is... Why did this change from an INT to a different data type? You have to speak with the business to find out if the requirements changed, or if someone is making a mistake in the excel sheets. You can't force a round peg through a square hole... To fix it the question is, is the peg supposed to be square or is the hole supposed to be round?
I did not change anything since the last post, I decided to take this rout when I couldn't figure out away to get the data inserted to the destination table.
We haven't said you changed anything. It is possible that changes have occurred in the data you are processing that has resulted in the problem(s) you are having.
April 25, 2012 at 1:17 pm
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
deebabat (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
Okay, it looks cleaner, but does it work? If so, is there another problem?I think the OP is saying that this is the source table for the data that needs to get into the other table. We need to change the VARCHAR(255) data to int for the proper columns. To the OP... Can you tell us how the data gets into the source table? Where does it come from? Why can't the source table be changed to match the destination (that would be the best solution)? What errors do you get converting the VARCHAR(255) to int?
The data come from many excel files which are uploaded using asp.net and some stored procedures, once the data are uploaded into the SMARTUpload table they are then inserted into the SMARTAdmission table. But for some reason the data are not being inserted to SMARTAdmission table like it used to be before. I can't really give the reason why it's no more inserting data into the SMARTAdmission table.
Thanks so much.
Looking back at some of your other posts, it seems the data has changed. For example, you were having problems converting 'MD-101289' to an int. The question is... Why did this change from an INT to a different data type? You have to speak with the business to find out if the requirements changed, or if someone is making a mistake in the excel sheets. You can't force a round peg through a square hole... To fix it the question is, is the peg supposed to be square or is the hole supposed to be round?
I did not change anything since the last post, I decided to take this rout when I couldn't figure out away to get the data inserted to the destination table.
Your problem lies with the main data source; i.e. the Excel files. The data changed. It had to! If everything was working fine, and then all of the sudden a data type that used to be INT changes to contain different data... Something changed. We can't tell you why it changed. What I can tell you, is if you change the data types on your destination table I can almost guarantee you will break other processes; i.e. Doing arithmetic operations on Family Income column. Go to your boss, tell him/her that the type of data for certain columns has changed from what it used to be. Ask if that was supposed to happen, or if someone is making mistakes while putting data into the Excel sheets.
Jared
CE - Microsoft
April 18, 2018 at 7:03 am
None of you people have identified the issue. The problem is that SQL server is returning an error "Cannot find data type varchar(50)."
I am getting the same message. This is in script generated by SQL Server 2014.
"Msg 2715, Level 16, State 6, Line 84
Column, parameter, or variable #3: Cannot find data type varchar(50)."
The supposedly offending statement in part reads
CREATE
TABLE [dbo].[prc_RIKs](
[RIKID] [int] IDENTITY(1,1) NOT NULL,
[ReqID] [int] NOT NULL,
[RIKName] [varchar(50)] NOT NULL,
[StockIssueQty] [int] NOT NULL,
[CCIDProviding] [int] NOT NULL,
[CustIDProviding] [int] NOT NULL,
[StockShipDate] [date] NULL,
[LastUpdatedDate] [datetime] NOT NULL,
[LastUpdatedPTUserID] [int] NOT NULL,
CONSTRAINT [PK_prc_RIKs] PRIMARY KEY CLUSTERED
(
[RIKID]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Which shows that the offending line is the perfectly valid line of "[RIKName] [varchar(50)] NOT NULL," What is this error.
April 18, 2018 at 2:02 pm
ejoell 66477 - Wednesday, April 18, 2018 7:03 AMNone of you people have identified the issue. The problem is that SQL server is returning an error "Cannot find data type varchar(50)."I am getting the same message. This is in script generated by SQL Server 2014.
"Msg 2715, Level 16, State 6, Line 84
Column, parameter, or variable #3: Cannot find data type varchar(50)."
The supposedly offending statement in part readsCREATE TABLE [dbo].[prc_RIKs](
[RIKID] [int] IDENTITY(1,1) NOT NULL,[ReqID] [int] NOT NULL,
[RIKName] [varchar(50)] NOT NULL,
[StockIssueQty] [int] NOT NULL,
[CCIDProviding] [int] NOT NULL,
[CustIDProviding] [int] NOT NULL,
[StockShipDate] [date] NULL,
[LastUpdatedDate] [datetime] NOT NULL,
[LastUpdatedPTUserID] [int] NOT NULL,
CONSTRAINT [PK_prc_RIKs] PRIMARY KEY CLUSTERED
(
[RIKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Which shows that the offending line is the perfectly valid line of "[RIKName] [varchar(50)] NOT NULL," What is this error.
Wow, that was easy once I took a closer look, the closing square bracket ']' belongs after VARCHAR and before the (50), like this: [varchar](50).
April 18, 2018 at 2:10 pm
ejoell 66477 - Wednesday, April 18, 2018 7:03 AMNone of you people have identified the issue.
6 year old thread, and Lynn identified the problem 6 years ago:
"By the way, what I see when comparing the two scripts for the table is that it appears that to create the second script a replace was done to replace int with varchar(255), which resulted in [int] being changed to [varchar(255)], which is why the new script doesn't work."
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2018 at 2:20 pm
GilaMonster - Wednesday, April 18, 2018 2:10 PMejoell 66477 - Wednesday, April 18, 2018 7:03 AMNone of you people have identified the issue.6 year old thread, and Lynn identified the problem 6 years ago:
"By the way, what I see when comparing the two scripts for the table is that it appears that to create the second script a replace was done to replace int with varchar(255), which resulted in [int] being changed to [varchar(255)], which is why the new script doesn't work."
Wow, I didn't go back far enough to see that. Thanks, Gail.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply