September 29, 2008 at 3:36 pm
This is the code I’m working on. It was creating a table with the same name and bombing. I changed the CREATE TABLE code to be dynamic and randomize the table names, but that statement keeps bombing. I’ve tried
1. ‘CREATE TABLE ‘ + @table and 'DECLARE ' + @table + ' TABLE
Either way I get an error re the datetime fields:
This is one: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near 'DATE'.
But I’ve also gotten errors re DATETI and TIMEST (when I tried TIMESTAMP on the DATETIME) field types.
Everything I’ve Googled isn’t what I need. Or my books. Any thoughts would be appreciated.
--This first part is just randomizing the table name
DECLARE @random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @random
declare @sql Varchar(1000), @table Varchar(100)
select @table = '@TRANSHIST'+(convert(nvarchar(30),
@random+10000))
select @table
set @sql =
'DECLARE ' + @table + ' TABLE
( RowNum INT IDENTITY NOT NULL
,Company int NOT NULL
,Fiscal_year int NOT NULL
,Acct_unit nvarchar(15) NOT NULL
,Account int NOT NULL
,sub_account int NOT NULL
,posting_date nvarchar(10) NOT NULL
,update_date nvarchar(10) NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(24,2) NOT NULL
,TotBegCredits decimal(24,2) NOT NULL
,TMONTH int NOT NULL
,account_desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(50) NOT NULL
,debits decimal (18,2) NOT NULL
,credits decimal (18,2) NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME NOT NULL
,JE int NOT NULL)'
exec (@sql)
September 29, 2008 at 4:05 pm
Based on the errors, it looks like @sql isn't big enough to hold all the text. It looks like it should be, but what do you get if you do
select @sql
just before the exec?
September 29, 2008 at 10:50 pm
David Webb (9/29/2008)
Based on the errors, it looks like @sql isn't big enough to hold all the text. It looks like it should be, but what do you get if you doselect @sql
just before the exec?
As rightly pointed by David,change the @sql variable's length to 4000 and check.It will work.As your dynamic query is built in a single statement,it takes up too much blank spaces.Hence the @sql is not able to accomodate the last line : ",JE int NOT NULL)".
Your Query Result:
DECLARE @TRANSHIST10210 TABLE ( RowNum INT IDENTITY NOT NULL ,Company int NOT NULL ,Fiscal_year int NOT NULL ,Acct_unit nvarchar(15) NOT NULL ,Account int NOT NULL ,sub_account int NOT NULL ,posting_date nvarchar(10) NOT NULL ,update_date nvarchar(10) NOT NULL ,reference nvarchar(150) NOT NULL ,SystemCode nvarchar(2) NOT NULL ,TotBegDebits decimal(24,2) NOT NULL ,TotBegCredits decimal(24,2) NOT NULL ,TMONTH int NOT NULL ,account_desc nvarchar(150) NOT NULL ,acct_unitname nvarchar(50) NOT NULL ,debits decimal (18,2) NOT NULL ,credits decimal (18,2) NOT NULL ,BegBal MONEY NOT NULL ,RunBal MONEY NOT NULL ,TransDescr nvarchar(150) NOT NULL ,StartDate DATETIME NOT NULL ,EndDate DATETIME NOT NULL
Increase either the size of variable or try separating the whole query into simple parts and concatenating them together.
September 30, 2008 at 7:48 am
Thanks guys!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply