Multiple-step OLE DB operation generated errors.

Database: SQL Server 2000 Standard (works fine)
Database: SQL Server 2005 Standard (fails)
Provider: SQLOLEDB
We can update recordset field (database: MS SQL 2000, client side cursor,
adLockOptimistic), regardless of the recordset's source (query or stored
procedure)
The problem begins with SQL 2005. We cannot update recordset field, if the
recordset is a result of stored procedure.
Error Source: Microsoft Cursor Engine
Error Description: Multiple-step operation generated errors. Check each
status value.> The problem begins with SQL 2005. We cannot update recordset field, if the
> recordset is a result of stored procedure.
Because that's not how you affect data. A recordset is for retrieving and
presenting data. If you want to change the data in the database, use a DML
statement (INSERT/UPDATE/DELETE). Better yet, call a stored procedure that
does that.
A|||Dmitriy Shapiro wrote:
> Database: SQL Server 2000 Standard (works fine)
> Database: SQL Server 2005 Standard (fails)
> Provider: SQLOLEDB
What development platform/language?
> We can update recordset field (database: MS SQL 2000, client side
> cursor, adLockOptimistic), regardless of the recordset's source
> (query or stored procedure)
> The problem begins with SQL 2005. We cannot update recordset field,
> if the recordset is a result of stored procedure.
I'm assuming you've used "SET NOCOUNT ON" in the procedure ...

> Error Source: Microsoft Cursor Engine
> Error Description: Multiple-step operation generated errors. Check
> each status value.
So have you looped through the connection's Errors collection to see the
error message(s)?
If you are developing for ASP, then I will echo Aaron's suggestion: use DML.
If it's a desktop application, then there are some valid reasons (handling
concurrency, etc.) for using a recordset to perform data maintenance.
If none of the above helps, you should post a repro script (DDL and
recordset code) to the group that is focussed on your development platform.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for replies. Our firewall block all notifications. Sorry.
The changes in recordset are not going back to the database. They are only
for UI. This is a legasy code.
Language: VB 6.0
Platform: Windows XP and Win 2003
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:ef4gCUrIGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Dmitriy Shapiro wrote:
> What development platform/language?
> I'm assuming you've used "SET NOCOUNT ON" in the procedure ...
>
> So have you looped through the connection's Errors collection to see the
> error message(s)?
> If you are developing for ASP, then I will echo Aaron's suggestion: use
> DML.
> If it's a desktop application, then there are some valid reasons (handling
> concurrency, etc.) for using a recordset to perform data maintenance.
> If none of the above helps, you should post a repro script (DDL and
> recordset code) to the group that is focussed on your development
> platform.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
> Thanks for replies. Our firewall block all notifications. Sorry.
' Data is trnasmitted but error messages are blocked? I don't think this is
possible.

> The changes in recordset are not going back to the database. They are
> only for UI. This is a legasy code.
I'm not sure I understand what you are saying here, or why it is relevant
that the code is legacy.

> Language: VB 6.0
> Platform: Windows XP and Win 2003
>
So you plan to follow up in a VB group ... ?
Try microsoft.public.vb.database or microsoft.public.vb.database.ado.
Also, you might try using SQL Profiler to trace the actual commands being
sent to the database by the application: it may provide a clue.
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> I'm not sure I understand what you are saying here
Here is an example:
'get recordset from database as result of "stored_procedure"
'GetData executes ADO command, with Client Side Cursor and optimistic
locking option
Set rs = GetData("stored_procedure")
'translate column1 to Spanish
'Our legasy code use this technique to translate some data, before
presenting them to the client
rs("column1").value = trnalsateToSpanish(rs("column1").value)
'show recordset in the grid
Display(rs)
These lines work fine when we connect to SQL Server 2000
They fail with SQL Server 2005

>why it is relevant
> that the code is legacy.
I would like to find solution that will have minimum impact on the code.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OXigYR1IGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Dmitriy Shapiro wrote:
> ' Data is trnasmitted but error messages are blocked? I don't think this
> is
> possible.
>
> I'm not sure I understand what you are saying here, or why it is relevant
> that the code is legacy.
>
> So you plan to follow up in a VB group ... ?
> Try microsoft.public.vb.database or microsoft.public.vb.database.ado.
> Also, you might try using SQL Profiler to trace the actual commands being
> sent to the database by the application: it may provide a clue.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
<snip>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Dmitriy Shapiro wrote:
> Here is an example:
> 'get recordset from database as result of "stored_procedure"
> 'GetData executes ADO command, with Client Side Cursor and optimistic
> locking option
> Set rs = GetData("stored_procedure")
>
Unless you have set the connection's CursorLocation property to adUseClient,
this line will result in a default server-side forward-only cursor.
To have control over the cursor type, you must:
Set rs= New ADODB.Recordset
rs.CursorLocation=adUseClient
Then either use the command object as the source argument in the recordset's
Open method:
rs.Open cmd
or use the stored-procedure-as-connection-method technique to bypass the
creation of the explicit Command object:
cn.stored_procedure parm1,...parmN, rs
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob,

>Unless you have set the connection's CursorLocation property to
>adUseClient,
> this line will result in a default server-side forward-only cursor.
I do have MyDBConnection.CursorLocation = adUseClient
Before I was unable to update recordset regardless if was result of stored
procedure or query.
Now I can only do it if it result of the query.
This code works:
Set rs = GetData("select column1 from table1")
rs("column1").value = "abc"
And this code does not work with new SQL Server 2005, but works with SQL
Server 2000:
Set rs = GetData("stored_procedure")
rs("column1").value = "abc"
Thank you.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:epIvE21IGHA.208@.tk2msftngp13.phx.gbl...
> Dmitriy Shapiro wrote:
> Unless you have set the connection's CursorLocation property to
> adUseClient,
> this line will result in a default server-side forward-only cursor.
> To have control over the cursor type, you must:
> Set rs= New ADODB.Recordset
> rs.CursorLocation=adUseClient
> Then either use the command object as the source argument in the
> recordset's
> Open method:
> rs.Open cmd
> or use the stored-procedure-as-connection-method technique to bypass the
> creation of the explicit Command object:
> cn.stored_procedure parm1,...parmN, rs
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
> Bob,
>
> I do have MyDBConnection.CursorLocation = adUseClient
> Before I was unable to update recordset regardless if was result of
> stored procedure or query.
> Now I can only do it if it result of the query.
> This code works:
> Set rs = GetData("select column1 from table1")
> rs("column1").value = "abc"
> And this code does not work with new SQL Server 2005, but works with
> SQL Server 2000:
> Set rs = GetData("stored_procedure")
>
I'm sorry, but without knowing what the GetData function and the stored
procedure look like, nobody will be able to help you. An I'm sure you are
going to be able to get more help from thhe VB experts in one of the VB
newsgroups.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Multiple-step OLE DB operation generated errors

I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.

Additional Information: Visual Studio - local machine
SQL Server/Analysis Services - Machine A
Reporting Services - Machine B

See this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706585&SiteID=1 is any help.

Multiple-step OLE DB operation generated errors

Hello....

I have two linked server... (ServerB and ServerC) which reside on ServerA. I am able to connect to the remote database using "Select" statements without any issues.

When I run this query, It is successful:

delete [SERVERB].MyDatabase.dbo.TableName
from [SERVERB].MyDatabase.dbo.TableName t1
Left join MyDatabase.dbo.TableName t2 on ( t1.ID = t2.ID and
t1.EmployeeNumber = t2.EmployeeNumber and
t1.AccountNumber = t2.AccountNumber)
where t2.ID is null;

However, when I change [SERVERB] to [SERVERC], I receive two errors:

"Could not find server 'ELEARN-FRM-BETA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

And

OLE DB provider "SQLNCLI" for linked server "ELEARN-FRM-BETA" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

When I run profiler on ServerC, I see traffic... mainly a whole bunch of exec "sp_cursorfetch" operations, so I know the connection is valid.

Any ideas?

Forch

Sorry... I forgot to mention that all three servers are SQL Server 2005 x64 edition with SP1.

Forch

sql

Multiple-step OLE DB operation generated errors

I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.

Additional Information: Visual Studio - local machine
SQL Server/Analysis Services - Machine A
Reporting Services - Machine B

See this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706585&SiteID=1 is any help.

multiple tempdb files and large operation in tempdb.

Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advanceIt will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>

multiple tempdb files and large operation in tempdb.

Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
It will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:

> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:

> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>

Multiple Step Stored Procedure

Hi,
I am writing a stored procedure in SQL Server 2000 the will require
multiple commits(?) during its operation. The first group of actions
that it must perform is to modify a table by adding some columns and
altering another. The next step reads data out of the table into a
temporary (in memory) table and does some processing on it and writes
it back to the original source table utilizing the recently added
columns. When I try to put this into a stored procedure, everything
after the first GO statement gets cut off, which in this case is the
guts of the procedure. Does anyone know how how to perform multiple
operatinos like this in one stored procedure?
Thanks in advance
Chris Lieb
CREATE TABLE [HRSC] (
[SEQ_NUM] [float] NULL ,
[HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'TX750201TX769999')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'OK735001OK735002')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'LA714000')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'NM870001NM884001')
----
--
-- Add new columns to the table
ALTER TABLE
[HRSC]
ADD
[Low_State] nvarchar(3) NULL,
[Low_SLIC] nvarchar(3) NULL,
[Low_Building] nvarchar(1) NULL,
[Low_IBI] nvarchar(2) NULL,
[High_State] nvarchar(3) NULL,
[High_SLIC] nvarchar(3) NULL,
[High_Building] nvarchar(1) NULL,
[High_IBI] nvarchar(2) NULL
-- Alter the data type of the HuReSC column to be more space-efficient
ALTER TABLE
[HRSC]
ALTER COLUMN
[HuReSC] nvarchar(18)
GO
-- Prepare the temporary table
DECLARE @.hold table (
[SEQ_NUM] [float] NULL ,
[HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Low_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[High_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[High_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[High_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[High_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
-- Populate the temporary table with data
INSERT INTO
@.hold ([SEQ_NUM], [HuReSC])
SELECT
[SEQ_NUM],
[HuReSC]
FROM
[HRSC
-- Parse data for low end of range
UPDATE
@.hold
SET
[Low_State] = SUBSTRING([HuReSC], 1, 2),
[Low_SLIC] = SUBSTRING([HuReSC], 3, 3),
[Low_Building] = SUBSTRING([HuReSC], 6, 1),
[Low_IBI] = SUBSTRING([HuReSC], 7, 2)
-- Parse data for high end of range where needed
UPDATE
@.hold
SET
[High_State] = SUBSTRING([HuReSC], 9, 2),
[High_SLIC] = SUBSTRING([HuReSC], 11, 3),
[High_Building] = SUBSTRING([HuReSC], 14, 1),
[High_IBI] = SUBSTRING([HuReSC], 15, 2)
WHERE
LEN([HuReSC]) > 8
-- Clear the real table
TRUNCATE TABLE [HRSC]
-- Populate the table with the parsed data
INSERT INTO
[HRSC]
SELECT
*
FROM
@.hold
GOSince you know the table structure up front, why on earth are you doing
this:
CREATE TABLE
INSERT data
ALTER TABLE add columns
ALTER TABLE ALTER COLUMN but it really doesn't change
INSERT more data
Why not just
CREATE TABLE
INSERT data
The problem here is that you are saying
CREATE PROCEDURE
...do some stuff...
GO
...do more stuff...
The GO is a batch separator, and hence that is where your CREATE PROCEDURE
is completed. Everything after that has nothing to do with the stored
procedure at all. To avoid getting bitten by this, I always use the
following struct:
CREATE PROCEDURE dbo.whatever
AS
BEGIN
... code...
END
GO
Now, if I inadvertently have a GO inside the stored procedure, it won't run,
unless I have horrible code, because the BEGIN and END do not match up.
Of course, if you remove the GO, you will not be able to refer to
non-existant columns within the same batch because the parser will
read-ahead and try to match your column list to the original CREATE TABLE.
So maybe you could explain why you're creating a table, then inserting data,
then adding nullable columns, then altering a column to be the same as it
was before.
A
"Chris Lieb" <chris.lieb@.gmail.com> wrote in message
news:1133819619.878838.109740@.z14g2000cwz.googlegroups.com...
> Hi,
> I am writing a stored procedure in SQL Server 2000 the will require
> multiple commits(?) during its operation. The first group of actions
> that it must perform is to modify a table by adding some columns and
> altering another. The next step reads data out of the table into a
> temporary (in memory) table and does some processing on it and writes
> it back to the original source table utilizing the recently added
> columns. When I try to put this into a stored procedure, everything
> after the first GO statement gets cut off, which in this case is the
> guts of the procedure. Does anyone know how how to perform multiple
> operatinos like this in one stored procedure?
> Thanks in advance
> Chris Lieb
> CREATE TABLE [HRSC] (
> [SEQ_NUM] [float] NULL ,
> [HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'TX750201TX769999')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'OK735001OK735002')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'LA714000')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'NM870001NM884001')
> ----
--
> -- Add new columns to the table
> ALTER TABLE
> [HRSC]
> ADD
> [Low_State] nvarchar(3) NULL,
> [Low_SLIC] nvarchar(3) NULL,
> [Low_Building] nvarchar(1) NULL,
> [Low_IBI] nvarchar(2) NULL,
> [High_State] nvarchar(3) NULL,
> [High_SLIC] nvarchar(3) NULL,
> [High_Building] nvarchar(1) NULL,
> [High_IBI] nvarchar(2) NULL
> -- Alter the data type of the HuReSC column to be more space-efficient
> ALTER TABLE
> [HRSC]
> ALTER COLUMN
> [HuReSC] nvarchar(18)
> GO
> -- Prepare the temporary table
> DECLARE @.hold table (
> [SEQ_NUM] [float] NULL ,
> [HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Low_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [High_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [High_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [High_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [High_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> -- Populate the temporary table with data
> INSERT INTO
> @.hold ([SEQ_NUM], [HuReSC])
> SELECT
> [SEQ_NUM],
> [HuReSC]
> FROM
> [HRSC
> -- Parse data for low end of range
> UPDATE
> @.hold
> SET
> [Low_State] = SUBSTRING([HuReSC], 1, 2),
> [Low_SLIC] = SUBSTRING([HuReSC], 3, 3),
> [Low_Building] = SUBSTRING([HuReSC], 6, 1),
> [Low_IBI] = SUBSTRING([HuReSC], 7, 2)
> -- Parse data for high end of range where needed
> UPDATE
> @.hold
> SET
> [High_State] = SUBSTRING([HuReSC], 9, 2),
> [High_SLIC] = SUBSTRING([HuReSC], 11, 3),
> [High_Building] = SUBSTRING([HuReSC], 14, 1),
> [High_IBI] = SUBSTRING([HuReSC], 15, 2)
> WHERE
> LEN([HuReSC]) > 8
> -- Clear the real table
> TRUNCATE TABLE [HRSC]
> -- Populate the table with the parsed data
> INSERT INTO
> [HRSC]
> SELECT
> *
> FROM
> @.hold
> GO
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Since you know the table structure up front, why on earth are you doing
> this:
> CREATE TABLE
> INSERT data
> ALTER TABLE add columns
> ALTER TABLE ALTER COLUMN but it really doesn't change
> INSERT more data
> Why not just
> CREATE TABLE
> INSERT data
The CREATE TABLE and INSERT statements were jsut so you could get the
original table set up to play with. They are not part of the routine
that I want to perform. Also, the ALTER TABLE statement does actually
change the data type of the column, it's just that the CREATE TABLE
that I gave you had already been effected by the ALTER TABLE, so it was
incorrect. I forget the original data type, but the ALTER TABLE
statement does do something.

> The problem here is that you are saying
> CREATE PROCEDURE
> ...do some stuff...
> GO
> ...do more stuff...
> The GO is a batch separator, and hence that is where your CREATE PROCEDURE
> is completed. Everything after that has nothing to do with the stored
> procedure at all. To avoid getting bitten by this, I always use the
> following struct:
> CREATE PROCEDURE dbo.whatever
> AS
> BEGIN
> ... code...
> END
> GO
> Now, if I inadvertently have a GO inside the stored procedure, it won't ru
n,
> unless I have horrible code, because the BEGIN and END do not match up.
> Of course, if you remove the GO, you will not be able to refer to
> non-existant columns within the same batch because the parser will
> read-ahead and try to match your column list to the original CREATE TABLE.
That is exactly the point: I need to have the ALTER TABLE statements
executed before the rest so that I do not get errors. If the batch is
not committed midway, SQL Server tries to interpret the entire script
before it executes and errors saying that the columns do not exist.

> So maybe you could explain why you're creating a table, then inserting dat
a,
> then adding nullable columns, then altering a column to be the same as it
> was before.
> A
Chris Lieb|||> That is exactly the point: I need to have the ALTER TABLE statements
> executed before the rest so that I do not get errors. If the batch is
> not committed midway, SQL Server tries to interpret the entire script
> before it executes and errors saying that the columns do not exist.
Yep, and that's the way SQL Server works. There are a couple of
workarounds, such as dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
Were it my project, I would probably find a way to get the data structure
right in the first place. Ideally, I would not be changing a table's
structure every time I call a stored procedure.
A|||Do it in two simple steps:
1) fix schema - do it once, but do it right;
2) fix data - do it as many times as needed without changing the schema.
That way you can set the tables 'in concrete' once and for all and then
focus on transforming data. Or do you need to change the tables over and ove
r
again?
ML
http://milambda.blogspot.com/|||I come into this situation alot. (I do a lot of dynamic sql but don't find
that is my favourite solution.) This is a little clunky, but it has worked
for me.
I would create 4 stored procedures, for instance,
spr_DoSomething_Main
spr_DoSomething_1
spr_DoSomething_2
spr_DoSomething_3
where main just says:
exec spr_DoSomething_1
exec spr_DoSomething_2
exec spr_DoSomething_3
If I have parameters it might look like this:
Create Procedure spr_DoSomething_Main
@.Param1 int,
@.Param2 int,
@.Param3 int
AS
exec spr_DoSomething_1 @.Param1
exec spr_DoSomething_2 @.Param1, @.Param2, @.Param3
exec spr_DoSomething_3 @.Param1
go
Whatever procedure needs whatever parameters gets only them. I also
sometimes do this to use stored procedures like classes for possible code
re-use and all...
"ML" wrote:

> Do it in two simple steps:
> 1) fix schema - do it once, but do it right;
> 2) fix data - do it as many times as needed without changing the schema.
> That way you can set the tables 'in concrete' once and for all and then
> focus on transforming data. Or do you need to change the tables over and o
ver
> again?
>
> ML
> --
> http://milambda.blogspot.com/|||Aaron Bertrand [SQL Server MVP] wrote:
> Yep, and that's the way SQL Server works. There are a couple of
> workarounds, such as dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
> Were it my project, I would probably find a way to get the data structure
> right in the first place. Ideally, I would not be changing a table's
> structure every time I call a stored procedure.
> A
OK, maybe a stored procedure isn't really the right route to have
chosen.
I am doing this to try to fix a schema that was created a few years ago
by another person who does not work here any more. I was making the
stored procedure so that once I figured out how to parse all of the
data that was on the development server, all I would have to do is have
the SP transferred to the live server and run and the data would be
fixed for me without making me go through and transform all of the data
manually. Having this on the development server also makes it easy to
fix the data when and if I need to mirror data off of the live server
in case something happens, like an error after a truncate statement
before the contents of the temp table are written back to the real
table.
Chris Lieb|||On 6 Dec 2005 06:50:39 -0800, Chris Lieb wrote:

>Aaron Bertrand [SQL Server MVP] wrote:
>OK, maybe a stored procedure isn't really the right route to have
>chosen.
>I am doing this to try to fix a schema that was created a few years ago
>by another person who does not work here any more. I was making the
>stored procedure so that once I figured out how to parse all of the
>data that was on the development server, all I would have to do is have
>the SP transferred to the live server and run and the data would be
>fixed for me without making me go through and transform all of the data
>manually. Having this on the development server also makes it easy to
>fix the data when and if I need to mirror data off of the live server
>in case something happens, like an error after a truncate statement
>before the contents of the temp table are written back to the real
>table.
>Chris Lieb
Hi Chris,
So this is a one-time job?
Instead of making a stored procedure, make an SQL script file. Store it
on your hard disk, tweak it until you're ready, then either copy and
paste into QA and execute against your prod DB or feed it through
osql.exe or a similar tool.
Unlike stored procedure's, SQL scripts can include "go", since a script
is just a load of commands sent to the server (just as if you were
typing very fast <g> ).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 6 Dec 2005 06:50:39 -0800, Chris Lieb wrote:
>
> Hi Chris,
> So this is a one-time job?
Once this is deployed, yes. Until then, it will be run many a time.

> Instead of making a stored procedure, make an SQL script file. Store it
> on your hard disk, tweak it until you're ready, then either copy and
> paste into QA and execute against your prod DB or feed it through
> osql.exe or a similar tool.
> Unlike stored procedure's, SQL scripts can include "go", since a script
> is just a load of commands sent to the server (just as if you were
> typing very fast <g> ).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I ended up making a DTS package out of the entire procedure, from
creating the tables, indexes, and triggers to transforming and
inserting the data.
Chris Lieb|||On 7 Dec 2005 14:35:28 -0800, Chris Lieb wrote:
(snip)
>Once this is deployed, yes. Until then, it will be run many a time.
Hi Chris,
In test, I hope <g>.
That's what I meant. A one-time job in the production DB.

>I ended up making a DTS package out of the entire procedure, from
>creating the tables, indexes, and triggers to transforming and
>inserting the data.
That's even better. My lack of experience with DTS is showing, I guess.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Multiple step OLEDB Error

The error message is

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

It is hard to pinpoint the exact problem w/o any additional info other than just the error msg here.

The error msg itself basically indicates that the application program was passing in an OLEDB property set, one or more of the properties were causing problem. What you should do is to enumerate through the property set and examine the corresponding status value, the error status should shed light on what went wrong.

Multiple SqlDataSource on a page

Hi,

I placed several SqlDataSource objects on my page which work with the same database (same connection string).

As I know, connection operation to database is costly in the performance prespective.

Do these SqlDataSource controls work with the same connection object or each of them create his own connecton object?

If each create it's own, then can they be changed to work with one connection object?

Thanks

As long as the connections strings are exactly identical, then the connection will get reused automatically as connections are automatically pooled.

Read tip #3:http://msdn.microsoft.com/msdnmag/issues/05/01/ASPNETPerformance/#S4

|||

Thanks,

Yet, another question:

If a SqlDataSource is not connected to a data UI control than does it still go retrieving the data or does it wait to be useed?

|||

It waits for the data to be requestsed such as by calling its Select() method

Multiple select in a Store problem

Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
StefanoCould you try putting SET NOCOUNT ON at the beginning of the insert?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ste" <ste@.3civettesulcomoinews.it> wrote in message
news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano|||Thank you, that's ok
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ha scritto nel messaggio
news:eOp4aflHFHA.2620@.tk2msftngp13.phx.gbl...
> Could you try putting SET NOCOUNT ON at the beginning of the insert?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ste" <ste@.3civettesulcomoinews.it> wrote in message
> news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi, I have a problem...
> Supposed that in a store procedure, I have two operation for example:
> insert into mytable field1,field2 values ('bob','bobby')
> select max(id) as id_last from mytable
> go
> in this case I have returned a recordset of one record, of last record
> inserted, ok?
> With ODBC Connection this work fine, but if I use SQLOLEDB Connection
> like:
> Provider=SQLOLEDB;Data Source=172.16.1.102;Network
> Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
> return this error:
> ADODB.Recordset error '800a0cc1'
> Item cannot be found in the collection corresponding to the requested name
> or ordinal.
> I use SQL2000Server in a machine and ASP on IIS6 in another machine
> Why
> thank you in advance and sorry for my bad english
> Stefano
>
>

Multiple select in a Store problem

Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano
Could you try putting SET NOCOUNT ON at the beginning of the insert?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ste" <ste@.3civettesulcomoinews.it> wrote in message
news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano
|||Thank you, that's ok
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ha scritto nel messaggio
news:eOp4aflHFHA.2620@.tk2msftngp13.phx.gbl...
> Could you try putting SET NOCOUNT ON at the beginning of the insert?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ste" <ste@.3civettesulcomoinews.it> wrote in message
> news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi, I have a problem...
> Supposed that in a store procedure, I have two operation for example:
> insert into mytable field1,field2 values ('bob','bobby')
> select max(id) as id_last from mytable
> go
> in this case I have returned a recordset of one record, of last record
> inserted, ok?
> With ODBC Connection this work fine, but if I use SQLOLEDB Connection
> like:
> Provider=SQLOLEDB;Data Source=172.16.1.102;Network
> Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
> return this error:
> ADODB.Recordset error '800a0cc1'
> Item cannot be found in the collection corresponding to the requested name
> or ordinal.
> I use SQL2000Server in a machine and ASP on IIS6 in another machine
> Why
> thank you in advance and sorry for my bad english
> Stefano
>
>