August 2, 2012 at 12:11 pm
Why am I getting an invalid object name error here for CustomColumns?
SET @nsql = '
SELECT
ColumnName + ''Column is missing from the Table' + 'TableName'+' of '+@StageDatabaseName+' STAGE database''
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumns ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumns st
ON a.ApplicationId=st.ApplicationId
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'
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
August 2, 2012 at 12:15 pm
Why am I getting an invalid object name error here for CustomColumns?
Because it doesn't exist? 😛
It is tough to figure that out from here. Does the table actually exist? You specified the schema as dbo, it is possible that table is in another schema? Another database? Another server?
_______________________________________________________________
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 2, 2012 at 12:22 pm
Sean Lange (8/2/2012)
Why am I getting an invalid object name error here for CustomColumns?
Because it doesn't exist? 😛
It is tough to figure that out from here. Does the table actually exist? You specified the schema as dbo, it is possible that table is in another schema? Another database? Another server?
My mistake, here is the entire code. (You might be blown away from the progress I made)
USE [BICentral]
GO
/****** Object: StoredProcedure [dbo].[usp_PreStageValidation_AC] Script Date: 08/01/2012 18:08:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
TEST usp_PreStageValidation_AC using this SQL
DECLARE
@StartDate DATETIME = GETDATE()
EXEC dbo.usp_PreStageValidation_AC
@ClientName ='ACHP'
,@ApplicationName ='CD'
EXEC usp_LoadLog_Select @StartDate
select c.ClientName,a.ApplicationName,ca.* from clientApplication ca
join client c
on ca.ClientID = c.ClientID
join application a
on ca.applicationid = a.applicationID
*/
ALTER PROCEDURE [dbo].[usp_PreStageValidation_AC]
(
@ClientName VARCHAR(100)
,@ApplicationName VARCHAR(100)
)
/* ====================================================================================================
** Date Created: 2012-08-01
** Created By:
** Purpose: To list the potential staging problems before the staging process begins
and where possible, automatically correct potential problems.
** Modify_dateModified_byWork item idDescription
** -------------------------------------------------------------------------------------------------
**
======================================================================================================*/
AS
SET NOCOUNT ON --this prevents record counts from being printed to the console
SET XACT_ABORT ON --if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back
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'
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- list stage tables not in the STAGE database
SET @nsql = '
SELECT
TableName+'' is missing from the '+@StageDatabaseName+' 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'
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- ********************************************************************
-- 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 + ''Column is missing from the Table' + 'TableName'+' of '+@StageDatabaseName+' STAGE database''
FROM
(
select
ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumns ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumns st
ON a.ApplicationId=st.ApplicationId
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'
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- list stage columns not in the source database--
SET @nsql = '
SELECT
ColumnName + ''Column is missing from the Table' + 'TableName'+' of '+@StageDatabaseName+' STAGE database''
FROM
(
select
ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumns ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumns st
ON a.ApplicationId=st.ApplicationId
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'
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- *******************************************************************
-- *******************************************************************
-- Is the data type of all staged columns the same in the source and stage database?
SET @nsql = '
SELECT
DataType of Column + ColumnName + '' of the Table ' + 'TableName+'' is not same as its SOURCE''
FROM
(
select
ct.TableName,ct.ColumnName, ct.DataType
from
dbo.[Application] a
INNER JOIN dbo.CustomColumns ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName, st.DataType
from
dbo.[Application] a
INNER JOIN dbo.StandardColumns st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name, Data_Type
FROM '+@SourceDatabaseName+'.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL AND
StageColumns.DataType <> CD.Data_Type'
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- *******************************************************************
-- 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
-- *******************************************************************
-- 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
-- *******************************************************************
-- 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
-- *******************************************************************
-- 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
-- *******************************************************************
-- 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)
-- *******************************************************************
-- 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
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
August 2, 2012 at 12:46 pm
Well the code certainly looks a lot better. But it doesn't help me identify why you are getting an invalid object name. I can't see your screen from here and I don't know what tables and such are in your databases. The code itself isn't going to help me or anybody else figure it out.
Take a look at my first response and answer those questions. You will find the reason somewhere in there.
_______________________________________________________________
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 2, 2012 at 12:51 pm
Sean Lange (8/2/2012)
Well the code certainly looks a lot better. But it doesn't help me identify why you are getting an invalid object name. I can't see your screen from here and I don't know what tables and such are in your databases. The code itself isn't going to help me or anybody else figure it out.Take a look at my first response and answer those questions. You will find the reason somewhere in there.
I think its supposed to be dbo.CustomTableColumns
August 2, 2012 at 12:58 pm
morepainot (8/2/2012)
Sean Lange (8/2/2012)
Well the code certainly looks a lot better. But it doesn't help me identify why you are getting an invalid object name. I can't see your screen from here and I don't know what tables and such are in your databases. The code itself isn't going to help me or anybody else figure it out.Take a look at my first response and answer those questions. You will find the reason somewhere in there.
I think its supposed to be dbo.CustomTableColumns
That would explain it. 😉
_______________________________________________________________
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 2, 2012 at 1:06 pm
Sean Lange (8/2/2012)
morepainot (8/2/2012)
Sean Lange (8/2/2012)
Well the code certainly looks a lot better. But it doesn't help me identify why you are getting an invalid object name. I can't see your screen from here and I don't know what tables and such are in your databases. The code itself isn't going to help me or anybody else figure it out.Take a look at my first response and answer those questions. You will find the reason somewhere in there.
I think its supposed to be dbo.CustomTableColumns
That would explain it. 😉
Ok that one is fixed. What a multi-identifier error come from? Im getting in my dynamic sql script for ApplicationID
August 2, 2012 at 1:17 pm
morepainot (8/2/2012)
Sean Lange (8/2/2012)
morepainot (8/2/2012)
Sean Lange (8/2/2012)
Well the code certainly looks a lot better. But it doesn't help me identify why you are getting an invalid object name. I can't see your screen from here and I don't know what tables and such are in your databases. The code itself isn't going to help me or anybody else figure it out.Take a look at my first response and answer those questions. You will find the reason somewhere in there.
I think its supposed to be dbo.CustomTableColumns
That would explain it. 😉
Ok that one is fixed. What a multi-identifier error come from? Im getting in my dynamic sql script for ApplicationID
That means that some object where you used multi-part identification ([schema].[ObjectName]) is incorrect. This is another one that you have to figure out. Something in your dynamic sql is identifying something incorrectly.
_______________________________________________________________
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 2, 2012 at 3:42 pm
Sean Lange (8/2/2012)
morepainot (8/2/2012)
Sean Lange (8/2/2012)
morepainot (8/2/2012)
Sean Lange (8/2/2012)
Well the code certainly looks a lot better. But it doesn't help me identify why you are getting an invalid object name. I can't see your screen from here and I don't know what tables and such are in your databases. The code itself isn't going to help me or anybody else figure it out.Take a look at my first response and answer those questions. You will find the reason somewhere in there.
I think its supposed to be dbo.CustomTableColumns
That would explain it. 😉
Ok that one is fixed. What a multi-identifier error come from? Im getting in my dynamic sql script for ApplicationID
That means that some object where you used multi-part identification ([schema].[ObjectName]) is incorrect. This is another one that you have to figure out. Something in your dynamic sql is identifying something incorrectly.
It could also be a [Table].[Column] relation, or any other [Something].[PartofSomething] as that is also a multipart identifier.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
August 3, 2012 at 9:42 am
Incorrect syntax near the keyword 'of'.
SET @nsql = '
SELECT
DataType of Column + ColumnName + '' of the Table ' + 'TableName+'' is not same as its SOURCE''
DECLARE @ApplicationName varchar(10) = ''
SELECT TargetColumns.*, Data_Type,coalesce(Character_maximum_length,Numeric_Precision) DataLength
FROM
(
SELECT
ct.TableName,ctc.ColumnName
FROM
dbo.Application a
JOIM 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
) TargetColumns
JOIN CD_Stage_NPHP.INFORMATION_SCHEMA.columns inf_col
on TargetColumns.TableName = inf_col.TABLE_NAME
AND TargetColumns.ColumnName = inf_col.COLUMN_NAME
WHERE CD.Column_Name IS NULL AND
StageColumns.DataType <> CD.Data_Type'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'completed step:5'
August 3, 2012 at 9:47 am
morepainot (8/3/2012)
Incorrect syntax near the keyword 'of'.
SET @nsql = 'SELECT
DataType of Column + ColumnName + '' of the Table ' + 'TableName+'' is not same as its SOURCE''
DECLARE @ApplicationName varchar(10) = ''
SELECT TargetColumns.*, Data_Type,coalesce(Character_maximum_length,Numeric_Precision) DataLength
FROM
(
SELECT
ct.TableName,ctc.ColumnName
FROM
dbo.Application a
JOIM 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
) TargetColumns
JOIN CD_Stage_NPHP.INFORMATION_SCHEMA.columns inf_col
on TargetColumns.TableName = inf_col.TABLE_NAME
AND TargetColumns.ColumnName = inf_col.COLUMN_NAME
WHERE CD.Column_Name IS NULL AND
StageColumns.DataType <> CD.Data_Type'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'completed step:5'
Looks to me that you have your single quotes messed up.
August 3, 2012 at 9:49 am
Lynn Pettis (8/3/2012)
morepainot (8/3/2012)
Incorrect syntax near the keyword 'of'.
SET @nsql = 'SELECT
DataType of Column + ColumnName + '' of the Table ' + 'TableName+'' is not same as its SOURCE''
DECLARE @ApplicationName varchar(10) = ''
SELECT TargetColumns.*, Data_Type,coalesce(Character_maximum_length,Numeric_Precision) DataLength
FROM
(
SELECT
ct.TableName,ctc.ColumnName
FROM
dbo.Application a
JOIM 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
) TargetColumns
JOIN CD_Stage_NPHP.INFORMATION_SCHEMA.columns inf_col
on TargetColumns.TableName = inf_col.TABLE_NAME
AND TargetColumns.ColumnName = inf_col.COLUMN_NAME
WHERE CD.Column_Name IS NULL AND
StageColumns.DataType <> CD.Data_Type'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'completed step:5'
Looks to me that you have your single quotes messed up.
I will NEVER understand this single quote, double quote business:angry:
August 3, 2012 at 9:52 am
morepainot (8/3/2012)
Lynn Pettis (8/3/2012)
morepainot (8/3/2012)
Incorrect syntax near the keyword 'of'.
SET @nsql = 'SELECT
DataType of Column + ColumnName + '' of the Table ' + 'TableName+'' is not same as its SOURCE''
DECLARE @ApplicationName varchar(10) = ''
SELECT TargetColumns.*, Data_Type,coalesce(Character_maximum_length,Numeric_Precision) DataLength
FROM
(
SELECT
ct.TableName,ctc.ColumnName
FROM
dbo.Application a
JOIM 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
) TargetColumns
JOIN CD_Stage_NPHP.INFORMATION_SCHEMA.columns inf_col
on TargetColumns.TableName = inf_col.TABLE_NAME
AND TargetColumns.ColumnName = inf_col.COLUMN_NAME
WHERE CD.Column_Name IS NULL AND
StageColumns.DataType <> CD.Data_Type'
print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
print 'completed step:5'
Looks to me that you have your single quotes messed up.
I will NEVER understand this single quote, double quote business:angry:
Writing dynamic sql is not easy. I have found the best way to it, if I really need to use it, is to start by writing it as standard sql, then work it into the format necessary to make it dynamic. And doing a lot of printing of the code along the way to make sure in the end it is what I need and expect.
August 3, 2012 at 9:54 am
I have written (necessary) dynamic sql that actually need '''''' (that is six single quotes) '''''' around pieces of code to get what I wanted. writing dynamic sql is not simple. It is even more difficult when you need to protect yourself for what users may pass to your code.
August 3, 2012 at 11:50 am
Ya, I just used 4 single qutoes and I think Im getting what I need. The databases here weird. I had to inner join other tables in order to get applicationid and applicationname
Viewing 15 posts - 76 through 90 (of 137 total)
You must be logged in to reply to this topic. Login to reply