Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to
access data on another Server running 2000. I get the following error, whe
n I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable the
se options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analy
zer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
JimReview the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:

>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure t
o access data on another Server running 2000. I get the following error, wh
en I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to b
e set for the connection. This ensures consistent query semantics. Enable th
ese options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Anal
yzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.sql

Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
Jim
Review the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".
|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:

>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim
|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>
|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.

Error: Could not find stored procedure !!

Hi,

Error: Could not find stored procedure.

I Installed the SQL Server SP2 and the error still occurs when ever I call the stored procedure from my windows app!!

Any Help ?

what's the name of SP you want to call?|||

In design window its : GetMonthRecord;1

in code window its: GetMonthRecord_1

I tried to rename it to : GetMonthRecord

the same error occurs !!

|||

Would you mind running this query and posting the results so that we can get some more information related to the object:

select uid,
left ([name], 30) as [name]
from sysobjects
where [name] like 'GetMonthRecord%'

|||

I run this query:

select uid,

left ([name], 30) as [name]

from sysobjects

where [name] like 'ThisMonthRecords%'

the result is:

1 ThisMonthRecords

|||

Now try:

exec ThisMonthRecords

and post the results

|||

executed succecfully the result is integer:

2

//

I'm facing the problem only with VS2005, when I add the procedure as queryTableAdapter, no error in code, but the error uccours after calling the SP.

Error: An invalid character was found in text content.

Hello All,
I am currently importing datarows into a sql 2000 database. when I use my
stored procedure which contains sp_xml_preparedocument... and FROM OPENXML,
I get the error stated above in the subject heading.
In reading other websites I have found that the error above:
An invalid character was found in text content.
You will get this error message if a character in the XML document does not
match the encoding attribute. Normally you will get this error message if
your XML document contains "foreign" characters, and the file was saved with
a single-byte encoding editor like Notepad, and no encoding attribute was
specified.
The actual error is:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 15
XML parsing error: An Invalid character was found in text content.
Based on what I found on the other webisite, the only foreign character in
my data the è in ARKTX="XXX CRèME 1x20 KG".
Is there anyway to encode this character so I do not get a parsing error?
Thank you in advance
Eric
I found that I have to place an xml declaration
of
<?xml version="1.0" encoding="ISO-8859-1"?>
in my XML string so the sp_xml_prepared_Document stored procedure will treat
the data as UTF-8 and not the Database's code page.
"E-Cube" wrote:

> Hello All,
> I am currently importing datarows into a sql 2000 database. when I use my
> stored procedure which contains sp_xml_preparedocument... and FROM OPENXML,
> I get the error stated above in the subject heading.
> In reading other websites I have found that the error above:
> An invalid character was found in text content.
> You will get this error message if a character in the XML document does not
> match the encoding attribute. Normally you will get this error message if
> your XML document contains "foreign" characters, and the file was saved with
> a single-byte encoding editor like Notepad, and no encoding attribute was
> specified.
>
> The actual error is:
> Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 15
> XML parsing error: An Invalid character was found in text content.
> Based on what I found on the other webisite, the only foreign character in
> my data the è in ARKTX="XXX CRèME 1x20 KG".
> Is there anyway to encode this character so I do not get a parsing error?
>
> Thank you in advance
> Eric
>

Error: An invalid character was found in text content.

Hello All,
I am currently importing datarows into a sql 2000 database. when I use my
stored procedure which contains sp_xml_preparedocument... and FROM OPENXML,
I get the error stated above in the subject heading.
In reading other websites I have found that the error above:
An invalid character was found in text content.
You will get this error message if a character in the XML document does not
match the encoding attribute. Normally you will get this error message if
your XML document contains "foreign" characters, and the file was saved with
a single-byte encoding editor like Notepad, and no encoding attribute was
specified.
The actual error is:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
15
XML parsing error: An Invalid character was found in text content.
Based on what I found on the other webisite, the only foreign character in
my data the è in ARKTX="XXX CRèME 1x20 KG".
Is there anyway to encode this character so I do not get a parsing error?
Thank you in advance
EricI found that I have to place an xml declaration
of
<?xml version="1.0" encoding="ISO-8859-1"?>
in my XML string so the sp_xml_prepared_Document stored procedure will treat
the data as UTF-8 and not the Database's code page.
"E-Cube" wrote:

> Hello All,
> I am currently importing datarows into a sql 2000 database. when I use my
> stored procedure which contains sp_xml_preparedocument... and FROM OPENXML
,
> I get the error stated above in the subject heading.
> In reading other websites I have found that the error above:
> An invalid character was found in text content.
> You will get this error message if a character in the XML document does no
t
> match the encoding attribute. Normally you will get this error message if
> your XML document contains "foreign" characters, and the file was saved wi
th
> a single-byte encoding editor like Notepad, and no encoding attribute was
> specified.
>
> The actual error is:
> Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Lin
e 15
> XML parsing error: An Invalid character was found in text content.
> Based on what I found on the other webisite, the only foreign character in
> my data the è in ARKTX="XXX CRèME 1x20 KG".
> Is there anyway to encode this character so I do not get a parsing error?
>
> Thank you in advance
> Eric
>sql

ERROR:- An INSERT EXEC statement cannot be nested.

HI,

WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.

BUT WE ARE GETTING A FEW HICCUPS. PLS HELP

THIS IS HOW IT GOES :-

CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .......;
ST2........;
END

CREATE PROCEDURE MY_PROC2
AS
BEGIN

CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP2
EXEC MY_PROC1

ST1 .......;
ST2........;

END

THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED

NO PROBLEM TILL NOW......

BUT,

CREATE PROCEDURE MY_PROC3
AS
BEGIN

CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP3
EXEC MY_PROC2

ST1 .......;
ST2........;

END

THEN IT GIVES AN ERROR AS :-

"An INSERT EXEC statement cannot be nested."

CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE......

WHAT IS THE NESTING LEVEL OF A PROCEDURE ?

IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?

PLS HELP ME OUT IN THIS

THANKSYou can nest stored procedures pretty deep (I think it's 32 levels or so). What you cannot do is have an INSERT #TMP3 EXEC proc1 in one procedure and have the next procedure that calls it with an INSERT #TEMP EXEC proc2.

You have to architect around this limitation. There is no setting to change the above that I'm aware of.|||Hi,

Thanks Derrick, But Is There A Way You Know To Get Around This One.

I Could Use Permanent Tables To Get Around This But It Takes Too Much Space,which Is A Constraint In Our Case.

Have To Use Temp Table :-

Is There A Way Around It ,as I Am Using The Results Of The First Procedure To Drive The Second One And The Results Of The Second One To Drive The Third .

Pls , If You Know Of Anything Pls Let Me Know.

Thanks.|||Without manual intervention, one level of INSERT...EXECUTE is the limit.

To do what you want, you really need an N-tier server. You can sort of kludge it via multiple instances, but you run out of RAM pretty quickly. You can also kludge it by compounding your cursor (rolling up all of the logically nested SELECTs into a single monster query).

There are a number of choices available, but due to your tight restrictions on RAM and disk, very few of those choices make good sense.

-PatP|||Hi,

I Got The Point Of Set -level Processing Rather Than Row-level Processing As You Had Said Before.so I Might Do Away With The Cursor Thing Altogether.but Is There Any Way To Use Mulitple Insert..exec Statement.

Now,that I Am Not Using Cursors ,i Don't Think Ram And Disk Matters Much Now.

Is There Any Way Out Of It Now ? Pls Do Help

Thanks.|||No. That's what we're trying to tell you. What you can do is have multiple queries that INSERT into a regular "process" table, which is just a normal table that records processes. You can then have a wrapper query that runs these in order. As long as all of your inserts are occurring on the second level, as opposed to the first order of queries meaning the wrapper query, you can run these infinitely.

Our EMC SnapClone process uses this methodology.|||Hi,

Thanks Derrick For The Help,but Frankly Speaking Being Still A Newcomer In The Field Of Ms-sql Databases.some Of The Words You Have Said Have Escaped My Vivid Imagination. Could You Pls Explain Me.

First,

What Do You Mean By A Wrapper Query ?

Second,

How To Implement It In Out Here ?

Could You Pls Explain This ?

Thanks.|||First,

What Do You Mean By A Wrapper Query ?
--This is just a query that call several subqueries and has overall control of a process.

Second,

How To Implement It In Out Here ?

Have each query right the results to a regular table. The query controls the overall process by reading these tables and deciding which query it needs to run next. As long as you stay one level under the wrapper query, you can run as many of these INSERT EXEC statements as you need to. If you post your query, I should be able to help you out more.|||DROP PROCEDURE PROC1
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END

DROP PROCEDURE PROC2
CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 6 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 7 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 2 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 3 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM #SAATHI A
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

DROP PROCEDURE PROC3
CREATE PROCEDURE PROC3
AS
BEGIN
CREATE TABLE #SAATH2(INTCUSTOMERID INT,TOTAL_COUNTS INT)

INSERT INTO #SAATH2
EXEC PROC2

DECLARE @.EKEK INT
DECLARE @.KAUNSARE VARCHAR(100)
SET @.EKEK = 1
SET @.KAUNSARE = 'International Pop'
WHILE @.EKEK <= 3
BEGIN
SELECT @.KAUNSARE AS NAAMRE,COUNT(*) AS TOTAL_COUNTS
FROM SAATH
WHERE SUBSTRING(PREFER,@.EKEK,1) = 1
GROUP BY SUBSTRING(PREFER,@.EKEK,1)
SET @.EKEK = @.EKEK + 1
IF @.EKEK=2
BEGIN
SET @.KAUNSARE = 'International Rock'
END
IF @.EKEK=3
BEGIN
SET @.KAUNSARE = 'Hindi Pop'
END
END
END

OUT HERE, THE PROBLEM ARISES IN PROC3 ,

THE THING IS THE RESULT OF 1 PROC IS I/P TO SECOND ONE AND THE RESULT OF 2 PROC IS I/P TO THIRD ONE.

COULD YOU DO SOMETHING ABOUT IT ?

HOW COULD WE IMPLEMENT WRAPPER QUERY OUT HERE, AS MAINTAINING ALL 3 PROCS ARE NECESSARY AS THEY ARE ALSO I/P'S TO OTHER PROCS.

AND SO ON ....

THANKS|||Here is an example of how to do it. ME is a linked server back to the same.

create proc a
as
select a = 'a'
go

create proc b
as
create table #b (b varchar(32) not null)
insert #b exec ME.master.dbo.a
select * from #b
go

create proc c
as
create table #c (c varchar(32) not null)
insert #c exec ME.master.dbo.b
select * from #c
go

create proc d
as
create table #d (d varchar(32) not null)
insert #d exec ME.master.dbo.c
select * from #d
go

exec d
go|||HI,

WHAT DO YOU MEAN BY :-
"ME is a linked server back to the same."

AND ANY WAY IF I AM USING : -

create proc a
as
select a = 'a'
go

create proc b
as
create table #b (b varchar(32) not null)
insert #b exec a
select * from #b
go

create proc c
as
create table #c (c varchar(32) not null)
insert #c exec b
select * from #c
go

create proc d
as
create table #d (d varchar(32) not null)
insert #d exec C
select * from #d
go

exec d
go

I AM GETTING AN ERROR WHICH IWAS GETTING BEFORE :-

"An INSERT EXEC statement cannot be nested."

HASN'T MADE MUCH DIFFERENCE ?

WHAT IS ME .. IS IT PRESENT IN ALL OF THE SQL SERVER 2000 ?

OR IS IT SOMETHING VIRTUAL THAT YOU HAVE CREATED ?

THANKS.|||"ME" is a linked server. In Enterprise Manager, open the Security folder, right click on Linked Servers, choose New Linked Server from the menu. I named the server "ME." Select "Microsoft OLE DB Provider for SQL Server" as the provider. Enter the actual name of your server in the Data Source field. On the security tab, select "Be made using the login's current security context." On the server options tab, check all of the check boxes.|||HI,

I AM GETTING AN ERROR THAT SAYS :-

"Could not find stored procedure 'master.dbo.a'.
Could not relay results of procedure 'a' from remote server 'ME'."

COULD YOU PLS EXPLAIN ME THE FUNDA OF LINKED SERVERS .?

I AM JUST BLINDLY F9OLLOWING YOU. AND GETTING NOWHERE .

PLS EXPLAIN IT TO ME.

THANKS,

CHETAN B.|||HI,

I AM GETTING AN ERROR THAT SAYS :-

"Could not find stored procedure 'master.dbo.a'.
Could not relay results of procedure 'a' from remote server 'ME'."

COULD YOU PLS EXPLAIN ME THE FUNDA OF LINKED SERVERS .?

I AM JUST BLINDLY FOLLOWING YOU. AND GETTING NOWHERE .

PLS EXPLAIN IT TO ME.

THANKS,

CHETAN B.

Error: "EXECUTE permission denied on object..."

I get this error when I try to preview a report that uses a stored procedure. The stored procedure does not exist in the database, but in the directory I run the report from. The stored procedure declares which database to use, and to create the temp table I want. Do I require special permissions on the sql db I am using to be able to create temp tables? Any help would be great, thanks!

Michael

How are you connecting to the stored procedure in a directory?

SQL Profiler may let you troubleshoot what user account and object this error message relates to.

|||

I had a developer do this awhile ago too; you require elevated permissions to create tables. Try just clearing the table rather than deleting and recreating the table every time the report is run it requires less security.

Error: 'SubQuery Returned More than 1 Value'

I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1. The code normally works however, every once in a while an exception is thrown:

Code Snippet

com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.

Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?

Stored Procedure:

Code Snippet

ALTER Procedure [dbo].[addRecord]
@.userID int,
@.itemID int,
@.info varchar(50),
@.comment varchar(50),
@.output int output
AS

Declare @.dateSubmitted datetime
set @.dateSubmitted = getDate();

--Insert the new record. THIS TABLE has an ID identity Primary Key
--column that auto-increments.
insert into RecordTable
(UserPerson, Information, DateSubmitted)
values (@.userID, @.info, @.dateSubmitted);

Get the ID Assigned in the record table. The Item table
has a Foreign key on this column.
Declare @.assignedID int
set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

/**Set the output parameter.*/
set @.output = @.assignedID;

--Now update the Item Table.
insert into Item
(ID, RecordID, Comment)
values (@.item,@.assignedID, @.comment);

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

|||

CharlieXXX wrote:

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

That's not possible. The error occurs even when the RecordTable is empty.
|||

SomeDeveloperPerson wrote:

CharlieXXX wrote:

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

That's not possible. The error occurs even when the RecordTable is empty.

Charlie... you're actually correct. I modified the line to:

Code Snippet

set @.assignedID = (select MAX(ID) from RecordTable where DateSubmitted = @.dateSubmitted)

And it works with no problems now. Apparently the code is executing faster than expected so that several records are being written in under a millisecond.
|||

You can reduce the 'effort' of the procedure with this simple alteration:

Instead of having another query to obtain the IDENTITY value of the just entered row, use the SCOPE_IDENTITY() function.

Code Snippet

--Insert the new record. THIS TABLE has an ID identity Primary Key
--column that auto-increments.
insert into RecordTable
(UserPerson, Information, DateSubmitted)
values (@.userID, @.info, @.dateSubmitted);

Declare @.assignedID int
set @.assignedID = SCOPE_IDENTITY()

It saves a small amount of unnecessary server 'work' since the SCOPE_IDENTITY() is part of the return information from the original insert.

And using MAX() in the fashion that you are could potentially have you obtaining the value from a row inserted by another user. Not a very reliable prospect.

ERROR: "Subquery returned more than 1 value."

I've got a big procedure written by a contractor. I'm trying to
execute it (against a test db until I get it worked out) and there is
one section of it that fails. I've isolated the section and run it in
Query Analyzer and it still fails. Here is the SQL:
DELETE FROM tblBedOccupancy WHERE (
ContactID IN (
SELECT ContactID FROM tblPeople WHERE Community IN (
SELECT DISTINCT BuildingID FROM lnkCompaniesBuildings WHERE CompanyID
<> 61
)
)
)
The error returned is:
Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
Line 18
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.
If I change DELETE to SELECT *, it runs fine. If I run the subqueries
they run fine. How can I get this to work?
Thanks,
Josh D> The error returned is:
> Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
> Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> The statement has been terminated.
Look closely at the error message. Did you notice the name
"tg_DeleteOccupancyRecord". What does this refer to? Looks like a
trigger - and a poorly written one to boot. The problem is the code in the
trigger.|||I'd agree with Scott, sounds like you have trigger written by someone who
didn't realize that triggers fire once per statement, not once per row.

> Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
> Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> The statement has been terminated.|||> I'd agree with Scott, sounds like you have trigger written by someone who
> didn't realize that triggers fire once per statement, not once per row.
...and if that was the contractor, this might constitute breach of contract
.
:)
ML|||> ...and if that was the contractor, this might constitute breach of
> contract.
> :)
Well, if the contractor's side of the agreement doesn't explicitly state, "I
know what I'm doing"...|||> Well, if the contractor's side of the agreement doesn't explicitly state, "Id">
> know what I'm doing"...
In continental law the "I know what I'm doing" part is presumed (praesumptio
iuris), and the contractor can only limit his own liability by stating the
opposite.
ML

Error with stored Procedure

does anyone see anything wrong with this stored procedure?? I keep getting a syntax error.

CREATE PROCEDURE [InsertGCTerms]
AS
INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]([TM #],[FirstName],[LastName],[SocialSecurityNumber],[DateHired],[DepartmentName],[Title])
SELECT a.TM#, a.LASTNAME, a.FIRSTNAME, a.SSN#, a.JOBTITLE, a.HIREDATE, a.DEPT#
FROM GOVEMPLYS AS a
WHERE a.STATUS = 'TERMINATED'
RETURN
GOCREATE PROCEDURE [InsertGCTerms]
AS
INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]([TM #],[FirstName],[LastName],[SocialSecurityNumber],[DateHired],[DepartmentName],[Title])
SELECT a.[TM#], a.LASTNAME, a.FIRSTNAME, a.[SSN#], a.HIREDATE,
a.[DEPT#], a.JOBTITLE FROM GOVEMPLYS AS a
WHERE a.STATUS = 'TERMINATED'
RETURN
GO|||Is GOVEMPLYS.DateHired a date field or a character string? If it is a character string, the run this to find records where someone has entered an illegal date string:

select HIREDATE from GOVEMPLYS where isdate(HIREDATE) = 0|||Is GOVEMPLYS.DateHired a date field or a character string? If it is a character string, the run this to find records where someone has entered an illegal date string:

select HIREDATE from GOVEMPLYS where isdate(HIREDATE) = 0

Error with stored procedure

I have the following code in my code behind page:

 Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
 Dim CM As New SqlCommand("spCCF_CrossTab", CN)
 CM.CommandType = CommandType.StoredProcedure
 CM.Parameters.Add(New SqlParameter("@.LocationID", "CCFIF"))
 CM.Parameters.Add(New SqlParameter("@.BeginDate", dtbStart.Text))
 CM.Parameters.Add(New SqlParameter("@.EndDate", dtbEnd.Text))
 CN.Open()
 DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
 dgReport.DataSource = DR
 dgReport.DataBind()

A SQL exception is thrown: Incorrect syntax near the keyword 'END'

But I turned on tracing in Enterprise Manager, the following request is sent to SQL:

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

In query analyzer the above line executes without error and returns the expected information.

My stored procedure is:

CREATE PROCEDURE spCCF_CrossTab
@.LocationID varchar(10),
@.BeginDate varchar(10),
@.EndDate varchar(10)
AS
declare @.select varchar(8000), @.sumfunc varchar(100), @.pivot varchar(100), @.table varchar(100), @.where varchar(1000)
select @.select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.Productivity_CCF.[Date] BETWEEN CONVERT(DATETIME, ''' + @.BeginDate + ''', 101) AND CONVERT(DATETIME, ''' + @.EndDate + ''', 101)) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name order by Action'
select @.sumfunc= 'COUNT(ActionID)'
select @.pivot='UserName'
select @.table= 'UserInfo'
select @.where='(dbo.UserInfo.LocationID = ''' + @.LocationID + ''' and dbo.UserInfo.Inactive<>1 )'
DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '
+ @.pivot + ' Is Not Null and ' + @.where)
SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )
SELECT @.delim=(CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END)
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')
EXEC (@.select)
SET ANSI_WARNINGS ON
GO

I've been banging my head on this for quite some time now, any insight someone might have as to the problem would be greatly appreciated! Thanks!I don't see the ELSE and END part of the CASE statement in the code below.

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', 'should be something more here? FROM ##pivot

But that doesn't explain why it works in Query Analyzer but not through code.|||I'm using the code from this article: http://www.sqlteam.com/item.asp?ItemID=2955
which I have seen recommended before on the forums so I'm assuming someone has gotten this to work.

I've added a few things but have left everything after the statement below unchanged.
<code>
SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, 'END)')
<code
Again, it does work successfully in query analyzer and SQL trace does not appear to be generating any errors.|||The code in the article runs fine for me against the Pub database. Yes, many people have gotten it to work.

But you are not running the exact code from the article, so that's a moot point.

My suggestion would be to specify the datatype of your parameters explicitly in your ASP.NET code. It looks like ASP.NET is assuming nVarchar. I don't know if that makes a difference, but I would try that. Additionally, I would add an Output parameter to the stored procedure and pass back the @.select variable to your ASP.NET page and have your ASP.NET page display it. This will show you exactly what the stored procedure is trying to execute and might help your debugging effort.

Terri|||The stored procedure runs fine for me when in Query Analzyer. The problems is when I'm trying to call it from the ASP page.

I removed my modifications from the SP and am still running into the same exact error on the aspx page. I am already explicity setting the datatype for the parameters. Setting an output parameter in the SP is doing nothing in my debug efforts since it never gets to that point in the code and is erroring prior.

The error my aspx page is throwing a SQL exception: Incorrect syntax near the keyword 'END'.

I'm still trudging away at this and am not getting any closer to solving the problem. Any other insight on this problem?
Thanks!|||We need to see what @.select holds when your stored procedure goes to EXEC it.

There are a few ways to do this. Can you add an OUTPUT parameter to your stored procedure to hold @.select, and then output the value of this parameter on your page and then let us know what it is?

Terri|||Can you tell me how to get the output parameter to return to the aspx page as I'm getting an error when trying to do so? i have the following:

...
arParms(3) = New SqlParameter("select", SqlDbType.NVarChar, 8000)
arParms(3).Direction = ParameterDirection.ReturnValue
...
Dim strReturn As String = arParms(3).ToString ' also tried response.write (arParms(3).Value)
Response.Write(strReturn)
SP: DECLARE @.sql varchar(8000)
and instead of exec @.select I have return @.select

Error I get: Syntax error converting the varchar value 'SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID), 'ashelts' = COUNT ... (and then too long of a select statement to write out the entire message).

In debugging I did notice that this line of code in the sp:

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, 'END)' )

was causing part of the problem because the select statement looked like:

SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID END)

So I removed the END, now I'm getting a new error but I can't get to completely write on the screen I get as SQL exception: The name 'SELECT dbo.ActionCodes... (part of the select statement). I've tried simplifying my query and it still is too long to display the entire error.

Thanks

On a frustrating side; What I still can't understand it WHY does the stored procedure work (with the parameters I have from the aspx page) in Query Analyzer?|||Ok - actually got the entire sql error finally:

The name 'SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID) FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name' is not a valid identifier.|||To get the value of @.select back to your ASP.NET page, I think you should have:

arParms(3) = New SqlParameter("@.select", SqlDbType.NVarChar, 8000)
arParms(3).Direction = ParameterDirection.Output

You shouldn't need to RETURN @.select in the stored procedure. Just comment out the EXEC @.select line so no errors are generated.

But I am afraid I am leading you astray. If your stored procedure was running correctly from query analyzer with the desired parameters, then your stored procedure should not need any revisions.

Is this still returning the correct results from Query Analyzer?

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

If so, can you temporarily forego the Command parameters and just put that exec statement into your ASP.NET page? (untested)

Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
Dim CM As New SqlCommand("exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'", CN)
CM.CommandType = CommandType.Text
CN.Open()
DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
dgReport.DataSource = DR
dgReport.DataBind()

Does this return the expected result?

Terri|||No. Query analyzer doesn't like this either (error 203 invalid identifier). HOWEVER, when I put this line back in the SP with the word 'END'

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' End )' )

Query analyzer works just fine with:

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

What the aspx page does:
without the word End I get error 203 invalid identifier.
With End I get the syntax error again "Incorrect syntax near the keyword 'End'."

The problem is I have to have about 8 different aspx pages that are going to need similar cross tab type reports - and I want to make the pages and code so I don't have to modify the aspx pages everytime there is a staff change. I can handle simple stored procedures but I really don't understand what is going on in this one enough to trouble shoot very well.

I appreciate your help in trying to get this worked out.|||For grins and giggles, what happens when you present the dates in ISO format?

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'20031103', @.EndDate = N'20031104'

Terri|||Tried that - still an error.
I decided to start from scratch - took just the code for the stored procedure and added a simple select statement.

Still the same darn error with the END. Removing it - I get a cross tab table - but the values are a sum of all the values for that row - I get a table that each row has the same value in each column. I see the importance of the END, have been reading up on the stuff function, but still can't get this to work.

Since I don't have a DBA accessible, I may have to (ugh!) go to a case statement and figure out how I will manage changing staff.|||If you are still having trouble, script out all of your tables, and provide some data via a series of INSERT statements. This one is really tough to help with without having the real "stuff" to mess with.

Terri|||I was able to make the scripts for the tables, but is there a tool for doing the inserts?
I found some different code for doing the cross tabs. Does the same thing: works fine from QA, but get a SQL syntax error "Incorrect syntax at keyword 'END'" from the aspx page.

I tried this on a sample database with sample code provided (copied the code, didn't even retype) - same exact situation: works in query analyzer, syntax error with the aspx page.

I'm really doubting the problem is in the code since I have had a number of circumstances where this works in QA. Could the problem be in the version of the framework or .NET? I'm using Visual Studio 2002, 1.0 of the framework.

CREATE PROCEDURE crosstabextended
@.select_stmt varchar(8000),
@.groupfn varchar(100),
@.pivot_column varchar(100),
@.output_table varchar(100),
@.select_table varchar(100)
AS
DECLARE @.sql varchar(8000)
DECLARE @.delimiter varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DROP TABLE ##pivot
EXEC ('SELECT ' + @.pivot_column + ' AS pivot INTO ##pivot FROM ' + @.select_table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot_column + ' FROM ' + @.select_table + ' WHERE '
+ @.pivot_column + ' Is Not Null' )
-- Add the string END to the group function (@.groupfn) variable
SELECT @.sql='', @.groupfn=stuff(@.groupfn, len(@.groupfn), 1, ' END)' )
--Check if pivot column starts with char or date
SELECT @.delimiter=CASE SIGN( CHARINDEX('char', data_type)+CHARINDEX('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @.sql=@.sql + '''' + CONVERT(varchar(100), pivot) + ''' = ' +
STUFF(@.groupfn,CHARINDEX( '(', @.groupfn )+1, 0, ' CASE ' + @.pivot_column + ' WHEN '
+ @.delimiter + CONVERT(varchar(100), pivot) + @.delimiter + ' THEN ' ) + ', ' FROM ##pivot
SELECT @.sql=LEFT(@.sql, LEN(@.sql)-1)
SELECT @.select_stmt=STUFF(@.select_stmt, CHARINDEX(' FROM ', @.select_stmt)+1, 0, ', ' + @.sql + ' ')
SELECT @.select_stmt=REPLACE(@.select_stmt, ' FROM', 'INTO '+ @.output_table + ' FROM')
IF EXISTS(SELECT table_name FROM tempdb.information_schema.tables WHERE table_name = @.output_table)
BEGIN
EXECUTE('DROP TABLE ' + @.output_table)
END
EXEC (@.select_stmt)
EXECUTE('SELECT * FROM ' + @.output_table)
SET ANSI_WARNINGS ON
GO

Code used in aspx page:

 Dim CN1 = New SqlConnection(ConfigurationSettings.AppSettings("TestString"))
 Dim CM As New SqlCommand("execute crosstabextended 'select Store_name as StoreName from storemaster inner join sales on (sales.store_id=storemaster.store_id) group by store_name', 'sum(qty)', 'toy_id', '##mytemp', 'toymaster'", CN1)
 CM.CommandType = CommandType.Text
CN1.Open()
 Dim da As SqlDataAdapter
 da = New SqlDataAdapter(CM)
da.Fill(dsData)

Thanks!|||Did you ever find a solution to this problem?

Error with Function and Procedures

I have just streamlined my pile of functions and reloaded the result into a Stored Procedure. I now have two different errors. here are the two FN's and the SP. This will be a long message so apologise for its length;

Function 1:-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnWTRalldata]

(@.dt_src_date datetime,@.chr_div char(2), @.vch_portfolio_no tinyint,@.vch_prop_cat nvarchar(4))

RETURNS

@.WeeklyTerrierRSPI TABLE

(Areacode varchar(2),siteref nvarchar(3),estatename nvarchar(100), Securitised nvarchar(255),unitref nvarchar(15),unittype nvarchar(30),unittype_count int, tenantname nvarchar(100),tenantstatus nvarchar(25), tenantstatus_count int,unitstatus nvarchar(15), unitstatus_count int,floortotal float,floortotocc float,initialvacarea float, initialvacnet float,TotalRent float,NetRent float,FinalRtLsincSC float, ErvTot float, tenancyterm datetime, landact nvarchar(255),datadate datetime,div_mgr varchar(50),portfolio_mgr varchar(50),propcat nvarchar (4))

AS

BEGIN

INSERT @.WeeklyTerrierRSPI

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype, src_terrier.unittype_count,

src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus, src_terrier.unitstatus_count, src_terrier.floortotal,

src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent, src_terrier.NetRent, src_terrier.FinalRtLsincSC,

src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate, src_div_mgr.div_mgr,

src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat

FROM src_terrier INNER JOIN

src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN

src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN

src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no

WHERE (src_terrier.datadate = @.dt_src_date) AND (src_terrier.Areacode = @.chr_div) AND ( src_centre_list.Portfolio_no = @.vch_portfolio_no) AND( src_centre_list.propcat = @.vch_prop_cat)

RETURN

END

GO

Function 2:-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnWTRalldataReport]

(@.dt_src_date datetime,@.chr_div char(2), @.vch_portfolio_no tinyint,@.vch_prop_cat nvarchar(4))

RETURNS

@.WeeklyTerrierRSPII TABLE

(Areacode varchar(2),siteref nvarchar(3),estatename nvarchar(100), Securitised nvarchar(255),unitref nvarchar(15),unittype nvarchar(30),unittype_count int, tenantname nvarchar(100),tenantstatus nvarchar(25), tenantstatus_count int,unitstatus nvarchar(15), unitstatus_count int,floortotal float,floortotocc float,floorspaceperc float,initialvacarea float, initialvacnet float,TotalRent float,NetRent float,FinalRtLsincSC float,rentrolldiscperc float,netrentpersqft float, ErvTot float, tenancyterm datetime, landact nvarchar(255),datadate datetime,div_mgr varchar(50),portfolio_mgr varchar(50),propcat nvarchar (4))

AS

BEGIN

INSERT @.WeeklyTerrierRSPII

SELECT fnWTRalldata.Areacode, fnWTRalldata.siteref, fnWTRalldata.estatename, fnWTRalldata.Securitised, fnWTRalldata.unitref, fnWTRalldata.unittype, fnWTRalldata.unittype_count,

fnWTRalldata.tenantname, fnWTRalldata.tenantstatus, fnWTRalldata.tenantstatus_count, fnWTRalldata.unitstatus, fnWTRalldata.unitstatus_count, fnWTRalldata.floortotal,

fnWTRalldata.floortotocc, fnWTRalldata.floortotocc / fnWTRalldata.floortotal AS floorspaceperc, fnWTRalldata.initialvacarea, fnWTRalldata.initialvacnet, fnWTRalldata.TotalRent,

fnWTRalldata.NetRent, fnWTRalldata.FinalRtLsincSC,(fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1 AS rentrolldiscperc,

fnWTRalldata.NetRent / fnWTRalldata.floortotocc AS netrentpersqft, fnWTRalldata.ErvTot, fnWTRalldata.tenancyterm, fnWTRalldata.landact, fnWTRalldata.datadate, fnWTRalldata.div_mgr,

fnWTRalldata.portfolio_mgr, fnWTRalldata.propcat

FROM dbo.fnWTRalldata (@.dt_src_date, @.chr_div , @.vch_portfolio_no, @.vch_prop_cat)

RETURN

END

GO

STORED PROCEDURE :-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spWTRalldatareportsummary]

(@.dt_src_date datetime,@.chr_div char(2), @.vch_portfolio_no tinyint,@.vch_prop_cat nvarchar(4))

AS

BEGIN

--SET NOCOUNT ON;

SELECT

Areacode,siteref,estatename, Securitised,unitref,unittype,unittype_count, tenantname,tenantstatus,

tenantstatus_count,unitstatus, unitstatus_count,floortotal,floortotocc,floorspaceperc,initialvacarea, initialvacnet,TotalRent,NetRent,FinalRtLsincSC,rentrolldiscperc,netrentpersqft, ErvTot, tenancyterm, landact,datadate,div_mgr,portfolio_mgr,propcat

FROM fnWTRalldataReport (@.dt_src_date, @.chr_div , @.vch_portfolio_no, @.vch_prop_cat)

END

GO

The Problem I have is two fold. When I execure the procedure and run

USE [DashboardSQL-2K5]

GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[spWTRalldatareportsummary]

@.dt_src_date = N'28/04/2006', @.chr_div = N'SW', @.vch_portfolio_no = 4, @.vch_prop_cat = N'core'

SELECT 'Return Value' = @.return_value

GO

if I put the date in as 28/04/2006 I get an error like:-

Msg 8114, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 0

Error converting data type nvarchar to datetime.

(1 row(s) affected)

If I put the date in as 04/28/2006 I get an error like :-

Msg 8134, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 18

Divide by zero error encountered.

The statement has been terminated.

(1 row(s) affected)

Could anyone help me on this problem please as my whole project is now being help up by something stupid I have done.

Thanks in advance

hi,

i hope u shold try the date format as '2006-06-01' or in case u want to use your own date format which u are using

try using

@.dt_src_date = N'28/04/2006'

try convert(varchar,@.dt_src_date,103) = N'28/04/2006'

hope should work

regards

www.snktheone.com

|||

I will give that a go

Thanks

Error with default database collation

Hello
I am using SQL Server 2000 SP4
In my stored procedure I create table variable that contains one varchar
field and join that table variable with one of the database tables. I keep
getting error "Cannot resolve collation conflict for equal to operation.".
Both fields should have same collation. Table's field due to the fact that
it's collation is set to <database default> and <database default> is
Latin1_General_CI_AS. And according to Books online table variable also has
a
collation taken from <database default> if not explicitly set to other. So
what is the problem? If table variable field explicitly assign collation
Latin1_General_CI_AS - everything works fine. Why? My server's default
collation is Cyrillic_General_CI_AS. But how that can affect? I am not
allowed to use explicit collation in my SP.
Probably it is a known bug in SQL Server... Are then any fixes for that?
Thanks in advance.Seems like SQL2K doesn't pick up the current database for the connection whe
n you create a table
variable:
CREATE DATABASE x COLLATE Cyrillic_General_CI_AS
GO
USE x
CREATE TABLE t(c1 varchar(10))
INSERT INTO t VALUES('asd')
GO
--Error on 2000 sp3, fine on 2005
DECLARE @.t table(c1 varchar(10))
INSERT INTO @.t VALUES('asd')
SELECT * FROM t INNER JOIN @.t AS t2 ON t.c1 = t2.c1
GO
--Fine on both 2000 sp3 and 2005, as expected
DECLARE @.t table(c1 varchar(10) COLLATE database_Default)
INSERT INTO @.t VALUES('asd')
SELECT * FROM t INNER JOIN @.t AS t2 ON t.c1 = t2.c1
GO
--Error as expected on both 2000 sp3 and 2005
CREATE TABLE #t (c1 varchar(10))
INSERT INTO #t VALUES('asd')
SELECT * FROM t INNER JOIN #t AS t2 ON t.c1 = t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in messag
e
news:C5DD7198-4122-483E-BFD6-E83AF28002F7@.microsoft.com...
> Hello
> I am using SQL Server 2000 SP4
> In my stored procedure I create table variable that contains one varchar
> field and join that table variable with one of the database tables. I keep
> getting error "Cannot resolve collation conflict for equal to operation.".
> Both fields should have same collation. Table's field due to the fact that
> it's collation is set to <database default> and <database default> is
> Latin1_General_CI_AS. And according to Books online table variable also ha
s a
> collation taken from <database default> if not explicitly set to other. So
> what is the problem? If table variable field explicitly assign collation
> Latin1_General_CI_AS - everything works fine. Why? My server's default
> collation is Cyrillic_General_CI_AS. But how that can affect? I am not
> allowed to use explicit collation in my SP.
> Probably it is a known bug in SQL Server... Are then any fixes for that?
> Thanks in advance.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
>|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name strin
g.
> You could try declaring your parameters as type varchar, but you have to b
e
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' = (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
> > Ok, I previously had a thread about "Column name as variable".
> > Here's a proc I wrote, and it compiles ok:
> > alter PROCEDURE rptRRTP_By_Date_Range
> > -- Add the parameters for the stored procedure here
> > @.Update_Field as sysname,
> > @.start_date as datetime,
> > @.end_date as datetime,
> > @.contr_Stat as varchar(2),
> > @.seq as int
> > AS
> > declare @.sql_stat as varchar(255)
> > BEGIN
> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> > ',' + @.end_date + ')
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
> > where sequence = ' + @.seq
> > Execute (@.sql_stat)
> > END
> > GO|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> You can't pass a function in as a parameter. Try:
>> DECLARE @.dt DATETIME;
>> SET @.dt = GETDATE();
>> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
> Ok, did that, but see error below.
>
>
>> --
>> Aaron Bertrand
>> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
>> "d.s." <nodamnspa...@.yahoo.com> wrote in message
>> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>>
>> > Ok, I previously had a thread about "Column name as variable".
>> > Here's a proc I wrote, and it compiles ok:
>> > alter PROCEDURE rptRRTP_By_Date_Range
>> > -- Add the parameters for the stored procedure here
>> > @.Update_Field as sysname,
>> > @.start_date as datetime,
>> > @.end_date as datetime,
>> > @.contr_Stat as varchar(2),
>> > @.seq as int
>> > AS
>> > declare @.sql_stat as varchar(255)
>> > BEGIN
>> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =>> > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
>> > ',' + @.end_date + ')
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
>> > where sequence = ' + @.seq
>> > Execute (@.sql_stat)
>> > END
>> > GO
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?
You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegro ups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

Error with BigDecimal used as stored procedure parameter

I have the following stored procedure:
create procedure MyNumericTestProc
(
@.param1 numeric(13,2) output
)
as
begin
if (@.param1 is NULL)
begin
set @.param1 = 5.25
end
set @.param1 = @.param1 + 0.01
select @.param1
end
I call it using the MS SQL Server JDBC Driver (SP3):
public class TestMyNumericTestProc
{
public static void main(String[] args)
{
try
{
testMyNumericTestProcUsingJDBC();
}
catch (ClassNotFoundexception e)
{
}
}
public void testMyNumericTestProcUsingJDBC() throws
ClassNotFoundException
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(
"jdbc:microsoft:sqlserver://MySystem\\MySQL2000Server:MyPort;databaseName=My
Database",
"username",
"password");
cs = conn.prepareCall("{call MyNumericTestProc(?)}");
BigDecimal d = new BigDecimal("300.10");
int scale = 3;
System.out.println("Input Value = " + d.toString());
System.out.println("Input Value Scale = " + d.scale());
System.out.println("Input Parameter Scale = " + scale);
// Set a BigDecimal inout parameter and execute call
cs.setObject(1, d, Types.DECIMAL);
cs.registerOutParameter(1, Types.DECIMAL, scale);
boolean csResult = cs.execute();
// Obtain result set
rs = cs.getResultSet();
rs.next();
d = rs.getBigDecimal(1);
System.out.println("ResultSet Value = " + d.toString());
System.out.println("ResultSet Scale = " + d.scale());
// Obtain value of the output parameter as object
Object obj = cs.getObject(1);
System.out.println("Output Param Value (as Object) = " +
((BigDecimal) obj).toString());
System.out.println("Output Param Scale (as Object) = " +
((BigDecimal) obj).scale());
// Obtain value of the output parameter as BigDeciaml
d = cs.getBigDecimal(1);
System.out.println("Output Param Value (as BigDecimal) = " +
d.toString());
System.out.println("Output Param Scale (as BigDecimal) = " +
d.scale());
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (cs != null) {
try { cs.close(); }
catch (SQLException e) {;}
}
if (conn != null) {
try { conn.close(); }
catch (SQLException e) {;}
}
}
}
}
The output of executing this class is as follows:
Input Value = 300.10
Input Value Scale = 2
Input Parameter Scale = 3
ResultSet Value = 30.02
ResultSet Scale = 2
Output Param Value (as Object) = 30.020
Output Param Scale (as Object) = 3
Output Param Value (as BigDecimal) = 30.020
Output Param Scale (as BigDecimal) = 3
Note that I use BigDecimal as the parameter type (which is the recommended
type fr DECIMAL and NUMERIC).
Given the stored procedure, I would have expected the value 300.11 as the
value of the output
parameter and within the result set.
It appears there is an error when the scale of the input value and the scale
specified by the output
parameter do not match, and the input parameter is a BigDecimal.
Is this a problem within the realm of the MS JDBC Driver? If it is not how
do I determine where the
error is occuring?
Try running your code against another driver. If it works, then it's
probably a MS JDBC Driver problem. And it will work.
Alin.
|||I revised the code to use the JDBC/ODBC Driver and re-ran. The output was
what one would expect:
Input Value = 300.10
Input Value Scale = 2
Input Parameter Scale = 3
ResultSet Value = 300.11
ResultSet Scale = 2
Output Param Value (as Object) = 300.110
Output Param Scale (as Object) = 3
Output Param Value (as BigDecimal) = 300.110
Output Param Scale (as BigDecimal) = 3
Now that I have determined this is a problem in the SQL Server JDBC Driver,
where do I file an error/bug report so that Microsoft is aware of the issue
(and possibly an idea of when the problem may be fixed)?
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1112824157.261614.198520@.f14g2000cwb.googlegr oups.com...
> Try running your code against another driver. If it works, then it's
> probably a MS JDBC Driver problem. And it will work.
> Alin.
>
|||Fred Foozle wrote:
> Now that I have determined this is a problem in the SQL Server JDBC
Driver,
> where do I file an error/bug report so that Microsoft is aware of the
issue
> (and possibly an idea of when the problem may be fixed)?
Microsoft engineers read this newsgroup, so they should be able to
either direct you to such a place or create a bug report themselves.
But I wouldn't wait for the bug to be fixed; MS only releases a new
JDBC driver version with a new SP and they usually fix a very limited
number of bugs; check the changelogs of their previous versions to see
what I mean.
Alin.
|||Hello Fred,
I have been able to reproduce the issue as reported. I filed a bug on it
and forwarded it to development.
Thanks,
Kamil
Kamil Sykora
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/securXity.
| Reply-To: "Fred Foozle" <ffoozle@.hotmail.com>
| From: "Fred Foozle" <ffoozle@.hotmail.com>
| Subject: Re: Error with BigDecimal used as stored procedure parameter
| Date: Thu, 7 Apr 2005 14:32:46 -0400
|
| I revised the code to use the JDBC/ODBC Driver and re-ran. The output was
| what one would expect:
|
| Input Value = 300.10
| Input Value Scale = 2
| Input Parameter Scale = 3
| ResultSet Value = 300.11
| ResultSet Scale = 2
| Output Param Value (as Object) = 300.110
| Output Param Scale (as Object) = 3
| Output Param Value (as BigDecimal) = 300.110
| Output Param Scale (as BigDecimal) = 3
|
|
| Now that I have determined this is a problem in the SQL Server JDBC
Driver,
| where do I file an error/bug report so that Microsoft is aware of the
issue
| (and possibly an idea of when the problem may be fixed)?
|
|
|
| "Alin Sinpalean" <alin@.earthling.net> wrote in message
| news:1112824157.261614.198520@.f14g2000cwb.googlegr oups.com...
| > Try running your code against another driver. If it works, then it's
| > probably a MS JDBC Driver problem. And it will work.
| >
| > Alin.
| >
|
|
|

Error with an Insert stored procedure

I am getting this error when executing an insert procedure
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would
be truncated.The length of the string you are trying to insert is larger than the maximum
length of the column you are trying to insert into. Either the datatypes of
the parameters that the stored procedure accepts do not map to the datatypes
of the columns of the table you are trying to insert into, or the procedure
uses INSERT INTO without a column list and you are trying to insert the
wrong data in the wrong column.
Jacco Schalkwijk
SQL Server MVP
"Earnie" <Earnie@.discussions.microsoft.com> wrote in message
news:6831DA16-57F3-4920-A254-42D7A849BCD2@.microsoft.com...
>I am getting this error when executing an insert procedure
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would
> be truncated.|||Try
set ansi_warnings off
GO
before the instert statement.!|||1) You can't use GO in a stored procedure
2) Do you really want to make information disappear? What if someone types
in a description to store in your database and the last sentence is cut off
because it is too long and you have set ansi_warnings off? Don't you think
the users will prefer to be given a warning that the description is too long
so that they can reword it in less words?
Jacco Schalkwijk
SQL Server MVP
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1107473880.870415.62730@.l41g2000cwc.googlegroups.com...
> Try
> set ansi_warnings off
> GO
> before the instert statement.!
>

Error while using Stored Procedure

Hi All,

Finally i was able to convert my UDF to Stored procedure in MSAS 2005!

Now while trying to use the stored procedure in MDX, i am getting a strange error:

Executing the query ...

Execution of the managed stored procedure CogInStr failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.

The System.SByte* type cannot be converted to an MDX type.

Execution complete

The MDX query was:

SELECT

{ FILTER([Measures].[Sales Amount], ASSP.CogInStr([Measures].[Sales Amount],"*", 0) > 0) }

ON AXIS(0)

FROM [Adventure Works]

The Stored procedure is failing even with complete function commented with only the definition as:

long ASSP::CubeInfo::Looks like you are returning string from your stored procedure. You should convert it to AdomdServer.MDXValue data type. Alternatively, you can implement the entire FILTER as sproc, just like it's done in ASSP - then you will return AdomdServer.Set data type.|||

When I include the namespace "Microsoft::AnalysisServices", i am getting compiler errors:

CubeInfo.cpp(6) : error C2039: 'AnalysisServices' : is not a member of 'Microsoft'
CubeInfo.cpp(6) : error C2871: 'AnalysisServices' : a namespace with this name does not exist

Do i need to upgrade or include some other files in order to get around these errors? I am currently using Microsoft Visual C++ .NET 2003 version.

Any pointers on this would be highly appreciated as it is blocking my work :-(

Thanks in advance,
Santosh.

|||

Hello Santosh,

You need to add a reference to server object model library, which is listed between .NET libraries as Microsoft.AnalysisServices.AdomdServer. If it’s not listed you can browse to find the Microsoft SQL Server\MSSQL.2\OLAP\bin\msmgdsrv.dll.

Hope this helps,

Irina

P.S. Officially we are not supporting stored procedures written with CLR from .Net 2003, I don't think that this particular error is caused by the version mistmatch.

|||

Hi Irina,

How to add this reference in C++? I guess you are referencing to C# in your post? Can you tell me how to add it for a .NET C++ project?

Thanks,

Santosh.

|||

Hi Santosh,

The following KB article explains how to do this: http://support.microsoft.com/kb/310674

But may I ask you a question? Why do you use managed C++ to create stored procedures"? I'm an avid follower of C++ too, but it seems that when creating a .NET application a C# is much better choice.

|||

The reason I am using C++ is my existing solution is in C++. Now, I want to add just another stored procedure project to it, rather than creating a new solution for C#.

I tried following the steps mentioned in that article:

In Visual C++ .NET 2003

By using Visual C++ .NET 2003, you can add a reference by means of the Add Reference dialog box. To add a project reference, follow these steps:

1.

In Solution Explorer, select the project.

2.

On the Project menu, click Add References.

3.

In the Add References dialog box, click the tab that corresponds with the category that you want to add a reference to.

4.

Click Browse, locate the component that you want on your local drive, and then click OK. The component is added to the Selected Components field.

5.

To add the selected reference to the current tab, click Add.

But, I am getting an error saying:

"Add Reference: Error adding reference to the project"

Any help?

Thanks,
Santosh.