Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 help with a table query

Author  Topic 

lmayer4
Starting Member

33 Posts

Posted - 2013-10-01 : 09:38:09
This seems okay but it doesnt seem to want to run past half way through. The database name changes every month, this just creates the holding tables for it.


declare
@sql varchar(500), @name varchar(100);

set
@name = (select 'sov01_usg'
+ '' +
case
when convert(varchar(10),getdate(),103)< '%15%' then '1'
else '2'
end+ '' +
'1'
+ '' +SUBSTRING(convert(varchar(8),getdate(),1),7,2)
+ '' +
case
when SUBSTRING(convert(varchar(8),getdate(),112),7,2)< '15' then SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5,2)+ '' +'01'
when SUBSTRING(convert(varchar(8),getdate(),112),7,2)>= '15' then SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5,2)+ '' +'15'
end)

/****** Object: Table [dbo].[Usage] Script Date: 02/11/2013 20:21:35 ******/
select
@sql = 'CREATE TABLE ' + @name + '.dbo.Usage(
[UsageID] [int] NOT NULL,
[BatchID] [int] NOT NULL,
[RecordNumber] [int] NOT NULL,
[InstanceNumber] [tinyint] NOT NULL,
[ProductID] [int] NOT NULL,
[UsageTypeID] [smallint] NOT NULL,
[CustID] [int] NOT NULL,
[UsgSvcID] [int] NULL,
[ProcessingFlags] [int] NULL,
[CallStartTime] [datetime] NOT NULL,
[ServiceNumber] [varchar](20) NOT NULL,
[AcctCode] [varchar](10) NULL,
[Duration] [int] NOT NULL,
[ProgramID] [smallint] NOT NULL,
[DistanceID] [tinyint] NULL,
[Cost] [money] NULL,
[Charge] [money] NULL,
[OrigPlace] [varchar](16) NULL,
[OrigLata] [smallint] NULL,
[OrigNPA] [smallint] NULL,
[OrigState] [varchar](2) NULL,
[OrigCountryCode] [smallint] NULL,
[OrigNumber] [varchar](16) NULL,
[OrigOCN] [varchar](4) NULL,
[TermPlace] [varchar](16) NULL,
[TermLata] [smallint] NULL,
[TermNPA] [smallint] NULL,
[TermState] [varchar](2) NULL,
[TermCountryCode] [smallint] NULL,
[TermNumber] [varchar](16) NULL,
[TermOCN] [varchar](4) NULL,
[RatePeriod] [varchar](1) NULL,
[ActualDuration] [int] NOT NULL,
[RatePlanDetailID] [int] NULL,
[Text1] [varchar](50) NULL,
[OrigNetworkElementID] [varchar](5) NULL,
[TermNetworkElementID] [varchar](5) NULL,
[PreDiscountDuration] [int] NULL,
[PreDiscountCharge] [money] NULL,
[Surcharge] [money] NULL
) ON [PRIMARY]'

print (@sql)
---exec(@sql)


I keep getting the message:

Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near ','.


Can anyone tell me why?

Thanks!

Laura

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 10:00:32
Increase the length of your @sql variable
declare
@sql varchar(MAX), @name varchar(100);
....
Go to Top of Page

lmayer4
Starting Member

33 Posts

Posted - 2013-10-01 : 10:03:25
Thanks James, I tried max before and it didnt work but I did increase it to 5000 and it worked great. Thanks again for the help.

Laura
Go to Top of Page
   

- Advertisement -