32 bit DLL in SQL Server 2005 64 bit

Hello,
I have been using a 32 bit DLL in SQL Server 2000 extended stored procedures
and it works just fine. Now I have upgraded my SQL Server 2000 32 bit to SQL
Server 2005 64 bit edition.
The same extended stored procedures now give error when I call them using
sp_OACreate.. It gives the error "Class not registered" even though the DLL
file is present in the "SysWow64" folder and it has been correctly registere
d
using regsvr32.
On a SQL Server 2005 32 bit edition, it works well. So, it is just the SQL
Server 2005 64 bit edition that seems cannot load the 32 bit DLL.. Is that
right?. What options do I have now so that I can continue the use the same 3
2
bit DLLs in 64 bit SQl Server 2005?.
Thanks for any help!
GaneshHi Ganesh
I am not an expert on this but it looks like only 64 but registrations can
be used by sp_OA... but I have not found anything to say that is the case.
John
"Ganesh Muthuvelu" wrote:

> Hello,
> I have been using a 32 bit DLL in SQL Server 2000 extended stored procedur
es
> and it works just fine. Now I have upgraded my SQL Server 2000 32 bit to S
QL
> Server 2005 64 bit edition.
> The same extended stored procedures now give error when I call them using
> sp_OACreate.. It gives the error "Class not registered" even though the DL
L
> file is present in the "SysWow64" folder and it has been correctly registe
red
> using regsvr32.
> On a SQL Server 2005 32 bit edition, it works well. So, it is just the SQL
> Server 2005 64 bit edition that seems cannot load the 32 bit DLL.. Is that
> right?. What options do I have now so that I can continue the use the same
32
> bit DLLs in 64 bit SQl Server 2005?.
> Thanks for any help!
> Ganesh|||For a 64 bit installation, you need 64 bits dll's.
You cannot mix 64 and 32 bit code in one process, that's why you cannot
use 32 bits dll's in a 64 bit process (or vice versa).
Ganesh Muthuvelu wrote:
> Hello,
> I have been using a 32 bit DLL in SQL Server 2000 extended stored procedur
es
> and it works just fine. Now I have upgraded my SQL Server 2000 32 bit to S
QL
> Server 2005 64 bit edition.
> The same extended stored procedures now give error when I call them using
> sp_OACreate.. It gives the error "Class not registered" even though the DL
L
> file is present in the "SysWow64" folder and it has been correctly registe
red
> using regsvr32.
> On a SQL Server 2005 32 bit edition, it works well. So, it is just the SQL
> Server 2005 64 bit edition that seems cannot load the 32 bit DLL.. Is that
> right?. What options do I have now so that I can continue the use the same
32
> bit DLLs in 64 bit SQl Server 2005?.
> Thanks for any help!
> Ganesh

32 bit DLL in SQL Server 2005 64 bit

Hello,
I have been using a 32 bit DLL in SQL Server 2000 extended stored procedures
and it works just fine. Now I have upgraded my SQL Server 2000 32 bit to SQL
Server 2005 64 bit edition.
The same extended stored procedures now give error when I call them using
sp_OACreate.. It gives the error "Class not registered" even though the DLL
file is present in the "SysWow64" folder and it has been correctly registered
using regsvr32.
On a SQL Server 2005 32 bit edition, it works well. So, it is just the SQL
Server 2005 64 bit edition that seems cannot load the 32 bit DLL.. Is that
right?. What options do I have now so that I can continue the use the same 32
bit DLLs in 64 bit SQl Server 2005?.
Thanks for any help!
Ganesh
Hi Ganesh
I am not an expert on this but it looks like only 64 but registrations can
be used by sp_OA... but I have not found anything to say that is the case.
John
"Ganesh Muthuvelu" wrote:

> Hello,
> I have been using a 32 bit DLL in SQL Server 2000 extended stored procedures
> and it works just fine. Now I have upgraded my SQL Server 2000 32 bit to SQL
> Server 2005 64 bit edition.
> The same extended stored procedures now give error when I call them using
> sp_OACreate.. It gives the error "Class not registered" even though the DLL
> file is present in the "SysWow64" folder and it has been correctly registered
> using regsvr32.
> On a SQL Server 2005 32 bit edition, it works well. So, it is just the SQL
> Server 2005 64 bit edition that seems cannot load the 32 bit DLL.. Is that
> right?. What options do I have now so that I can continue the use the same 32
> bit DLLs in 64 bit SQl Server 2005?.
> Thanks for any help!
> Ganesh

32 bit DLL in SQL Server 2005 64 bit

Hello,
I have been using a 32 bit DLL in SQL Server 2000 extended stored procedures
and it works just fine. Now I have upgraded my SQL Server 2000 32 bit to SQL
Server 2005 64 bit edition.
The same extended stored procedures now give error when I call them using
sp_OACreate.. It gives the error "Class not registered" even though the DLL
file is present in the "SysWow64" folder and it has been correctly registered
using regsvr32.
On a SQL Server 2005 32 bit edition, it works well. So, it is just the SQL
Server 2005 64 bit edition that seems cannot load the 32 bit DLL.. Is that
right?. What options do I have now so that I can continue the use the same 32
bit DLLs in 64 bit SQl Server 2005?.
Thanks for any help!
GaneshHi Ganesh
I am not an expert on this but it looks like only 64 but registrations can
be used by sp_OA... but I have not found anything to say that is the case.
John
"Ganesh Muthuvelu" wrote:
> Hello,
> I have been using a 32 bit DLL in SQL Server 2000 extended stored procedures
> and it works just fine. Now I have upgraded my SQL Server 2000 32 bit to SQL
> Server 2005 64 bit edition.
> The same extended stored procedures now give error when I call them using
> sp_OACreate.. It gives the error "Class not registered" even though the DLL
> file is present in the "SysWow64" folder and it has been correctly registered
> using regsvr32.
> On a SQL Server 2005 32 bit edition, it works well. So, it is just the SQL
> Server 2005 64 bit edition that seems cannot load the 32 bit DLL.. Is that
> right?. What options do I have now so that I can continue the use the same 32
> bit DLLs in 64 bit SQl Server 2005?.
> Thanks for any help!
> Ganesh|||For a 64 bit installation, you need 64 bits dll's.
You cannot mix 64 and 32 bit code in one process, that's why you cannot
use 32 bits dll's in a 64 bit process (or vice versa).
Ganesh Muthuvelu wrote:
> Hello,
> I have been using a 32 bit DLL in SQL Server 2000 extended stored procedures
> and it works just fine. Now I have upgraded my SQL Server 2000 32 bit to SQL
> Server 2005 64 bit edition.
> The same extended stored procedures now give error when I call them using
> sp_OACreate.. It gives the error "Class not registered" even though the DLL
> file is present in the "SysWow64" folder and it has been correctly registered
> using regsvr32.
> On a SQL Server 2005 32 bit edition, it works well. So, it is just the SQL
> Server 2005 64 bit edition that seems cannot load the 32 bit DLL.. Is that
> right?. What options do I have now so that I can continue the use the same 32
> bit DLLs in 64 bit SQl Server 2005?.
> Thanks for any help!
> Ganeshsql

3 tables wildcard searches

Hi,

I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.

There are 3 tables involved, the jobs, the applicants and the applications.

I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.

There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.

My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?

So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?

Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.

So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.

Thanks in advance.Can you post the URL for this assignment, or at least scan in the page(s) you got in class if they aren't available online?

-PatP|||If I understand the question correctly, something like this might work:

create proc usp_search(@.searchstring varchar(100))
as
select * -- replace with required cols
from applicants
join applications on applicants.applicantid = applications.applicantid
join jobs on applications.jobid = jobs.jobid
where forename like @.searchstring + '%' -- Auto Wild Card (Begins With)
or surname like @.searchstring + '%'
or jobtitle like @.searchstring + '%'
or jobref = @.searchstring
or applicantid = @.searchstring

3 questions

Hi -- I'm new to SQL and thus also new to SQL Server.
I'm working on a project that has data stored in 4 databases. I want to
create a 5th database for my work. I will need to retrieve data from those
other 4 databases -- usually creating tables in my 5th database. I want to
put together stored procedures to do this. I can extract data and create
tables within one database, but have no idea how to reach out to a different
one.
Eventually, in this project I will need to read the records, in order and
create a running calculation -- not a running sum. I can do this within VB
pretty easily, but I don't know if this can be done is a query. The
calculation is: (field 2, record n) =
(field 1, record n) * (1 - field 2, record n-1) + (field2,record n)
Finally, can anyone suggest a good book that might help me with some of
these types of questions?
I'd appreciate any help on any portion of the above!
Thanks,
ArtArt,
To access the tables in the other databases (assuming the same server) just
qualify the object using DATABASE.OWNER.OBJECT i.e., pubs.dbo.authors.
Books (SQL):
The Guru's Guide to Transact-SQL
by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
Professional SQL Server 2000 Programming
by Robert Vieira
http://www.amazon.com/exec/obidos/t...=glance&s=books
Joe Celko's SQL for Smarties (2005 ed)
by Joe Celko
http://www.amazon.com/exec/obidos/t...=books&n=507846
HTH
Jerry
"Art" <Art@.discussions.microsoft.com> wrote in message
news:D5386C63-4FEA-42A6-8958-A2CF67FCB726@.microsoft.com...
> Hi -- I'm new to SQL and thus also new to SQL Server.
> I'm working on a project that has data stored in 4 databases. I want to
> create a 5th database for my work. I will need to retrieve data from
> those
> other 4 databases -- usually creating tables in my 5th database. I want
> to
> put together stored procedures to do this. I can extract data and create
> tables within one database, but have no idea how to reach out to a
> different
> one.
> Eventually, in this project I will need to read the records, in order and
> create a running calculation -- not a running sum. I can do this within
> VB
> pretty easily, but I don't know if this can be done is a query. The
> calculation is: (field 2, record n) =
> (field 1, record n) * (1 - field 2, record n-1) + (field2,record n)
> Finally, can anyone suggest a good book that might help me with some of
> these types of questions?
> I'd appreciate any help on any portion of the above!
> Thanks,
> Art|||Jerry,
Thanks very much! For both answers.
Art
"Jerry Spivey" wrote:

> Art,
> To access the tables in the other databases (assuming the same server) jus
t
> qualify the object using DATABASE.OWNER.OBJECT i.e., pubs.dbo.authors.
> Books (SQL):
> The Guru's Guide to Transact-SQL
> by Ken Henderson
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Professional SQL Server 2000 Programming
> by Robert Vieira
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Joe Celko's SQL for Smarties (2005 ed)
> by Joe Celko
> http://www.amazon.com/exec/obidos/t...=books&n=507846
> HTH
> Jerry
> "Art" <Art@.discussions.microsoft.com> wrote in message
> news:D5386C63-4FEA-42A6-8958-A2CF67FCB726@.microsoft.com...
>
>|||>> I can extract data and create tables within one database, but have no
Use 3-part naming like database.owner.object
Perhaps if you post the DDLs, sample data & expected results, others could
give it a try. ( www.aspfaq.com/5006 ). In some cases, using a client side
programming language might be the option. In other cases, you might have to
use some looping construct like a cursor.
SQL Server Books Online should give you most of the basics on t-SQL.
Anith|||Thanks Anith,
I had been using a programming language, but was wondering if I might be
better off using SQL (assuming it's possible). Thanks again.
Art
"Anith Sen" wrote:

> Use 3-part naming like database.owner.object
>
> Perhaps if you post the DDLs, sample data & expected results, others could
> give it a try. ( www.aspfaq.com/5006 ). In some cases, using a client side
> programming language might be the option. In other cases, you might have t
o
> use some looping construct like a cursor.
>
> SQL Server Books Online should give you most of the basics on t-SQL.
> --
> Anith
>
>|||Art, you are in trouble. Let's get back to the basics of an RDBMS.
Rows are not records; fields are not columns; tables are not files;
your post is nonsense. But it tells us that you have no idea about
RDBMS, so your design is probably screw to hell and breakfast.
You seem to be doing attribute splitting on databases; Otherwise, it
would not make sense to mix them.|||"But it tells us that you have no idea about RDBMS"
Hey --CELKO--, keep your posts focused on the issue at hand, not the person
posting. The forum is here to help each other, not to put down people.
This a constant problem with you. Your input is valuable and appreciated,
but these kind of comments are not (at least by me).
Are there no moderators on this site?
"--CELKO--" wrote:

> Art, you are in trouble. Let's get back to the basics of an RDBMS.
> Rows are not records; fields are not columns; tables are not files;
> your post is nonsense. But it tells us that you have no idea about
> RDBMS, so your design is probably screw to hell and breakfast.
> You seem to be doing attribute splitting on databases; Otherwise, it
> would not make sense to mix them.
>

3 min 30 seconds for a Stored Proc execution

In my stored proc I was using SELECT count(*) on table for each user. This
stored proc was finishing in 30 seconds. And customer was happy with the
results.
Now there is a change in my stored proc and i have to use SELECT count (
distinct startdate ) on that table where startdate is of type "int". After
making this change my stored proc is finishing in 3 min 30 seconds which is
unacceptable for customer.
Is there a way to speed up the execution of this stored procedure? There is
already an index on this table which includes "startdate" column.
This is the table structure:
=================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[interactions]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[interactions]
GO
CREATE TABLE [dbo].[interactions] (
[companyid] [int] NOT NULL ,
[interactionid] [bigint] NOT NULL ,
[startdate] [int] NOT NULL ,
[resourcetype] [int] NOT NULL ,
[resourceid] [int] NOT NULL ,
[originator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[destinator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[duration] [int] NOT NULL ,
[interactiontype] [int] NOT NULL ,
[status] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[interactions] WITH NOCHECK ADD
CONSTRAINT [pk_interactions] PRIMARY KEY CLUSTERED
(
[companyid],
[interactionid]
) ON [PRIMARY]
GO
CREATE INDEX [ix_interactions_startdate] ON
[dbo].[interactions]([companyid], [startdate]) ON [PRIMARY]
GO
This is my query:
===========
select @.v_NumberOfCalls = count( distinct i.startdate )
from interactions i
inner join historyactions ha ON
( i.interactionid = ha.interactionid ) AND
( i.companyid = ha.companyid ) AND
( ha.companyid = @.a_CompanyId ) AND
( ha.workgroupid = @.a_WorkGroupId ) AND
( ha.actionid in ( 70005 ) ) AND
( ha.userid = @.v_userid ) AND
( i.startdate between @.v_StartDateTime and @.v_EndDateTime )
Thanks
SyedAre there really more than one row with the same startdate for a given
CompanyID? If not then why the distinct? You probably want to have the
clustered index on CompanyId, Startdate instead of the PK. You will
probably get less page splits and faster results.
Andrew J. Kelly SQL MVP
"JY" <jy1970us@.yahoo.com> wrote in message
news:OB6If.1405$_D5.131478@.news20.bellglobal.com...
> In my stored proc I was using SELECT count(*) on table for each user. This
> stored proc was finishing in 30 seconds. And customer was happy with the
> results.
> Now there is a change in my stored proc and i have to use SELECT count (
> distinct startdate ) on that table where startdate is of type "int". After
> making this change my stored proc is finishing in 3 min 30 seconds which
> is unacceptable for customer.
> Is there a way to speed up the execution of this stored procedure? There
> is already an index on this table which includes "startdate" column.
>
> This is the table structure:
> =================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[interactions]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[interactions]
> GO
> CREATE TABLE [dbo].[interactions] (
> [companyid] [int] NOT NULL ,
> [interactionid] [bigint] NOT NULL ,
> [startdate] [int] NOT NULL ,
> [resourcetype] [int] NOT NULL ,
> [resourceid] [int] NOT NULL ,
> [originator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [destinator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [duration] [int] NOT NULL ,
> [interactiontype] [int] NOT NULL ,
> [status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[interactions] WITH NOCHECK ADD
> CONSTRAINT [pk_interactions] PRIMARY KEY CLUSTERED
> (
> [companyid],
> [interactionid]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [ix_interactions_startdate] ON
> [dbo].[interactions]([companyid], [startdate]) ON [PRIMARY]
> GO
>
> This is my query:
> ===========
> select @.v_NumberOfCalls = count( distinct i.startdate )
> from interactions i
> inner join historyactions ha ON
> ( i.interactionid = ha.interactionid ) AND
> ( i.companyid = ha.companyid ) AND
> ( ha.companyid = @.a_CompanyId ) AND
> ( ha.workgroupid = @.a_WorkGroupId ) AND
> ( ha.actionid in ( 70005 ) ) AND
> ( ha.userid = @.v_userid ) AND
> ( i.startdate between @.v_StartDateTime and @.v_EndDateTime )
>
> Thanks
> Syed
>|||There are 1624034 rows in "interactions" table.
There are 20764239 rows in "historyactions" table.
I ran Index Tuning Wizard for my query and it told me that query is ok and
it is using the "ix_interactions_startdate" index 100%.
Any input please.
Thanks
"JY" <jy1970us@.yahoo.com> wrote in message
news:OB6If.1405$_D5.131478@.news20.bellglobal.com...
> In my stored proc I was using SELECT count(*) on table for each user. This
> stored proc was finishing in 30 seconds. And customer was happy with the
> results.
> Now there is a change in my stored proc and i have to use SELECT count (
> distinct startdate ) on that table where startdate is of type "int". After
> making this change my stored proc is finishing in 3 min 30 seconds which
> is unacceptable for customer.
> Is there a way to speed up the execution of this stored procedure? There
> is already an index on this table which includes "startdate" column.
>
> This is the table structure:
> =================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[interactions]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[interactions]
> GO
> CREATE TABLE [dbo].[interactions] (
> [companyid] [int] NOT NULL ,
> [interactionid] [bigint] NOT NULL ,
> [startdate] [int] NOT NULL ,
> [resourcetype] [int] NOT NULL ,
> [resourceid] [int] NOT NULL ,
> [originator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [destinator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [duration] [int] NOT NULL ,
> [interactiontype] [int] NOT NULL ,
> [status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[interactions] WITH NOCHECK ADD
> CONSTRAINT [pk_interactions] PRIMARY KEY CLUSTERED
> (
> [companyid],
> [interactionid]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [ix_interactions_startdate] ON
> [dbo].[interactions]([companyid], [startdate]) ON [PRIMARY]
> GO
>
> This is my query:
> ===========
> select @.v_NumberOfCalls = count( distinct i.startdate )
> from interactions i
> inner join historyactions ha ON
> ( i.interactionid = ha.interactionid ) AND
> ( i.companyid = ha.companyid ) AND
> ( ha.companyid = @.a_CompanyId ) AND
> ( ha.workgroupid = @.a_WorkGroupId ) AND
> ( ha.actionid in ( 70005 ) ) AND
> ( ha.userid = @.v_userid ) AND
> ( i.startdate between @.v_StartDateTime and @.v_EndDateTime )
>
> Thanks
> Syed
>|||yes there really are more than one row with the same startdate for a given
CompanyID. We cannot change the primary key since our software is a
reporting software and only use this database.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiazxhOMGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Are there really more than one row with the same startdate for a given
> CompanyID? If not then why the distinct? You probably want to have the
> clustered index on CompanyId, Startdate instead of the PK. You will
> probably get less page splits and faster results.
> --
> Andrew J. Kelly SQL MVP
>
> "JY" <jy1970us@.yahoo.com> wrote in message
> news:OB6If.1405$_D5.131478@.news20.bellglobal.com...
>|||I am not asking you to change the PK definition, only to change which index
is clustered. What kind of indexes do you have on the HistoryActions table?
What is the query plan showing?
Andrew J. Kelly SQL MVP
"JY" <jy1970us@.yahoo.com> wrote in message
news:CV7If.1679$_D5.136961@.news20.bellglobal.com...
> yes there really are more than one row with the same startdate for a given
> CompanyID. We cannot change the primary key since our software is a
> reporting software and only use this database.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uiazxhOMGHA.3732@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 13 Feb 2006 16:11:07 -0500, "JY" <jy1970us@.yahoo.com> wrote:
>Is there a way to speed up the execution of this stored procedure? There is
>already an index on this table which includes "startdate" column.
Maybe a little.
It's ugly, but try doing two single-table queries, either as a single
statement or into a #temp or @.temp, then join those.
20m scan is going to take, what, 30 seconds maybe?
And how many records will you get from this query, on average, at max?
If that number is reasonably small, my guess is you can get it under a
minute, maybe better.
J.|||have you tried an index on interactions( startdate, interactionid,
companyid) and an index on historyactions( companyid, companyid,
interactionid, workgroupid, actionid)?

2k Replication errs .... Error 2812

Hey,
Having some probs w replication between 2 2k srvs running sql2k.

The dist agent is experiencing errors........

"Could not find stored procedure 'sp_MSins_tester'

Error number: 2812

Initially it gave an error of 18452 on dist agent of Could not connect.
I updated the id/pw for the subscriber and that went away and generated the above.

Any thoughts on what I should try next?

--mattHi Matt ,

from what I have experienced (both errors) you will have to create a stored procedure for you dist agent , that you should solve the problem.you might want to check sql books online there is more info there.

Hope this helps in some way..

Regards
Burner|||What did you end up doing to get by this problem ?|||Which service pack do you have installed for sql server 2000 (on both sql servers) ?

2601 Error When Creating or Altering a SPROC SS2005

I get the following error(in profiler) when creating or altering a stored
procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
To duplicate create a table (table1) start profiler, add errors and
warnings, start the trace, create the proc below
create procedure test
As
select * from table1
select * from table1
go
The number of exceptions you see in the profiler trace is the same as the
number of columns on table1. Is this some kind of database engine problem
where the engine is trying to insert/update sys.sql_dependencies?I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
(object doesn't exist).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
>I get the following error(in profiler) when creating or altering a stored
> procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> To duplicate create a table (table1) start profiler, add errors and
> warnings, start the trace, create the proc below
> create procedure test
> As
> select * from table1
> select * from table1
> go
> The number of exceptions you see in the profiler trace is the same as the
> number of columns on table1. Is this some kind of database engine problem
> where the engine is trying to insert/update sys.sql_dependencies?
>|||Tibor,
Thanks for the reply. I went back and tested this and it happens in my
production version:
9.00.1399.06 RTM Enterprise Edition (64-bit)
But it does not happen in my development version:
9.00.2047.00 SP1 Developer Edition
Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
and see if it goes away.
Thanks
"Tibor Karaszi" wrote:
> I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
> (object doesn't exist).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
> >I get the following error(in profiler) when creating or altering a stored
> > procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> >
> > To duplicate create a table (table1) start profiler, add errors and
> > warnings, start the trace, create the proc below
> >
> > create procedure test
> > As
> > select * from table1
> > select * from table1
> > go
> >
> > The number of exceptions you see in the profiler trace is the same as the
> > number of columns on table1. Is this some kind of database engine problem
> > where the engine is trying to insert/update sys.sql_dependencies?
> >
> >
>
>|||Upgraded to:
9.00.2047.00 SP1 Enterprise Edition (64-bit)
Still has the same issue. Can anyone else duplicate this issue?
Thanks
"hpux9@.nospam.nospam" wrote:
> Tibor,
> Thanks for the reply. I went back and tested this and it happens in my
> production version:
> 9.00.1399.06 RTM Enterprise Edition (64-bit)
> But it does not happen in my development version:
> 9.00.2047.00 SP1 Developer Edition
> Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
> and see if it goes away.
> Thanks
> "Tibor Karaszi" wrote:
> > I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
> > (object doesn't exist).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
> > >I get the following error(in profiler) when creating or altering a stored
> > > procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> > >
> > > To duplicate create a table (table1) start profiler, add errors and
> > > warnings, start the trace, create the proc below
> > >
> > > create procedure test
> > > As
> > > select * from table1
> > > select * from table1
> > > go
> > >
> > > The number of exceptions you see in the profiler trace is the same as the
> > > number of columns on table1. Is this some kind of database engine problem
> > > where the engine is trying to insert/update sys.sql_dependencies?
> > >
> > >
> >
> >
> >

208 error in stored procedure

Finding 208 errors in profiler trace while executing a
stored procedure yet the results look okay. Anyone run
across this before?Could be a variety of things, see http://tinyurl.com/swjv
"George H" <anonymous@.discussions.microsoft.com> wrote in message
news:037b01c39e5c$b55ebad0$a301280a@.phx.gbl...
> Finding 208 errors in profiler trace while executing a
> stored procedure yet the results look okay. Anyone run
> across this before?|||Look at KB article 243352. This will tell you how to tun it off.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"George H" <anonymous@.discussions.microsoft.com> wrote in message
news:037b01c39e5c$b55ebad0$a301280a@.phx.gbl...
> Finding 208 errors in profiler trace while executing a
> stored procedure yet the results look okay. Anyone run
> across this before?

20598 MSrepl_errors information required

I'd like to know how to know what replication stored procedure and parameters
to that is being run for a 20598 error I'm receiving.
in MSrepl_errors, for all these 20598 errors
error_text is: "The row was not found at the Subscriber when applying the
replicated command."
xact_seqno is: 0x0000... (ZERO)
so there seems to be no chance to use sp_browsereplcmds <xact_seqno>
My configuration: Transactional "nosync" replication
SQL Server 2000 SP4
The Distribution Agent Profile has the following:
-SkipError 2601:2627:20598
This is due to the fact my Publisher has a 2 year history of data and we're
replicating new data to a fresh DB . Deletes/Updates are not found, hence
the 20598 errors.
However, I'd like to know what data is erroring out. I will likely be able
to reduce these errors to a minimum but don't have any information on them to
investigate.
Suggestions?
Jack Griffith
Enable logging as per this kb article.
http://support.microsoft.com/kb/312292
Note that it appears that you are trying to update a row on the subscriber
which has already been deleted.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jack Griffith" <JackGriffith@.discussions.microsoft.com> wrote in message
news:188524CC-3706-4D41-B916-319D5C0715F0@.microsoft.com...
> I'd like to know how to know what replication stored procedure and
> parameters
> to that is being run for a 20598 error I'm receiving.
>
> in MSrepl_errors, for all these 20598 errors
> error_text is: "The row was not found at the Subscriber when applying the
> replicated command."
> xact_seqno is: 0x0000... (ZERO)
> so there seems to be no chance to use sp_browsereplcmds <xact_seqno>
> My configuration: Transactional "nosync" replication
> SQL Server 2000 SP4
> The Distribution Agent Profile has the following:
> -SkipError 2601:2627:20598
> This is due to the fact my Publisher has a 2 year history of data and
> we're
> replicating new data to a fresh DB . Deletes/Updates are not found, hence
> the 20598 errors.
> However, I'd like to know what data is erroring out. I will likely be
> able
> to reduce these errors to a minimum but don't have any information on them
> to
> investigate.
> Suggestions?
> --
> Jack Griffith
|||Updating a row that has been deleted is indicated by the xact_seqno being 0x00?
Jack Griffith
"Hilary Cotter" wrote:

> Enable logging as per this kb article.
> http://support.microsoft.com/kb/312292
> Note that it appears that you are trying to update a row on the subscriber
> which has already been deleted.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Jack Griffith" <JackGriffith@.discussions.microsoft.com> wrote in message
> news:188524CC-3706-4D41-B916-319D5C0715F0@.microsoft.com...
>
>
|||- First of all. Thank you! I really appreciate your quick response to my
initial request. I applied the suggestion from the knowledgebase article,
heeding the warning regarding the impact of logging.
- The net result was that I was able to identify and resolve one of the
sources of my errors which reduced about 90% of those occuring. I'm left
with 10% unsolved and no practical way to identify them.
==> My question is:
Why is the xact_seqno in MSrepl_errors = 0x00 when it should be ( I think!):
0x0004c6c4000006570007
If MSrepl_errors could the text of the error, and xact_seqno, it would take
huge burdon off, as I cannot afford to run the logging given the onerous load.
More details are below to explain this question.
- The impact of error logging as per your suggestion was huge! In one hour,
my log filled up with about 50Mb's and the transaction latency went from
1,500 ms to 1,500,000ms.
- As soon as I started to log the activity, there were no errors. I was
thinking it was a classic "Heisenberg uncertainty principle" situation, but
in the last couple of minutes of the hour, I got 2 errors.
- Had to stop logging at that point. I reset the logging level to '0' which
should log errors only. However, it didn't seem to do that.
- I inspected the error logs and was a bit misled. You have to be careful
to group all of the error activity together in spite of the other unrelated
transaction activity that is interleaved. It's somewhat logical but
disorienting.
- OK the two errors were attributed to UPDATE statements to a table for
which the rows are non-existent in the Subscriber side. It was not
unexpected in this case.
- What is unexpected, is the inability of the MSrepl_errors table to track
the information. I say this because I find the following information in the
log:
============= start of error information =================
Last transaction timestamp: 0x0004c6c400000656000200000000
Transaction seqno: 0x0004c6c4000006570007
--^^^^^^^^^^^^^^^^^
Command Id: 1
Partial: 0
Type: 0
Command: [4/1/2007 6:42:46 PM]<SQL Server name>.<DB Name>:
{...several CALL sp_MSins<no problem table>...}
{CALL to sp_MSupd<culprit table> }
^^^^^^^^^^^^^^^^^^^^^^^^
{...several CALL sp_MSins<no problem table>...}
Skipped 1 error(s) when applying transactions at the Subscriber.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[4/1/2007 6:42:46 PM]<SQL Server name>.<DB Name>: update
MSreplication_subscriptions set transaction_timestamp = ?, "time" = ? where
publisher = ? and publisher_db = ? and independent_agent = 0 and
subscription_type = 0
Last transaction timestamp: 0x0004c6c400000657000700000000
Transaction seqno: 0x0004c6c4000006590009
Command Id: 1
Partial: 0
Type: 0
Command: [4/1/2007 6:42:46 PM]<SQL Server name>.distribution: {call
sp_MSadd_distribution_history(13, 3, ?, ?, 92518, 92518, 0.00, 0x01, 1, ?,
0, 0x01, 0x00)}
The commit timestamp value is: 0x0004c6c400000654000600000000
if exists (select * from sysobjects where name = 'syspublications') if
exists( select * from syspublications where repl_freq = 0) exec
sp_replsetoriginator ?, ?
ErrorId = 974, SourceTypeId = 5
ErrorCode = '20598'
ErrorText = 'The row was not found at the Subscriber when applying the
replicated command.'
[4/1/2007 6:42:46 PM]<SQL Server name>.distribution: {call
sp_MSadd_repl_error(974, 0, 5, ?, N'20598', ?)}
=================== end of error stream ====================
- So to repeat: why is the xact_seqno in MSrepl_errors = 0x00 when it
should be ( I think!): 0x0004c6c4000006570007
Bug?
Jack Griffith

2005 Stored Procedure Question - How to pass in additional LIKEconditions

Hi Paparush,

I believe the problem is because you're mixing up dynamic SQL with
static SQL.
The @.SearchTerm variable that you are building up can not be used to
specify additional LIKE criteria for the Subject column, each LIKE
condition needs to be individually coded into your static SQL.

The alternative is to build the entire SELECT as dynamic SQL. This
provides additional flexibility, but there are performance and
security implications when doing this (google SQL injection).

Good luck!
JOn Dec 17, 10:33 am, jhofm...@.googlemail.com wrote:

Quote:

Originally Posted by

Hi Paparush,
>
I believe the problem is because you're mixing up dynamic SQL with
static SQL.
The @.SearchTerm variable that you are building up can not be used to
specify additional LIKE criteria for the Subject column, each LIKE
condition needs to be individually coded into your static SQL.
>
The alternative is to build the entire SELECT as dynamic SQL. This
provides additional flexibility, but there are performance and
security implications when doing this (google SQL injection).
>
Good luck!
J

Hi J,
Thanks for the reply.

Yes..the dynamic SQL is the approach I've been trying all morning and
I've gotten it to work. I have to build the entire SELECT string in
my app's code, based on the search term the user enters and then stuff
this into @.SearchTerm and pass this string into the code below.

declare @.SearchTerm nvarchar(max)

EXEC sp_ExecuteSQL @.Search

2005 clr returning xmldocument

I would like to return an xmldocument from a 2005 vb clr stored procedure.

This is my definition for the stored procedure. passing in a string, return xmldoc.

Can I not return an xmldoc as output? The solution will build, but not run.

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Sub SP_Transform(ByVal cc As String, <Out()> ByVal RetValue As XmlDocument)

Error 1 Column, parameter, or variable #2: Cannot find data type XmlDocument. SqlServerProject1

Interesting question. I'll have to try it.

My gut, however, is that you should just return the XML data using the new Xml Data Type in SQL Server 2005 and just use a DataReader like dr.GetSqlXml(index) in ADO.NET to read the XML and load it into an XmlDocument.

I wrote an example of that here-

Reading XML Data Type into XmlDocument Using ADO.NET - SQL Server 2005 Tutorials

Regards,

Dave

2005 - linked server query

I had a database, stored procedure, and related job in 2000 that
checked other servers (via linked server functionality) for failed
jobs. Works fine in 2000. I migrated the database over to my local
2005 instance, and now the query does not work any longer. The below
query returns the error below. Both the 2000 instance and the 2005
instance use Windows Authentication. Does anyone have any ideas?
SELECT 'LinkedServername',
j.job_id,
j.name,
jh.sql_message_id,
jh.sql_severity,
jh.message,
jh.run_date,
jh.run_time
FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
[LinkedServername].[msdb].[dbo].[sysjobs] j
WHERE jh.run_status = 0
and jh.job_id = j.job_id
and Not Exists (
Select *
From failedjobs f
Where jh.run_status = 0
and jh.job_id = j.job_id
and jh.job_id = f.job_id
and jh.run_date = f.run_date
and jh.run_time = f.run_time)
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
does not exist or the current user does not have permissions on that
table.Anyone?|||Hi
Do you have permissions on the DB and table? Security has been tightened up,
so things that worked in 2000 may not work in 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131561766.037644.219120@.z14g2000cwz.googlegroups.com...
>I had a database, stored procedure, and related job in 2000 that
> checked other servers (via linked server functionality) for failed
> jobs. Works fine in 2000. I migrated the database over to my local
> 2005 instance, and now the query does not work any longer. The below
> query returns the error below. Both the 2000 instance and the 2005
> instance use Windows Authentication. Does anyone have any ideas?
>
> SELECT 'LinkedServername',
> j.job_id,
> j.name,
> jh.sql_message_id,
> jh.sql_severity,
> jh.message,
> jh.run_date,
> jh.run_time
> FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
> [LinkedServername].[msdb].[dbo].[sysjobs] j
> WHERE jh.run_status = 0
> and jh.job_id = j.job_id
> and Not Exists (
> Select *
> From failedjobs f
> Where jh.run_status = 0
> and jh.job_id = j.job_id
> and jh.job_id = f.job_id
> and jh.run_date = f.run_date
> and jh.run_time = f.run_time)
> Msg 7314, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
> not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
> does not exist or the current user does not have permissions on that
> table.
>|||Using SQL authentication, I'm dbo/sa on everything. In windows
authentication, I'm in a SQL DBA group which is aliased over to
sa/dbo/Sys Admin. I include both b/c this stored procedure goes out to
lots of linked servers and some are windows, some are sql. It works on
my 2000 instance - same stored procedure, same query, same linked
servers.

2005 - linked server query

I had a database, stored procedure, and related job in 2000 that
checked other servers (via linked server functionality) for failed
jobs. Works fine in 2000. I migrated the database over to my local
2005 instance, and now the query does not work any longer. The below
query returns the error below. Both the 2000 instance and the 2005
instance use Windows Authentication. Does anyone have any ideas?
SELECT 'LinkedServername',
j.job_id,
j.name,
jh.sql_message_id,
jh.sql_severity,
jh.message,
jh.run_date,
jh.run_time
FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
[LinkedServername].[msdb].[dbo].[sysjobs] j
WHERE jh.run_status = 0
and jh.job_id = j.job_id
and Not Exists (
Select *
From failedjobs f
Where jh.run_status = 0
and jh.job_id = j.job_id
and jh.job_id = f.job_id
and jh.run_date = f.run_date
and jh.run_time = f.run_time)
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
does not exist or the current user does not have permissions on that
table.
Anyone?
|||Hi
Do you have permissions on the DB and table? Security has been tightened up,
so things that worked in 2000 may not work in 2005.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131561766.037644.219120@.z14g2000cwz.googlegr oups.com...
>I had a database, stored procedure, and related job in 2000 that
> checked other servers (via linked server functionality) for failed
> jobs. Works fine in 2000. I migrated the database over to my local
> 2005 instance, and now the query does not work any longer. The below
> query returns the error below. Both the 2000 instance and the 2005
> instance use Windows Authentication. Does anyone have any ideas?
>
> SELECT 'LinkedServername',
> j.job_id,
> j.name,
> jh.sql_message_id,
> jh.sql_severity,
> jh.message,
> jh.run_date,
> jh.run_time
> FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
> [LinkedServername].[msdb].[dbo].[sysjobs] j
> WHERE jh.run_status = 0
> and jh.job_id = j.job_id
> and Not Exists (
> Select *
> From failedjobs f
> Where jh.run_status = 0
> and jh.job_id = j.job_id
> and jh.job_id = f.job_id
> and jh.run_date = f.run_date
> and jh.run_time = f.run_time)
> Msg 7314, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
> not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
> does not exist or the current user does not have permissions on that
> table.
>
|||Using SQL authentication, I'm dbo/sa on everything. In windows
authentication, I'm in a SQL DBA group which is aliased over to
sa/dbo/Sys Admin. I include both b/c this stored procedure goes out to
lots of linked servers and some are windows, some are sql. It works on
my 2000 instance - same stored procedure, same query, same linked
servers.

2005 - linked server query

I had a database, stored procedure, and related job in 2000 that
checked other servers (via linked server functionality) for failed
jobs. Works fine in 2000. I migrated the database over to my local
2005 instance, and now the query does not work any longer. The below
query returns the error below. Both the 2000 instance and the 2005
instance use Windows Authentication. Does anyone have any ideas?
SELECT 'LinkedServername',
j.job_id,
j.name,
jh.sql_message_id,
jh.sql_severity,
jh.message,
jh.run_date,
jh.run_time
FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
[LinkedServername].[msdb].[dbo].[sysjobs] j
WHERE jh.run_status = 0
and jh.job_id = j.job_id
and Not Exists (
Select *
From failedjobs f
Where jh.run_status = 0
and jh.job_id = j.job_id
and jh.job_id = f.job_id
and jh.run_date = f.run_date
and jh.run_time = f.run_time)
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
does not exist or the current user does not have permissions on that
table.Anyone?|||Hi
Do you have permissions on the DB and table? Security has been tightened up,
so things that worked in 2000 may not work in 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131561766.037644.219120@.z14g2000cwz.googlegroups.com...
>I had a database, stored procedure, and related job in 2000 that
> checked other servers (via linked server functionality) for failed
> jobs. Works fine in 2000. I migrated the database over to my local
> 2005 instance, and now the query does not work any longer. The below
> query returns the error below. Both the 2000 instance and the 2005
> instance use Windows Authentication. Does anyone have any ideas?
>
> SELECT 'LinkedServername',
> j.job_id,
> j.name,
> jh.sql_message_id,
> jh.sql_severity,
> jh.message,
> jh.run_date,
> jh.run_time
> FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
> [LinkedServername].[msdb].[dbo].[sysjobs] j
> WHERE jh.run_status = 0
> and jh.job_id = j.job_id
> and Not Exists (
> Select *
> From failedjobs f
> Where jh.run_status = 0
> and jh.job_id = j.job_id
> and jh.job_id = f.job_id
> and jh.run_date = f.run_date
> and jh.run_time = f.run_time)
> Msg 7314, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
> not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
> does not exist or the current user does not have permissions on that
> table.
>|||Using SQL authentication, I'm dbo/sa on everything. In windows
authentication, I'm in a SQL DBA group which is aliased over to
sa/dbo/Sys Admin. I include both b/c this stored procedure goes out to
lots of linked servers and some are windows, some are sql. It works on
my 2000 instance - same stored procedure, same query, same linked
servers.