April 25, 2012 at 9:41 am
I have a table that I wanted to drop and recreate by changing some of the data type from INT to VARCHAR but when I ran the query I got this error msg:Msg 2715, Level 16, State 7, Line 2
Column, parameter, or variable #4: Cannot find data type varchar(50). I don't know why.
April 25, 2012 at 9:43 am
deebabat (4/25/2012)
I have a table that I wanted to drop and recreate by changing some of the data type from INT to VARCHAR but when I ran the query I got this error msg:Msg 2715, Level 16, State 7, Line 2Column, parameter, or variable #4: Cannot find data type varchar(50). I don't know why.
Nothing here to see, don't know what you ran.
April 25, 2012 at 9:53 am
What Lynn is saying is to please post the statements that you ran so we can look at them and help you.
Jared
CE - Microsoft
April 25, 2012 at 9:56 am
SQLKnowItAll (4/25/2012)
What Lynn is saying is to please post the statements that you ran so we can look at them and help you.
Dang it, I was trying to subtle. 😉
April 25, 2012 at 10:07 am
Lynn Pettis (4/25/2012)
SQLKnowItAll (4/25/2012)
What Lynn is saying is to please post the statements that you ran so we can look at them and help you.Dang it, I was trying to subtle. 😉
I wanted to jump in because I am curious about this one 🙂 Sorry!
Jared
CE - Microsoft
April 25, 2012 at 10:08 am
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
SQLKnowItAll (4/25/2012)
What Lynn is saying is to please post the statements that you ran so we can look at them and help you.Dang it, I was trying to subtle. 😉
I wanted to jump in because I am curious about this one 🙂 Sorry!
Unfortunately, he isn't around at the moment, so we both have to wait.
April 25, 2012 at 10:31 am
Lynn Pettis (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
SQLKnowItAll (4/25/2012)
What Lynn is saying is to please post the statements that you ran so we can look at them and help you.Dang it, I was trying to subtle. 😉
I wanted to jump in because I am curious about this one 🙂 Sorry!
Unfortunately, he isn't around at the moment, so we both have to wait.
Here is the ORIGINAL CODE:
USE [TestSMARTDB]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Client ID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Client ID]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Race Group ID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Race Group ID]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Family Income]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Family Income]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Information_Code]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Information_Code]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_IsBUP]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_IsBUP]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_AppendDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_AppendDate]
END
GO
USE [TestSMARTDB]
GO
/****** Object: Table [dbo].[SMARTAdmissions] Script Date: 04/25/2012 12:25:59 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SMARTAdmissions]') AND type in (N'U'))
DROP TABLE [dbo].[SMARTAdmissions]
GO
USE [TestSMARTDB]
GO
/****** Object: Table [dbo].[SMARTAdmissions] Script Date: 04/25/2012 12:25:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SMARTAdmissions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SMARTUploadID] [int] NULL,
[Admission ID] [int] NULL,
[Treatment Services Group ID] [int] NULL,
[Client Intake ID] [int] NULL,
[Client ID] [int] NULL,
[Race Group ID] [int] NULL,
[Agency ID] [int] NULL,
[Clinic ID] [int] NULL,
[National Provider Identifier] [int] NULL,
[Facility Identifier] [int] NULL,
[Unique Client ID] [varchar](255) NULL,
[Admission Creation Date] [datetime] NULL,
[Agency Client ID] [varchar](50) NULL,
[Agency Client ID bsas] [bigint] NULL,
[Social Security Number] [varchar](255) NULL,
[Client Type] [varchar](255) NULL,
[Date of Admission] [datetime] NULL,
[Transaction Type] [varchar](255) NULL,
[Number of Prior Admissions] [int] NULL,
[Source of Referral] [varchar](255) NULL,
[Sex] [int] NULL,
[Race] [varchar](255) NULL,
[Ethnicity] [int] NULL,
[Date of Birth] [datetime] NULL,
[County of Residence] [int] NULL,
[Zip Code] [varchar](255) NULL,
[Marital Status] [int] NULL,
[Highest School Grade Completed] [int] NULL,
[Employment Status] [int] NULL,
[Family Income] [int] NULL,
[Primary Source of Income] [int] NULL,
[Living Arrangement] [int] NULL,
[Number of Dependant Children] [int] NULL,
[Health Coverage] [int] NULL,
[Currently Pregnant?] [int] NULL,
[Current Mental Health Problems?] [int] NULL,
[Tobacco use in the past 30 days?] [int] NULL,
[Number of days waiting to enter treatment] [varchar](255) NULL,
[ASI Medical Score] [float] NULL,
[ASI Employment Score] [float] NULL,
[ASI Alcohol Score] [float] NULL,
[ASI Drug Score] [float] NULL,
[ASI Legal Score] [float] NULL,
[ASI Family Score] [float] NULL,
[ASI Psychiatric Score] [float] NULL,
[In a Controlled Environment past 30 Days?] [int] NULL,
[POSIT Substance Abuse Score] [float] NULL,
[POSIT Physical Health Score] [float] NULL,
[POSIT Mental Health Score] [float] NULL,
[POSIT Family Score] [float] NULL,
[POSIT Peer Score] [float] NULL,
[POSIT Education Status Score] [float] NULL,
[POSIT Vocational Status Score] [float] NULL,
[POSIT Social Skill Score] [float] NULL,
[POSIT Leisure Recreatal Score] [float] NULL,
[POSIT Aggression Score] [float] NULL,
[POSIT STD HIV Risk Score] [float] NULL,
[Primary Substance] [int] NULL,
[Primary Severity] [int] NULL,
[Primary Frequency] [int] NULL,
[Primary Route] [int] NULL,
[Primary Age of First Use] [int] NULL,
[Secondary Substance] [int] NULL,
[Secondary Severity] [int] NULL,
[Secondary Frequency] [int] NULL,
[Secondary Route] [int] NULL,
[Secondary Age of First Use] [int] NULL,
[Tertiary Substance] [int] NULL,
[Tertiary Severity] [int] NULL,
[Tertiary Frequency] [int] NULL,
[Tertiary Route] [int] NULL,
[Tertiary Age of First Use] [int] NULL,
[Treatment Setting] [varchar](255) NULL,
[Attending Grades K-12] [int] NULL,
[Attending GED Program] [int] NULL,
[Attending Vocational Training] [int] NULL,
[Attending Higher Education] [int] NULL,
[Number of Arrests in the Past Year] [int] NULL,
[Number of Arrests in the Past 30 Days] [int] NULL,
[Special Funding 1] [int] NULL,
[Special Funding 2] [int] NULL,
[Special Funding 3] [int] NULL,
[Special Project 1] [int] NULL,
[Special Project 2] [int] NULL,
[Special Project 3] [int] NULL,
[Number of Days in Support Group in Last 30 Days] [int] NULL,
[Number of Days Attended AA/NA in Last 30 Days] [int] NULL,
[Additional Information] [varchar](255) NULL,
[Is Submit Without ASI Scores] [int] NULL,
[Intake Date] [datetime] NULL,
[Intake Creation Date] [datetime] NULL,
[IFB] [bit] NULL,
[HATSAdmissionID] [int] NULL,
[senttofeds] [datetime] NULL,
[Information_Code] [int] NULL,
[IsBUP_TxSvc] [tinyint] NULL,
[AppendDate] [datetime] NULL,
[SrcFileDate] [datetime] NULL,
CONSTRAINT [PK_SMARTAdmissions] 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].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Client ID] DEFAULT ((0)) FOR [Client ID]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Race Group ID] DEFAULT ((0)) FOR [Race Group ID]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Family Income] DEFAULT ((0)) FOR [Family Income]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Information_Code] DEFAULT ((0)) FOR [Information_Code]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_IsBUP] DEFAULT ((0)) FOR [IsBUP_TxSvc]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_AppendDate] DEFAULT (getdate()) FOR [AppendDate]
GO
NOTE: I wanted to change the some of the data type from INT to VARCHAR
April 25, 2012 at 10:35 am
Ok, now what is the new code you ran? The code that caused the error. Also, If you can... I would remove all of the spaces from your column names. This is poor design.
Jared
CE - Microsoft
April 25, 2012 at 10:37 am
Not sure. I can run your code with no problems (other than not having your database), but that was easily fixed.
April 25, 2012 at 10:49 am
deebabat (4/25/2012)
Lynn Pettis (4/25/2012)
SQLKnowItAll (4/25/2012)
Lynn Pettis (4/25/2012)
SQLKnowItAll (4/25/2012)
What Lynn is saying is to please post the statements that you ran so we can look at them and help you.Dang it, I was trying to subtle. 😉
I wanted to jump in because I am curious about this one 🙂 Sorry!
Unfortunately, he isn't around at the moment, so we both have to wait.
Here is the ORIGINAL CODE:
USE [TestSMARTDB]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Client ID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Client ID]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Race Group ID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Race Group ID]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Family Income]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Family Income]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Information_Code]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Information_Code]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_IsBUP]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_IsBUP]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_AppendDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_AppendDate]
END
GO
USE [TestSMARTDB]
GO
/****** Object: Table [dbo].[SMARTAdmissions] Script Date: 04/25/2012 12:25:59 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SMARTAdmissions]') AND type in (N'U'))
DROP TABLE [dbo].[SMARTAdmissions]
GO
USE [TestSMARTDB]
GO
/****** Object: Table [dbo].[SMARTAdmissions] Script Date: 04/25/2012 12:25:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SMARTAdmissions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SMARTUploadID] [int] NULL,
[Admission ID] [int] NULL,
[Treatment Services Group ID] [int] NULL,
[Client Intake ID] [int] NULL,
[Client ID] [int] NULL,
[Race Group ID] [int] NULL,
[Agency ID] [int] NULL,
[Clinic ID] [int] NULL,
[National Provider Identifier] [int] NULL,
[Facility Identifier] [int] NULL,
[Unique Client ID] [varchar](255) NULL,
[Admission Creation Date] [datetime] NULL,
[Agency Client ID] [varchar](50) NULL,
[Agency Client ID bsas] [bigint] NULL,
[Social Security Number] [varchar](255) NULL,
[Client Type] [varchar](255) NULL,
[Date of Admission] [datetime] NULL,
[Transaction Type] [varchar](255) NULL,
[Number of Prior Admissions] [int] NULL,
[Source of Referral] [varchar](255) NULL,
[Sex] [int] NULL,
[Race] [varchar](255) NULL,
[Ethnicity] [int] NULL,
[Date of Birth] [datetime] NULL,
[County of Residence] [int] NULL,
[Zip Code] [varchar](255) NULL,
[Marital Status] [int] NULL,
[Highest School Grade Completed] [int] NULL,
[Employment Status] [int] NULL,
[Family Income] [int] NULL,
[Primary Source of Income] [int] NULL,
[Living Arrangement] [int] NULL,
[Number of Dependant Children] [int] NULL,
[Health Coverage] [int] NULL,
[Currently Pregnant?] [int] NULL,
[Current Mental Health Problems?] [int] NULL,
[Tobacco use in the past 30 days?] [int] NULL,
[Number of days waiting to enter treatment] [varchar](255) NULL,
[ASI Medical Score] [float] NULL,
[ASI Employment Score] [float] NULL,
[ASI Alcohol Score] [float] NULL,
[ASI Drug Score] [float] NULL,
[ASI Legal Score] [float] NULL,
[ASI Family Score] [float] NULL,
[ASI Psychiatric Score] [float] NULL,
[In a Controlled Environment past 30 Days?] [int] NULL,
[POSIT Substance Abuse Score] [float] NULL,
[POSIT Physical Health Score] [float] NULL,
[POSIT Mental Health Score] [float] NULL,
[POSIT Family Score] [float] NULL,
[POSIT Peer Score] [float] NULL,
[POSIT Education Status Score] [float] NULL,
[POSIT Vocational Status Score] [float] NULL,
[POSIT Social Skill Score] [float] NULL,
[POSIT Leisure Recreatal Score] [float] NULL,
[POSIT Aggression Score] [float] NULL,
[POSIT STD HIV Risk Score] [float] NULL,
[Primary Substance] [int] NULL,
[Primary Severity] [int] NULL,
[Primary Frequency] [int] NULL,
[Primary Route] [int] NULL,
[Primary Age of First Use] [int] NULL,
[Secondary Substance] [int] NULL,
[Secondary Severity] [int] NULL,
[Secondary Frequency] [int] NULL,
[Secondary Route] [int] NULL,
[Secondary Age of First Use] [int] NULL,
[Tertiary Substance] [int] NULL,
[Tertiary Severity] [int] NULL,
[Tertiary Frequency] [int] NULL,
[Tertiary Route] [int] NULL,
[Tertiary Age of First Use] [int] NULL,
[Treatment Setting] [varchar](255) NULL,
[Attending Grades K-12] [int] NULL,
[Attending GED Program] [int] NULL,
[Attending Vocational Training] [int] NULL,
[Attending Higher Education] [int] NULL,
[Number of Arrests in the Past Year] [int] NULL,
[Number of Arrests in the Past 30 Days] [int] NULL,
[Special Funding 1] [int] NULL,
[Special Funding 2] [int] NULL,
[Special Funding 3] [int] NULL,
[Special Project 1] [int] NULL,
[Special Project 2] [int] NULL,
[Special Project 3] [int] NULL,
[Number of Days in Support Group in Last 30 Days] [int] NULL,
[Number of Days Attended AA/NA in Last 30 Days] [int] NULL,
[Additional Information] [varchar](255) NULL,
[Is Submit Without ASI Scores] [int] NULL,
[Intake Date] [datetime] NULL,
[Intake Creation Date] [datetime] NULL,
[IFB] [bit] NULL,
[HATSAdmissionID] [int] NULL,
[senttofeds] [datetime] NULL,
[Information_Code] [int] NULL,
[IsBUP_TxSvc] [tinyint] NULL,
[AppendDate] [datetime] NULL,
[SrcFileDate] [datetime] NULL,
CONSTRAINT [PK_SMARTAdmissions] 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].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Client ID] DEFAULT ((0)) FOR [Client ID]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Race Group ID] DEFAULT ((0)) FOR [Race Group ID]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Family Income] DEFAULT ((0)) FOR [Family Income]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Information_Code] DEFAULT ((0)) FOR [Information_Code]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_IsBUP] DEFAULT ((0)) FOR [IsBUP_TxSvc]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_AppendDate] DEFAULT (getdate()) FOR [AppendDate]
GO
NOTE: I wanted to change the some of the data type from INT to VARCHAR
HERE IS THE CODE:
USE [TestSMARTDB]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Client ID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Client ID]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Race Group ID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Race Group ID]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Family Income]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Family Income]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_Information_Code]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_Information_Code]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_IsBUP]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_IsBUP]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SMARTAdmissions_AppendDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SMARTAdmissions] DROP CONSTRAINT [DF_SMARTAdmissions_AppendDate]
END
GO
USE [TestSMARTDB]
GO
/****** Object: Table [dbo].[SMARTAdmissions] Script Date: 04/25/2012 12:25:59 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SMARTAdmissions]') AND type in (N'U'))
DROP TABLE [dbo].[SMARTAdmissions]
GO
USE [TestSMARTDB]
GO
/****** Object: Table [dbo].[SMARTAdmissions] Script Date: 04/25/2012 12:25:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SMARTAdmissions](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[SMARTUploadID] [INT] NULL,
[Admission ID] [varchar(255)] NULL,
[Treatment Services Group ID] [varchar(255)] NULL,
[Client Intake ID] [varchar(255)] NULL,
[Client ID] [varchar(255)] NULL,
[Race Group ID] [varchar(255)] NULL,
[Agency ID] [varchar(255)] NULL,
[Clinic ID] [varchar(255)] NULL,
[National Provider Identifier] [varchar(255)] NULL,
[Facility Identifier] [varchar(255)] NULL,
[Unique Client ID] [varchar](255) NULL,
[Admission Creation Date] [datetime] NULL,
[Agency Client ID] [varchar](50) NULL,
[Agency Client ID bsas] [bigvarchar(255)] NULL,
[Social Security Number] [varchar](255) NULL,
[Client Type] [varchar](255) NULL,
[Date of Admission] [datetime] 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] [datetime] 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,
[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] [float] NULL,
[ASI Employment Score] [float] NULL,
[ASI Alcohol Score] [float] NULL,
[ASI Drug Score] [float] NULL,
[ASI Legal Score] [float] NULL,
[ASI Family Score] [float] NULL,
[ASI Psychiatric Score] [float] NULL,
[In a Controlled Environment past 30 Days?] [varchar(255)] NULL,
[POSIT Substance Abuse Score] [float] NULL,
[POSIT Physical Health Score] [float] NULL,
[POSIT Mental Health Score] [float] NULL,
[POSIT Family Score] [float] NULL,
[POSIT Peer Score] [float] NULL,
[POSIT Education Status Score] [float] NULL,
[POSIT Vocational Status Score] [float] NULL,
[POSIT Social Skill Score] [float] NULL,
[POSIT Leisure Recreatal Score] [float] NULL,
[POSIT Aggression Score] [float] NULL,
[POSIT STD HIV Risk Score] [float] 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,
[Additional Information] [varchar](255) NULL,
[Is Submit Without ASI Scores] [varchar(255)] NULL,
[varchar(255)ake Date] [datetime] NULL,
[varchar(255)ake Creation Date] [datetime] NULL,
[IFB] [bit] NULL,
[HATSAdmissionID] [varchar(255)] NULL,
[senttofeds] [datetime] NULL,
[Information_Code] [varchar(255)] NULL,
[IsBUP_TxSvc] [tinyvarchar(255)] NULL,
[AppendDate] [datetime] NULL,
[SrcFileDate] [datetime] NULL,
CONSTRAINT [PK_SMARTAdmissions] 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].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Client ID] DEFAULT ((0)) FOR [Client ID]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Race Group ID] DEFAULT ((0)) FOR [Race Group ID]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Family Income] DEFAULT ((0)) FOR [Family Income]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_Information_Code] DEFAULT ((0)) FOR [Information_Code]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_IsBUP] DEFAULT ((0)) FOR [IsBUP_TxSvc]
GO
ALTER TABLE [dbo].[SMARTAdmissions] ADD CONSTRAINT [DF_SMARTAdmissions_AppendDate] DEFAULT (getdate()) FOR [AppendDate]
GO
April 25, 2012 at 10:52 am
Let me ask you... Why are you changing these columns to VARCHAR? Specifically, the family income? That is money, not character data. I wouldn't make these changes, personally.
Jared
CE - Microsoft
April 25, 2012 at 10:54 am
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.
April 25, 2012 at 11:03 am
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.
Jared
CE - Microsoft
April 25, 2012 at 12:05 pm
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.
April 25, 2012 at 12:17 pm
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.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply