April 16, 2012 at 12:54 pm
In the actual OPENQUERY, you do a select count(*). Add a column alias there.
In the select that uses the openquery, you simply do a select * from openquery(...)). Change the * to the column alias you add in the OPENQUERY, and do this select @YourVariable = TheAS400Count from openquery(...)).
April 16, 2012 at 12:57 pm
Welsh Corgi (4/16/2012)
I'm sorry but I do not follow.I do not have a column Alias in the SQL Select?
This:
SET @SQLQuery = 'SELECT @cnt = COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName
DECLARE @MyCnt INT;
DECLARE @SQLRowCount int
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @SQLQuery, @ParmDefinition, @cnt = @SQLRowCount OUTPUT;
SELECT @SQLRowCount AS SQLRowCount;
SET @AS400TableName = 'BUR_TYPE'
SET@OpenQueryPrefix = 'SELECT @cnt = AS400Count FROM OPENQUERY(LSCTLR40DAT, ''SELECT COUNT(*) AS400Count FROM '
SET@OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @AS400TableName + @OpenQuerySuffix
DECLARE @MyCnt INT;
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @MyCnt OUTPUT;
SELECT @MyCnt AS MyCount;
April 16, 2012 at 1:11 pm
I changed my code slightly so I can remove when I convert it to a Stored Procedure but I get the same results:
SQLRowCount
5
00001
5
MyCount
NULL
SET @SQLQuery = 'SELECT @cnt = COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName
DECLARE @MyCnt INT;
DECLARE @SQLRowCount int
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @SQLQuery, @ParmDefinition, @cnt = @SQLRowCount OUTPUT;
SELECT @SQLRowCount AS SQLRowCount;
SET @AS400TableName = 'BUR_TYPE'
SET @AS400SchemaName = 'CYP'
SET @LinkedServerPre = 'LS'
SET @LinkedServerPost = 'R40DAT'
SET@OpenQueryPrefix = 'SELECT * FROM OPENQUERY('
SET@OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre + @AS400SchemaName + @LinkedServerPost
SET@OpenQueryPrefix = @OpenQueryPrefix + ', ''SELECT COUNT(*) FROM '
SET@OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @AS400TableName + @OpenQuerySuffix
--DECLARE @MyCnt INT;
--DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @MyCnt OUTPUT;
SELECT @MyCnt AS MyCount;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 16, 2012 at 1:15 pm
The select count(*) in the openquery still has no alias and you are still doing a select * from the openquery and not assigning anything to the variable that ultimately needs to be returned from the exec sp_executesql for getting your count from the AS400 table.
April 16, 2012 at 1:20 pm
Lynn Pettis (4/16/2012)
The select count(*) in the openquery still has no alias and you are still doing a select * from the openquery and not assigning anything to the variable that ultimately needs to be returned from the exec sp_executesql for getting your count from the AS400 table.
I'm sorry but I do not see what line of code that you are referring to and how it is different from your previous post?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 16, 2012 at 1:23 pm
Welsh Corgi (4/16/2012)
Lynn Pettis (4/16/2012)
The select count(*) in the openquery still has no alias and you are still doing a select * from the openquery and not assigning anything to the variable that ultimately needs to be returned from the exec sp_executesql for getting your count from the AS400 table.I'm sorry but I do not see what line of code that you are referring to and how it is different from your previous post?
Here:
SET @SQLQuery = 'SELECT @cnt = COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName
DECLARE @MyCnt INT;
DECLARE @SQLRowCount int
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @SQLQuery, @ParmDefinition, @cnt = @SQLRowCount OUTPUT;
SELECT @SQLRowCount AS SQLRowCount;
SET @AS400TableName = 'BUR_TYPE'
SET @AS400SchemaName = 'CYP'
SET @LinkedServerPre = 'LS'
SET @LinkedServerPost = 'R40DAT'
--SET@OpenQueryPrefix = 'SELECT * FROM OPENQUERY('
SET@OpenQueryPrefix = 'SELECT @cnt = AS400TableCount FROM OPENQUERY(' -- << here
SET@OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre + @AS400SchemaName + @LinkedServerPost
--SET@OpenQueryPrefix = @OpenQueryPrefix + ', ''SELECT COUNT(*) FROM '
SET@OpenQueryPrefix = @OpenQueryPrefix + ', ''SELECT COUNT(*) AS400TableCount FROM ' -- << and here
SET@OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @AS400TableName + @OpenQuerySuffix
--DECLARE @MyCnt INT;
--DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @MyCnt OUTPUT;
SELECT @MyCnt AS MyCount;
April 16, 2012 at 1:30 pm
I reverted to you version of the code and it works.
What Line are you referring to because I do see an Table Alias?
I use them all of the time.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 16, 2012 at 1:34 pm
Looking at this line:
SET@OpenQueryPrefix = @OpenQueryPrefix + ', ''SELECT COUNT(*) AS400TableCount FROM ' -- << and here
AS400TableCount is a column alias, I am naming the column that contains the value count(*) to be AS400TableCount.
In the select query where I select from the OPENQUERY data source (a virtual table) I then select the AS400TableCount and assign it to a variable so that I can output it from sp_executesql.
April 16, 2012 at 1:39 pm
Lynn Pettis (4/16/2012)
Looking at this line:SET@OpenQueryPrefix = @OpenQueryPrefix + ', ''SELECT COUNT(*) AS400TableCount FROM ' -- << and here
AS400TableCount is a column alias, I am naming the column that contains the value count(*) to be AS400TableCount.
In the select query where I select from the OPENQUERY data source (a virtual table) I then select the AS400TableCount and assign it to a variable so that I can output it from sp_executesql.
omg, dumb.
No excuse but I did not sleep very well, last night.:blush:
Thank you for your help and patience.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply