Can I avoid slammer attack without install sp3 for sql server 2k

Hi,

I guess there is some problem setting up transactional replication
after i install sp3 for sql server 2k. I get primary key violation in sp_MSget_repl_commands. So I need to revert back to sp2. But what should i do to avoid slammer attack with sp2 installed on my server

Thanks
Nikhil.This is a known issue. You should contact PSS and ask them to look up
SRX030113606317.

Can CONTAINSTABLE get its search terms from a table?

(SQL Server 2000, SP3)
Hello all!
We've got an issue with a Full Text Search query that is failing because of too many
search terms in the CONTAINSTABLE clause (upwards of 3000 elements separated with " OR ",
I think).
I was wondering if the CONTAINSTABLE clause could somehow be refactored to obtain its
search terms from a temporary table? I certainly don't see any evidence of this in BOL --
but I thought I'd check here.
Thanks for any help you can provide! :-)
John PetersonJohn,
Are you getting a syntax error on the FTS query? If so, what is the syntax
error? If you reduce the number of terms, i.e., reduce the total length of
the search string, does the error go away? There was a bug in the past
builds of SQL Server in regards to the length of the search string, but it
was fixed in an early SP.
Is the containstable query in a stored procedure or is it an ad hoc query?
You could "refactor" the query to use a temp table of terms, but that would
most likely require cursors or a while loop, both could be detrimental to
the SQL FTS query performance... Instead, try something like this and change
contains to containstable and be careful and use the correct number of
single quotes...
use pubs
go
DROP PROCEDURE usp_FTSearchPubsInfo
go
CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
AS
declare @.s as varchar (8000)
set @.s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@.vcSearchText+''''+')'
exec (@.s)
go
-- Small / Simple example of mutiple parameters...
EXEC usp_FTSearchPubsInfo '("pulp*") or ("waste" and "paper" or
"wastepaper") or
("recycle* paper") or (("paper slurry") and ("paper sludge")) or
("biodegrad* paper") or
("paper" and "dispos*") or (("paper" near "bleach*") or ("paper" near
"chemical*"))'
go
With usp_FTSearchPubsInfo you can add to the length of the search string up
to 8000 bytes (the max varchar size), but I've not tested this on SQL 2000
SP3 to 8000 bytes, so, your 3000 elements *might* exceed this limit... Give
it a try and let us know...
Regards,
John
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#faBMtBpDHA.2064@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> We've got an issue with a Full Text Search query that is failing because
of too many
> search terms in the CONTAINSTABLE clause (upwards of 3000 elements
separated with " OR ",
> I think).
> I was wondering if the CONTAINSTABLE clause could somehow be refactored to
obtain its
> search terms from a temporary table? I certainly don't see any evidence
of this in BOL --
> but I thought I'd check here.
> Thanks for any help you can provide! :-)
> John Peterson
>|||Hello John!
I *was* getting a syntax error in the FTS query -- and it would go away when I removed a
bunch of the search terms. This was the error:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ' "Aberdeen" OR "Acton" OR "Acworth" OR "Addison" OR
"Advance" OR "Ajax" OR "Alameda" OR "Albuquerque" OR "Algonquin" OR "Aliquip'.
The query is an ad-hoc query that gets built by an .ASP application and sent to SQL Server
via an ADOConnection/ADORecordset.
Alas, the string that's being sent is like 20K -- so I think that it far exceeds that 8000
character limit. :-(
Fundamentally, I think we need to change our searching component to ensure that people
can't enter in a billion search terms. But, by the same token, I felt that if we could
fix this relatively quickly/easily, that would be good too.
Any other ideas that we might be able to try?
As always, thank you so much for your help! :-)
John Peterson
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23qAU02BpDHA.2732@.TK2MSFTNGP11.phx.gbl...
> John,
> Are you getting a syntax error on the FTS query? If so, what is the syntax
> error? If you reduce the number of terms, i.e., reduce the total length of
> the search string, does the error go away? There was a bug in the past
> builds of SQL Server in regards to the length of the search string, but it
> was fixed in an early SP.
> Is the containstable query in a stored procedure or is it an ad hoc query?
> You could "refactor" the query to use a temp table of terms, but that would
> most likely require cursors or a while loop, both could be detrimental to
> the SQL FTS query performance... Instead, try something like this and change
> contains to containstable and be careful and use the correct number of
> single quotes...
> use pubs
> go
> DROP PROCEDURE usp_FTSearchPubsInfo
> go
> CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
> AS
> declare @.s as varchar (8000)
> set @.s='select pub_id, pr_info from pub_info where
> contains(pr_info,'+''''+@.vcSearchText+''''+')'
> exec (@.s)
> go
> -- Small / Simple example of mutiple parameters...
> EXEC usp_FTSearchPubsInfo '("pulp*") or ("waste" and "paper" or
> "wastepaper") or
> ("recycle* paper") or (("paper slurry") and ("paper sludge")) or
> ("biodegrad* paper") or
> ("paper" and "dispos*") or (("paper" near "bleach*") or ("paper" near
> "chemical*"))'
> go
> With usp_FTSearchPubsInfo you can add to the length of the search string up
> to 8000 bytes (the max varchar size), but I've not tested this on SQL 2000
> SP3 to 8000 bytes, so, your 3000 elements *might* exceed this limit... Give
> it a try and let us know...
> Regards,
> John
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#faBMtBpDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > We've got an issue with a Full Text Search query that is failing because
> of too many
> > search terms in the CONTAINSTABLE clause (upwards of 3000 elements
> separated with " OR ",
> > I think).
> >
> > I was wondering if the CONTAINSTABLE clause could somehow be refactored to
> obtain its
> > search terms from a temporary table? I certainly don't see any evidence
> of this in BOL --
> > but I thought I'd check here.
> >
> > Thanks for any help you can provide! :-)
> >
> > John Peterson
> >
> >
>|||You're welcome, John
That's the syntax error I suspected you were getting... Yes, I think it
would be a good idea to change your "searching component to ensure that
people can't enter in a billion search terms" as even Google limits the
number of *effective* search terms to 10, even though you can enter as many
search terms as you want. Perhaps adding a "tips" or "help" statement to
your ASP application page might be helpful as well, stating a limit of 10
(or whatever number) of search words are allowed...
Relative to the below stored procedure, and a "quick fix" for this would be
allowing your users to enter a trailing * (asterisk), for example book* - to
find book, books, booking, booked, etc. so that they will not have to enter
all word variations. Note, also add a tip that only a trailing * (asterisk)
is allowed as SQL FTS only supports this syntax...
Regards,
John
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eUMRMFCpDHA.3612@.TK2MSFTNGP11.phx.gbl...
> Hello John!
> I *was* getting a syntax error in the FTS query -- and it would go away
when I removed a
> bunch of the search terms. This was the error:
> Server: Msg 170, Level 15, State 1, Line 5
> Line 5: Incorrect syntax near ' "Aberdeen" OR "Acton" OR "Acworth" OR
"Addison" OR
> "Advance" OR "Ajax" OR "Alameda" OR "Albuquerque" OR "Algonquin" OR
"Aliquip'.
> The query is an ad-hoc query that gets built by an .ASP application and
sent to SQL Server
> via an ADOConnection/ADORecordset.
> Alas, the string that's being sent is like 20K -- so I think that it far
exceeds that 8000
> character limit. :-(
> Fundamentally, I think we need to change our searching component to ensure
that people
> can't enter in a billion search terms. But, by the same token, I felt
that if we could
> fix this relatively quickly/easily, that would be good too.
> Any other ideas that we might be able to try?
> As always, thank you so much for your help! :-)
> John Peterson
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23qAU02BpDHA.2732@.TK2MSFTNGP11.phx.gbl...
> > John,
> > Are you getting a syntax error on the FTS query? If so, what is the
syntax
> > error? If you reduce the number of terms, i.e., reduce the total length
of
> > the search string, does the error go away? There was a bug in the past
> > builds of SQL Server in regards to the length of the search string, but
it
> > was fixed in an early SP.
> >
> > Is the containstable query in a stored procedure or is it an ad hoc
query?
> > You could "refactor" the query to use a temp table of terms, but that
would
> > most likely require cursors or a while loop, both could be detrimental
to
> > the SQL FTS query performance... Instead, try something like this and
change
> > contains to containstable and be careful and use the correct number of
> > single quotes...
> >
> > use pubs
> > go
> > DROP PROCEDURE usp_FTSearchPubsInfo
> > go
> > CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
> > AS
> > declare @.s as varchar (8000)
> > set @.s='select pub_id, pr_info from pub_info where
> > contains(pr_info,'+''''+@.vcSearchText+''''+')'
> > exec (@.s)
> > go
> >
> > -- Small / Simple example of mutiple parameters...
> > EXEC usp_FTSearchPubsInfo '("pulp*") or ("waste" and "paper" or
> > "wastepaper") or
> > ("recycle* paper") or (("paper slurry") and ("paper sludge")) or
> > ("biodegrad* paper") or
> > ("paper" and "dispos*") or (("paper" near "bleach*") or ("paper" near
> > "chemical*"))'
> > go
> >
> > With usp_FTSearchPubsInfo you can add to the length of the search
string up
> > to 8000 bytes (the max varchar size), but I've not tested this on SQL
2000
> > SP3 to 8000 bytes, so, your 3000 elements *might* exceed this limit...
Give
> > it a try and let us know...
> >
> > Regards,
> > John
> >
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#faBMtBpDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > (SQL Server 2000, SP3)
> > >
> > > Hello all!
> > >
> > > We've got an issue with a Full Text Search query that is failing
because
> > of too many
> > > search terms in the CONTAINSTABLE clause (upwards of 3000 elements
> > separated with " OR ",
> > > I think).
> > >
> > > I was wondering if the CONTAINSTABLE clause could somehow be
refactored to
> > obtain its
> > > search terms from a temporary table? I certainly don't see any
evidence
> > of this in BOL --
> > > but I thought I'd check here.
> > >
> > > Thanks for any help you can provide! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>

Can any one help me with this SQLXML 3.0 SP3 Problem?

I'm having a problem with SQLXML. I have written a small VB.NET
application to manipulate xml files and import them into an SQL Server
database.
Every time I run the application, the import fails. The error log
contains the following xml:
<code>
<?xml version="1.0"?>
<Result State="FAILED">
<Error>
<HResult>0x80004005I32</HResult>
<Description><![CDATA[Error connecting to the data
source.]]></Description>
<Source>XML BulkLoad for SQL Server</Source>
<Type>FATAL</Type>
</Error>
</Result State>
</code>
I'm at a complete loss as to what's going on here. Any input would be
greatly appreciated. I've included most of my code for reference. If
anything else is needed, please let me know.
The code that is supposed to be connecting to the database and executing
the bulk transfer is as follows:
<code>
Private Function importToSQL(ByVal importXML As String)
(where importXML = C:\SQL EXCHANGE\IDS\IN\filename.xml)
Dim noErrors As Boolean
Dim connectionString As String = "PROVIDER=SQLOLEDB; Server=(local);
database=database; user id=username; password=password"
Dim errorLog As String = importXML & ".errlog"
Dim dataSchema As String = "C:\SQL EXCHANGE\IDS\IN\IDS XML Importer\IDS
XML Importer.xsd"
Dim bulkLoad As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3
Try
bulkLoad.KeepIdentity = False
bulkLoad.KeepNulls = True
bulkLoad.ErrorLogFile = errorLog
bulkLoad.ConnectionString = connectionString
bulkLoad.Execute(dataSchema, importXML)
Catch ex As Exception
noErrors = False
End Try
End Function
</code>
Lastly, a snippet of the .xsd file that I'm using to show that its
structure:
<code>
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="File">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="CLAIM" sql:relation="IMPORT_IHS_DENTAL_CLAIMS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CLAIM_NUM" type="xsd:string"
sql:field="CLAIM_NUM" />
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~
<xsd:element name="_240_TREATMENT_ZIP" type="xsd:string"
sql:field="_240_TREATMENT_ZIP" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:schema>
</code>
Thanks in advance.
--
Grant Smith
A+, Net+, MCP x 2Try a semicolon after the password.
Rick Sawtell
MCT, MCSD, MCDBA
"Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
message news:4Yudf.327077$084.13767@.attbi_s22...
> I'm having a problem with SQLXML. I have written a small VB.NET
> application to manipulate xml files and import them into an SQL Server
> database.
> Every time I run the application, the import fails. The error log
> contains the following xml:
> <code>
> <?xml version="1.0"?>
> <Result State="FAILED">
> <Error>
> <HResult>0x80004005I32</HResult>
> <Description><![CDATA[Error connecting to the data
> source.]]></Description>
> <Source>XML BulkLoad for SQL Server</Source>
> <Type>FATAL</Type>
> </Error>
> </Result State>
> </code>
> I'm at a complete loss as to what's going on here. Any input would be
> greatly appreciated. I've included most of my code for reference. If
> anything else is needed, please let me know.
> The code that is supposed to be connecting to the database and executing
> the bulk transfer is as follows:
> <code>
> Private Function importToSQL(ByVal importXML As String)
> (where importXML = C:\SQL EXCHANGE\IDS\IN\filename.xml)
> Dim noErrors As Boolean
> Dim connectionString As String = "PROVIDER=SQLOLEDB; Server=(local);
> database=database; user id=username; password=password"
> Dim errorLog As String = importXML & ".errlog"
> Dim dataSchema As String = "C:\SQL EXCHANGE\IDS\IN\IDS XML Importer\IDS
> XML Importer.xsd"
> Dim bulkLoad As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3
> Try
> bulkLoad.KeepIdentity = False
> bulkLoad.KeepNulls = True
> bulkLoad.ErrorLogFile = errorLog
> bulkLoad.ConnectionString = connectionString
> bulkLoad.Execute(dataSchema, importXML)
> Catch ex As Exception
> noErrors = False
> End Try
> End Function
> </code>
> Lastly, a snippet of the .xsd file that I'm using to show that its
> structure:
> <code>
> <?xml version="1.0" ?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="File">
> <xsd:complexType>
> <xsd:choice maxOccurs="unbounded">
> <xsd:element name="CLAIM" sql:relation="IMPORT_IHS_DENTAL_CLAIMS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="CLAIM_NUM" type="xsd:string"
> sql:field="CLAIM_NUM" />
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~
> <xsd:element name="_240_TREATMENT_ZIP" type="xsd:string"
> sql:field="_240_TREATMENT_ZIP" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> </code>
> Thanks in advance.
> --
> Grant Smith
> A+, Net+, MCP x 2|||Hi
The error implies that you have not connected correctly to your database.
Make sure that your connection string is correct. Check out the examples at
http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp
John
"Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
message news:4Yudf.327077$084.13767@.attbi_s22...
> I'm having a problem with SQLXML. I have written a small VB.NET
> application to manipulate xml files and import them into an SQL Server
> database.
> Every time I run the application, the import fails. The error log contains
> the following xml:
> <code>
> <?xml version="1.0"?>
> <Result State="FAILED">
> <Error>
> <HResult>0x80004005I32</HResult>
> <Description><![CDATA[Error connecting to the data
> source.]]></Description>
> <Source>XML BulkLoad for SQL Server</Source>
> <Type>FATAL</Type>
> </Error>
> </Result State>
> </code>
> I'm at a complete loss as to what's going on here. Any input would be
> greatly appreciated. I've included most of my code for reference. If
> anything else is needed, please let me know.
> The code that is supposed to be connecting to the database and executing
> the bulk transfer is as follows:
> <code>
> Private Function importToSQL(ByVal importXML As String)
> (where importXML = C:\SQL EXCHANGE\IDS\IN\filename.xml)
> Dim noErrors As Boolean
> Dim connectionString As String = "PROVIDER=SQLOLEDB; Server=(local);
> database=database; user id=username; password=password"
> Dim errorLog As String = importXML & ".errlog"
> Dim dataSchema As String = "C:\SQL EXCHANGE\IDS\IN\IDS XML Importer\IDS
> XML Importer.xsd"
> Dim bulkLoad As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3
> Try
> bulkLoad.KeepIdentity = False
> bulkLoad.KeepNulls = True
> bulkLoad.ErrorLogFile = errorLog
> bulkLoad.ConnectionString = connectionString
> bulkLoad.Execute(dataSchema, importXML)
> Catch ex As Exception
> noErrors = False
> End Try
> End Function
> </code>
> Lastly, a snippet of the .xsd file that I'm using to show that its
> structure:
> <code>
> <?xml version="1.0" ?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="File">
> <xsd:complexType>
> <xsd:choice maxOccurs="unbounded">
> <xsd:element name="CLAIM" sql:relation="IMPORT_IHS_DENTAL_CLAIMS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="CLAIM_NUM" type="xsd:string" sql:field="CLAIM_NUM" />
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~
> <xsd:element name="_240_TREATMENT_ZIP" type="xsd:string"
> sql:field="_240_TREATMENT_ZIP" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> </code>
> Thanks in advance.
> --
> Grant Smith
> A+, Net+, MCP x 2|||John Bell wrote:
> Hi
> The error implies that you have not connected correctly to your database.
> Make sure that your connection string is correct. Check out the examples a
t
> http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp
> John
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
Thanks for the input John, but I changed my connection string to match
PerfecXML (which is what I started with, i might add) save for making
UID and PWD appropriate for my server, and I still got the same results.
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Rick Sawtell wrote:
> Try a semicolon after the password.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I added a semicolon as you directed and still got the same results.
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Rick Sawtell wrote:
> Try a semicolon after the password.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I tried this and got the same results.
Any more ideas?
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||John Bell wrote:
> Hi
> The error implies that you have not connected correctly to your database.
> Make sure that your connection string is correct. Check out the examples a
t
> http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp
> John
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I changed my connection string to exactly what PerfectXML had as an example
(by the way, I started with this connection string originally) save for
the username and password values and still got the same results.
Anything else you can think of?
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Hi
If the connection string
PROVIDER=SQLOLEDB. 1;SERVER=(local);DATABASE=database;UID=u
sername;PWD=passwo
rd;
does not work, then you may want to check conectivity in general
through query analyser.
John
Grant Smith - eNVENT Technologies wrote:
> Rick Sawtell wrote:
> I added a semicolon as you directed and still got the same results.
> --
> Grant Smith
> A+, Net+, MCP x 2
> Quality Production Liaison
> Hewlett Packard Company
> Database Administrator
> Renaissance Systems and Services, LLC

Can an IDENTITY column be updated?

(SQL Server 2000, SP3)
Hello all!
I have a simple table:
create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
insert into Test (Name) values ('Test')
And I'd like to potentially update the IDENTITY column Id:
update Test set Id = 100 where Id = 1
However, I get the following error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'Id'.
Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same error.
Is there any way to update a column with an IDENTITY property?
Thanks!
John PetersonThanks Sue! Ah, I see the blurb in BOL that says an IDENTITY can't be updated. Bummer.
:-(
It seems to me that in older versions of SQL Server, one could update a column with the
IDENTITY property. But, no longer (or my memory isn't what it once was ;-).
Thanks again!
John Peterson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:dc1njvsscenku5aoklp93tl6rjptcibdcf@.4ax.com...
> Identity columns can't be updated - I think it's documented
> under the UPDATE topic in BOL T-SQL reference. One possible
> option would be to set identity_insert on, use the existing
> values for a new record and insert the new record with the
> identity value you need to use and then delete the old
> record.
> -Sue
> On Wed, 13 Aug 2003 20:10:46 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3)
> >
> >Hello all!
> >
> >I have a simple table:
> >
> >create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
> >insert into Test (Name) values ('Test')
> >
> >And I'd like to potentially update the IDENTITY column Id:
> >
> >update Test set Id = 100 where Id = 1
> >
> >However, I get the following error:
> >
> >Server: Msg 8102, Level 16, State 1, Line 1
> >Cannot update identity column 'Id'.
> >
> >Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same
error.
> >
> >Is there any way to update a column with an IDENTITY property?
> >
> >Thanks!
> >
> >John Peterson
> >
>

Can a database become suspect after being opened in STANDBY mode?

Hello,
I have an MS SQL Server 2000 SP3 database. My question is whether I can be
confident it won't become suspect after it has been successfully opened in
STANDBY mode. Can I use the fact as an indicator that the database won't go
suspect when I later try to open it RECOVERY mode?
-- Many thanks, OskarOn Oct 4, 5:20 pm, Oskar <Os...@.discussions.microsoft.com> wrote:
> Hello,
> I have an MS SQL Server 2000 SP3 database. My question is whether I can be
> confident it won't become suspect after it has been successfully opened in
> STANDBY mode. Can I use the fact as an indicator that the database won't go
> suspect when I later try to open it RECOVERY mode?
> -- Many thanks, Oskar
Didn't test it, but I do think that the database can become suspect
even if it is in standby mode. If you have a hardware failure, it can
cause the database to get into suspect status. For example if
something gets wrong with your storage, and the disk suddenly
disappears, then the database can get into suspect status. Of course
it is rare that something like that happens.
Adi|||At that stage hardware faults would already be fixed.
"Adi" wrote:
> On Oct 4, 5:20 pm, Oskar <Os...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have an MS SQL Server 2000 SP3 database. My question is whether I can be
> > confident it won't become suspect after it has been successfully opened in
> > STANDBY mode. Can I use the fact as an indicator that the database won't go
> > suspect when I later try to open it RECOVERY mode?
> >
> > -- Many thanks, Oskar
> Didn't test it, but I do think that the database can become suspect
> even if it is in standby mode. If you have a hardware failure, it can
> cause the database to get into suspect status. For example if
> something gets wrong with your storage, and the disk suddenly
> disappears, then the database can get into suspect status. Of course
> it is rare that something like that happens.
> Adi
>

Can @@ROWCOUNT return NULL?

SQL Server 2000 SP3.

Is it possible for the @.@.ROWCOUNT function to return NULL after a
statement? I am troubleshooting a relatively large stored procedure with
multiple SELECT statements and a couple of INSERTs into table variables.
Immediately after each statement I save the value returned by @.@.ROWCOUNT to
a local variable. That information eventually is passed back to the client
via one output parameter, for all statements in the procedure.
Occasionally, the value returned via that parameter is NULL. This cannot be
reproduced by re-running the SP with the same input parameters.

Before doing any further troubleshooting, I would like to rule out the
possibility that @.@.ROWCOUNT can actually return a NULL under some
circumstances. From searching the archives, it appears that in SQL Server
7.0 this could happen in the context of a DML query on a table with
triggers. This is not the case here - the only DML queries are INSERTs into
table variables, all other queries in the SP are SELECTs.

Any related information would be appreciated.

--
remove a 9 to reply by emailDimitri Furman (dfurman@.cloud99.net) writes:
> Is it possible for the @.@.ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @.@.ROWCOUNT
> to a local variable. That information eventually is passed back to the
> client via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot
> be reproduced by re-running the SP with the same input parameters.
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @.@.ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL
> Server 7.0 this could happen in the context of a DML query on a table
> with triggers. This is not the case here - the only DML queries are
> INSERTs into table variables, all other queries in the SP are SELECTs.

I have never heard of a case where @.@.rowcount can return NULL. Books
Online gives one hint when @.@.rowcount is not good: when more than two
milliard rows can be affected. In this case, you should try
rowcount_big(). Could this apply to you?

If not, I would recommend that you start troubleshooting. If it is not
repeatable, it will certainly be difficult...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try removing
SET NOCOUNT ON
from the stored procedure.

GeoSynch

"Dimitri Furman" <dfurman@.cloud99.net> wrote in message
news:Xns96C19F85E9F56dfurmancloud99@.127.0.0.1...
> SQL Server 2000 SP3.
> Is it possible for the @.@.ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @.@.ROWCOUNT to
> a local variable. That information eventually is passed back to the client
> via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot be
> reproduced by re-running the SP with the same input parameters.
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @.@.ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL Server
> 7.0 this could happen in the context of a DML query on a table with
> triggers. This is not the case here - the only DML queries are INSERTs into
> table variables, all other queries in the SP are SELECTs.
> Any related information would be appreciated.
> --
> remove a 9 to reply by email|||GeoSynch (SpamSlayed@.Casablanca.com) writes:
> Try removing
> SET NOCOUNT ON
> from the stored procedure.

@.@.rowcount should always return a value even if NOCOUNT is on. This
option controls whether rowcount information is passed to the client.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Calling stored procs on AS/400

I'm already able to pull data out of the AS/400 tables using a linked server from SQL 2000 SP3.
What I'd like to do is to be able to run stored procs on the AS/400 from SQL Server. Not sure what SPs are called in AS/400 parlance, but you get the idea.
Anyone done this before?
If not, any idea how to kick-off any kind of SQL task on the AS400 from SQL Server?
Hi,
Generally we do not support executing remote stored procedures if linked
server is non-SQL Server. I assume that you are using DB2. You may check if
the following can work on DB2.
1. Create a trigger on a table in DB2 database, which calls stored
procedures. The triggering action may be insertion, update, deletion.
2. Execute openquery to the DB2 linked server from SQL Server (e.g. insert
into table, etc.)
Thus the trigger can fire the stored procedure. I understand that it may be
inconvenient for you. However, we do not have better method.
If you use ADO or ADO.NET in code, you can execute the stored procedure on
DB2.
Calling Stored Procedure From ADO.NET Results in SQLCODE: -188 (330096)
http://support.microsoft.com/?id=330096
Thank you,
Bill Cheng
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided as is with no warranties and confers no rights.
| Thread-Topic: Calling stored procs on AS/400
| thread-index: AcRendMRxBNgJ+1tQUeVNCcN746SiQ==
| X-WBNR-Posting-Host: 80.132.66.129
| From: "=?Utf-8?B?SmFtZXM=?=" <news@.att.com>
| Subject: Calling stored procs on AS/400
| Date: Wed, 30 Jun 2004 05:29:01 -0700
| Lines: 10
| Message-ID: <05E76F43-0C6D-4CB0-9118-BE46C1314E00@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349340
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I'm already able to pull data out of the AS/400 tables using a linked
server from SQL 2000 SP3.
| What I'd like to do is to be able to run stored procs on the AS/400 from
SQL Server. Not sure what SPs are called in AS/400 parlance, but you get
the idea.
|
| Anyone done this before?
|
| If not, any idea how to kick-off any kind of SQL task on the AS400 from
SQL Server?
|
|
|
|
|
|||OK, trigger sounds good.
""Bill Cheng"" wrote:

> Hi,
> Generally we do not support executing remote stored procedures if linked
> server is non-SQL Server. I assume that you are using DB2. You may check if
> the following can work on DB2.
> 1. Create a trigger on a table in DB2 database, which calls stored
> procedures. The triggering action may be insertion, update, deletion.
> 2. Execute openquery to the DB2 linked server from SQL Server (e.g. insert
> into table, etc.)
> Thus the trigger can fire the stored procedure. I understand that it may be
> inconvenient for you. However, we do not have better method.
> If you use ADO or ADO.NET in code, you can execute the stored procedure on
> DB2.
> Calling Stored Procedure From ADO.NET Results in SQLCODE: -188 (330096)
> http://support.microsoft.com/?id=330096
>
> Thank you,
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! – www.microsoft.com/security
> This posting is provided “as is” with no warranties and confers no rights.
> --
> | Thread-Topic: Calling stored procs on AS/400
> | thread-index: AcRendMRxBNgJ+1tQUeVNCcN746SiQ==
> | X-WBNR-Posting-Host: 80.132.66.129
> | From: "=?Utf-8?B?SmFtZXM=?=" <news@.att.com>
> | Subject: Calling stored procs on AS/400
> | Date: Wed, 30 Jun 2004 05:29:01 -0700
> | Lines: 10
> | Message-ID: <05E76F43-0C6D-4CB0-9118-BE46C1314E00@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGXA03.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349340
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I'm already able to pull data out of the AS/400 tables using a linked
> server from SQL 2000 SP3.
> | What I'd like to do is to be able to run stored procs on the AS/400 from
> SQL Server. Not sure what SPs are called in AS/400 parlance, but you get
> the idea.
> |
> | Anyone done this before?
> |
> | If not, any idea how to kick-off any kind of SQL task on the AS400 from
> SQL Server?
> |
> |
> |
> |
> |
>
|||Hi,
I am glad to know that it can work for you. I am happy to work with you.
Thanks again for choosing Microsoft newsgroups.
Thank you,
Bill Cheng
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided as is with no warranties and confers no rights.
| Thread-Topic: Calling stored procs on AS/400
| thread-index: AcRfcnJUe/OsxZDeS3+6uUwDO+xsUw==
| X-WBNR-Posting-Host: 80.132.81.90
| From: "=?Utf-8?B?SmFtZXM=?=" <news@.att.com>
| References: <05E76F43-0C6D-4CB0-9118-BE46C1314E00@.microsoft.com>
<4O8IPgxXEHA.2244@.cpmsftngxa06.phx.gbl>
| Subject: RE: Calling stored procs on AS/400
| Date: Thu, 1 Jul 2004 06:51:02 -0700
| Lines: 70
| Message-ID: <EF8371DE-2652-4173-858E-7DF9749DE1F3@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349558
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| OK, trigger sounds good.
|
| ""Bill Cheng"" wrote:
|
| > Hi,
| >
| > Generally we do not support executing remote stored procedures if
linked
| > server is non-SQL Server. I assume that you are using DB2. You may
check if
| > the following can work on DB2.
| > 1. Create a trigger on a table in DB2 database, which calls stored
| > procedures. The triggering action may be insertion, update, deletion.
| > 2. Execute openquery to the DB2 linked server from SQL Server (e.g.
insert
| > into table, etc.)
| >
| > Thus the trigger can fire the stored procedure. I understand that it
may be
| > inconvenient for you. However, we do not have better method.
| >
| > If you use ADO or ADO.NET in code, you can execute the stored procedure
on
| > DB2.
| > Calling Stored Procedure From ADO.NET Results in SQLCODE: -188 (330096)
| > http://support.microsoft.com/?id=330096
| >
| >
| > Thank you,
| >
| > Bill Cheng
| > Microsoft Online Partner Support
| > Get Secure! – www.microsoft.com/security
| > This posting is provided “as is” with no warranties and confers no
rights.
| > --
| > | Thread-Topic: Calling stored procs on AS/400
| > | thread-index: AcRendMRxBNgJ+1tQUeVNCcN746SiQ==
| > | X-WBNR-Posting-Host: 80.132.66.129
| > | From: "=?Utf-8?B?SmFtZXM=?=" <news@.att.com>
| > | Subject: Calling stored procs on AS/400
| > | Date: Wed, 30 Jun 2004 05:29:01 -0700
| > | Lines: 10
| > | Message-ID: <05E76F43-0C6D-4CB0-9118-BE46C1314E00@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGXA03.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349340
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I'm already able to pull data out of the AS/400 tables using a linked
| > server from SQL 2000 SP3.
| > | What I'd like to do is to be able to run stored procs on the AS/400
from
| > SQL Server. Not sure what SPs are called in AS/400 parlance, but you
get
| > the idea.
| > |
| > | Anyone done this before?
| > |
| > | If not, any idea how to kick-off any kind of SQL task on the AS400
from
| > SQL Server?
| > |
| > |
| > |
| > |
| > |
| >
| >
|

Calling stored procs on AS/400

I'm already able to pull data out of the AS/400 tables using a linked server
from SQL 2000 SP3.
What I'd like to do is to be able to run stored procs on the AS/400 from SQL
Server. Not sure what SPs are called in AS/400 parlance, but you get the id
ea.
Anyone done this before?
If not, any idea how to kick-off any kind of SQL task on the AS400 from SQL
Server?Hi,
Generally we do not support executing remote stored procedures if linked
server is non-SQL Server. I assume that you are using DB2. You may check if
the following can work on DB2.
1. Create a trigger on a table in DB2 database, which calls stored
procedures. The triggering action may be insertion, update, deletion.
2. Execute openquery to the DB2 linked server from SQL Server (e.g. insert
into table, etc.)
Thus the trigger can fire the stored procedure. I understand that it may be
inconvenient for you. However, we do not have better method.
If you use ADO or ADO.NET in code, you can execute the stored procedure on
DB2.
Calling Stored Procedure From ADO.NET Results in SQLCODE: -188 (330096)
http://support.microsoft.com/?id=330096
Thank you,
Bill Cheng
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided as is with no warranties and confers no rights.
--
| Thread-Topic: Calling stored procs on AS/400
| thread-index: AcRendMRxBNgJ+1tQUeVNCcN746SiQ==
| X-WBNR-Posting-Host: 80.132.66.129
| From: "examnotes" <news@.att.com>
| Subject: Calling stored procs on AS/400
| Date: Wed, 30 Jun 2004 05:29:01 -0700
| Lines: 10
| Message-ID: <05E76F43-0C6D-4CB0-9118-BE46C1314E00@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349340
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I'm already able to pull data out of the AS/400 tables using a linked
server from SQL 2000 SP3.
| What I'd like to do is to be able to run stored procs on the AS/400 from
SQL Server. Not sure what SPs are called in AS/400 parlance, but you get
the idea.
|
| Anyone done this before?
|
| If not, any idea how to kick-off any kind of SQL task on the AS400 from
SQL Server?
|
|
|
|
||||OK, trigger sounds good.
""Bill Cheng"" wrote:

> Hi,
> Generally we do not support executing remote stored procedures if linked
> server is non-SQL Server. I assume that you are using DB2. You may check i
f
> the following can work on DB2.
> 1. Create a trigger on a table in DB2 database, which calls stored
> procedures. The triggering action may be insertion, update, deletion.
> 2. Execute openquery to the DB2 linked server from SQL Server (e.g. insert
> into table, etc.)
> Thus the trigger can fire the stored procedure. I understand that it may b
e
> inconvenient for you. However, we do not have better method.
> If you use ADO or ADO.NET in code, you can execute the stored procedure on
> DB2.
> Calling Stored Procedure From ADO.NET Results in SQLCODE: -188 (330096)
> http://support.microsoft.com/?id=330096
>
> Thank you,
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! – www.microsoft.com/security
> This posting is provided “as is” with no warranties and confers no rig
hts.
> --
> | Thread-Topic: Calling stored procs on AS/400
> | thread-index: AcRendMRxBNgJ+1tQUeVNCcN746SiQ==
> | X-WBNR-Posting-Host: 80.132.66.129
> | From: "examnotes" <news@.att.com>
> | Subject: Calling stored procs on AS/400
> | Date: Wed, 30 Jun 2004 05:29:01 -0700
> | Lines: 10
> | Message-ID: <05E76F43-0C6D-4CB0-9118-BE46C1314E00@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349340
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I'm already able to pull data out of the AS/400 tables using a linked
> server from SQL 2000 SP3.
> | What I'd like to do is to be able to run stored procs on the AS/400 from
> SQL Server. Not sure what SPs are called in AS/400 parlance, but you get
> the idea.
> |
> | Anyone done this before?
> |
> | If not, any idea how to kick-off any kind of SQL task on the AS400 from
> SQL Server?
> |
> |
> |
> |
> |
>|||Hi,
I am glad to know that it can work for you. I am happy to work with you.
Thanks again for choosing Microsoft newsgroups.
Thank you,
Bill Cheng
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided as is with no warranties and confers no rights.
--
| Thread-Topic: Calling stored procs on AS/400
| thread-index: AcRfcnJUe/OsxZDeS3+6uUwDO+xsUw==
| X-WBNR-Posting-Host: 80.132.81.90
| From: "examnotes" <news@.att.com>
| References: <05E76F43-0C6D-4CB0-9118-BE46C1314E00@.microsoft.com>
<4O8IPgxXEHA.2244@.cpmsftngxa06.phx.gbl>
| Subject: RE: Calling stored procs on AS/400
| Date: Thu, 1 Jul 2004 06:51:02 -0700
| Lines: 70
| Message-ID: <EF8371DE-2652-4173-858E-7DF9749DE1F3@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349558
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| OK, trigger sounds good.
|
| ""Bill Cheng"" wrote:
|
| > Hi,
| >
| > Generally we do not support executing remote stored procedures if
linked
| > server is non-SQL Server. I assume that you are using DB2. You may
check if
| > the following can work on DB2.
| > 1. Create a trigger on a table in DB2 database, which calls stored
| > procedures. The triggering action may be insertion, update, deletion.
| > 2. Execute openquery to the DB2 linked server from SQL Server (e.g.
insert
| > into table, etc.)
| >
| > Thus the trigger can fire the stored procedure. I understand that it
may be
| > inconvenient for you. However, we do not have better method.
| >
| > If you use ADO or ADO.NET in code, you can execute the stored procedure
on
| > DB2.
| > Calling Stored Procedure From ADO.NET Results in SQLCODE: -188 (330096)
| > http://support.microsoft.com/?id=330096
| >
| >
| > Thank you,
| >
| > Bill Cheng
| > Microsoft Online Partner Support
| > Get Secure! – www.microsoft.com/security
| > This posting is provided “as is” with no warranties and confers no
rights.
| > --
| > | Thread-Topic: Calling stored procs on AS/400
| > | thread-index: AcRendMRxBNgJ+1tQUeVNCcN746SiQ==
| > | X-WBNR-Posting-Host: 80.132.66.129
| > | From: "examnotes" <news@.att.com>
| > | Subject: Calling stored procs on AS/400
| > | Date: Wed, 30 Jun 2004 05:29:01 -0700
| > | Lines: 10
| > | Message-ID: <05E76F43-0C6D-4CB0-9118-BE46C1314E00@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349340
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I'm already able to pull data out of the AS/400 tables using a linked
| > server from SQL 2000 SP3.
| > | What I'd like to do is to be able to run stored procs on the AS/400
from
| > SQL Server. Not sure what SPs are called in AS/400 parlance, but you
get
| > the idea.
| > |
| > | Anyone done this before?
| > |
| > | If not, any idea how to kick-off any kind of SQL task on the AS400
from
| > SQL Server?
| > |
| > |
| > |
| > |
| > |
| >
| >
|