error: identifier stored proc name is out of scope

Does anyone know what this error message is telling me.

Thanks in advance everyone.

RBCan you show us the code that is generating this error?

Terri|||Terri,

 Public Shared Function GetSelectedFunds() As DataTable
 'Dim GlobalConnString As String = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
 'Dim SelectedCenter As Integer = ddlCenters.SelectedItem.Value
 Try
 Return ExecuteDataset(SqlHelper.GlobalConnString, CommandType.StoredProcedure, "GetSelectedFOIFunds", New SqlParameter("@.ParentFund", Test.SelectedCenter)).Tables(0)
 Catch Ex As Exception
 Throw New ApplicationException("An error occurred while executing GetSelectedFunds", Ex)
 End Try
 End Function
 Sub subCenterListChange(ByVal S As Object, ByVal E As EventArgs)
 SelectedCenter = ddlCenters.SelectedItem.Value
 ddlFOI.DataSource = GetSelectedFunds()
 ddlFOI.DataBind()
 End Sub

What i'm trying to do is once a user makes a selection on the first dropdown list then I want to fire off the second drop down list and fill it with a result set that is based on the first selection.

For some reason it is not firing off and it is saying the following:

''error: identifier 'stored procedure name' is out of scope'

Any help is appricated:

Thanks again

RB

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: Could not create an acceptable cursor.

I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs.

The error I get is:

OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.

The full script I am running is:


CREATE procedure [dbo].[proc_AuditServer]
as

/*
** Auditing Script for SQL Servers.
**
** D Maxwell, June 2007
**
** This script takes configuration and job status information
** and writes it to a designated logging server. I'll describe
** each section in detail, below. We write to the local box first,
** Then upload everything to the logging server.
**
** This is the SQL 2005 version.
*/

/*
** We want to know exactly what server this is, so
** we get the server name, instance name, as well as
** SQL Version, Edition, and Service Pack level.
*/


truncate table admin.dbo.sql_servers

insert into admin.dbo.sql_servers
select convert(varchar(15), serverproperty('ServerName')),
convert(varchar(25), serverproperty('InstanceName')),
convert(char(9), serverproperty('ProductVersion')),
convert(varchar(4), serverproperty('ProductLevel')),
convert(varchar(20), serverproperty('Edition')),
getdate()

/*
** Now, having that, we get the list of databases,
** as well as thier creation dates and file names.
*/

truncate table admin.dbo.databases

insert into admin.dbo.databases
select
convert(varchar(15), serverproperty('ServerName')),
dbid,
name,
crdate,
filename
from master..sysdatabases
where dbid > 4
order by dbid

/*
** We need to know how the server is configured, so we
** can compare it to a list of preferred configuration
** values, as well as the defaults. I cut this out of
** sp_configure.
*/

truncate table admin.dbo.server_config

insert into admin.dbo.server_config
select
convert(varchar(15), serverproperty('ServerName')),
name,
config_value = c.value,
run_value = master.dbo.syscurconfigs.value
from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs
where type = 'C'
and number = c.config
and number = master.dbo.syscurconfigs.config

and
((c.status & 2 <> 0 )
OR
(c.status & 2 = 0)
)
order by lower(name)

/*
** The next configuration item we want to get is the
** list of jobs that run on the server. We're looking
** specifically for backup and other maintenance jobs.
** (Which will hopefully be named appropriately...)
** We use Neil Boyle's job report script for this.
** My comments and changes prefaced by a 'DM:'
*/

truncate table admin.dbo.jobs

insert into admin.dbo.jobs
select
convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting
j.job_id, -- DM: More unique than a name.
convert(varchar(22), j.name) as job_name,
case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule

, case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
end as frequency

-- All the subsrings are because the times are stored as an integer with no leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00)

, substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at

,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at
from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c
where j.job_id = s.job_id and s.schedule_id = c.schedule_id
order by j.name, start_at

/*
** Now that we know what jobs we have, let's find out
** how they did recently.
*/

truncate table job_status

insert into job_status
select convert(varchar(15), serverproperty('ServerName')),
job_id, run_status, run_date,
run_time, run_duration
from msdb..sysjobhistory
where step_name = '(job outcome)' -- The last 90 days' worth.
and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', ''))
order by run_date desc


/*
** If this server is already known to the audit server,
** we need to remove the existing data from the audit
** tables.
*/


declare @.known bit
set @.known =
(select count(*)
from [192.168.0.126].AUDITDB.dbo.sql_servers
where server_name =
(select convert(varchar(15), serverproperty('servername'))))

/*
** Now we remove the existing information from the audit tables,
** if need be.
*/

if @.known = 1
begin

delete from [192.168.0.126].AUDITDB.dbo.sql_servers
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.databases
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.server_config
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.jobs
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.job_status
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

end



/*
** Finally, we upload the new info from here to the audit server.
*/

insert into [192.168.0.126].AUDITDB.dbo.sql_servers
select * from admin.dbo.sql_servers

insert into [192.168.0.126].AUDITDB.dbo.server_config
select * from admin.dbo.server_config

insert into [192.168.0.126].AUDITDB.dbo.databases
select * from admin.dbo.databases

insert into [192.168.0.126].AUDITDB.dbo.jobs
select * from admin.dbo.jobs

insert into [192.168.0.126].AUDITDB.dbo.job_status
select * from admin.dbo.job_status



This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test.

Any ideas as to what I should look at next? Thanks.

-D.Check DB_option to see whether local and global cursors are default, SP1 for SQL2005 is older one and latest is SP2.

Also see this http://support.microsoft.com/kb/302477 that talks about hotfix.|||OK, I went back and read that article again. It says this happens "If a cursor is created on a system stored procedure that returns schema information from a remote server..." I'm sorry, but I'm still learning to program in SQL and I don't see where I'm doing that. Is it in the DELETE FROM statement? And that article talks about SQL 2000 SP2. I'm running SQL 2000 SP4 on that box. Would I still need the hotfix?

I tried this with a server running SQL2005 SP2 (Version 9.00.3159), and got the same error.

I also tried setting the cursor option on the databases involved on both servers, to both global and local, in all 4 configurations. Still getting the same error.

Any other ideas?

Error: ConnectionCheckForData (CheckforData())

Hi,
I'm running SQL 2k SP3a on a 2.2P4 workstation with 250GB
drives and Windows Server 2003 sp1. I'm running a fairly
simple stored proc and I get the following error:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I've tried changing protocols in the client utiltiy (tcp
and named pipes) as suggested elsewhere. There are no
errors in the application log or the sql log.
Any ideas what is happening? The same query doesn't
crash on W2k Server/SQL Server 2k. It happens repeatedly,
but sometimes on the first, second or third execution of
the stored proc.
Thanks for any insight.Fred,
You could try to adjust couple of parameters of your SQL
Server:
network packet size (B):
default 4096, change it to eg. 1024
remote query timeout (s):
default 30 or 60, change it to eg. 600
You can see the current configuration with "sp_configure".
You could also design your procedure to interact
with "the client program" more frequently. One
possibility is to add rows like
PRINT 'Just sending something to the interface'
to your procedure. The idea is to keep the connection
alive.
I've tested these and it helped with some procedures (but
not with everyone).
If anyone has any other ideas, please let us know!
.mika
>--Original Message--
>Hi,
>I'm running SQL 2k SP3a on a 2.2P4 workstation with
250GB
>drives and Windows Server 2003 sp1. I'm running a
fairly
>simple stored proc and I get the following error:
>[Microsoft][ODBC SQL Server Driver][Shared Memory]
>ConnectionCheckForData (CheckforData()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation.
>Connection Broken
>I've tried changing protocols in the client utiltiy (tcp
>and named pipes) as suggested elsewhere. There are no
>errors in the application log or the sql log.
>Any ideas what is happening? The same query doesn't
>crash on W2k Server/SQL Server 2k. It happens
repeatedly,
>but sometimes on the first, second or third execution of
>the stored proc.
>Thanks for any insight.
>.
>|||Hi Fred,
Have had this problem recently, it seems a common error with quite a
few different symptoms. I will tell you our story, which may or may
not help.
For our case, we had this error executing dynamic SQL over ADO.NET,
but could also recreate the problem via Query Analyser using different
protocols.
We were using a complex query and got either your error or the
following:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
We only got this error under certain conditions but could reproduce it
faithfully under those conditions.
Sniffing around the newsgroups, the general opinion was that if you
changed the query slightly, then in some cases the problem just "went
away".
I found 2 solutions to our problem.
Solution 1
Our query was using a view and by putting a "TOP 100 PERCENT" clause
in the view the problem went away. This is the solution we went with.
Solution 2
By using "OPTION(MERGE JOIN)" or "OPTION(LOOP JOIN)" in the SQL
statement, the problem went away. By forcing "OPTION(HASH JOIN)" the
problem re-appeared.
My theory is that merge joins were causing the problem (possibly in
conjunction with parallel query execution) but can't prove anything
because when you get this problem, you can't even get a query plan.
Looking at the query plan after using "TOP 100 PERCENT" I noticed that
there were no merge joins, although that proves nothing.
I wish you luck.
"Fred Jones" <anonymous@.discussions.microsoft.com> wrote in message news:<98f701c3ea99$2d681e80$a501280a@.phx.gbl>...
> Hi,
> I'm running SQL 2k SP3a on a 2.2P4 workstation with 250GB
> drives and Windows Server 2003 sp1. I'm running a fairly
> simple stored proc and I get the following error:
> [Microsoft][ODBC SQL Server Driver][Shared Memory]
> ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> I've tried changing protocols in the client utiltiy (tcp
> and named pipes) as suggested elsewhere. There are no
> errors in the application log or the sql log.
> Any ideas what is happening? The same query doesn't
> crash on W2k Server/SQL Server 2k. It happens repeatedly,
> but sometimes on the first, second or third execution of
> the stored proc.
> Thanks for any insight.

Error: ConnectionCheckForData (CheckforData())

Hi,
I'm running SQL 2k SP3a on a 2.2P4 workstation with 250GB
drives and Windows Server 2003 sp1. I'm running a fairly
simple stored proc and I get the following error:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I've tried changing protocols in the client utiltiy (tcp
and named pipes) as suggested elsewhere. There are no
errors in the application log or the sql log.
Any ideas what is happening? The same query doesn't
crash on W2k Server/SQL Server 2k. It happens repeatedly,
but sometimes on the first, second or third execution of
the stored proc.
Thanks for any insight.Hi Fred,
Have had this problem recently, it seems a common error with quite a
few different symptoms. I will tell you our story, which may or may
not help.
For our case, we had this error executing dynamic SQL over ADO.NET,
but could also recreate the problem via Query Analyser using different
protocols.
We were using a complex query and got either your error or the
following:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
We only got this error under certain conditions but could reproduce it
faithfully under those conditions.
Sniffing around the newsgroups, the general opinion was that if you
changed the query slightly, then in some cases the problem just "went
away".
I found 2 solutions to our problem.
Solution 1
Our query was using a view and by putting a "TOP 100 PERCENT" clause
in the view the problem went away. This is the solution we went with.
Solution 2
By using "OPTION(MERGE JOIN)" or "OPTION(LOOP JOIN)" in the SQL
statement, the problem went away. By forcing "OPTION(HASH JOIN)" the
problem re-appeared.
My theory is that merge joins were causing the problem (possibly in
conjunction with parallel query execution) but can't prove anything
because when you get this problem, you can't even get a query plan.
Looking at the query plan after using "TOP 100 PERCENT" I noticed that
there were no merge joins, although that proves nothing.
I wish you luck.
"Fred Jones" <anonymous@.discussions.microsoft.com> wrote in message news:<98f701c3ea99$2
d681e80$a501280a@.phx.gbl>...
> Hi,
> I'm running SQL 2k SP3a on a 2.2P4 workstation with 250GB
> drives and Windows Server 2003 sp1. I'm running a fairly
> simple stored proc and I get the following error:
> [Microsoft][ODBC SQL Server Driver][Shared Memory]
> ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> I've tried changing protocols in the client utiltiy (tcp
> and named pipes) as suggested elsewhere. There are no
> errors in the application log or the sql log.
> Any ideas what is happening? The same query doesn't
> crash on W2k Server/SQL Server 2k. It happens repeatedly,
> but sometimes on the first, second or third execution of
> the stored proc.
> Thanks for any insight.sql

Error: 605, Severity: 21, State: 1

Environment:
SQL SERVER 2000 sp3 with security patch 818
Cluster environment: active-passive
HP running Windows 2003 with patches
Situation:
A stored procedure is running along with several other BCP
running.
I truned on DBCC TRACEON (818 -1)
I would like help with this error:
ERROR:
DATE/TIME: 10/1/2003 10:16:52 AM
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:3360903) in
database 'WaveAggDetach' belongs to
object 'aggTradePlanVolume', not to
object 'aggTradePlanVolumeInvoiced'.
COMMENT: (None)
JOB RUN: (None)Does the database come out clean from a DBCC CHECKDB?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Katherine" <kphelps@.oceanspray.com> wrote in message news:01ee01c3883e$1675e1c0$a401280a@.phx.gbl...
> Environment:
> SQL SERVER 2000 sp3 with security patch 818
> Cluster environment: active-passive
> HP running Windows 2003 with patches
> Situation:
> A stored procedure is running along with several other BCP
> running.
> I truned on DBCC TRACEON (818 -1)
> I would like help with this error:
> ERROR:
> DATE/TIME: 10/1/2003 10:16:52 AM
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:3360903) in
> database 'WaveAggDetach' belongs to
> object 'aggTradePlanVolume', not to
> object 'aggTradePlanVolumeInvoiced'.
> COMMENT: (None)
> JOB RUN: (None)
>
>|||Does the stored procedure contain a cursor..?
If so try changing the cursor type to INSESNSITIVE.
Have a look at :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q156662
--
HTH
Ryan Waight, MCDBA, MCSE
"Katherine" <kphelps@.oceanspray.com> wrote in message
news:01ee01c3883e$1675e1c0$a401280a@.phx.gbl...
> Environment:
> SQL SERVER 2000 sp3 with security patch 818
> Cluster environment: active-passive
> HP running Windows 2003 with patches
> Situation:
> A stored procedure is running along with several other BCP
> running.
> I truned on DBCC TRACEON (818 -1)
> I would like help with this error:
> ERROR:
> DATE/TIME: 10/1/2003 10:16:52 AM
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:3360903) in
> database 'WaveAggDetach' belongs to
> object 'aggTradePlanVolume', not to
> object 'aggTradePlanVolumeInvoiced'.
> COMMENT: (None)
> JOB RUN: (None)
>
>

Error: 17968, Severity: 15, State: 1

I am running a job every night. It runs fine until last
saturday. In job we are executing 25 stored procedures.
We are getting error after running 12 procedures and
gives error -- "Line 1: Incorrect syntax near '>'.
[SQLSTATE 42000] (Error 170). The step failed". Actually
There is no syntax error. When I checked error log it
shows -- "Operating system error 170., Line 1: Incorrect
syntax near '>'.2004-08-23 20:33:07.27 logon Login
succeeded for user 'US\dashbrd'. Connection: Trusted.
Error encountered while executing xp_sendmail @.query..
Error: 17968, Severity: 15, State: 1 ".
We are running job again starting with stored procedure
12. It is running fine. I don't understand why it
happens? Can any one help melittle tip , when you Operating System Error x , type NET HELPMSG x to see
what it corresponds to , sometimes a valuable clue to whats going on
ie NET HELPMSG 170 gives you "the requested resource was in use"
Andy.
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
> I am running a job every night. It runs fine until last
> saturday. In job we are executing 25 stored procedures.
> We are getting error after running 12 procedures and
> gives error -- "Line 1: Incorrect syntax near '>'.
> [SQLSTATE 42000] (Error 170). The step failed". Actually
> There is no syntax error. When I checked error log it
> shows -- "Operating system error 170., Line 1: Incorrect
> syntax near '>'.2004-08-23 20:33:07.27 logon Login
> succeeded for user 'US\dashbrd'. Connection: Trusted.
> Error encountered while executing xp_sendmail @.query..
> Error: 17968, Severity: 15, State: 1 ".
> We are running job again starting with stored procedure
> 12. It is running fine. I don't understand why it
> happens? Can any one help me|||In error it shows error occured during xp_Sendmail, but
we are not sending any message at that time. Why it
looking for xp_sendmail?In stored procedure we are
updating temp table and deleting temp table after
inserting data into original table. Is while deleting
temp table causes this problem?
>--Original Message--
>little tip , when you Operating System Error x , type
NET HELPMSG x to see
>what it corresponds to , sometimes a valuable clue to
whats going on
>ie NET HELPMSG 170 gives you "the requested resource was
in use"
>Andy.
>"kishore" <anonymous@.discussions.microsoft.com> wrote in
message
>news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
Actually[vbcol=seagreen]
Incorrect[vbcol=seagreen]
>
>.
>|||check to see if there are any triggers on the tables being updated.
Andy.
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...[vbcol=seagreen]
> In error it shows error occured during xp_Sendmail, but
> we are not sending any message at that time. Why it
> looking for xp_sendmail?In stored procedure we are
> updating temp table and deleting temp table after
> inserting data into original table. Is while deleting
> temp table causes this problem?
> NET HELPMSG x to see
> whats going on
> in use"
> message
> Actually
> Incorrect|||No triggers are firing. Up to last week it ran fine.

>--Original Message--
>check to see if there are any triggers on the tables
being updated.
>Andy.
>"kishore" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...
was[vbcol=seagreen]
in[vbcol=seagreen]
last[vbcol=seagreen]
procedures.[vbcol=seagreen]
it[vbcol=seagreen]
Login[vbcol=seagreen]
Trusted.[vbcol=seagreen]
@.query..[vbcol=seagreen]
procedure[vbcol=seagreen]
>
>.
>|||Hi
You may want to try profiling this and see what is being sent to the server.
It sounds as if it could be an unescaped quote type of problem' Is there
any dynamic SQL being used?
John
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:1c0f01c48cfb$58107510$a601280a@.phx.gbl...[vbcol=seagreen]
> No triggers are firing. Up to last week it ran fine.
>
> being updated.
> message
> was
> in
> last
> procedures.
> it
> Login
> Trusted.
> @.query..
> procedure|||Hi
I can't really suggest any more except maybe dropping and recreating all the
procedures and the job.
John
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:093a01c48ea5$487d3650$a401280a@.phx.gbl...[vbcol=seagreen]
> Created a trace file using profiler. It doesn't help me.
> There is no dynamic sql. In profiler it shows stored
> procedure is completed successfully. After completing the
> stored proc,it sending message as step is failed. Since
> step fails, it doesn't go to the next step.I don't
> understand what is the cause for this error?
>
> sent to the server.
> problem' Is there
> message
> in
> but
> deleting
> type
> to
> resource
> wrote
> until
> and
> near '>'.
> log

Error: 17968, Severity: 15, State: 1

I am running a job every night. It runs fine until last
saturday. In job we are executing 25 stored procedures.
We are getting error after running 12 procedures and
gives error -- "Line 1: Incorrect syntax near '>'.
[SQLSTATE 42000] (Error 170). The step failed". Actually
There is no syntax error. When I checked error log it
shows -- "Operating system error 170., Line 1: Incorrect
syntax near '>'.2004-08-23 20:33:07.27 logon Login
succeeded for user 'US\dashbrd'. Connection: Trusted.
Error encountered while executing xp_sendmail @.query..
Error: 17968, Severity: 15, State: 1 ".
We are running job again starting with stored procedure
12. It is running fine. I don't understand why it
happens? Can any one help melittle tip , when you Operating System Error x , type NET HELPMSG x to see
what it corresponds to , sometimes a valuable clue to whats going on
ie NET HELPMSG 170 gives you "the requested resource was in use"
Andy.
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
> I am running a job every night. It runs fine until last
> saturday. In job we are executing 25 stored procedures.
> We are getting error after running 12 procedures and
> gives error -- "Line 1: Incorrect syntax near '>'.
> [SQLSTATE 42000] (Error 170). The step failed". Actually
> There is no syntax error. When I checked error log it
> shows -- "Operating system error 170., Line 1: Incorrect
> syntax near '>'.2004-08-23 20:33:07.27 logon Login
> succeeded for user 'US\dashbrd'. Connection: Trusted.
> Error encountered while executing xp_sendmail @.query..
> Error: 17968, Severity: 15, State: 1 ".
> We are running job again starting with stored procedure
> 12. It is running fine. I don't understand why it
> happens? Can any one help me|||In error it shows error occured during xp_Sendmail, but
we are not sending any message at that time. Why it
looking for xp_sendmail?In stored procedure we are
updating temp table and deleting temp table after
inserting data into original table. Is while deleting
temp table causes this problem?
>--Original Message--
>little tip , when you Operating System Error x , type
NET HELPMSG x to see
>what it corresponds to , sometimes a valuable clue to
whats going on
>ie NET HELPMSG 170 gives you "the requested resource was
in use"
>Andy.
>"kishore" <anonymous@.discussions.microsoft.com> wrote in
message
>news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
>> I am running a job every night. It runs fine until last
>> saturday. In job we are executing 25 stored procedures.
>> We are getting error after running 12 procedures and
>> gives error -- "Line 1: Incorrect syntax near '>'.
>> [SQLSTATE 42000] (Error 170). The step failed".
Actually
>> There is no syntax error. When I checked error log it
>> shows -- "Operating system error 170., Line 1:
Incorrect
>> syntax near '>'.2004-08-23 20:33:07.27 logon Login
>> succeeded for user 'US\dashbrd'. Connection: Trusted.
>> Error encountered while executing xp_sendmail @.query..
>> Error: 17968, Severity: 15, State: 1 ".
>> We are running job again starting with stored procedure
>> 12. It is running fine. I don't understand why it
>> happens? Can any one help me
>
>.
>|||check to see if there are any triggers on the tables being updated.
Andy.
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...
> In error it shows error occured during xp_Sendmail, but
> we are not sending any message at that time. Why it
> looking for xp_sendmail?In stored procedure we are
> updating temp table and deleting temp table after
> inserting data into original table. Is while deleting
> temp table causes this problem?
> >--Original Message--
> >little tip , when you Operating System Error x , type
> NET HELPMSG x to see
> >what it corresponds to , sometimes a valuable clue to
> whats going on
> >
> >ie NET HELPMSG 170 gives you "the requested resource was
> in use"
> >
> >Andy.
> >"kishore" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
> >> I am running a job every night. It runs fine until last
> >> saturday. In job we are executing 25 stored procedures.
> >> We are getting error after running 12 procedures and
> >> gives error -- "Line 1: Incorrect syntax near '>'.
> >> [SQLSTATE 42000] (Error 170). The step failed".
> Actually
> >> There is no syntax error. When I checked error log it
> >> shows -- "Operating system error 170., Line 1:
> Incorrect
> >> syntax near '>'.2004-08-23 20:33:07.27 logon Login
> >> succeeded for user 'US\dashbrd'. Connection: Trusted.
> >>
> >> Error encountered while executing xp_sendmail @.query..
> >>
> >> Error: 17968, Severity: 15, State: 1 ".
> >>
> >> We are running job again starting with stored procedure
> >> 12. It is running fine. I don't understand why it
> >> happens? Can any one help me
> >
> >
> >.
> >|||No triggers are firing. Up to last week it ran fine.
>--Original Message--
>check to see if there are any triggers on the tables
being updated.
>Andy.
>"kishore" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...
>> In error it shows error occured during xp_Sendmail, but
>> we are not sending any message at that time. Why it
>> looking for xp_sendmail?In stored procedure we are
>> updating temp table and deleting temp table after
>> inserting data into original table. Is while deleting
>> temp table causes this problem?
>> >--Original Message--
>> >little tip , when you Operating System Error x , type
>> NET HELPMSG x to see
>> >what it corresponds to , sometimes a valuable clue to
>> whats going on
>> >
>> >ie NET HELPMSG 170 gives you "the requested resource
was
>> in use"
>> >
>> >Andy.
>> >"kishore" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
>> >> I am running a job every night. It runs fine until
last
>> >> saturday. In job we are executing 25 stored
procedures.
>> >> We are getting error after running 12 procedures and
>> >> gives error -- "Line 1: Incorrect syntax near '>'.
>> >> [SQLSTATE 42000] (Error 170). The step failed".
>> Actually
>> >> There is no syntax error. When I checked error log
it
>> >> shows -- "Operating system error 170., Line 1:
>> Incorrect
>> >> syntax near '>'.2004-08-23 20:33:07.27 logon
Login
>> >> succeeded for user 'US\dashbrd'. Connection:
Trusted.
>> >>
>> >> Error encountered while executing xp_sendmail
@.query..
>> >>
>> >> Error: 17968, Severity: 15, State: 1 ".
>> >>
>> >> We are running job again starting with stored
procedure
>> >> 12. It is running fine. I don't understand why it
>> >> happens? Can any one help me
>> >
>> >
>> >.
>> >
>
>.
>|||Hi
You may want to try profiling this and see what is being sent to the server.
It sounds as if it could be an unescaped quote type of problem' Is there
any dynamic SQL being used?
John
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:1c0f01c48cfb$58107510$a601280a@.phx.gbl...
> No triggers are firing. Up to last week it ran fine.
>
> >--Original Message--
> >check to see if there are any triggers on the tables
> being updated.
> >
> >Andy.
> >"kishore" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...
> >> In error it shows error occured during xp_Sendmail, but
> >> we are not sending any message at that time. Why it
> >> looking for xp_sendmail?In stored procedure we are
> >> updating temp table and deleting temp table after
> >> inserting data into original table. Is while deleting
> >> temp table causes this problem?
> >> >--Original Message--
> >> >little tip , when you Operating System Error x , type
> >> NET HELPMSG x to see
> >> >what it corresponds to , sometimes a valuable clue to
> >> whats going on
> >> >
> >> >ie NET HELPMSG 170 gives you "the requested resource
> was
> >> in use"
> >> >
> >> >Andy.
> >> >"kishore" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
> >> >> I am running a job every night. It runs fine until
> last
> >> >> saturday. In job we are executing 25 stored
> procedures.
> >> >> We are getting error after running 12 procedures and
> >> >> gives error -- "Line 1: Incorrect syntax near '>'.
> >> >> [SQLSTATE 42000] (Error 170). The step failed".
> >> Actually
> >> >> There is no syntax error. When I checked error log
> it
> >> >> shows -- "Operating system error 170., Line 1:
> >> Incorrect
> >> >> syntax near '>'.2004-08-23 20:33:07.27 logon
> Login
> >> >> succeeded for user 'US\dashbrd'. Connection:
> Trusted.
> >> >>
> >> >> Error encountered while executing xp_sendmail
> @.query..
> >> >>
> >> >> Error: 17968, Severity: 15, State: 1 ".
> >> >>
> >> >> We are running job again starting with stored
> procedure
> >> >> 12. It is running fine. I don't understand why it
> >> >> happens? Can any one help me
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Created a trace file using profiler. It doesn't help me.
There is no dynamic sql. In profiler it shows stored
procedure is completed successfully. After completing the
stored proc,it sending message as step is failed. Since
step fails, it doesn't go to the next step.I don't
understand what is the cause for this error?
>--Original Message--
>Hi
>You may want to try profiling this and see what is being
sent to the server.
>It sounds as if it could be an unescaped quote type of
problem' Is there
>any dynamic SQL being used?
>John
>"kishore" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c0f01c48cfb$58107510$a601280a@.phx.gbl...
>> No triggers are firing. Up to last week it ran fine.
>>
>> >--Original Message--
>> >check to see if there are any triggers on the tables
>> being updated.
>> >
>> >Andy.
>> >"kishore" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...
>> >> In error it shows error occured during xp_Sendmail,
but
>> >> we are not sending any message at that time. Why it
>> >> looking for xp_sendmail?In stored procedure we are
>> >> updating temp table and deleting temp table after
>> >> inserting data into original table. Is while
deleting
>> >> temp table causes this problem?
>> >> >--Original Message--
>> >> >little tip , when you Operating System Error x ,
type
>> >> NET HELPMSG x to see
>> >> >what it corresponds to , sometimes a valuable clue
to
>> >> whats going on
>> >> >
>> >> >ie NET HELPMSG 170 gives you "the requested
resource
>> was
>> >> in use"
>> >> >
>> >> >Andy.
>> >> >"kishore" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message
>> >> >news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
>> >> >> I am running a job every night. It runs fine
until
>> last
>> >> >> saturday. In job we are executing 25 stored
>> procedures.
>> >> >> We are getting error after running 12 procedures
and
>> >> >> gives error -- "Line 1: Incorrect syntax
near '>'.
>> >> >> [SQLSTATE 42000] (Error 170). The step failed".
>> >> Actually
>> >> >> There is no syntax error. When I checked error
log
>> it
>> >> >> shows -- "Operating system error 170., Line 1:
>> >> Incorrect
>> >> >> syntax near '>'.2004-08-23 20:33:07.27 logon
>> Login
>> >> >> succeeded for user 'US\dashbrd'. Connection:
>> Trusted.
>> >> >>
>> >> >> Error encountered while executing xp_sendmail
>> @.query..
>> >> >>
>> >> >> Error: 17968, Severity: 15, State: 1 ".
>> >> >>
>> >> >> We are running job again starting with stored
>> procedure
>> >> >> 12. It is running fine. I don't understand why it
>> >> >> happens? Can any one help me
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||Hi
I can't really suggest any more except maybe dropping and recreating all the
procedures and the job.
John
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:093a01c48ea5$487d3650$a401280a@.phx.gbl...
> Created a trace file using profiler. It doesn't help me.
> There is no dynamic sql. In profiler it shows stored
> procedure is completed successfully. After completing the
> stored proc,it sending message as step is failed. Since
> step fails, it doesn't go to the next step.I don't
> understand what is the cause for this error?
>
> >--Original Message--
> >Hi
> >
> >You may want to try profiling this and see what is being
> sent to the server.
> >It sounds as if it could be an unescaped quote type of
> problem' Is there
> >any dynamic SQL being used?
> >
> >John
> >
> >"kishore" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1c0f01c48cfb$58107510$a601280a@.phx.gbl...
> >> No triggers are firing. Up to last week it ran fine.
> >>
> >>
> >> >--Original Message--
> >> >check to see if there are any triggers on the tables
> >> being updated.
> >> >
> >> >Andy.
> >> >"kishore" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...
> >> >> In error it shows error occured during xp_Sendmail,
> but
> >> >> we are not sending any message at that time. Why it
> >> >> looking for xp_sendmail?In stored procedure we are
> >> >> updating temp table and deleting temp table after
> >> >> inserting data into original table. Is while
> deleting
> >> >> temp table causes this problem?
> >> >> >--Original Message--
> >> >> >little tip , when you Operating System Error x ,
> type
> >> >> NET HELPMSG x to see
> >> >> >what it corresponds to , sometimes a valuable clue
> to
> >> >> whats going on
> >> >> >
> >> >> >ie NET HELPMSG 170 gives you "the requested
> resource
> >> was
> >> >> in use"
> >> >> >
> >> >> >Andy.
> >> >> >"kishore" <anonymous@.discussions.microsoft.com>
> wrote
> >> in
> >> >> message
> >> >> >news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
> >> >> >> I am running a job every night. It runs fine
> until
> >> last
> >> >> >> saturday. In job we are executing 25 stored
> >> procedures.
> >> >> >> We are getting error after running 12 procedures
> and
> >> >> >> gives error -- "Line 1: Incorrect syntax
> near '>'.
> >> >> >> [SQLSTATE 42000] (Error 170). The step failed".
> >> >> Actually
> >> >> >> There is no syntax error. When I checked error
> log
> >> it
> >> >> >> shows -- "Operating system error 170., Line 1:
> >> >> Incorrect
> >> >> >> syntax near '>'.2004-08-23 20:33:07.27 logon
> >> Login
> >> >> >> succeeded for user 'US\dashbrd'. Connection:
> >> Trusted.
> >> >> >>
> >> >> >> Error encountered while executing xp_sendmail
> >> @.query..
> >> >> >>
> >> >> >> Error: 17968, Severity: 15, State: 1 ".
> >> >> >>
> >> >> >> We are running job again starting with stored
> >> procedure
> >> >> >> 12. It is running fine. I don't understand why it
> >> >> >> happens? Can any one help me
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Error: 17968, Severity: 15, State: 1

I am running a job every night. It runs fine until last
saturday. In job we are executing 25 stored procedures.
We are getting error after running 12 procedures and
gives error -- "Line 1: Incorrect syntax near '>'.
[SQLSTATE 42000] (Error 170). The step failed". Actually
There is no syntax error. When I checked error log it
shows -- "Operating system error 170., Line 1: Incorrect
syntax near '>'.2004-08-23 20:33:07.27 logon Login
succeeded for user 'US\dashbrd'. Connection: Trusted.
Error encountered while executing xp_sendmail @.query..
Error: 17968, Severity: 15, State: 1 ".
We are running job again starting with stored procedure
12. It is running fine. I don't understand why it
happens? Can any one help me
little tip , when you Operating System Error x , type NET HELPMSG x to see
what it corresponds to , sometimes a valuable clue to whats going on
ie NET HELPMSG 170 gives you "the requested resource was in use"
Andy.
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
> I am running a job every night. It runs fine until last
> saturday. In job we are executing 25 stored procedures.
> We are getting error after running 12 procedures and
> gives error -- "Line 1: Incorrect syntax near '>'.
> [SQLSTATE 42000] (Error 170). The step failed". Actually
> There is no syntax error. When I checked error log it
> shows -- "Operating system error 170., Line 1: Incorrect
> syntax near '>'.2004-08-23 20:33:07.27 logon Login
> succeeded for user 'US\dashbrd'. Connection: Trusted.
> Error encountered while executing xp_sendmail @.query..
> Error: 17968, Severity: 15, State: 1 ".
> We are running job again starting with stored procedure
> 12. It is running fine. I don't understand why it
> happens? Can any one help me
|||In error it shows error occured during xp_Sendmail, but
we are not sending any message at that time. Why it
looking for xp_sendmail?In stored procedure we are
updating temp table and deleting temp table after
inserting data into original table. Is while deleting
temp table causes this problem?
>--Original Message--
>little tip , when you Operating System Error x , type
NET HELPMSG x to see
>what it corresponds to , sometimes a valuable clue to
whats going on
>ie NET HELPMSG 170 gives you "the requested resource was
in use"
>Andy.
>"kishore" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:10d201c48bd5$fa04a5c0$a301280a@.phx.gbl...
Actually[vbcol=seagreen]
Incorrect
>
>.
>
|||check to see if there are any triggers on the tables being updated.
Andy.
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...[vbcol=seagreen]
> In error it shows error occured during xp_Sendmail, but
> we are not sending any message at that time. Why it
> looking for xp_sendmail?In stored procedure we are
> updating temp table and deleting temp table after
> inserting data into original table. Is while deleting
> temp table causes this problem?
> NET HELPMSG x to see
> whats going on
> in use"
> message
> Actually
> Incorrect
|||No triggers are firing. Up to last week it ran fine.

>--Original Message--
>check to see if there are any triggers on the tables
being updated.
>Andy.
>"kishore" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:09cd01c48c3b$01e37720$a401280a@.phx.gbl...
was[vbcol=seagreen]
in[vbcol=seagreen]
last[vbcol=seagreen]
procedures.[vbcol=seagreen]
it[vbcol=seagreen]
Login[vbcol=seagreen]
Trusted.[vbcol=seagreen]
@.query..[vbcol=seagreen]
procedure
>
>.
>
|||Hi
You may want to try profiling this and see what is being sent to the server.
It sounds as if it could be an unescaped quote type of problem? Is there
any dynamic SQL being used?
John
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:1c0f01c48cfb$58107510$a601280a@.phx.gbl...[vbcol=seagreen]
> No triggers are firing. Up to last week it ran fine.
>
> being updated.
> message
> was
> in
> last
> procedures.
> it
> Login
> Trusted.
> @.query..
> procedure
|||Hi
I can't really suggest any more except maybe dropping and recreating all the
procedures and the job.
John
"kishore" <anonymous@.discussions.microsoft.com> wrote in message
news:093a01c48ea5$487d3650$a401280a@.phx.gbl...[vbcol=seagreen]
> Created a trace file using profiler. It doesn't help me.
> There is no dynamic sql. In profiler it shows stored
> procedure is completed successfully. After completing the
> stored proc,it sending message as step is failed. Since
> step fails, it doesn't go to the next step.I don't
> understand what is the cause for this error?
>
> sent to the server.
> problem? Is there
> message
> in
> but
> deleting
> type
> to
> resource
> wrote
> until
> and
> near '>'.
> log

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 Working With DTS -- "Class Not Registered"

I have a DTS package (stored in a .dts file) that was created in SQL Server 2000 and I am trying to work with it in SQL 2005. When I try to import it into the Legacy > Data Transformation Services node in Management Studio, I get a window that reads "Class Not Registered (Microsoft Data Transformation Services (DTS) Package)". I get the same error if I try to just execute the package itself from a query window.

I have Legacy Support installed, and I also have Integration Services installed.

Any ideas? We have three installations of SQL 2005 and I am getting the same error on all of them.

Have you installed Microsoft SQL Server 2000 DTS Designer Components from here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&displaylang=en

The DTS designer does not get installed with SSIS.

Thanks.

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