August 1, 2011 at 3:46 pm
Hi there,
I am trying to collect data from multiple servers/databases by running a specific stored procedure that has number of parameters. One of these parameters has to be null.
I've done simillar collection of information using CURSOR and OPENROWSET like following:
--****************************************************************
--declarations
DECLARE @serverName nvarchar(50), @dbName nvarchar(50)
DECLARE the_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT serverName,dbName
FROM dbo.tServer s
join dbo.tDatabase db on db.dbServerID = s.serverID
WHERE dbActive = 1
--*****************************************************************
--open cursor
OPEN the_cursor
FETCH NEXT FROM the_cursor INTO @serverName,@dbName
WHILE @@FETCH_STATUS = 0
BEGIN
--*****************************************************************
--execute sp
DECLARE @intPar1 int,@intPar2 int
SET @intPar1 = 1111
SET @intPar2 = 1111
EXEC('SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server='+ @serverName +';Uid=me;Pwd=mypassword;Database='+ @dbName + ''',
''
SET NOCOUNT ON;
declare @temp table (RetVal int)
INSERT INTO @temp EXEC stubbornSP '+ @intPar1 +','+ @intPar2 +', ''''7/27/2011'''', ''''7/27/2011''''
SELECT * FROM @temp
''
) AS a;')
END
-******************************************************************
--close cursor
CLOSE aw_db_cursor
DEALLOCATE aw_db_cursor
All of this works fine until when I am trying to pass null as a parameter into stored procedure like this:
EXEC('SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server='+ @serverName +';Uid=me;Pwd=mypassword;Database='+ @dbName + ''',
''
SET NOCOUNT ON;
declare @temp table (RetVal int)
INSERT INTO @temp EXEC stubbornSP null,'+ @intPar2 +', ''''7/27/2011'''', ''''7/27/2011''''
SELECT * FROM @temp
''
) AS a;')
see "EXEC stubbornSP null,..." This is when I get following error:
The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
I've tried already following:
INSERT INTO @temp EXEC stubbornSP @intPar1 = null,@intPart2 = '+ @intPar2 +', @Date1=''''7/27/2011'''', @Date2=''''7/27/2011''''
without success. Also tried @intPar1 = default. No go.
I appreciate any kind of hint or suggestion.
Thanks
August 2, 2011 at 5:46 am
This was removed by the editor as SPAM
August 2, 2011 at 7:06 am
Try to add the following straight after SET NOCOUNT ON line in your dynamic opwnrowset sql:
IF(1 = 0)
BEGIN
SELECT null Col1Name, null Col2Name ...
SET FMTONLY OFF
RETURN
END
You will need to list all columns you expecting to be returned. It may that you will also need to convert null to relevant datatype.
If you curious enough, you can google what IF (1=0) construction is for and how SQL see it 😉
August 3, 2011 at 8:52 am
thanks a lot "stewartc-708166" and spasibo "Eugene Elutin"...
but still no go.
Stored procedure treating somehow differently a NULL value... What do you mean?
The stored procedure has following declarations:
ALTER procedure stubbornSP
@intPar1 int = NULL,
@intPar2 int,
@DateFrom datetime,
@DateTo datetime
and then it just depends if that first parameter is null it would return unfiltered data, if there is some random number in first parameter returning dataset is reduced to whatever filter implies. But I think this is where the problem comes from... I've tried to make the passing parameter null using this function NULLIF(' ', ''). no go. I couldn't get this function work, was giving me syntax error. That same sp works fine directly with or without NULL for that first parameter when executed directly on the server.
I've googled "IF(1 = 0)" 😀 but I don't think I understand much of it as tried a rewrite like this:
EXEC('SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server='+ 'ASPFNDDT12K3' +';Uid=AW;Pwd=AW#;Database='+ 'AW_Iridian' + ''',
''
SET NOCOUNT ON;
IF(1=0)
BEGIN
declare @temp table (RetVal int)
INSERT INTO @temp EXEC stubbornSP null,1111, ''''7/27/2011'''', ''''7/27/2011''''
SELECT * FROM @temp
SET FMTONLY OFF
RETURN
END''
) AS a;')
and it produced same error:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "
SET NOCOUNT ON;
IF(1=0)
BEGIN
declare @temp table (RetVal int)
INSERT INTO @temp EXEC stubbornSP null,1111, '7/27/2011', '7/27/2011'
SELECT * FROM @temp
SET FMTONLY OFF
RETURN
END". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
The thing here is that I need to run sp first as it populates permanent table on that remote server and then I am reading from that table.
Please help 🙁
August 3, 2011 at 9:20 am
This was removed by the editor as SPAM
August 3, 2011 at 9:32 am
It's nothing to do with how NULL values are handled...
And the eeror you get clearly tells you that : "...object has no columns"
It's all to do with what OpenQuery expects:
It requires a result set to be returned, if it cannot see it, it returns the above error.
That's why I thought you should try the mentioned techniques (it's usually used when you want to use stored proc from BCP), basically it tells the calling process what to expect in the resultset.
You shouldn't do anything in IF(1=0) block except returning resultset structure and setting FMTONLY.
Can you change your sp not to take NULL and try to call it via openrowset? Does it work?
August 3, 2011 at 9:57 am
The sp has a lot of proprietary code, so I can’t post it as is.
When I tried to simplify it to fake it’s content based on some temp tables ... it worked with NULL :crazy:… go figure :hehe:.
Now I’ll try to recreate it as close as possible to that production version so it resembles behavior. It’ll take a bit of time. I’ll post it as soon as possible. I really appreciate you taking time.
Eugene, as for sp itself – I can’t change it. It belongs to a different dev team within my company and this sp has a bug that I am trying to work around, and this bug got lowest priority level, so it might get fixed in the next century if at all:crying:. By the way I was trying to place whole sp into openrowset ;-), but it so huge … I run out of space :(.
August 3, 2011 at 1:53 pm
Hi again,
I finally got to the bottom of this problem. I didn't think it would be SP's problem, since it is executing without an error on the server it resides on, but apparently I was wrong. Stored procedure sums up the column that has NULLs in it…
Here is a simple recreation of the problem:
first stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC stubbornSP --1
CREATE PROCEDURE [dbo].stubbornSP
@intPar1 int = NULL
AS
CREATE TABLE #tmpTL (SK int,
Quantity float NULL)
INSERT INTO #tmpTL (SK,Quantity) VALUES (13117704,307330003.89760630358)
INSERT INTO #tmpTL (SK) VALUES (13117705)
CREATE TABLE #tmpBalFwd (
TLQuantity float NULL)
IF @intPar1 IS NULL
BEGIN
INSERT INTO #tmpBalFwd (
TLQuantity)
SELECT
SUM(tl.Quantity)
FROM
#tmpTL tl
SELECT -1 AS RetVal
END
ELSE
BEGIN
SELECT 0 AS RetVal
END
and the openrowset for this sp:
EXEC('SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server=MyServer;Uid=me;Pwd=myPassword;Database=myDB'',
''
SET NOCOUNT ON;
declare @temp table (RetVal int)
INSERT INTO @temp EXEC stubbornSP null
SELECT * FROM @temp
''
) AS a;')
I reduced it a bit to make simpler yet.
This will produce an error:
Msg 7357, Level 16, State 1, Line 1
Cannot process the object "
SET NOCOUNT ON;
declare @temp table (RetVal int)
INSERT INTO @temp EXEC stubbornSP null
SELECT * FROM @temp
". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
but if you modify the SP and take away that last row insert, problem goes away:cool:....
Is there a way to solve it without touching the SP? Again it is not my territory...
August 4, 2011 at 12:18 pm
Hi stewartc-708166,
Apparently you were right about NULLs.
I hope you'll have time to review my stubbornSP. You'd notice that it is aggregate function that makes all the difference. When the stored procedure is executed directly, it ignores NULLs for aggregate functions, when it is executed through openrowset it can't handle NULLs. Is there any setting that would make this execution ignore NULLs for aggregate functions as it usually does?
August 4, 2011 at 1:30 pm
Try to use
SET ANSI_WARNINGS OFF
August 4, 2011 at 1:42 pm
:w00t::-D:-D bingo !!!!!!!!! you r the BEST... where do I hail you as such? there is no corresponding EmotIcon to show my exhilaration ...... :hehe::-D:hehe::smooooth:
August 4, 2011 at 1:56 pm
Sorry, I should have advise you about this setting straight away...
But, I'm happy that your problem is finally soved. Good Luck!
August 4, 2011 at 2:03 pm
Thanks a lot 😉
I should have known too ... Wasted so much time on digging wrong direction 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply