Modified Stored Procedure Scripts Wrong ANSI Settings

Query Analyzer for 2000 was smart enough to realize that a stored procedure was created with the ANSI settings like such:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER OFF

go

However, when I use SSMS to modify a stored procedure from the context menu in the object explorer, instead get:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

Which is not even the default for my connection.

I'm working on a legacy code base with tons of double quoted strings, so I really need the ANSI settings to stay where they were without fighting the SQL editor about it.

Any suggestions? Is this a bug? I'm using SQL2005 RTM.

Matthew Martin

I would do this from your script in the query pane:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE <yourprocedure>
...

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

GO

Should be something like this at the end.

HTH, Jens Suessmeyer.

|||

True, I can revert to OSQL and generate the script correctly with my keyboard, the problem is that EM used to be able to script out a stored procedure with the correct settings (namely the settings that were in effect when the stored procedure was last altered), now with SSMS, right clicking on a stored procedure and selecting modify will script out a ALTER PROCEDURE script with the wrong settings.

Either I've hit a SSMS bug or I haven't found the 'Make-it-work-the-way-it-used-to' check box. I'm hoping it is the later.

Matthew Martin

|||

This is a known issue in SSMS. It will be fixed in SP1.

Modified Date For Stored Procedures

In SQL Server is there a way to know when a procedure was last
modified? I only see the "Create Date" column on the Enterprise
Manager.

Thanks Experts!jjone99 (jjone99@.hotmail.com) writes:
> In SQL Server is there a way to know when a procedure was last
> modified? I only see the "Create Date" column on the Enterprise
> Manager.

No, in SQL 2000 there is not.

This is addressed in the next version of SQL Server, currently in beta.

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

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

moderate sql procedure question

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

jagdipa wrote:

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

hi jagdipa,
we're on the same situation on that one, I've decided since then to use ado.net to update optional data, and use only stored procs if the data to be update/inserted is consistent; meaning it updates all column and not a few...

|||Once approach you could try is this:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50) = NULL,
@.new_V_Address1 varchar(50) = NULL,
@.new_V_Address2 varchar(50) = NULL,
@.new_V_Address3 varchar(50) = NULL,
@.new_V_TownCity varchar(50) = NULL,
@.new_I_RegionID bigint = NULL,
@.new_V_Postcode varchar(10) = NULL
)
AS
UPDATE T_Sites SET
[V_SiteName] = ISNULL(@.new_V_SiteName,[V_SiteName]),
[V_Address1] = ISNULL(@.new_V_Address1,[V_Address1]),
[V_Address2] = ISNULL(@.new_V_Address2,[V_Address2]),
[V_Address3] = ISNULL(@.new_V_Address3,[V_Address3]),
[V_TownCity] = ISNULL(@.new_V_TownCity,[V_TownCity]),
[I_RegionID] = ISNULL(@.new_I_RegionID,[I_RegionID]),
[V_PostCode] = ISNULL(@.new_V_Postcode,[V_PostCode])
WHERE
I_SiteID= @.old_I_SiteID

GO

|||good one terry, i've used default values before but never thought on this implementation.. sweet..|||

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

|||

meantown2 wrote:

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

hi meantown, the poster wants to update only a selected column, if he/she is going to pass all values he might update columns he doesn't want to update. The reply of terry will prevent him/her from doing that, passing only selected parameters.

|||The reason I asked this was not really for this stored procedure (I just used this one for an easy example).

I have a B2B website where there are a lot of text fields on one webform. The data entered could grow. To try to improve effieciecy, I wanted to only pass the values that have changed.

This does mean a little extra work on the webform though. I will have to go through each value and check if it has changed.

So that leads me to my next question (this is a hard question to put down in writing, but I've tried my best to put my point across):

Lets say I have loaded the old values into a webform.
Then the user changes some of these and hits Save.
Now I need to check whether the values have changed. I could do this by comparing the values against the ones in the database - but this means an extra database access to retrieve the old values. Is there a way I can use the viewstate and somehow compare the values entered by the user against the ones stored in the viewstate?

Note: I dont want to put a load of invisible input fields everywhere and save the old values in that.

Thanks in advance for any help.

Jagdip

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIA
when you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegr oups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>
|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.
|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegr oups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>
|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.
|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegro ups.com...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>
|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
|||OK, it seems like SQL Server doesn't allow you to change the owner of the model database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't allow you to change the
owner of model to anything else but "sa", you have to try to find out how and why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegro ups.com...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIAwhen you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegroups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegroups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegroups.com...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.|||OK, it seems like SQL Server doesn't allow you to change the owner of the model database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't allow you to change the
owner of model to anything else but "sa", you have to try to find out how and why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegroups.com...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>sql

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIAwhen you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegroups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegroups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegroups.c
om...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.|||OK, it seems like SQL Server doesn't allow you to change the owner of the mo
del database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't all
ow you to change the
owner of model to anything else but "sa", you have to try to find out how an
d why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information
in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not
supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process
.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegroups.c
om...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>

Mobile Replication

Dear All,

I need to make Sql Server Mobile replication with Sql Server 2005, could any body tell where can I find step by step procedure to make this?

Thanks and Regards

This article will get you started with using replication with SQL Server Mobile

http://msdn2.microsoft.com/en-us/library/ms171801.aspx

Using Merge Replication with SQL Mobile

http://msdn2.microsoft.com/en-us/library/ms172407.aspx

|||

Hi,

I follow the steps mentioned in http://msdn2.microsoft.com/en-us/library/ms171908.aspx

but when I try to run the replication agent I get the following error:

Executed as user: cat-10g\snapshot_agent. The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.

any advise?

Thank s and regards

|||

hi

it worked only after adding the snapshot_agent user to administrators group.

thanks

|||Hi,

I'm a newbe SQL Server developer and had been facing the exact same problem for few days. I could finally start the agent. Thanks ataha.

In addition, I also tried if other user group would work since I didn't want to give full administrator rights to the snapshot_agent. Then I found several SQL Server 2005 related groups are added after SQL Server 2005 was installed. I put snapshot_agent to SQLServer2005SQLAgentUser$computername$instancename and it worked. It's still administrator but I think it's somewhat better than it.

http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Thanks again.

Mobile Replication

Dear All,

I need to make Sql Server Mobile replication with Sql Server 2005, could any body tell where can I find step by step procedure to make this?

Thanks and Regards

This article will get you started with using replication with SQL Server Mobile

http://msdn2.microsoft.com/en-us/library/ms171801.aspx

Using Merge Replication with SQL Mobile

http://msdn2.microsoft.com/en-us/library/ms172407.aspx

|||

Hi,

I follow the steps mentioned in http://msdn2.microsoft.com/en-us/library/ms171908.aspx

but when I try to run the replication agent I get the following error:

Executed as user: cat-10g\snapshot_agent. The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.

any advise?

Thank s and regards

|||

hi

it worked only after adding the snapshot_agent user to administrators group.

thanks

|||Hi,

I'm a newbe SQL Server developer and had been facing the exact same problem for few days. I could finally start the agent. Thanks ataha.

In addition, I also tried if other user group would work since I didn't want to give full administrator rights to the snapshot_agent. Then I found several SQL Server 2005 related groups are added after SQL Server 2005 was installed. I put snapshot_agent to SQLServer2005SQLAgentUser$computername$instancename and it worked. It's still administrator but I think it's somewhat better than it.

http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Thanks again.

mmc.exe-Entry Point Not Found

WHEN I start enterprise manager on SQL 2000, ERROR message
occurred as folows:
mmc.exe-Entry Point Not Found
The procedure entry point wNetCachePassword could not be
located in the dynamic link library MPR.DLL.
Please tell me if you experienced !
thanksCan you find the dll mpr.dll on that server? If not, copy it from another
server.
Once you have located it, go to
http://www.microsoft.com/windows2000/downloads/servicepacks/SP4/supporttools.asp
There is a great tool that is part of the package you can download at that
site -- dependency walker (also you can use the dependency tool from Visual
Studio). Open the dll with the dependency walker and it will tell you what
is missing from your server. Then you can get the appropriate files. Good
luck.
Another thing to is to go to http://www.microsoft.com/mmc, and try
reinstalling the management console.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Matthew Jin" <jinmz@.yahoo.com> wrote in message
news:2a4401c3fc92$e82ec5b0$a101280a@.phx.gbl...
> WHEN I start enterprise manager on SQL 2000, ERROR message
> occurred as folows:
> mmc.exe-Entry Point Not Found
> The procedure entry point wNetCachePassword could not be
> located in the dynamic link library MPR.DLL.
> Please tell me if you experienced !
> thanks
>

mmc.exe-Entry Point Not Found

WHEN I start enterprise manager on SQL 2000, ERROR message
occurred as folows:
mmc.exe-Entry Point Not Found
The procedure entry point wNetCachePassword could not be
located in the dynamic link library MPR.DLL.
Please tell me if you experienced !
thanksCan you find the dll mpr.dll on that server? If not, copy it from another
server.
Once you have located it, go to
http://www.microsoft.com/windows200...rosoft.com/mmc, and try
reinstalling the management console.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Matthew Jin" <jinmz@.yahoo.com> wrote in message
news:2a4401c3fc92$e82ec5b0$a101280a@.phx.gbl...
> WHEN I start enterprise manager on SQL 2000, ERROR message
> occurred as folows:
> mmc.exe-Entry Point Not Found
> The procedure entry point wNetCachePassword could not be
> located in the dynamic link library MPR.DLL.
> Please tell me if you experienced !
> thanks
>

Misunderstanding of backup

I always assumed that the backup & restore procedure stored simply
data, not structure.

Now I find that if I add a new table to a database, then restore using
an old backup, the new table is gone.

Is there any way to restore JUST the data from a backup? If not, is
there any way to archive & import just data? The import/export wizard
only seems to send a single table to a flat file, but I need all the
tables.

Is there perhaps a command-line parameterthat isn't available in the
wizard that would accomplish this?You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/d...backpc_6ng9.asp

--
David Portas
SQL Server MVP
--|||You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/d...backpc_6ng9.asp

--
David Portas
SQL Server MVP
--|||
David Portas wrote:
> You can restore to a different database or server and then copy over
> the data.
> http://msdn.microsoft.com/library/d...backpc_6ng9.asp
> --
> David Portas
> SQL Server MVP
> --

Must the data be copied using the MOVE command, or can I simply
physically relocate the ldf & mdf files to the target server?|||
David Portas wrote:
> You can restore to a different database or server and then copy over
> the data.
> http://msdn.microsoft.com/library/d...backpc_6ng9.asp
> --
> David Portas
> SQL Server MVP
> --

Must the data be copied using the MOVE command, or can I simply
physically relocate the ldf & mdf files to the target server?|||You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--|||You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--|||
David Portas wrote:
> You can always detach and reattach the files (sp_attach_db and sp_detach_db)
> but as you'll have to restore them first you may as well restore them where
> you want them to start with.
> --
> David Portas
> SQL Server MVP
> --

Once the new temp database is created, what procedure would you
recommend for copying just the data between databases?|||Darryl (DarrylJ@.yahoo.com) writes:
> David Portas wrote:
>> You can always detach and reattach the files (sp_attach_db and
>> sp_detach_db) but as you'll have to restore them first you may as well
>> restore them where you want them to start with.
>>
> Once the new temp database is created, what procedure would you
> recommend for copying just the data between databases?

Depends on why, and how often etc. If you want to do it on a regular
basis, replication could be the way to go. For a one-off thing,
it depends on whether the target database already has data, and
what you want to do with it etc.

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

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

Missing Resultset

Hi Guys
I have a report where I pass a parameter to a stored procedure that then
returns a resultset to the report.
Everything works well apart from when I choose my *All option. If this *All
option is chosen no data is returned. If run the Dataset and enters the *All
option manually it returns data. If I capture what is sent to the SQLServer
and execute that in Query Analizer it returns data.If I run the report it
comes up empty no errors just no data.
If I run the report with any other option than the *All data is shown the
way it should.
Any Ideas?
Thanks.
Regards
JonasFound the error/mistake.
When you have a report parameter and want to use a Stored Procedure (that at
this stage are not able to recieve a parameter) RS automatically creates a
filter.
Once the filter were deleted everything worked.
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:eGr76HPgEHA.396@.TK2MSFTNGP12.phx.gbl...
> Hi Guys
> I have a report where I pass a parameter to a stored procedure that then
> returns a resultset to the report.
> Everything works well apart from when I choose my *All option. If this
*All
> option is chosen no data is returned. If run the Dataset and enters the
*All
> option manually it returns data. If I capture what is sent to the
SQLServer
> and execute that in Query Analizer it returns data.If I run the report it
> comes up empty no errors just no data.
> If I run the report with any other option than the *All data is shown the
> way it should.
> Any Ideas?
> Thanks.
> Regards
> Jonas
>
>

Missing results from stored proc

ok...I must be blind.
I have this procedure (which I didn't write):
CREATE PROCEDURE dbo.spWebqryAccountList41257
(@.StartDate nvarchar(15)
,@.EndDate nvarchar(15)
,@.ClientID int
,@.ArpCode nvarchar(15)
,@.brchid nvarchar(5)
,@.username nvarchar(10)
)
AS
SELECT *
FROM
dbo.vwWebClaimDetailNext4
WHERE
([Date Handled] between @.StartDate AND @.EndDate)
AND ([Client ID] = @.ClientID)
and ARPCODE in (@.ArpCode)
and BranchID = @.brchid
and username = @.username
Note that all it is doing is selecting from a view and narrowing the results
I have two executions:
EXEC spWebqryAccountList41257
@.StartDate = '2/1/2006',
@.EndDate = '3/3/2006',
@.ClientID = 2287,
@.ARPCode = 'A',
@.BrchID = 'A#79',
@.Username = 'ME2287'
Result = 4 records
EXEC spWebqryAccountList41257
@.StartDate = '2/1/2006',
@.EndDate = '3/3/2006',
@.ClientID = 2287,
@.ARPCode = 'A',
@.BrchID = 'Mil#104',
@.Username = 'ME2287'
Result = 0 records
The only difference between the two is the @.BrchID parameter.
The underlying view run with the same parameters as the second execution in
the WHERE clause returns records (7, to be exact) for the second set, but
the stored proc does not.
If I do this:
SELECT *
FROM
dbo.vwWebClaimDetailNext4
WHERE
([Date Handled] between '2/1/2006' and '3/3/2006')
AND ([Client ID] =2287)
and ARPCODE in ('A')
and BranchID = 'Mil#104'
and username = 'ME2287'
I get the right results...
I've been looking at the # sign as the culprit, but both branches have one
(as do many others).
Help?
--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htmNevermind...the @.brchid parameter isn't big enough to hold what is being
passed...
Thanks for looking :-)
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Kevin3NF" <Kevin@.DontNeedNoSpam3NF-inc.com> wrote in message
news:ORLUL$ZQGHA.3896@.TK2MSFTNGP15.phx.gbl...
> ok...I must be blind.
> I have this procedure (which I didn't write):
> CREATE PROCEDURE dbo.spWebqryAccountList41257
> (@.StartDate nvarchar(15)
> ,@.EndDate nvarchar(15)
> ,@.ClientID int
> ,@.ArpCode nvarchar(15)
> ,@.brchid nvarchar(5)
> ,@.username nvarchar(10)
> )
> AS
> SELECT *
> FROM
> dbo.vwWebClaimDetailNext4
> WHERE
> ([Date Handled] between @.StartDate AND @.EndDate)
> AND ([Client ID] = @.ClientID)
> and ARPCODE in (@.ArpCode)
> and BranchID = @.brchid
> and username = @.username
>
> Note that all it is doing is selecting from a view and narrowing the
> results
> I have two executions:
> EXEC spWebqryAccountList41257
> @.StartDate = '2/1/2006',
> @.EndDate = '3/3/2006',
> @.ClientID = 2287,
> @.ARPCode = 'A',
> @.BrchID = 'A#79',
> @.Username = 'ME2287'
> Result = 4 records
> EXEC spWebqryAccountList41257
> @.StartDate = '2/1/2006',
> @.EndDate = '3/3/2006',
> @.ClientID = 2287,
> @.ARPCode = 'A',
> @.BrchID = 'Mil#104',
> @.Username = 'ME2287'
> Result = 0 records
> The only difference between the two is the @.BrchID parameter.
> The underlying view run with the same parameters as the second execution
> in the WHERE clause returns records (7, to be exact) for the second set,
> but the stored proc does not.
> If I do this:
> SELECT *
> FROM
> dbo.vwWebClaimDetailNext4
> WHERE
> ([Date Handled] between '2/1/2006' and '3/3/2006')
> AND ([Client ID] =2287)
> and ARPCODE in ('A')
> and BranchID = 'Mil#104'
> and username = 'ME2287'
> I get the right results...
> I've been looking at the # sign as the culprit, but both branches have one
> (as do many others).
> Help?
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
>
>

Missing Replication Stored procedure

We have had many SQL Server merge replications up and running for several
years now. Then we started having an major issue where information started
disappearing from the database.
We found the data still there but it was all in the conflict viewer. The
error that was given was "The row was updated at '(name of the 1st
subscriber)' but could not be updated at '(name of the 1st subscriber)'.
Could not find stored procedure 'ap_upd_E62019530D9D42907AC976C5A3134364'.
we recreated the subscriptions thinking that would be the end of it but a
few days later we had the same issue but with a different stored procedure
name.
The stored procedure that says is missing at the subscriber is always there
on the publisher. Also, the number of these "ap_" procedures seems to be
growing and sometimes is goes to "bp_". Meaning that in one database we now
have "sp_upd_E62019530D9D42907AC976C5A3134364",
"ap_upd_E62019530D9D42907AC976C5A3134364", and
"bp_upd_E62019530D9D42907AC976C5A3134364" and all of these stored procedures
are identical in there SQL
Does anybody have any knowledge of this issue or know where to look? This
problem is starting to accelerate and we are having real trouble finding a
solution
I've run into this problem myself. I ran into it when I deployed waves of
filtered subscriptions. You can grab the procs from the publisher and script
them out and put them in place on the subscriber. The problem is next
subscriber you deploy you might encounter this problem again.
I did raise a support incident with Microsoft on this one, but its over 3
years old now. The engineer was a Rand Boyd if that helps you to track it
down. I don't believe it was solved by a service pack as we were unable to
reproduce it.
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
"Ken Hundley" <KenHundley@.discussions.microsoft.com> wrote in message
news:D5F05DF7-3449-4D8C-B8BA-531132792723@.microsoft.com...
> We have had many SQL Server merge replications up and running for several
> years now. Then we started having an major issue where information
> started
> disappearing from the database.
> We found the data still there but it was all in the conflict viewer. The
> error that was given was "The row was updated at '(name of the 1st
> subscriber)' but could not be updated at '(name of the 1st subscriber)'.
> Could not find stored procedure 'ap_upd_E62019530D9D42907AC976C5A3134364'.
> we recreated the subscriptions thinking that would be the end of it but a
> few days later we had the same issue but with a different stored procedure
> name.
> The stored procedure that says is missing at the subscriber is always
> there
> on the publisher. Also, the number of these "ap_" procedures seems to be
> growing and sometimes is goes to "bp_". Meaning that in one database we
> now
> have "sp_upd_E62019530D9D42907AC976C5A3134364",
> "ap_upd_E62019530D9D42907AC976C5A3134364", and
> "bp_upd_E62019530D9D42907AC976C5A3134364" and all of these stored
> procedures
> are identical in there SQL
> Does anybody have any knowledge of this issue or know where to look? This
> problem is starting to accelerate and we are having real trouble finding a
> solution
|||That is what I have done...
I re-created all the missing sp on the subscriber computer, but after
several days there seems to be more that appear.
Maybe I will just open a ticket with MS and see if they can figure out what
is happening.
Thanks
"Hilary Cotter" wrote:

> I've run into this problem myself. I ran into it when I deployed waves of
> filtered subscriptions. You can grab the procs from the publisher and script
> them out and put them in place on the subscriber. The problem is next
> subscriber you deploy you might encounter this problem again.
> I did raise a support incident with Microsoft on this one, but its over 3
> years old now. The engineer was a Rand Boyd if that helps you to track it
> down. I don't believe it was solved by a service pack as we were unable to
> reproduce it.
> --
> 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
>
> "Ken Hundley" <KenHundley@.discussions.microsoft.com> wrote in message
> news:D5F05DF7-3449-4D8C-B8BA-531132792723@.microsoft.com...
>
>

Missing Fields

I am calling a stored procedure from reporting services. The problem I'm
having is that it's only returning the first field in the result set in the
field list.
When I query under the data tab, data and field names are there and all data
is returned successfully.
I have tried refreshing the fieds, refreshing data, creating a new
report...you name it.
I have used both the sql server and ole db providers and both produce the
same behavoir.
The stored procedure has 5 parameters. I have tried both temp tables and
table variables in the stored procedure to no avail.
When I manually add the fields I get errors as well.
Can anyone provide any help or insight? I'm stumped on this one.
Thanks!OK...I think I have this figured out, although I'm not really happy about the
behavoir. Perhaps MS can put this on their list of issues.
It appears that if you use a stored procedure within another stored
procedure (say the one your calling to populate your report) , it throws off
the fields that are returned.
In my case, I have a stored procedure internal to the one I was calling that
figures out the current date. The return value was the only field being
returned within the reporting services .net interface.
Perhaps it's the way I'm calling the SP and assigning the return value to an
internal variable.
Nonetheless...I hope this helps. Hopefully someone from MS will comment on
this behavoir for us.
"Scott M" wrote:
> I am calling a stored procedure from reporting services. The problem I'm
> having is that it's only returning the first field in the result set in the
> field list.
> When I query under the data tab, data and field names are there and all data
> is returned successfully.
> I have tried refreshing the fieds, refreshing data, creating a new
> report...you name it.
> I have used both the sql server and ole db providers and both produce the
> same behavoir.
>
> The stored procedure has 5 parameters. I have tried both temp tables and
> table variables in the stored procedure to no avail.
>
> When I manually add the fields I get errors as well.
> Can anyone provide any help or insight? I'm stumped on this one.
> Thanks!|||Can you duplicate this behavior with either adventureworks2000 or northwind.
I would like to investigate this and see if there is a workaround.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott M" <ScottM@.discussions.microsoft.com> wrote in message
news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> OK...I think I have this figured out, although I'm not really happy about
the
> behavoir. Perhaps MS can put this on their list of issues.
> It appears that if you use a stored procedure within another stored
> procedure (say the one your calling to populate your report) , it throws
off
> the fields that are returned.
> In my case, I have a stored procedure internal to the one I was calling
that
> figures out the current date. The return value was the only field being
> returned within the reporting services .net interface.
> Perhaps it's the way I'm calling the SP and assigning the return value to
an
> internal variable.
> Nonetheless...I hope this helps. Hopefully someone from MS will comment
on
> this behavoir for us.
> "Scott M" wrote:
> > I am calling a stored procedure from reporting services. The problem
I'm
> > having is that it's only returning the first field in the result set in
the
> > field list.
> >
> > When I query under the data tab, data and field names are there and all
data
> > is returned successfully.
> >
> > I have tried refreshing the fieds, refreshing data, creating a new
> > report...you name it.
> >
> > I have used both the sql server and ole db providers and both produce
the
> > same behavoir.
> >
> >
> > The stored procedure has 5 parameters. I have tried both temp tables
and
> > table variables in the stored procedure to no avail.
> >
> >
> > When I manually add the fields I get errors as well.
> >
> > Can anyone provide any help or insight? I'm stumped on this one.
> >
> > Thanks!|||Bruce,
I don't have those databases installed here at work...but I can give you the
essence of what happened.
There was a stored procedure called within a wrapper stored procedure. The
internal stored procedure was written to return the current period code
(200408, 200409 etc).
The wrapper stored procedure returned a recordset based on the period code
that was returned in the internal stored procedure.
When the report executed the wrapper stored procedure, it was only returning
the record from the internal stored procedure. So all I got was one field
with a value of the current period code.
I resolved the issue by creating a function to return the period code. That
seemed to fix it.
There might be a better way to call an internal stored procedure than the
way this was used. It was assigning an internally declared variable within
the wrapper stored procedure to the output parameter on the stored procedure
that returns the current period code.
Not sure if that helps. Seems like a bug to me, but perhaps it's by design
for some strange reason. Nonetheless, I'll have to re-write a lot of SP's in
order to utilize Reporting Services since the developer who originally
created a lot of the procedures in our environment didn't seem to utilize
functions.
Thanks for your response. Let me know if I can provide any further insight.
Scott
"Bruce L-C [MVP]" wrote:
> Can you duplicate this behavior with either adventureworks2000 or northwind.
> I would like to investigate this and see if there is a workaround.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott M" <ScottM@.discussions.microsoft.com> wrote in message
> news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> > OK...I think I have this figured out, although I'm not really happy about
> the
> > behavoir. Perhaps MS can put this on their list of issues.
> >
> > It appears that if you use a stored procedure within another stored
> > procedure (say the one your calling to populate your report) , it throws
> off
> > the fields that are returned.
> >
> > In my case, I have a stored procedure internal to the one I was calling
> that
> > figures out the current date. The return value was the only field being
> > returned within the reporting services .net interface.
> >
> > Perhaps it's the way I'm calling the SP and assigning the return value to
> an
> > internal variable.
> >
> > Nonetheless...I hope this helps. Hopefully someone from MS will comment
> on
> > this behavoir for us.
> >
> > "Scott M" wrote:
> >
> > > I am calling a stored procedure from reporting services. The problem
> I'm
> > > having is that it's only returning the first field in the result set in
> the
> > > field list.
> > >
> > > When I query under the data tab, data and field names are there and all
> data
> > > is returned successfully.
> > >
> > > I have tried refreshing the fieds, refreshing data, creating a new
> > > report...you name it.
> > >
> > > I have used both the sql server and ole db providers and both produce
> the
> > > same behavoir.
> > >
> > >
> > > The stored procedure has 5 parameters. I have tried both temp tables
> and
> > > table variables in the stored procedure to no avail.
> > >
> > >
> > > When I manually add the fields I get errors as well.
> > >
> > > Can anyone provide any help or insight? I'm stumped on this one.
> > >
> > > Thanks!
>
>|||I've encountered the same problem. I've created about 50 reports based on
stored procedures and this is the first time this has occured.
The proc in question has 11 parameters. It returns the result set in the
Data Preview section, but only returns a single field in the field list. The
field returned is not even in the actual result set.
The proc I'm calling does call other procs. It was created bby a third party
and is used for other functions as well as the report I'm writing, so I can't
modify it.
Any ideas?
"Bruce L-C [MVP]" wrote:
> Can you duplicate this behavior with either adventureworks2000 or northwind.
> I would like to investigate this and see if there is a workaround.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott M" <ScottM@.discussions.microsoft.com> wrote in message
> news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> > OK...I think I have this figured out, although I'm not really happy about
> the
> > behavoir. Perhaps MS can put this on their list of issues.
> >
> > It appears that if you use a stored procedure within another stored
> > procedure (say the one your calling to populate your report) , it throws
> off
> > the fields that are returned.
> >
> > In my case, I have a stored procedure internal to the one I was calling
> that
> > figures out the current date. The return value was the only field being
> > returned within the reporting services .net interface.
> >
> > Perhaps it's the way I'm calling the SP and assigning the return value to
> an
> > internal variable.
> >
> > Nonetheless...I hope this helps. Hopefully someone from MS will comment
> on
> > this behavoir for us.
> >
> > "Scott M" wrote:
> >
> > > I am calling a stored procedure from reporting services. The problem
> I'm
> > > having is that it's only returning the first field in the result set in
> the
> > > field list.
> > >
> > > When I query under the data tab, data and field names are there and all
> data
> > > is returned successfully.
> > >
> > > I have tried refreshing the fieds, refreshing data, creating a new
> > > report...you name it.
> > >
> > > I have used both the sql server and ole db providers and both produce
> the
> > > same behavoir.
> > >
> > >
> > > The stored procedure has 5 parameters. I have tried both temp tables
> and
> > > table variables in the stored procedure to no avail.
> > >
> > >
> > > When I manually add the fields I get errors as well.
> > >
> > > Can anyone provide any help or insight? I'm stumped on this one.
> > >
> > > Thanks!
>
>