error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

Error evaluating expression

Greetings my SQL friends.

Why do I get an error message saying that the following expression won't evaluate?

"Select * from Price_grp where price_grp_id >= " + (dt_str, 10, 1252) @.[User::MIN_PRICE_GRP_ID] +
" and " + (dt_str, 10, 1252) @.[User::MIN_PRICE_GRP_ID] + " > 0"

Thanks for your help in advance.

Hi friends,

I have solved the problem now. I didn't change the code but I got it to evaluate in the end. I don't know what I did to fix it but perhaps there was some issue with the spaces. I don't know...

|||

Do you still have a note of the error message you had when it would not evaluate?

Donald

|||

Afraid not. Like I said, not quite sure how I sorted it out but I reckon there was some issue with white spaces or something because the same expression worked without any changes!

Thanks.

Error during merge web sync

Hello,

We have 5 subscribers trying to replicate via web synchronization. Two of the subscribers get the following message and the other three are fine. Any ideas on what the problem is or where to troubleshoot? Thanks in advance.

John

Error on client output:

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Are you using a mix of .NET 1.1 and .NET 2.0 in your applications? If so, that can cause this error to happen. Try migrating your application to .NET 2.0 completely and see if that fixes your problem.|||

Mahesh,

Our app does uses a class that was developed using 1.1. However this class has nothing to do with replication. Could this still cause this error?

John

|||

On the IIS machine if there is an app that uses .NET 1.1, it could cause this to happen.

Another way to troubleshoot is to run https://Machine.domain/virtualDir/replisapi.dll?diag and you should see SUCCESS in all the class instantiation on that page. If not, then that is the cause of the problem.

|||

Mahesh,

This is a web server box that only has two virtual directories. Both are used for replication. One for sqlexpress replication and the other for WM 5.0 devices. We get successes from both sqlexpressurl/replisapi.dll?diag and wm50url/sqlcesa30.dll?diag, with the exception of the reconciler for the wm50url/sqlcesa30.dll?diag for version 8.0 which we are not using. This sites are all running asp 2.0

John

|||I have seen typically that once is 3 times you can get successes. Other times it could fail to load the dll and hance cause the sync to fail. Can you try on a different IIS box that doesnt have anything except the virtual directory to rule out this possibility?|||Hi John,

I have had this exact same message. Basically this occured because changes to the publisher database meant the subscribers required a new data partition snapshot. I pre-generated the snapshot and I have the option to allow subscribers to auto generate snapshots switched off. When the subscriber connects it does not recognise and hence does not download the pre-generated snapshot file and there for began the process of enumerating all the data that is needed for the subscriber. Immediately after downloading x number of chunks it then pops the same messaqge you receive.

If I was to switch auto generate snapshots option on then the subscriber happily runs the snapshot agent then downloads and applies it as expected. This was not ideal as we did not want auto-snapshot on for server load reasons.

We were at this timeusing FTP not UNC for delivery of snapshots. We had trouble with UNC shares between the servers which forced our hand on this. I spent two weeks in conversation with Microft Tech Support, until I tried finally to solve the UNC issue. I managed to get the UNC share visible from our webserver and hey presto it sorted our problem out and the subscribers happily retrieve pre-generated snapshots.

Not sure if this is anyway similar situation but might be useful

Cheers
Rab|||

Rab,

I'll give that a try thanks for the input I appreciate it.

John

|||

Mahesh,

We are having the same problem. The client application is solely 2.0 framework, but the client unit also has framework 1.1 for some other applications not related to the replication process. The webserver is solely 2.0 framework and doesn't even have 1.1 installed on the machine. Is there another reason for these errors? It looks like we get the 14151 error first the the web synchronization right after it. SQL 2k5 SP1.

Server logs:

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Replication-Replication Merge Subsystem: agent INSTANCE_NAME failed. The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Error: 14151, Severity: 18, State: 1.

Error during merge web sync

Hello,

We have 5 subscribers trying to replicate via web synchronization. Two of the subscribers get the following message and the other three are fine. Any ideas on what the problem is or where to troubleshoot? Thanks in advance.

John

Error on client output:

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Are you using a mix of .NET 1.1 and .NET 2.0 in your applications? If so, that can cause this error to happen. Try migrating your application to .NET 2.0 completely and see if that fixes your problem.|||

Mahesh,

Our app does uses a class that was developed using 1.1. However this class has nothing to do with replication. Could this still cause this error?

John

|||

On the IIS machine if there is an app that uses .NET 1.1, it could cause this to happen.

Another way to troubleshoot is to run https://Machine.domain/virtualDir/replisapi.dll?diag and you should see SUCCESS in all the class instantiation on that page. If not, then that is the cause of the problem.

|||

Mahesh,

This is a web server box that only has two virtual directories. Both are used for replication. One for sqlexpress replication and the other for WM 5.0 devices. We get successes from both sqlexpressurl/replisapi.dll?diag and wm50url/sqlcesa30.dll?diag, with the exception of the reconciler for the wm50url/sqlcesa30.dll?diag for version 8.0 which we are not using. This sites are all running asp 2.0

John

|||I have seen typically that once is 3 times you can get successes. Other times it could fail to load the dll and hance cause the sync to fail. Can you try on a different IIS box that doesnt have anything except the virtual directory to rule out this possibility?|||Hi John,

I have had this exact same message. Basically this occured because changes to the publisher database meant the subscribers required a new data partition snapshot. I pre-generated the snapshot and I have the option to allow subscribers to auto generate snapshots switched off. When the subscriber connects it does not recognise and hence does not download the pre-generated snapshot file and there for began the process of enumerating all the data that is needed for the subscriber. Immediately after downloading x number of chunks it then pops the same messaqge you receive.

If I was to switch auto generate snapshots option on then the subscriber happily runs the snapshot agent then downloads and applies it as expected. This was not ideal as we did not want auto-snapshot on for server load reasons.

We were at this timeusing FTP not UNC for delivery of snapshots. We had trouble with UNC shares between the servers which forced our hand on this. I spent two weeks in conversation with Microft Tech Support, until I tried finally to solve the UNC issue. I managed to get the UNC share visible from our webserver and hey presto it sorted our problem out and the subscribers happily retrieve pre-generated snapshots.

Not sure if this is anyway similar situation but might be useful

Cheers
Rab
|||

Rab,

I'll give that a try thanks for the input I appreciate it.

John

|||

Mahesh,

We are having the same problem. The client application is solely 2.0 framework, but the client unit also has framework 1.1 for some other applications not related to the replication process. The webserver is solely 2.0 framework and doesn't even have 1.1 installed on the machine. Is there another reason for these errors? It looks like we get the 14151 error first the the web synchronization right after it. SQL 2k5 SP1.

Server logs:

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Replication-Replication Merge Subsystem: agent INSTANCE_NAME failed. The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Error: 14151, Severity: 18, State: 1.

Error during installation of reporting services

Hi,
I've stuck with an error message and I can't sort it out. I am trying to
install a standard reporting service on a server separated from database
server.
Web server (report services): no domain
Databasebase server: a member of a domain.
I can install RS with domain authentication mode but I can't with SQL
authentication such as sa.
Errror message:
login failed for user '(null)'. reason: not associated with a trusted sql
server connection.
Anyone have an workaround for this error?
ThanksI found that using setup /i command lines and I did without any problem.
"Oliver" <dummy@.hotmail.com> wrote in message
news:eRbesy9EFHA.1936@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I've stuck with an error message and I can't sort it out. I am trying to
> install a standard reporting service on a server separated from database
> server.
> Web server (report services): no domain
> Databasebase server: a member of a domain.
> I can install RS with domain authentication mode but I can't with SQL
> authentication such as sa.
> Errror message:
> login failed for user '(null)'. reason: not associated with a trusted sql
> server connection.
> Anyone have an workaround for this error?
> Thanks
>|||Running setup with the /i command line did not have any impact on this issue
for me.
"Oliver" wrote:
> I found that using setup /i command lines and I did without any problem.
> "Oliver" <dummy@.hotmail.com> wrote in message
> news:eRbesy9EFHA.1936@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I've stuck with an error message and I can't sort it out. I am trying to
> > install a standard reporting service on a server separated from database
> > server.
> > Web server (report services): no domain
> > Databasebase server: a member of a domain.
> >
> > I can install RS with domain authentication mode but I can't with SQL
> > authentication such as sa.
> >
> > Errror message:
> > login failed for user '(null)'. reason: not associated with a trusted sql
> > server connection.
> >
> > Anyone have an workaround for this error?
> >
> > Thanks
> >
> >
>
>

Error during deploy

Hi,
I am publishing my reports to a server and for 2 out of 7 reports I receive
the following error message:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException was
thrown.
On my machine the reports worked fine.
The server does process the other reports correctly.
The server is a SQL Server 2000, my machine has SQL Server 2005 Beta 2.
These two failing reports don't seem to have anything special compared to
the others.
Any suggestions what might be causing this? (And how to solve it?)
Thanks a lot for your help,
Edgar
--
Message posted via http://www.sqlmonster.comCheck the log files for more details.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Edgar Walther via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee24db616d0849f59f74c0c0f1ef3f9d@.SQLMonster.com...
> Hi,
> I am publishing my reports to a server and for 2 out of 7 reports I
> receive
> the following error message:
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException was
> thrown.
> On my machine the reports worked fine.
> The server does process the other reports correctly.
> The server is a SQL Server 2000, my machine has SQL Server 2005 Beta 2.
> These two failing reports don't seem to have anything special compared to
> the others.
> Any suggestions what might be causing this? (And how to solve it?)
> Thanks a lot for your help,
> Edgar
> --
> Message posted via http://www.sqlmonster.com|||Where can I find these log files?
I've looked in the windows application event log for ReportingServices
events, but there were no events (except report server started).
Thanks,
Edgar
"Jeff A. Stucker" wrote:
> Check the log files for more details.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Edgar Walther via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:ee24db616d0849f59f74c0c0f1ef3f9d@.SQLMonster.com...
> > Hi,
> >
> > I am publishing my reports to a server and for 2 out of 7 reports I
> > receive
> > the following error message:
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException was
> > thrown.
> >
> > On my machine the reports worked fine.
> > The server does process the other reports correctly.
> > The server is a SQL Server 2000, my machine has SQL Server 2005 Beta 2.
> >
> > These two failing reports don't seem to have anything special compared to
> > the others.
> >
> > Any suggestions what might be causing this? (And how to solve it?)
> >
> > Thanks a lot for your help,
> >
> > Edgar
> >
> > --
> > Message posted via http://www.sqlmonster.com
>
>|||In a default installation, here:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:32D4111D-7B88-47A3-830D-257BF001587E@.microsoft.com...
> Where can I find these log files?
> I've looked in the windows application event log for ReportingServices
> events, but there were no events (except report server started).
> Thanks,
> Edgar
>
> "Jeff A. Stucker" wrote:
>> Check the log files for more details.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Edgar Walther via SQLMonster.com" <forum@.SQLMonster.com> wrote in
>> message
>> news:ee24db616d0849f59f74c0c0f1ef3f9d@.SQLMonster.com...
>> > Hi,
>> >
>> > I am publishing my reports to a server and for 2 out of 7 reports I
>> > receive
>> > the following error message:
>> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
>> > was
>> > thrown.
>> >
>> > On my machine the reports worked fine.
>> > The server does process the other reports correctly.
>> > The server is a SQL Server 2000, my machine has SQL Server 2005 Beta 2.
>> >
>> > These two failing reports don't seem to have anything special compared
>> > to
>> > the others.
>> >
>> > Any suggestions what might be causing this? (And how to solve it?)
>> >
>> > Thanks a lot for your help,
>> >
>> > Edgar
>> >
>> > --
>> > Message posted via http://www.sqlmonster.com
>>sql

Error during Copy Database Wizard operation

Do anyone know why i got "Failed to create the share OMWWIZD" error message when trying to copy databases from SQL Server 7.0 to SQL Server 2000?
Thanks!Probably something to do with security permissions between the two servers. If so, pick an administrative account which is the same on both. Run the wizard as that account. Test access by logging into one server and map a drive to the other.

--jfp|||Thanks for the suggestion! I tried using a domain admin to copy the database over and it works! I was using a local admin account before to establish the connection.

Thanks again.

--Speedster.|||hi

I have the same problem but i'm in workstation ...
i used Administrator account in the source server and the destination server, but it doesn't work :eek:

What can i do ?|||- quoting "hi I have the same problem but i'm in workstation ...
i used Administrator account in the source server and the destination server, but it doesn't work What can i do ?"

asanhaji, are you accessing it from a windows workstation via Enterprise Manger? Is it possible to be at one of the servers locally and try to copy it over? Unless you are a logged into the workstation with a domain admin account OR connect to the server in Enterprise manager with a domain admin account you may not have any luck in copying the stuff over. You may need to connect to both servers using a DBO account (typically sa is the user name for it).

slashman

error during .NET Remoting

Hi

When I run the reports in my .NET application, I get an error message when I attempt to use .NET remoting while processing a report :

"The type CrystalDecisions.CrystalReports.Engine.FormatEngine in Assembly CrystalDecisions.CrystalReports.Engine, Version=11.2.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304 is not marked as serializable."

I checked the net for solution, and the work around for this error message, use Report Web Services, or the Crystal Enterprise Report Application Server (CE RAS) or Crystal Enterprise (CE) .NET SDK in your application.

But this will be a major change in the application.

Has anybody else got the same error and found a simpler solution ?

Thanking in advanceSee if you find answer here
http://support.businessobjects.com/

Error Dropping Database using SMO

I use the method shown below to drop a SQL Server database using SMO. On occasion, I'll get the following error message when this method is called. I'm not sure what I can do to prevent an error like this from happening. I don't think I have any control over it. The error message is this:

Drop all active database connections failed for Server 'SDTPLCHost1\SQLServer2005'. : An exception occurred while executing a Transact-SQL statement or batch.: Process ID 57 is not an active process ID.

Here is the method I wrote that uses SMO to drop a database:

public void DropDatabase(string serverName, string databaseName)

{

Server smoServer;

try

{

smoServer = new Server(serverName);

if (!smoServer.Databases.Contains(databaseName))

return;

smoServer.KillAllProcesses(databaseName);

smoServer.KillDatabase(databaseName);

}

catch (Exception ex)

{

throw new Exception(BuildExceptionMessage(ex));

}

}

Use the KILLDatabase method, they changed it to bahave the way you would expect to:

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

I don't understand. You said to use the KillDatabase method. I am using the KillDatabase method! You then include a link to an article which seems to imply that the KillDatabase method doesn't work the way it should and then you supply some sample code to make dropping a database work the way it should.

So, am I correct that in order to drop a database, I should use the code you referenced in the link? It seems I should. I've used this technique before to drop a database (without using SMO) and it's always worked.

Amos.

|||Sorry, forgot to mentioned that they kept track on that issue with the SMO connect bug, reflecting the sources they changed it to:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));

Error deploying report with SQL Server (Visual Studio) 2005

Hi All,
I'm trying to deploy a report that have just been created in Visual
Studio 2005 (for Microsoft CRM) I'm getting an error message
******************************
The project cannot be deployed because no target server is specified.
Provide a value for the TargetServerURL property in the property page
of this project.
******************************
I'm sorry I'm new with this Visual Studio 2005 ... I'm not finding the
place to put the TargetServerURL value in that property page ... The
worst thing is that it is probably right under my nose ... ;-)
Anybody can help a dummy ? ... ThanksProject -> your Project Name Properties (Last Menu Item) on the pop up
window you'll find what you're looking for.
Manny
Frank wrote:
>Hi All,
>I'm trying to deploy a report that have just been created in Visual
>Studio 2005 (for Microsoft CRM) I'm getting an error message
>******************************
>The project cannot be deployed because no target server is specified.
>Provide a value for the TargetServerURL property in the property page
>of this project.
>******************************
>I'm sorry I'm new with this Visual Studio 2005 ... I'm not finding the
>place to put the TargetServerURL value in that property page ... The
>worst thing is that it is probably right under my nose ... ;-)
>Anybody can help a dummy ? ... Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200701/1

error deploying analysis server cube

Has anyone ever received this message before?
"The Description element at line 3123, column 29 (namespace
http://schemas.microsoft.com/analys...ces/2003/engine) cannot appear
under
Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Cubes/Cu
be/Dimensions/Dimension."
All I was doing was adding description to the properties of the dimension
tables. I was doing this so that the report user can see pop-up descriptions
in Report Builder.
I haven't been able to resolve this problem. Not only that - Now that I've
received this error, I haven't been able to reset the solution file so that
I
don't get this error. I have even removed all the description changes I
made, but to no avail.
Can anyone help me?
--JPThere are a couple of things you could try.
1) you could run Profiler while you are trying to deploy, this way you
would be able to capture the XMLA that is being sent to the server. You
would then be able to copy and paste the XMLA into a text editor and
search it. Maybe there is one change that you have missed undoing.
2) If all else fails, you could re-create your project from the database
on the server by starting a new project and choosing the "import
Analysis Services 9.0 database option"
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell
In article <16360AA6-10E3-4E75-9124-D939176CC1C2@.microsoft.com>,
Garza_JP@.discussions.microsoft.com says...
> Has anyone ever received this message before?
> "The Description element at line 3123, column 29 (namespace
> http://schemas.microsoft.com/analys...ces/2003/engine) cannot appear
> under
> Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Cubes/
Cube/Dimensions/Dimension."
> All I was doing was adding description to the properties of the dimension
> tables. I was doing this so that the report user can see pop-up descriptio
ns
> in Report Builder.
> I haven't been able to resolve this problem. Not only that - Now that I've
> received this error, I haven't been able to reset the solution file so tha
t I
> don't get this error. I have even removed all the description changes I
> made, but to no avail.
> Can anyone help me?
> --JP
>

Error Creating Data Source View in VS 2005 BI Tool for SQL Server 2005 View with > 32 Columns

I keep getting an error message in Visual Studio 2005 (SQL Server Business Intelligence Development Studio) = "Cannot have more than 32 columns" when I use the wizard to create a data source view using a view I created in a SQL Server 2005 database, which I use as a data source. The view does have more than 32 columns; however, I have been successful in creating data source views for other DB views with more than 32 columns. It seems that for some DB views with more than 32 columns the wizard ignores this problem, but for others - admittedly with a larger number of columns - it does not.

Any insights and potential workarounds? Thanks!

Please file a bug on http://connect.microsoft.com/SQLServer/Feedback and we'll look into this. If possible, please include a SQL DDL script that will create the source database which causes problems.

As a possible work around, you can try creating the DSV without this table and then adding the table in the DSV editor and see if that works.

Thanks

|||

Thanks. I will file a bug report, as you suggested. Regarding your suggestion, I forgot to mention that I tried that too and got the same error message. But it gave me another idea to try, which worked. I created the data source view using the wizard without selecting a table or a view from the data source, and then I used the New Named Query menu option to re-create the view (i.e., using the same SQL statement to create the view in the SQL Server 2005 DB). That did the trick! (BTW, there is another issue with the New Named Query pop-up window when you enter a SQL query and click on the OK button without entering a name for the query.)

FYI, below are the message and the program location details for the error related to the maximum number of columns in a data source view:

Message:

Cannot have more than 32 columns. (Microsoft Visual Studio)

Program Location:

at System.Data.DataKey..ctor(DataColumn[] columns, Boolean copyColumns)
at System.Data.UniqueConstraint.Create(String constraintName, DataColumn[] columns)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()

Error Creating Data Source View in VS 2005 BI Tool for SQL Server 2005 View with > 32 Col

I keep getting an error message in Visual Studio 2005 (SQL Server Business Intelligence Development Studio) = "Cannot have more than 32 columns" when I use the wizard to create a data source view using a view I created in a SQL Server 2005 database, which I use as a data source. The view does have more than 32 columns; however, I have been successful in creating data source views for other DB views with more than 32 columns. It seems that for some DB views with more than 32 columns the wizard ignores this problem, but for others - admittedly with a larger number of columns - it does not.

Any insights and potential workarounds? Thanks!

Please file a bug on http://connect.microsoft.com/SQLServer/Feedback and we'll look into this. If possible, please include a SQL DDL script that will create the source database which causes problems.

As a possible work around, you can try creating the DSV without this table and then adding the table in the DSV editor and see if that works.

Thanks

|||

Thanks. I will file a bug report, as you suggested. Regarding your suggestion, I forgot to mention that I tried that too and got the same error message. But it gave me another idea to try, which worked. I created the data source view using the wizard without selecting a table or a view from the data source, and then I used the New Named Query menu option to re-create the view (i.e., using the same SQL statement to create the view in the SQL Server 2005 DB). That did the trick! (BTW, there is another issue with the New Named Query pop-up window when you enter a SQL query and click on the OK button without entering a name for the query.)

FYI, below are the message and the program location details for the error related to the maximum number of columns in a data source view:

Message:

Cannot have more than 32 columns. (Microsoft Visual Studio)

Program Location:

at System.Data.DataKey..ctor(DataColumn[] columns, Boolean copyColumns)
at System.Data.UniqueConstraint.Create(String constraintName, DataColumn[] columns)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()

error creating cursor handle

this is the message wath i got back from a simple table update procedure
error creating cursor handle
WHY??which database engine do you use?
how is anyone supposed to figure out what went wrong if you don't post actual simple table update procedure?
does this error have error code? If so, which one is it?|||the error was solved on anodher forum|||So you cross-posted this message. Generally, it is a bad habit - it causes confusion (should I answer this question or that?). But, if you've already done that, you could have posted link to the answer too.

Error copying SQL 2000 DB to SQL 2005

Hi all,
We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
is the error message received. We are doing this by using the 'copy
database' feature in SQL 2005, is this the correct way to migrate a DB
to 2005? The database is not in use so we are unsure why this error
would be occuring. And on top of this we did not instruct SQL to drop
the DB?
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message
ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW_BUBBA_USE R,(Job
outcome),,The job failed. The Job was invoked by User DOMAIN\user.
The last step to run was step 1 (CDW_BUBBA_USER_Step).,
00:02:28,0,0,,,,0
05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Step,,Execute d as user: DOMAIN
\USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
Task Task just started the execution.: 0% complete End Progress
Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
Bubba_USER_Transfer Objects Task
Description: ERROR : errorCode=-1073548784 description=Executing the
query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
N'test_sb') DROP DATABASE [test_sb] " failed with the following
error: "Cannot drop database "test_sb" because it is currently in
use.". Possible failure reasons: Problems with the query<c/>
"ResultSet" property not set correctly<c/> parameters not set
correctly<c/> or connection not established correctly. helpFile=
helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
DA807BCDC2AC} StackTrace: at
Microsoft.SqlServer.Management.Dts.D... The package execution fa...
The step failed.,00:02:28,0,0,,,,0
Hello,
That should wor, do you the destination DB already there in SQL 2005 server.
If yes then try dropping the databaase and see if it works.
Alternate methodology is
1. Backup the database in sql 2000
2. COpy the file to SQL 2005 server
3. restore the database in SQL 2005. THis will automatically upgrade to SQL
2005.
4. Run a update statistics after upgrade,
Thanks
Hari
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegro ups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW_BUBBA_USE R,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Step,,Execute d as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>
|||>> "Cannot drop database "test_sb" because it is currently in use."
A database cannot be dropped if there is currently a connection open to it.
Try closing all connections on the server being copied to that are connected
to the database you are trying to copy over. sp_who2 will show you this,
and you can use the KILL command to kill off any spids that show connection
to that database. Make sure you are on the correct server when you do this!
:-)
TheSQLGuru
President
Indicium Resources, Inc.
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegro ups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW_BUBBA_USE R,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Step,,Execute d as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>
|||The best way is backup the database, copy the backup to destination server,
restore the database at destination server, change the compatibility to 9.0,
update statistic.
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegro ups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW_BUBBA_USE R,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Step,,Execute d as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>

Error copying SQL 2000 DB to SQL 2005

Hi all,
We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
is the error message received. We are doing this by using the 'copy
database' feature in SQL 2005, is this the correct way to migrate a DB
to 2005? The database is not in use so we are unsure why this error
would be occuring. And on top of this we did not instruct SQL to drop
the DB?
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message
ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW_BUBBA_USER,(Job
outcome),,The job failed. The Job was invoked by User DOMAIN\user.
The last step to run was step 1 (CDW_BUBBA_USER_Step).,
00:02:28,0,0,,,,0
05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Step,,Executed as user: DOMAIN
\USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
Task Task just started the execution.: 0% complete End Progress
Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
Bubba_USER_Transfer Objects Task
Description: ERROR : errorCode=-1073548784 description=Executing the
query "IF EXISTS (SELECT name FROM sys.databases WHERE name = N'test_sb') DROP DATABASE [test_sb] " failed with the following
error: "Cannot drop database "test_sb" because it is currently in
use.". Possible failure reasons: Problems with the query<c/>
"ResultSet" property not set correctly<c/> parameters not set
correctly<c/> or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
DA807BCDC2AC} StackTrace: at
Microsoft.SqlServer.Management.Dts.D... The package execution fa...
The step failed.,00:02:28,0,0,,,,0Hello,
That should wor, do you the destination DB already there in SQL 2005 server.
If yes then try dropping the databaase and see if it works.
Alternate methodology is
1. Backup the database in sql 2000
2. COpy the file to SQL 2005 server
3. restore the database in SQL 2005. THis will automatically upgrade to SQL
2005.
4. Run a update statistics after upgrade,
Thanks
Hari
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Step,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name => N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>|||>> "Cannot drop database "test_sb" because it is currently in use."
A database cannot be dropped if there is currently a connection open to it.
Try closing all connections on the server being copied to that are connected
to the database you are trying to copy over. sp_who2 will show you this,
and you can use the KILL command to kill off any spids that show connection
to that database. Make sure you are on the correct server when you do this!
:-)
TheSQLGuru
President
Indicium Resources, Inc.
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Step,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name => N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>|||The best way is backup the database, copy the backup to destination server,
restore the database at destination server, change the compatibility to 9.0,
update statistic.
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Step,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name => N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>

Error copying SQL 2000 DB to SQL 2005

Hi all,
We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
is the error message received. We are doing this by using the 'copy
database' feature in SQL 2005, is this the correct way to migrate a DB
to 2005? The database is not in use so we are unsure why this error
would be occuring. And on top of this we did not instruct SQL to drop
the DB?
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message
ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW
_BUBBA_USER,(Job
outcome),,The job failed. The Job was invoked by User DOMAIN\user.
The last step to run was step 1 (CDW_BUBBA_USER_Step).,
00:02:28,0,0,,,,0
05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Ste
p,,Executed as user: DOMAIN
\USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
Task Task just started the execution.: 0% complete End Progress
Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
Bubba_USER_Transfer Objects Task
Description: ERROR : errorCode=-1073548784 description=Executing the
query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
N'test_sb') DROP DATABASE [test_sb] " failed with the following
error: "Cannot drop database "test_sb" because it is currently in
use.". Possible failure reasons: Problems with the query<c/>
"ResultSet" property not set correctly<c/> parameters not set
correctly<c/> or connection not established correctly. helpFile=
helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
DA807BCDC2AC} StackTrace: at
Microsoft.SqlServer.Management.Dts.D... The package execution fa...
The step failed.,00:02:28,0,0,,,,0Hello,
That should wor, do you the destination DB already there in SQL 2005 server.
If yes then try dropping the databaase and see if it works.
Alternate methodology is
1. Backup the database in sql 2000
2. COpy the file to SQL 2005 server
3. restore the database in SQL 2005. THis will automatically upgrade to SQL
2005.
4. Run a update statistics after upgrade,
Thanks
Hari
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW
_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Ste
p,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>|||>> "Cannot drop database "test_sb" because it is currently in use."
A database cannot be dropped if there is currently a connection open to it.
Try closing all connections on the server being copied to that are connected
to the database you are trying to copy over. sp_who2 will show you this,
and you can use the KILL command to kill off any spids that show connection
to that database. Make sure you are on the correct server when you do this!
:-)
TheSQLGuru
President
Indicium Resources, Inc.
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW
_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Ste
p,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>|||The best way is backup the database, copy the backup to destination server,
restore the database at destination server, change the compatibility to 9.0,
update statistic.
"Ash" <ash.singh@.gmail.com> wrote in message
news:1177983868.501535.78840@.e65g2000hsc.googlegroups.com...
> Hi all,
> We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this
> is the error message received. We are doing this by using the 'copy
> database' feature in SQL 2005, is this the correct way to migrate a DB
> to 2005? The database is not in use so we are unsure why this error
> would be occuring. And on top of this we did not instruct SQL to drop
> the DB?
>
> Date,Source,Severity,Step ID,Server,Job Name,Step
> Name,Notifications,Message,Duration,Sql Severity,Sql Message
> ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,0,USER,CDW
_BUBBA_USER,(Job
> outcome),,The job failed. The Job was invoked by User DOMAIN\user.
> The last step to run was step 1 (CDW_BUBBA_USER_Step).,
> 00:02:28,0,0,,,,0
> 05/01/2007 09:05:31,CDW_BUBBA_USER,Error,
> 1,USER,CDW_BUBBA_USER,CDW_BUBBA_USER_Ste
p,,Executed as user: DOMAIN
> \USER. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp
> 1984-2005. All rights reserved. Started: 9:05:33 AM Progress:
> 2007-05-01 09:05:34.82 Source: Bubba_USER_Transfer Objects
> Task Task just started the execution.: 0% complete End Progress
> Error: 2007-05-01 09:07:58.57 Code: 0x00000000 Source:
> Bubba_USER_Transfer Objects Task
> Description: ERROR : errorCode=-1073548784 description=Executing the
> query "IF EXISTS (SELECT name FROM sys.databases WHERE name =
> N'test_sb') DROP DATABASE [test_sb] " failed with the following
> error: "Cannot drop database "test_sb" because it is currently in
> use.". Possible failure reasons: Problems with the query<c/>
> "ResultSet" property not set correctly<c/> parameters not set
> correctly<c/> or connection not established correctly. helpFile=
> helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-
> DA807BCDC2AC} StackTrace: at
> Microsoft.SqlServer.Management.Dts.D... The package execution fa...
> The step failed.,00:02:28,0,0,,,,0
>

'Error converting data type varchar to int' : Solution

Here is the solution to this error message if anyone gets one in the future...It took me about two days to figure out.

This error occurs if you save your stored procedure before execute it. Always execute first before you save. If you save before executing, the server will store its own defaults, usually integers.

This error aslo occurs if your datatypes do not match when passing values from code into the variables of a stored procedure.

It also occurs if the datatypes in your sql file do not match those of the original stored procedure.

You can check to see if the file saved in the "Projects" folder matches with the original by doing the following:

Expand Database, Expand Programmability, Expand Stored Procedures, Expand (Your Stored Procedure), Expand Parameters. Read the datatypes that are revealed in the tree.

Then go to File>Open>Projects>(save sql file). Click open.

View the datatypes in the file. If the datatypes in the file do not match the datatypes in the tree, what you must do to correct, as one solution, is delete both the file and stored procedure.

Then create a new stored procedure by right clicking the stored procedure node. Rewrite the store procedure, execute and then save. Everything should be okay.

Hi,

Thanks that you come back to the forums and want to give something of your solution to your actual problem back.

But I don′t know what your actual problem was (as you did not mentioned it clearly). Perhaps we could summarize this a bit more like Problem: xxxx, Solution: xxx. But from the information given I cannot tell what your actual problem was. I can′t even see why you do all the saves and deletes :-) ?

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-