August 7, 2012 at 9:14 am
Ok, so I am pretty much through my script. I am getting 2 errors througout the script and of course I dont know why. I think its because I have an the code before so I need a truncate table or alter table code..but if you guys can lead me in to the right direction that would be fantastic. Thank you!
The errors I am getting are from tbPlanStartEnd and tbPlanGroup. It says There is already an object named 'tbPlanStartEnd' in the database and There is already an object named 'tbPlanGroup' in the database
BEGIN TRY
DECLARE
@ClientID INT
, @ApplicationID INT
, @nsql NVARCHAR(max)
, @SourceDatabaseName VARCHAR(100)
, @StageDatabaseName VARCHAR(100)
, @MessageText VARCHAR(1000)
, @OverallStartDateTime DATETIME = GETDATE()
, @StartDateTime DATETIME = GETDATE()
, @ElapsedMilisecond BIGINT = 0
, @ErrorCount INT=0
, @RowCount INT
DECLARE @Messages TABLE -- used to collect messages along the way
(
MessageId INT IDENTITY(1,1)
, MessageText VARCHAR(1000)
, isError BIT DEFAULT(1)
, ElapsedMilisecond BIGINT DEFAULT(0)
)
SELECT
@ClientId=ClientId
FROM dbo.Client
WHERE ClientName=@ClientName
SELECT
@ApplicationId=a.ApplicationId
, @SourceDatabaseName=(SELECT DatabaseName FROM dbo.DatabaseLocation WHERE DatabaseID=ca.SourceDatabaseID)
, @StageDatabaseName=(SELECT DatabaseName FROM dbo.DatabaseLocation WHERE DatabaseID=ca.TargetDatabaseID)
FROM
dbo.Client c
INNER JOIN dbo.ClientApplication ca
ON c.ClientId=ca.ClientId
AND c.ClientName=@ClientName
INNER JOIN dbo.Application a
ON ca.ApplicationId=a.ApplicationId
AND a.ApplicationName =@ApplicationName
IF @ClientID IS NULL
BEGIN
SET @MessageText=OBJECT_NAME(@@procid)+': Client Name: '+@ClientName+' is invalid'
INSERT INTO @Messages
(MessageText)
VALUES
(@MessageText)
RAISERROR
( @MessageText
, 11 -- severity higher than 10 will exit the TRY block imediately, to prevent other false errors from being reported
, 1
)
END
IF @ApplicationId IS NULL
BEGIN
SET @MessageText=OBJECT_NAME(@@procid)+' Application Name: '+@ApplicationName+' is invalid for Client Name: '+@ClientName
INSERT INTO @Messages
(MessageText)
VALUES
(@MessageText)
RAISERROR
( @MessageText
, 11 -- severity higher than 10 will exit the TRY block imediately, to prevent other false errors from being reported
, 1
)
END
SET @MessageText='START: '+OBJECT_NAME(@@procid)+': for Client: '+ @ClientName + ' Application: ' +@ApplicationName
SET @ElapsedMilisecond = DATEDIFF(MS,@StartDateTime,GETDATE())
INSERT INTO @Messages
(MessageText,isError)
VALUES
(@MessageText,0)
-- *******************************************************************
-- Does the SOURCE database exist?
-- *******************************************************************
-- if the SOURCE database is not defined
IF @SourceDatabaseName IS NULL
INSERT INTO @Messages
(MessageText)
VALUES
('No SOURCE database is defone for ClientName:'+ @ClientName + ' ApplicationName: ' +@ApplicationName)
-- if the SOURCE database does not exist
IF NOT EXISTS (SELECT Name FROM SYS.DATABASES WHERE Name = @SourceDatabaseName )
INSERT INTO @Messages
(MessageText)
VALUES
('SOURCE database: '+@SourceDatabaseName+' does not exist')
-- *******************************************************************
-- Does the STAGE database exist?
-- *******************************************************************
-- if the STAGE database is not defined
IF @StageDatabaseName IS NULL
INSERT INTO @Messages
(MessageText)
VALUES
('No STAGE database is defone for ClientName:'+ @ClientName + ' ApplicationName: ' +@ApplicationName)
-- if the STAGE database does not exist
IF NOT EXISTS (SELECT Name FROM SYS.DATABASES WHERE Name = @StageDatabaseName )
INSERT INTO @Messages
(MessageText)
VALUES
('STAGE database: '+@StageDatabaseName+' does not exist')
-- *******************************************************************
-- Do all staged tables exist in the source and in the stage database?
-- *******************************************************************
-- list stage tables not in the SOURCE database
SET @nsql = '
SELECT
TableName +'+'''is missing from the @SourceDatabaseName SOURCE database''
FROM
(
select
ct.TableName
from
dbo.Application a
INNER JOIN dbo.CustomTables ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.Application a
INNER JOIN dbo.StandardTables st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTableList
LEFT JOIN
(
SELECT
Table_name
FROM '+@SourceDatabaseName+'.INFORMATION_SCHEMA.tables
) SOURCE
ON StageTableList.tableName=SOURCE.Table_name
WHERE SOURCE.Table_name IS NULL'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'Completed Step: 1'
-- list stage tables not in the STAGE database
SET @nsql = '
SELECT
TableName +'+'''is missing from the @SourceDatabaseName STAGE database''
FROM
(
select
ct.TableName
from
dbo.Application a
INNER JOIN dbo.CustomTables ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.Application a
INNER JOIN dbo.StandardTables st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTableList
LEFT JOIN
(
SELECT
Table_name
FROM '+@StageDatabaseName+'.INFORMATION_SCHEMA.tables
) STAGE
ON StageTableList.tableName=STAGE.Table_name
WHERE STAGE.Table_name IS NULL'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'Completed Step:2'
-- ********************************************************************
-- Do all staged columns exist in the source and in the stage database?
--*********************************************************************
-- list stage columns not in the STAGE database
SET @nsql = '
SELECT
ColumnName +'+'''is missing from the '+@SourceDatabaseName+' Stage Database''
FROM
(
select
ct.TableName,ctc.ColumnName
from
dbo.Application a
INNER JOIN dbo.CustomTables ct
ON a.ApplicationId=ct.ApplicationId
INNER JOIN dbo.Client c
ON ct.ClientId=c.ClientId
INNER JOIN dbo.CustomTableColumns ctc
ON ct.CustomTableId=ctc.CustomTableId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,stc.ColumnName
from
dbo.Application a
INNER JOIN dbo.StandardTables st
ON a.ApplicationId=st.ApplicationId
INNER JOIN dbo.StandardTableColumns stc
ON st.StandardTableID=stc.StandardTableID
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM '+@StageDatabaseName+'.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'Completed Step:3'
-- list stage columns not in the source database--
SET @nsql = '
SELECT
ColumnName +' +'''is missing fromt the @SourceDatabaseName Stage Database''
FROM
(
select
ct.TableName,ctc.ColumnName
from
dbo.Application a
INNER JOIN dbo.CustomTables ct
ON a.ApplicationId=ct.ApplicationId
INNER JOIN dbo.Client c
ON ct.ClientId=c.ClientId
INNER JOIN dbo.CustomTableColumns ctc
ON ct.CustomTableId=ctc.CustomTableId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,stc.ColumnName
from
dbo.Application a
INNER JOIN dbo.StandardTables st
ON a.ApplicationId=st.ApplicationId
INNER JOIN dbo.StandardTableColumns stc
ON st.StandardTableID=stc.StandardTableID
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM '+@SourceDatabaseName+'.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'Completed step:4'
-- *******************************************************************
--Is the data type of all staged columns the same in the source and stage database?
-- *******************************************************************
SET @nsql = '
SELECT
''Data_Type of Column''+ ColumnName + ''Of the Table'' + TableName + ''is not same as its SOURCE''
FROM
(
SELECT
ct.TableName,ctc.ColumnName
FROM
dbo.Application a
JOIN CustomTables ct
on a.ApplicationID = ct.ApplicationID
AND a.ApplicationName=@ApplicationName
INNER JOIN dbo.CustomTableColumns ctc
ON ct.CustomTableID = ctc.CustomTableID
UNION
SELECT
st.TableName,stc.ColumnName
FROM
dbo.Application a
JOIN StandardTables st
on a.ApplicationID = st.ApplicationID
AND a.ApplicationName=@ApplicationName
AND st.ActiveInd = '' ''
INNER JOIN dbo.StandardTableColumns stc
ON st.StandardTableID = stc.StandardTableID
) StageColumns
JOIN CD_Stage_NPHP.INFORMATION_SCHEMA.columns inf_col
on StageColumns.TableName = inf_col.TABLE_NAME
AND StageColumns.ColumnName = inf_col.COLUMN_NAME
WHERE inf_col.Column_Name IS NULL ' --AND
-- ----StageColumns.DataType <> inf_col.Data_Type'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'completed step:5'
-- *******************************************************************
-- Does the tbPlanStartEnd table exist in the source database
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @SourceDatabaseName AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanStartEnd' )
INSERT INTO @Messages
(MessageText)
VALUES
('the table tbPlanStartEnd does not exist in Source Database')
-- *******************************************************************
-- Is the tbPlanStartEnd table populated in the source database
ELSE
BEGIN
SELECT @RowCount = COUNT(*) FROM tbPlanStartEnd;
IF @RowCount = 0
INSERT INTO @Messages
(MessageText)
VALUES
('the table tbPlanStartEnd is not Populated with data in Source Database')
END
print 'completed step:6'
-- *******************************************************************
-- Does the tbPlanGroup table exist in the source database
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @SourceDatabaseName AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' )
INSERT INTO @Messages
(MessageText)
VALUES
('the table tbPlanGroup does not exist in Source Database')
-- *******************************************************************
-- Is the grp_name column in the tbPlanGroup table in the source database
ELSE IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @SourceDatabaseName AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'grp_name' )
INSERT INTO @Messages
(MessageText)
VALUES
('the column grp_name of the table tbPlanGroup of Source Database does not exist')
-- *******************************************************************
-- Is the tbPlanGroup table populated in the source database
ELSE
BEGIN
SELECT @RowCount = COUNT(*) FROM tbPlanGroup;
IF @RowCount = 0
INSERT INTO @Messages
(MessageText)
VALUES
('the table tbPlanGroup is not Populated with data in Source Database')
END
print 'completed step:7'
-- *******************************************************************
-- automatically handle problems if you can
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @SourceDatabaseName AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanStartEnd' )
BEGIN
-- *******************************************************************
-- Does the tbPlanStartEnd table exist in the source database, if not, CREATE it.
-- *******************************************************************
CREATE TABLE tbPlanStartEnd
(
TABLE_CATALOG NVARCHAR(128),
TABLE_SCHEMA NVARCHAR(128),
TABLE_NAME NVARCHAR (128),
COLUMN_NAME NVARCHAR(128),
ORDINAL_POSITION INT,
COLUMN_DEFAULT NVARCHAR(MAX),
IS_NULLABLE VARCHAR(3),
DATA_TYPE NVARCHAR(128),
CHARACTER_MAXIMUM_LENGTH INT,
CHARACTER_OCTET_LENGTH INT,
NUMERIC_PRECISION TINYINT,
RADIX_NUMERIC_SCALE SMALLINT,
DATETIME_PRECISION SMALLINT,
CHARACTER_SET_CATALOG NVARCHAR(128),
CHARACTER_SET_SCHEMA NVARCHAR(128),
CHARACTER_SET_NAME NVARCHAR(128),
COLLATION_CATALOG NVARCHAR(128),
COLLATION_SCHEMA NVARCHAR(128),
COLLATION_NAME NVARCHAR(128),
DOMAIN_CATALOG NVARCHAR(128),
DOMAIN_SCHEMA NVARCHAR(128),
DOMAIN_NAME NVARCHAR(128),
);
END
print 'completed step:8'
-- *******************************************************************
-- Does the tbPlanGroup table exist in the source database, if not, create it.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @SourceDatabaseName AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' )
BEGIN
CREATE TABLE tbPlanGroup
(
TABLE_CATALOG NVARCHAR(128),
TABLE_SCHEMA NVARCHAR(128),
TABLE_NAME NVARCHAR (128),
COLUMN_NAME NVARCHAR(128),
ORDINAL_POSITION INT,
COLUMN_DEFAULT NVARCHAR(MAX),
IS_NULLABLE VARCHAR(3),
DATA_TYPE NVARCHAR(128),
CHARACTER_MAXIMUM_LENGTH INT,
CHARACTER_OCTET_LENGTH INT,
NUMERIC_PRECISION TINYINT,
RADIX_NUMERIC_SCALE SMALLINT,
DATETIME_PRECISION SMALLINT,
CHARACTER_SET_CATALOG NVARCHAR(128),
CHARACTER_SET_SCHEMA NVARCHAR(128),
CHARACTER_SET_NAME NVARCHAR(128),
COLLATION_CATALOG NVARCHAR(128),
COLLATION_SCHEMA NVARCHAR(128),
COLLATION_NAME NVARCHAR(128),
DOMAIN_CATALOG NVARCHAR(128),
DOMAIN_SCHEMA NVARCHAR(128),
DOMAIN_NAME NVARCHAR(128),
);
END
print 'completed step:9'
-- *******************************************************************
-- Is the grp_name column in the tbPlanGroup table in the source database, if not, add it.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @SourceDatabaseName AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'grp_name' )
ALTER TABLE tbPlanGroup
ADD Group_Name VARCHAR(128);
-- Record the elapsed time of the procedure
-- *******************************************************************
SET @MessageText='END: '+OBJECT_NAME(@@procid)+': for Client: '+ @ClientName + ' Application: ' +@ApplicationName
SET @ElapsedMilisecond = DATEDIFF(MS,@OverallStartDateTime, GETDATE())
INSERT INTO @Messages
(MessageText, isError, ElapsedMilisecond)
VALUES
(@MessageText,0, @ElapsedMilisecond)
print 'completed step:10'
-- *******************************************************************
-- Store the messages in the LoadLog table
-- *******************************************************************
INSERT INTO dbo.LoadLog
(
ClientID
, ApplicationID
, TableName
, [Message]
, IsError
, LoadLogDate
, ElapasedMilisecond
)
SELECT
@ClientID
, @ApplicationID
, NULL --@TableName
, MessageText
, IsError
, GETDATE()
, ElapsedMilisecond
FROM @Messages
-- *******************************************************************
-- If there were any errors RAISERROR
-- *******************************************************************
SELECT @ErrorCount=COUNT(*) FROM @Messages WHERE isError=1
IF @ErrorCount>0
BEGIN
SET @MessageText='There were '+CAST(@ErrorCount as VARCHAR)+' errors in the pre-stage validation'
RAISERROR
(
@MessageText
, 11 -- severity higher than 10 will exit the TRY block imediately
, 1
);
END
print 'completed step:11'
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage NVARCHAR(4000)
, @ErrorSeverity INT
, @ErrorState INT
, @BriefMsg NVARCHAR (800)
SELECT
@ErrorMessage = ERROR_MESSAGE()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
, @BriefMsg = CAST (ERROR_MESSAGE() AS NVARCHAR (500))
SET @MessageText='ABORTED: '+OBJECT_NAME(@@procid)+': '+ @BriefMsg
EXEC dbo.usp_LoadLog_Insert @Message=@MessageText, @IsError=1
RAISERROR
(
@ErrorMessage
, @ErrorSeverity
, @ErrorState
)
END CATCH
print 'completed: final step'
August 7, 2012 at 9:26 am
morepainot (8/7/2012)
Ok, so I am pretty much through my script. I am getting 2 errors througout the script and of course I dont know why. I think its because I have an the code before so I need a truncate table or alter table code..but if you guys can lead me in to the right direction that would be fantastic. Thank you!The errors I am getting are from tbPlanStartEnd and tbPlanGroup. It says There is already an object named 'tbPlanStartEnd' in the database and There is already an object named 'tbPlanGroup' in the database
I assume from the following you must be looking for this table in a different database? This DMV does not include the whole server, only the current database. Your check is not looking in the source database. It is looking in the whatever the current database. Also your create table statement is creating the table in the current database. I think you want to be creating it in the @SourceDatabaseName?? It seems this same logic flaw is consistent with both your error messages. You should look VERY carefully through this thing because you are trying to execute ddl statements across more than 1 database. This requires some careful planning and frequently dynamic sql.
-- automatically handle problems if you can
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @SourceDatabaseName AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanStartEnd' )
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2012 at 9:41 am
Sean Lange (8/7/2012)
morepainot (8/7/2012)
Ok, so I am pretty much through my script. I am getting 2 errors througout the script and of course I dont know why. I think its because I have an the code before so I need a truncate table or alter table code..but if you guys can lead me in to the right direction that would be fantastic. Thank you!The errors I am getting are from tbPlanStartEnd and tbPlanGroup. It says There is already an object named 'tbPlanStartEnd' in the database and There is already an object named 'tbPlanGroup' in the database
I assume from the following you must be looking for this table in a different database? This DMV does not include the whole server, only the current database. Your check is not looking in the source database. It is looking in the whatever the current database. Also your create table statement is creating the table in the current database. I think you want to be creating it in the @SourceDatabaseName?? It seems this same logic flaw is consistent with both your error messages. You should look VERY carefully through this thing because you are trying to execute ddl statements across more than 1 database. This requires some careful planning and frequently dynamic sql.
-- automatically handle problems if you can
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @SourceDatabaseName AND
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanStartEnd' )
Yes, I am suppose to create the table in @sourcedatabasename. I think I figured out where I was making the mistake. Im in a development server so I can pretty much run this code as many as times as possible across many different dbs without worry.
Thank you, Sean
August 7, 2012 at 12:46 pm
manub22 (7/26/2012)
Lot of problems can be easily done by SET based approach rather than using CURSORS. People not expert in writing SQL queries and/or not aware of new features end up using CURSORS.
Which is kind of funny. It took me a long time to find the syntax for a cursor back when I started. And I was only using them because the concept of using UPDATE on a JOIN was something I didn't think you could do by SQL syntax, and so like a fool I wrote a cursor to do a bunch of updates.
Later on, we hired a pretty good SQL developer who wrote this awesome stored proc that calculated AR balances at any point in time. The stored proc was long and tediuous to read, but in it I saw UPDATEs on JOINs, and went back and started eliminating every single cursor in my code.
Since then (almost 10 years ago) I've just flat hated cursors... Some days my boss tries to use them.... and I have to bite my tongue and hold back from slapping his wrist. 🙂
Cursors, While Loops, etc... just bad thinking with a SET based declarative language like SQL. Stop telling the cabbie how to get to the airport, let him find the way, he lives in timbuktu, you don't. 🙂
August 7, 2012 at 12:48 pm
Jeff Moden (7/26/2012)
Sean Lange (7/26/2012)
@Jeff - You need start the official anti-RBAR alliance and offer membership to those who have proven to be members. We could have a secret handshake and membership cards that offer nothing.How about membership cards that actually DO mean something... especially to future employers. 😉
I heartily agree... 🙂
August 7, 2012 at 12:59 pm
Lynn Pettis (8/1/2012)
morepainot (8/1/2012)
Ok, I took care of those errors. Now the only errors I am getting are"Msg 2714, Level 16, State 3, Procedure usp_PreStageValidation, Line 185
There is already an object named 'usp_PreStageValidation' in the database."
Im guessing thats because I am running the code?
No, it means the procedure alread exists and you are trying to create it again. Two things you can do. One, change the CREATE PROCEDURE to ALTER PROCEDURE. Two, add a DROP PROCEDURE before the CREATE PROCEDURE.
And if you add the DROP PROCEDURE test first to see if the PROCEDURe exists....
Something like
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchRecords]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SearchRecords]
That way you don't get errors trying to drop the procs that don't exist.
August 7, 2012 at 1:14 pm
Ok, next time I will remind myself to read the dates on messages in here... at least one response to something that was already handled last month. Oops 🙂
August 8, 2012 at 2:26 pm
What am I missing in this dynamic sql?
BEGIN
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
EXEC sp_executeSQL @nSQL;
END
PRINT @nSQL
PRINT 'completed step: 11'
August 8, 2012 at 2:29 pm
morepainot (8/8/2012)
What am I missing in this dynamic sql?
BEGINSET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
EXEC sp_executeSQL @nSQL;
END
PRINT @nSQL
PRINT 'completed step: 11'
Squirrel noises? Honestly how would we know what you are missing? Are you getting an error? If so, what is it? You need to provide a little context.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2012 at 2:31 pm
Oh man...I meant to add in my error message :doze:
Incorrect syntax near '('
August 8, 2012 at 2:33 pm
morepainot (8/8/2012)
Oh man...I meant to add in my error message :doze:Incorrect syntax near '('
No offense but that doesn't exactly help. Try adding a print command before you execute your dynamic sql. My guess is there is something wrong there.
print @nSQL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2012 at 2:39 pm
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.
August 8, 2012 at 2:44 pm
morepainot (8/8/2012)
What am I missing in this dynamic sql?
BEGINSET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
EXEC sp_executeSQL @nSQL;
END
PRINT @nSQL
PRINT 'completed step: 11'
I think the paraenthesis around the add part of your alter table statement are causing problems. Get rid of them.
August 8, 2012 at 2:50 pm
Dont I need to use parenthesis to open and close the alter table statement? If I delete it then ill get an error near VARCHAR
August 8, 2012 at 2:56 pm
morepainot (8/8/2012)
I have a print command at ever end. When I test the code its kicking back to this part of the code saying incorrrect syntax near '('. I dont know where im missing what.
Try printing it BEFORE you execute it so you can see what it is. I think that once you fix the parenthesis issue you will find another issue in your dynamic code too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 91 through 105 (of 137 total)
You must be logged in to reply to this topic. Login to reply