problem with c# stored procedure calling a class

I wrote a stored procedure in c# that calls a class. It deployed fine but when I try to execute it, I get the error...

Msg 6522, Level 16, State 1, Procedure UpdateJobAdSearch, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UpdateJobAdSearch':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at JobAd.WhaleJobAd.CreateSearchableRecord()
at JobAd.WhaleJobAd.CreateSearchableRecord(String JobId)
at StoredProcedures.UpdateJobAdSearch(String JobAdId)

I read an article on MSDN that didn't directly relate, but mentioned the same error message. It suggested using...

System.Data.SqlClient.SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
pSql.Assert();

I tried that, but I get the same error.

I finally found the answer to this. I needed to set the projects properties > database permission level to external. However, I don't understand why. I've scaled back my code, pulled it out the class so everything executes in the stored procedures method call and all it does it create 2 connections to the local database and pull info from 6 tables and put that info into 1. It's not trying to access anything outside the database, so why does it have to be set to external? In order to get that to work, I had to then alter the database and turn trustworthy on which is scary because of the security holes that opens up.|||

cakewalkr7:

I've scaled back my code, pulled it out the class so everything executes in the stored procedures method call and all it does it create 2 connections to the local database and pull info from 6 tables and put that info into 1.

I think maybe connections opened by the class are considered as access to external resource. Did you try to open a connection in this way?

SqlConnection conn = new SqlConnection("context connection = true");

sql

Problem with Bulk Upload. Uploads twice.

When I run the following stored procedure I get these results. My file has
24 rows in it. However I am uploading it twice for some reason.
Stored Procedure:
CREATE PROCEDURE [dbo].[dsp_Manual_Import]
(
@.PathFileName varchar(50),
@.SQL varchar(2000)
)
AS
SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
WITH (FIELDTERMINATOR = ',') "
EXEC (@.SQL)
return
GO
Results:
(24 row(s) affected)
(24 row(s) affected)
Stored Procedure: ILVS.dbo.dsp_Manual_Import
Return Code = 0Have you confirmed the duplicate rows were inserted by running a query
against the table afterward?
Are there any triggers on the table, perhaps it is inserting to an audit
table?
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
> When I run the following stored procedure I get these results. My file
> has
> 24 rows in it. However I am uploading it twice for some reason.
>
> Stored Procedure:
> CREATE PROCEDURE [dbo].[dsp_Manual_Import]
> (
> @.PathFileName varchar(50),
> @.SQL varchar(2000)
> )
> AS
>
> SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
> WITH (FIELDTERMINATOR = ',') "
> EXEC (@.SQL)
>
> return
> GO
> Results:
> (24 row(s) affected)
>
> (24 row(s) affected)
> Stored Procedure: ILVS.dbo.dsp_Manual_Import
> Return Code = 0|||Yes I checked the table and it is importing twice. There are no triggers.
"JT" wrote:

> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||Another concern is that I only am importing 24 records, and it is reporting
48.
"JT" wrote:

> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||That's strange. Just as an experiment, try the following and confirm if they
all behave the same way.
#1 Execute dsp_Manual_Import manually from Query Analyzer instead of from
your application.
#2 Execute the same bulk insert command from Query Analyzer instead of
from within the stored procedure.
#3 Execute a similar bulk insert command against a different table.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:F28E3D8A-5386-4173-998C-5865E2866C23@.microsoft.com...
> Another concern is that I only am importing 24 records, and it is
> reporting 48.
> "JT" wrote:
>

problem with aspnet_ "Could not find stored procedure"

I have developed an asp.net 2.0 web application that uses sql2000 and with storded procedures.
Everything has been working great untill i yesterday found that someone have put in new stored procedures
in the database. The procedures starts with "dbo.aspnet_" like dbo.aspnet_CheckSchemaVersion.
Because i din't create them and i surtenly not call them from the application, i deleted them all.

Now when i try to use my web application nothing works. The app, somehow calls the procedure and becuase they are deleted
an error message is thrown like: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

But the strange thing is that the problem only accure when i publish the site on the web server.
if i would publish it to a local folder on my desktop ore just run in debug mode against the database there are no problems.

I don't want to use the aspnet procedures that probebly comes from aspnet_regsql.exe and that im not calling from the code but reacts somehow,
I just want it to tun as it was designed to do.
And if i need them, can someone explain why?

please help :)

Dude, the "aspnet_" stuff is from Microsoft... it's the Membership provider. To get it back run "aspnet_regsql' in c:\windows\microsoft.net\framework\v2.blah (or wherever your windows files are).

|||

Yo Dude :) I did what you said and of course it worked. But i don't know why aspnet_regsql.exe is suddenly needed and all of those procedures that comes along. It worked without it before.

thx :)

sql

Problem with ALTER TABLE in stored procedure

Hello,
I am trying to drop a column from a table with a stored procedure that
chacks if the column exists before droping it. However, I am having trouble
with passing the Table Name and Column Name to the ALTER TABLE command. The
code is:
CREATE PROCEDURE usp_DeleteColumnEx
@.TableName varchar(200),
@.ColumnName varchar(200)
AS
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')= 1
AND O.Name = @.TableName
AND C.Name = @.ColumnName)
ALTER TABLE @.TableName DROP COLUMN @.ColumnName
When I try to execute this the server returns an error: "Incorrect syntax
near '@.TableName'". I am assuming that I cannot just pass the table name as
a
parameter. Is there a another way to do this?
Thank you for your help.
Daniel> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
You can't do this - SQL Server has to know what objects you're talking
about. The best you could do is dynamic SQL, please read:
http://www.sommarskog.se/dynamic_sql.html
On a side note, why on earth are you changing your table structure on the
fly like this? Sounds very dangerous and suspicious, but not in the Austin
Powers way.|||"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9A9DCE9F-191A-4E76-AD5A-FC6E7A316E61@.microsoft.com...
> Hello,
> I am trying to drop a column from a table with a stored procedure that
> chacks if the column exists before droping it. However, I am having
> trouble
> with passing the Table Name and Column Name to the ALTER TABLE command.
> The
> code is:
> CREATE PROCEDURE usp_DeleteColumnEx
> @.TableName varchar(200),
> @.ColumnName varchar(200)
> AS
> IF EXISTS
> (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
> WHERE ObjectProperty(O.ID,'IsUserTable')= 1
> AND O.Name = @.TableName
> AND C.Name = @.ColumnName)
> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
> When I try to execute this the server returns an error: "Incorrect syntax
> near '@.TableName'". I am assuming that I cannot just pass the table name
> as a
> parameter. Is there a another way to do this?
> Thank you for your help.
> Daniel
Why would you want a proc that drops columns? Help me understand what you
are trying to achieve.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Aaron and David,
Thank you for your replies. The reason I need to alter the table dynamically
is
that I have a table where each record is corresponds to one employee, each
column represents a different skill involved in the employees' everyday work
.
In each column every employee have assigned a number corresponding to their
skill level. According to their skills they are assigned to their work
stations, which is VERY important for the management.
This is a basically how the table looks like:
Create table SkillsCheck (
emp_id int,
skill1 int,
skill2 int,
skill3 int,
skill4 int)
The problem is that the company has the option to change what skills they
want to track, so when that happens I have to drop or add a column to the
table to reflect the current situation. For example, they might decide that
they do not want to track skill3 anymore, but want to track skill5, so my
program needs to drop skill3 column and add skill5 column. I am working with
VB.NET and I can execute the ALTER TABLE statement from my code, but I think
that it would be better to use stored procedure that check if the column
exists before adding or droping.
After considering the situation I decided that would be easier to alter the
table instead of keeping the information in multiple tables. If you have
encountered a similiar scenario maybe you can give me an advice for more
efficient approach to this problem.
Thank you for your help,
Daniel
"Daniel" wrote:

> Hello,
> I am trying to drop a column from a table with a stored procedure that
> chacks if the column exists before droping it. However, I am having troubl
e
> with passing the Table Name and Column Name to the ALTER TABLE command. Th
e
> code is:
> CREATE PROCEDURE usp_DeleteColumnEx
> @.TableName varchar(200),
> @.ColumnName varchar(200)
> AS
> IF EXISTS
> (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
> WHERE ObjectProperty(O.ID,'IsUserTable')= 1
> AND O.Name = @.TableName
> AND C.Name = @.ColumnName)
> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
> When I try to execute this the server returns an error: "Incorrect syntax
> near '@.TableName'". I am assuming that I cannot just pass the table name a
s a
> parameter. Is there a another way to do this?
> Thank you for your help.
> Daniel|||Daniel wrote:
> Aaron and David,
> Thank you for your replies. The reason I need to alter the table dynamical
ly
> is
> that I have a table where each record is corresponds to one employee, each
> column represents a different skill involved in the employees' everyday wo
rk.
> In each column every employee have assigned a number corresponding to thei
r
> skill level. According to their skills they are assigned to their work
> stations, which is VERY important for the management.
> This is a basically how the table looks like:
> Create table SkillsCheck (
> emp_id int,
> skill1 int,
> skill2 int,
> skill3 int,
> skill4 int)
> The problem is that the company has the option to change what skills they
> want to track, so when that happens I have to drop or add a column to the
> table to reflect the current situation. For example, they might decide tha
t
> they do not want to track skill3 anymore, but want to track skill5, so my
> program needs to drop skill3 column and add skill5 column. I am working wi
th
> VB.NET and I can execute the ALTER TABLE statement from my code, but I thi
nk
> that it would be better to use stored procedure that check if the column
> exists before adding or droping.
> After considering the situation I decided that would be easier to alter th
e
> table instead of keeping the information in multiple tables. If you have
> encountered a similiar scenario maybe you can give me an advice for more
> efficient approach to this problem.
> Thank you for your help,
> Daniel
>
> "Daniel" wrote:
>
The type of relationship between employees and skills is called
many-to-many. The textbook solution looks like this:
CREATE TABLE EmployeeSkills
(emp_id INTEGER NOT NULL
REFERENCES Employees (emp_id),
skill_code INTEGER NOT NULL
REFERENCES Skills (skill_code),
PRIMARY KEY (emp_id,skill_code));
This has huge advantages over the design that you proposed: It can
support any number of skills. No redundancy. No nulls required. Joins
and queries always reference just one skills column. The table
structure doesn't ever need to change (!).
I recommend you read up and study some relational design theory. Most
database architects would consider your suggestion as a serious design
flaw. To appreciate why you need to understand principles like
normalization and the normal forms, which are some of the tools we use
to design effective databases.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||As you stated, it would take a schema changed to accommodate future
requirements. Such as that, you're better off by creating a skills table to
store the skills. Any changes will just be a simple delete from the tables.
Also, this approach will not incur a table lock like you have when doing
schema update.
e.g.
create table skills(skillid int primary key, skillname sysname)
create table skillscheck(empid int primary key, skillid int foreign key
references skills(skillid))
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:3505642D-711F-43F6-8D73-DA004C791B34@.microsoft.com...
> Aaron and David,
> Thank you for your replies. The reason I need to alter the table
> dynamically
> is
> that I have a table where each record is corresponds to one employee, each
> column represents a different skill involved in the employees' everyday
> work.
> In each column every employee have assigned a number corresponding to
> their
> skill level. According to their skills they are assigned to their work
> stations, which is VERY important for the management.
> This is a basically how the table looks like:
> Create table SkillsCheck (
> emp_id int,
> skill1 int,
> skill2 int,
> skill3 int,
> skill4 int)
> The problem is that the company has the option to change what skills they
> want to track, so when that happens I have to drop or add a column to the
> table to reflect the current situation. For example, they might decide
> that
> they do not want to track skill3 anymore, but want to track skill5, so my
> program needs to drop skill3 column and add skill5 column. I am working
> with
> VB.NET and I can execute the ALTER TABLE statement from my code, but I
> think
> that it would be better to use stored procedure that check if the column
> exists before adding or droping.
> After considering the situation I decided that would be easier to alter
> the
> table instead of keeping the information in multiple tables. If you have
> encountered a similiar scenario maybe you can give me an advice for more
> efficient approach to this problem.
> Thank you for your help,
> Daniel
>
> "Daniel" wrote:
>|||Correction. Don't forget to add a column for the skill level:
CREATE TABLE EmployeeSkills
(emp_id INTEGER NOT NULL
REFERENCES Employees (emp_id),
skill_code INTEGER NOT NULL
REFERENCES Skills (skill_code),
skill_level INTEGER NOT NULL
CHECK (skill_level BETWEEN 0 AND 10),
PRIMARY KEY (emp_id,skill_code));
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
I will follow your advice and will read about many-to-many relationships.
After reading more on Dynamic SQL, looks like it is not the best choice.
Thanks again,
Daniel
"David Portas" wrote:

> Correction. Don't forget to add a column for the skill level:
> CREATE TABLE EmployeeSkills
> (emp_id INTEGER NOT NULL
> REFERENCES Employees (emp_id),
> skill_code INTEGER NOT NULL
> REFERENCES Skills (skill_code),
> skill_level INTEGER NOT NULL
> CHECK (skill_level BETWEEN 0 AND 10),
> PRIMARY KEY (emp_id,skill_code));
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||OJ,
Thanks you your reply. Looks like I need to revise the database design and
reorganize the tables differently.
Best Regards,
Daniel
"oj" wrote:

> As you stated, it would take a schema changed to accommodate future
> requirements. Such as that, you're better off by creating a skills table t
o
> store the skills. Any changes will just be a simple delete from the tables
.
> Also, this approach will not incur a table lock like you have when doing
> schema update.
> e.g.
> create table skills(skillid int primary key, skillname sysname)
> create table skillscheck(empid int primary key, skillid int foreign key
> references skills(skillid))
>
> --
> -oj
>
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:3505642D-711F-43F6-8D73-DA004C791B34@.microsoft.com...
>
>

Problem with accessing stored procedure in the report

Hi,

I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)

Could not find stored procedure 'stpBranch'.

But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.

Input string was not in a correct format.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[FormatException: Input string was not in a correct format.] System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715 System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102 Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.

I am not sure why I am getting this error and I greatly appreciate any help.

Thanks

ngk,

I have seen this before. Are you using any schema namespaces in your stored procedure name.

Such as HumanResources.GetAllEmployees instead of the old default of dbo.GetAllEmployees.

If so are you also running your stored procedures in Query Analyser as the "SAME" user that your Report Datasource uses.
Pay close attention to these details...

I have seen where 1 sql user has the default schema set to HumanResources and the stored procedure call is made such as exec GetAllEmployees instead of
exec HumanResources.GetAllEmployees.

In this situation any user that makes the first call (exec GetAllEmployees ) and has the default schema of HumanResources will succeed.
Any user that does not have this default will return an error because it is looking for dbo.GetAllEmployees and this may not exist.

Hope this helps.. if not please provide more details on what users you are using and the exact call you are making for the stored procedure.

|||

Hi Bret,

Thanks for the info. I am not using any schema namespaces in my stored procedures. I actually got this error when I tried to connect to a remote sql server. Now I have installed developer editon on my local machine and I have reporting services also on my local machine. I have been able to connect to the strored procedures and deploy the reports to the local report server and view the reports without any problems. I am not sure whether I may have to face the same issue when the reports are deployed to the remote SQL DB and Reporting Services production servers.

Thanks

Problem with accessing stored procedure in the report

Hi,

I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)

Could not find stored procedure 'stpBranch'.

But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.

Input string was not in a correct format.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[FormatException: Input string was not in a correct format.] System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715 System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102 Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.

I am not sure why I am getting this error and I greatly appreciate any help.

Thanks

ngk,

I have seen this before. Are you using any schema namespaces in your stored procedure name.

Such as HumanResources.GetAllEmployees instead of the old default of dbo.GetAllEmployees.

If so are you also running your stored procedures in Query Analyser as the "SAME" user that your Report Datasource uses.
Pay close attention to these details...

I have seen where 1 sql user has the default schema set to HumanResources and the stored procedure call is made such as exec GetAllEmployees instead of
exec HumanResources.GetAllEmployees.

In this situation any user that makes the first call (exec GetAllEmployees ) and has the default schema of HumanResources will succeed.
Any user that does not have this default will return an error because it is looking for dbo.GetAllEmployees and this may not exist.

Hope this helps.. if not please provide more details on what users you are using and the exact call you are making for the stored procedure.

|||

Hi Bret,

Thanks for the info. I am not using any schema namespaces in my stored procedures. I actually got this error when I tried to connect to a remote sql server. Now I have installed developer editon on my local machine and I have reporting services also on my local machine. I have been able to connect to the strored procedures and deploy the reports to the local report server and view the reports without any problems. I am not sure whether I may have to face the same issue when the reports are deployed to the remote SQL DB and Reporting Services production servers.

Thanks

Problem with accessing stored procedure in the report

Hi,

I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)

Could not find stored procedure 'stpBranch'.

But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.

Input string was not in a correct format.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[FormatException: Input string was not in a correct format.]

System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715

System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102

Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149

Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75

System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.

I am not sure why I am getting this error and I greatly appreciate any help.

Thanks

ngk,

I have seen this before. Are you using any schema namespaces in your stored procedure name.

Such as HumanResources.GetAllEmployees instead of the old default of dbo.GetAllEmployees.

If so are you also running your stored procedures in Query Analyser as the "SAME" user that your Report Datasource uses.
Pay close attention to these details...

I have seen where 1 sql user has the default schema set to HumanResources and the stored procedure call is made such as exec GetAllEmployees instead of
exec HumanResources.GetAllEmployees.

In this situation any user that makes the first call (exec GetAllEmployees ) and has the default schema of HumanResources will succeed.
Any user that does not have this default will return an error because it is looking for dbo.GetAllEmployees and this may not exist.

Hope this helps.. if not please provide more details on what users you are using and the exact call you are making for the stored procedure.

|||

Hi Bret,

Thanks for the info. I am not using any schema namespaces in my stored procedures. I actually got this error when I tried to connect to a remote sql server. Now I have installed developer editon on my local machine and I have reporting services also on my local machine. I have been able to connect to the strored procedures and deploy the reports to the local report server and view the reports without any problems. I am not sure whether I may have to face the same issue when the reports are deployed to the remote SQL DB and Reporting Services production servers.

Thanks

problem with a stored procedure

Hello guys,
I have again problem with a stored procedure in sql server.
I would like to execute this query
declare @.sSQL varchar(255)
SET @.sSQL =
'SELECT TOP 1 [value], date, name, [times]
FROM (SELECT value, date, times, name
FROM view
WHERE ValueDate =
(SELECT MAX(date)
AS maxdate
FROM view
v
WHERE (date
BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
WHERE (name = ''TEST'')
GROUP BY [value], date, [times], name
ORDER BY [times] DESC'
EXEC (@.sSQL)
When I execute the query I have no problem but ... when i tried to
stored it I have this several problem I do not know why ... something
like
Line 4: Incorrect syntax near '='
I would like be able to pass variable on it and create function to
store the results in a table.
Please someone could help me on that.
Inaina
Replace EXEC(@.sql) with PRINT @.sql in order to debug and try run it in the
QA.
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144763910.125857.198630@.e56g2000cwe.googlegroups.com...
> Hello guys,
> I have again problem with a stored procedure in sql server.
> I would like to execute this query
> declare @.sSQL varchar(255)
>
> SET @.sSQL =
> 'SELECT TOP 1 [value], date, name, [times]
> FROM (SELECT value, date, times, name
> FROM view
> WHERE ValueDate =
> (SELECT MAX(date)
> AS maxdate
> FROM view
> v
> WHERE (date
> BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
> WHERE (name = ''TEST'')
> GROUP BY [value], date, [times], name
> ORDER BY [times] DESC'
> EXEC (@.sSQL)
> When I execute the query I have no problem but ... when i tried to
> stored it I have this several problem I do not know why ... something
> like
> Line 4: Incorrect syntax near '='
> I would like be able to pass variable on it and create function to
> store the results in a table.
> Please someone could help me on that.
> Ina
>|||> When I execute the query I have no problem but ... when i tried to
> stored it
What does "tried to stored it" mean?

> I would like be able to pass variable on it and create function to
> store the results in a table.
Well, you can't execute dynamic SQL inside a function. If you provide
better specifications on exactly what you are trying to accomplish (instead
of describing how you are already trying to accomplish it), we may be able
to provide better assistance.|||Is it actually spanning multiple lines in your stored procedure? I don't
think this is allowed. Strings have to be contained on a single line.
Either put everything on one line, or concatenate each line to the end of
@.sSQL
Also, 255 is probably not long enough to contain your SQL. Bump this up to
2000 and it should handle most reasonable sql statements.
Lastly, don't ever use dynamic SQL if you have a choice. There are times
when it is needed, but you should simply be able to pass the parameters
inside the stored procedure without dynamic SQL.
Look up "Parameters" in books on line for an explanation of how to use them.
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144763910.125857.198630@.e56g2000cwe.googlegroups.com...
> Hello guys,
> I have again problem with a stored procedure in sql server.
> I would like to execute this query
> declare @.sSQL varchar(255)
>
> SET @.sSQL =
> 'SELECT TOP 1 [value], date, name, [times]
> FROM (SELECT value, date, times, name
> FROM view
> WHERE ValueDate =
> (SELECT MAX(date)
> AS maxdate
> FROM view
> v
> WHERE (date
> BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
> WHERE (name = ''TEST'')
> GROUP BY [value], date, [times], name
> ORDER BY [times] DESC'
> EXEC (@.sSQL)
> When I execute the query I have no problem but ... when i tried to
> stored it I have this several problem I do not know why ... something
> like
> Line 4: Incorrect syntax near '='
> I would like be able to pass variable on it and create function to
> store the results in a table.
> Please someone could help me on that.
> Ina
>|||> Is it actually spanning multiple lines in your stored procedure? I don't
> think this is allowed. Strings have to be contained on a single line.
Strings can span lines. The only (obvious) limitation is the fact that they
need to be enclosed in single quotes.
ML
http://milambda.blogspot.com/|||Thank you for your help and I am sorry ... but I am really newbie and I
am trying to introduce my self in sql server programming.
What I am trying to do it is:
My main query gives to me the max value for a ticket during a period of
time. What I would like to do is the to have the max value for each end
of month for a ticket. each ticket has its own creation date and as
this period I would like to have the max value until today.
For example:
NameTicket |creation_date |month |year |value
----
-
Ticket1 2005-09-15 September 2005 120
ticket 1 2005-09-15 October 2005 125
Ticket 1 2005-09-15 November 2005 152
Ticket 1 2005-09-15 December 2005 152
Sometimes there is no value for one month so it needs to take the max
value for the previous month.
What I would like to do with my query is how to set parameter for sql,
in that what I can set the ticketname and period.
Thank you a lot for your help
Ina|||Thank you for your help and I am sorry ... but I am really newbie and I
am trying to introduce my self in sql server programming.
What I am trying to do it is:
My main query gives to me the max value for a ticket during a period of
time. What I would like to do is the to have the max value for each end
of month for a ticket. each ticket has its own creation date and as
this period I would like to have the max value until today.
For example:
NameTicket |creation_date |month |year |value
----
-
Ticket1 2005-09-15 September 2005 120
ticket 1 2005-09-15 October 2005 125
Ticket 1 2005-09-15 November 2005 152
Ticket 1 2005-09-15 December 2005 152
Sometimes there is no value for one month so it needs to take the max
value for the previous month.
What I would like to do with my query is how to set parameter for sql,
in that what I can set the ticketname and period.
Thank you a lot for your help
Ina|||This sounds better and fairly simple to acomplish. All we need now is DDL an
d
sample data.
This article will help you help us:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||Thank you I will go through that and try to find the solution of my
problem. Thank you :D|||Hello,
I tried to see that but what I need it is to have the max of each month
since the startdate until endate. I already have the max of the period
a set. how to have the list of all period.
Can I use the function month() to accomplish it?
ina

problem with a stored procedure

Hello guys,
I have again problem with a stored procedure in sql server.
I would like to execute this query
declare @.sSQL varchar(255)
SET @.sSQL =
'SELECT TOP 1 [value], date, name, [times]
FROM (SELECT value, date, times, name
FROM view
WHERE ValueDate =
(SELECT MAX(date)
AS maxdate
FROM view
v
WHERE (date
BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
WHERE (name = ''MIREMONT TEST'')
GROUP BY [value], date, [times], name
ORDER BY [times] DESC'
EXEC (@.sSQL)
When I execute the query I have no problem but ... when i tried to
stored it I have this several problem I do not know why ... something
like
Line 4: Incorrect syntax near '='
I would like be able to pass variable on it and create function to
store the results in a table.
Please someone could help me on that.
InaI think variable @.sSQL je very short:
Try declare @.sSQL varchar(2000)
"ina" wrote:

> Hello guys,
> I have again problem with a stored procedure in sql server.
> I would like to execute this query
> declare @.sSQL varchar(255)
>
> SET @.sSQL =
> 'SELECT TOP 1 [value], date, name, [times]
> FROM (SELECT value, date, times, name
> FROM view
> WHERE ValueDate =
> (SELECT MAX(date)
> AS maxdate
> FROM view
> v
> WHERE (date
> BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
> WHERE (name = ''MIREMONT TEST'')
> GROUP BY [value], date, [times], name
> ORDER BY [times] DESC'
> EXEC (@.sSQL)
> When I execute the query I have no problem but ... when i tried to
> stored it I have this several problem I do not know why ... something
> like
> Line 4: Incorrect syntax near '='
> I would like be able to pass variable on it and create function to
> store the results in a table.
> Please someone could help me on that.
> Ina
>

Problem with a SP. Help!

Why does only the first INSERT statement work?

CREATE PROCEDURE insert_inventory
@.item_name varchar(20),
@.description varchar(100),
@.notes varchar(255),
@.amount varchar(8)
AS
DECLARE @.item_id int
DECLARE @.transaction_date datetime

IF (@.item_name = '') SET @.item_name = NULL
IF (@.description = '') SET @.description = NULL
IF (@.notes = '') SET @.notes = NULL
IF (@.amount = '') SET @.amount = NULL

SET @.item_id = IDENT_CURRENT('inventory')
SET @.transaction_date = GETDATE()

INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))

Also, where I have IF(@.blah = '') SET...

I use these to convert blank fields in VB6 to NULL values, is there any easier way?do you get any error on the second insert?

if @.blah='' set... can be removed, but you will have to check for the value somewhere if you care for it: either in your vb6, in the if as you do now, or in the values clause (...values (nullif(@.blah, ''), ...)|||Try to change your SP this way (you have to get id after insert - not before):

SET @.transaction_date = GETDATE()
begin tran
INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)
if @.@.error<>0 begin
rollback
return
end
SET @.item_id = IDENT_CURRENT('inventory')

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))
commit|||i think ident_current(..) returns the _last_ new identity value for the specified table, not the identity value generated by the current scope. in this case if your insert occurred before another insert in another scope you may acquire the value that is not yours. scope_identity() guarantees that identity value belongs to insert from your session.

also, it's better to write the error handler this way:

declare @.error int, @.id int
...insert operation
select @.error = @.@.error, @.id = scope_identity()
if @.error <> 0 begin
raiserror (...)
rollback tran
return (1)
end
commit tran|||Cheers, for those, they were all good recommendations which I am now using, but it still would only execute the first INSERT statement. That is until I used this:

SET NOCOUNT ON

Works beautifully now!!!

Rayden

Problem with a select in a stored procedure

Does anybody know what is wrong with this code from a stored procedure:
DECLARE tables_cursor CURSOR FOR
SELECT tf_change_out_id, destination, re_table, date_in
FROM tf_change_out_table
WHERE date_out = NULL
ORDER BY tf_change_out_id

Here is the error I'm getting:
Error 107: The column prefix 'tf_change_out_table' does not match with a table name or alias name used in the query.Perhaps there's something before the cursor declaration that results in the error below. If you'd select the cursor-declaration, and parse it, is there an error message?|||Silly me! The error was further down the code. Why can't they give line numbers with all the errors?|||That woud make it too easy, and then everyone would think they could write SQL :D


Besides ... if it was hard to write, it should be hard to read ;)

Problem with a scheduled job

I have a stored procedure that runs as a step in a scheduled job. For
some reason the job does not seem to finish when ran from the job but
does fine when run from a window in SQL Query.

I know the job is not working because the number of rows that are
inserted into the table (see code) is considerably less than the manual
runnning of it.

I have included the code for the stored procedure, the output from the
job, and the output from the manual run.

I know somebody will probably ask WHY I am using a cursor. We have no
control over the possibility of having a PK conflict since the data
comes from outside sources. If I do it as just a INSERT INTO..SELECT
than nothing goes in when I have a violation. As a business rule we
would rather have MOST of the data inserted into the historical tables
with a log of the ones that did not make it. We can then go back and
deal with the ones that did not go in.

Of course, if there is a better way I would love to hear it...

Number Rows
----
10456 vNormalizedClearingPosition_Sage
10407 ClearingPosition
51 Will cause PK violation

SQL Command
----
EXEC spExportToClearingPosition 'Sage'

Code
--

CREATE PROCEDURE spExportToClearingPosition (
@.clearingFirm VARCHAR(10),
@.reportDate DATETIME = NULL
)
AS
SET NOCOUNT ON

-- If report date is not specified use todays date.
SET @.reportDate = COALESCE(@.reportDate, CONVERT(VARCHAR(10), GetDate(),
101))

DECLARE
@.err INT,
@.errMsg VARCHAR(50),
@.descMsg VARCHAR(150)

-- declare variables for holding values during cursor looping
DECLARE
@.source VARCHAR(10),
@.rawRowId INT,
@.tradeDate DATETIME,
@.symbol VARCHAR(15),
@.identity VARCHAR(15),
@.identitySource VARCHAR(10),
@.exchange VARCHAR(5),
@.account VARCHAR(10),
@.name VARCHAR(75),
@.securityType VARCHAR(15),
@.position INT,
@.closingPrice DECIMAL(18, 6),
@.expiry DATETIME,
@.optionStrikePrice DECIMAL(18, 6),
@.optionSide VARCHAR(1),
@.optionMultiplier INT,
@.underlyingSymbol VARCHAR(15),
@.underlyingIdentity VARCHAR(15),
@.underlyingIdentitySource VARCHAR(10),
@.underlyingName VARCHAR(75),
@.underlyingClosingPrice DECIMAL(18, 6),
@.underlyingDividendDate DATETIME,
@.underlyingDividendPrice DECIMAL(18, 6)

-- ************************************************** ***********
-- Remove existing rows from historical table for specific
-- report date and just for specified clearing firm.
-- ************************************************** ***********

-- set source for deletion (will also check for valid clearing firm)
IF UPPER(@.clearingFirm) = 'MERRILL'
SET @.source = 'Merrill'
ELSE
IF UPPER(@.clearingFirm) = 'SAGE'
SET @.source = 'Sage'
ELSE
IF UPPER(@.clearingFirm) = 'PAX'
SET @.source = 'Pax'
ELSE
BEGIN
-- invalid clearing firm
RAISERROR('Invalid clearing firm "%s" was passed in.', 16, 1,
@.clearingFirm)
RETURN -100
END

DELETE FROM Historical.dbo.ClearingPosition
WHERE
[ReportDate] = @.reportDate
AND [Source] = @.source

-- ************************************************** ***********
-- Populate cursor based on clearing firm.
-- ************************************************** ***********
IF UPPER(@.clearingFirm) = 'MERRILL'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],

[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity], [UnderlyingIdentitySource],
[UnderlyingName], [UnderlyingClosingPrice], [UnderlyingDividendDate],
[UnderlyingDividendPrice]
FROM
vNormalizedClearingPosition_Merrill
WHERE
[ReportDate] = @.reportDate
ELSE
IF UPPER(@.clearingFirm) = 'SAGE'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],

[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity], [UnderlyingIdentitySource],
[UnderlyingName], [UnderlyingClosingPrice], [UnderlyingDividendDate],
[UnderlyingDividendPrice]
FROM
vNormalizedClearingPosition_Sage
WHERE
[ReportDate] = @.reportDate
ELSE
IF UPPER(@.clearingFirm) = 'PAX'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],

[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity], [UnderlyingIdentitySource],
[UnderlyingName], [UnderlyingClosingPrice], [UnderlyingDividendDate],
[UnderlyingDividendPrice]
FROM
vNormalizedClearingPosition_Pax
WHERE
[ReportDate] = @.reportDate

-- ************************************************** ***********
-- Process cusor and insert into historical table
-- ************************************************** ***********

-- open cursor and fetch first row
OPEN cPosition
FETCH cPosition INTO@.reportDate, @.source, @.rawRowId,
@.tradeDate, @.symbol, @.identity, @.identitySource, @.exchange,
@.account, @.name, @.securityType, @.position, @.closingPrice,
@.expiry, @.optionStrikePrice, @.optionSide, @.optionMultiplier,
@.underlyingSymbol, @.underlyingIdentity, @.underlyingIdentitySource,
@.underlyingName, @.underlyingClosingPrice, @.underlyingDividendDate,
@.underlyingDividendPrice

-- loop until no more rows
WHILE @.@.Fetch_Status = 0
BEGIN
-- insert row into normalized table
INSERT INTO Historical.dbo.ClearingPosition ([ReportDate],
[Source],[RawRowId],
[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity],
[UnderlyingIdentitySource], [UnderlyingName], [UnderlyingClosingPrice],
[UnderlyingDividendDate], [UnderlyingDividendPrice]
)
VALUES(@.reportDate, @.source, @.rawRowId,
@.tradeDate, @.symbol, @.identity, @.identitySource, @.exchange,
@.account, @.name, @.securityType, @.position, @.closingPrice,
@.expiry, @.optionStrikePrice, @.optionSide, @.optionMultiplier,
@.underlyingSymbol, @.underlyingIdentity, @.underlyingIdentitySource,
@.underlyingName, @.underlyingClosingPrice, @.underlyingDividendDate,
@.underlyingDividendPrice
)

-- check for error message
SET @.err = @.@.Error
IF @.err <> 0
BEGIN
-- create error message
IF @.err = 2627
SET @.errMsg = '2627 - PRIMARY KEY violation.'
ELSE
SET @.errMsg = 'Unexpected error : ' + LTRIM(RTRIM(STR(@.err)))

-- build description message
SET @.descMsg = 'Source: ' + COALESCE(@.source, 'NULL') + ', Symbol: '
+ COALESCE(@.symbol, 'NULL') + ', Identity: ' + COALESCE(@.identity,
'NULL') + ', Account: ' + COALESCE(@.account, 'NULL') + ', Position: ' +
COALESCE(LTRIM(RTRIM(STR(@.position))), 'NULL')

IF @.securityType = 'Future' OR @.securityType = 'Option' OR
@.securityType = 'Future Option'
SET @.descMsg = @.descMsg + ', Expiry: ' +
COALESCE(CONVERT(VARCHAR(10), @.expiry, 101), 'NULL')

IF @.securityType = 'Future' OR @.securityType = 'Option' OR
@.securityType = 'Future Option'
SET @.descMsg = @.descMsg + ', Strike: ' +
COALESCE(LTRIM(RTRIM(STR(@.optionStrikePrice))), 'NULL') + ', OptionSide:
' + COALESCE(@.optionSide, 'NULL')

-- log error in exception table
INSERT INTO ExportException ([ReportDate], [ErrorMessage],
[RowDescription], [TableName], [RawRowId])
VALUES (@.reportDate, @.errMsg, @.descMsg, 'Clearing.dbo.' +
@.clearingFirm + 'Position', @.rawRowId)
END

-- get next row
FETCH cPosition INTO@.reportDate, @.source, @.rawRowId,
@.tradeDate, @.symbol, @.identity, @.identitySource, @.exchange,
@.account, @.name, @.securityType, @.position, @.closingPrice,
@.expiry, @.optionStrikePrice, @.optionSide, @.optionMultiplier,
@.underlyingSymbol, @.underlyingIdentity, @.underlyingIdentitySource,
@.underlyingName, @.underlyingClosingPrice, @.underlyingDividendDate,
@.underlyingDividendPrice
END

-- clean up
CLOSE cPosition
DEALLOCATE cPosition

-- return everything good
RETURN 0

Job Output
----

Job 'Morning Batch Raw Export' : Step 3, 'Export Sage Positions' : Began
Executing 2003-10-24 09:09:30

Msg 2627, Sev 14: Violation of PRIMARY KEY constraint
'PK_ClearingPosition'. Cannot insert duplicate key in object
'ClearingPosition'. [SQLSTATE 23000]
Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]
Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007]
Msg 2627, Sev 14: Violation of PRIMARY KEY constraint
'PK_ClearingPosition'. Cannot insert duplicate key in object
'ClearingPosition'. [SQLSTATE 23000]
Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]

Manual Output
----

Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.

and so on...
(about 50+ PRIMARY KEY violations)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Apparently my post was too long. Posting code and error messages again.

Code
--
CREATE PROCEDURE spExportToClearingPosition (
@.clearingFirm VARCHAR(10),
@.reportDate DATETIME = NULL
)
AS
SET NOCOUNT ON

-- If report date is not specified use todays date.
SET @.reportDate = COALESCE(@.reportDate, CONVERT(VARCHAR(10), GetDate(),
101))

DECLARE
@.err INT,
@.errMsg VARCHAR(50),
@.descMsg VARCHAR(150)

-- declare variables for holding values during cursor looping
DECLARE
@.source VARCHAR(10),
@.rawRowId INT,
@.tradeDate DATETIME,
@.symbol VARCHAR(15),
@.identity VARCHAR(15),
@.identitySource VARCHAR(10),
@.exchange VARCHAR(5),
@.account VARCHAR(10),
@.name VARCHAR(75),
@.securityType VARCHAR(15),
@.position INT,
@.closingPrice DECIMAL(18, 6),
@.expiry DATETIME,
@.optionStrikePrice DECIMAL(18, 6),
@.optionSide VARCHAR(1),
@.optionMultiplier INT,
@.underlyingSymbol VARCHAR(15),
@.underlyingIdentity VARCHAR(15),
@.underlyingIdentitySource VARCHAR(10),
@.underlyingName VARCHAR(75),
@.underlyingClosingPrice DECIMAL(18, 6),
@.underlyingDividendDate DATETIME,
@.underlyingDividendPrice DECIMAL(18, 6)

-- ************************************************** ***********
-- Remove existing rows from historical table for specific
-- report date and just for specified clearing firm.
-- ************************************************** ***********

-- set source for deletion (will also check for valid clearing firm)
IF UPPER(@.clearingFirm) = 'MERRILL'
SET @.source = 'Merrill'
ELSE
IF UPPER(@.clearingFirm) = 'SAGE'
SET @.source = 'Sage'
ELSE
IF UPPER(@.clearingFirm) = 'PAX'
SET @.source = 'Pax'
ELSE
BEGIN
-- invalid clearing firm
RAISERROR('Invalid clearing firm "%s" was passed in.', 16, 1,
@.clearingFirm)
RETURN -100
END

DELETE FROM Historical.dbo.ClearingPosition
WHERE
[ReportDate] = @.reportDate
AND [Source] = @.source

-- ************************************************** ***********
-- Populate cursor based on clearing firm.
-- ************************************************** ***********
-- do Merrill SELECT (similiar to Sage except for view name)

IF UPPER(@.clearingFirm) = 'SAGE'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],

[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity], [UnderlyingIdentitySource],
[UnderlyingName], [UnderlyingClosingPrice], [UnderlyingDividendDate],
[UnderlyingDividendPrice]
FROM
vNormalizedClearingPosition_Sage
WHERE
[ReportDate] = @.reportDate
ELSE

-- do Pax SELECT (similiar to Sage except for view name)

-- ************************************************** ***********
-- Process cusor and insert into historical table
-- ************************************************** ***********

-- open cursor and fetch first row
OPEN cPosition
FETCH cPosition INTO@.reportDate, @.source, @.rawRowId,
@.tradeDate, @.symbol, @.identity, @.identitySource, @.exchange,
@.account, @.name, @.securityType, @.position, @.closingPrice,
@.expiry, @.optionStrikePrice, @.optionSide, @.optionMultiplier,
@.underlyingSymbol, @.underlyingIdentity, @.underlyingIdentitySource,
@.underlyingName, @.underlyingClosingPrice, @.underlyingDividendDate,
@.underlyingDividendPrice

-- loop until no more rows
WHILE @.@.Fetch_Status = 0
BEGIN
-- insert row into normalized table
INSERT INTO Historical.dbo.ClearingPosition ([ReportDate],
[Source],[RawRowId],
[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity],
[UnderlyingIdentitySource], [UnderlyingName], [UnderlyingClosingPrice],
[UnderlyingDividendDate], [UnderlyingDividendPrice]
)
VALUES(@.reportDate, @.source, @.rawRowId,
@.tradeDate, @.symbol, @.identity, @.identitySource, @.exchange,
@.account, @.name, @.securityType, @.position, @.closingPrice,
@.expiry, @.optionStrikePrice, @.optionSide, @.optionMultiplier,
@.underlyingSymbol, @.underlyingIdentity, @.underlyingIdentitySource,
@.underlyingName, @.underlyingClosingPrice, @.underlyingDividendDate,
@.underlyingDividendPrice
)

-- check for error message
SET @.err = @.@.Error
IF @.err <> 0
BEGIN
-- create error message
IF @.err = 2627
SET @.errMsg = '2627 - PRIMARY KEY violation.'
ELSE
SET @.errMsg = 'Unexpected error : ' + LTRIM(RTRIM(STR(@.err)))

-- build description message
SET @.descMsg = 'Source: ' + COALESCE(@.source, 'NULL') + ', Symbol: '
+ COALESCE(@.symbol, 'NULL') + ', Identity: ' + COALESCE(@.identity,
'NULL') + ', Account: ' + COALESCE(@.account, 'NULL') + ', Position: ' +
COALESCE(LTRIM(RTRIM(STR(@.position))), 'NULL')

IF @.securityType = 'Future' OR @.securityType = 'Option' OR
@.securityType = 'Future Option'
SET @.descMsg = @.descMsg + ', Expiry: ' +
COALESCE(CONVERT(VARCHAR(10), @.expiry, 101), 'NULL')

IF @.securityType = 'Future' OR @.securityType = 'Option' OR
@.securityType = 'Future Option'
SET @.descMsg = @.descMsg + ', Strike: ' +
COALESCE(LTRIM(RTRIM(STR(@.optionStrikePrice))), 'NULL') + ', OptionSide:
' + COALESCE(@.optionSide, 'NULL')

-- log error in exception table
INSERT INTO ExportException ([ReportDate], [ErrorMessage],
[RowDescription], [TableName], [RawRowId])
VALUES (@.reportDate, @.errMsg, @.descMsg, 'Clearing.dbo.' +
@.clearingFirm + 'Position', @.rawRowId)
END

-- get next row
FETCH cPosition INTO@.reportDate, @.source, @.rawRowId,
@.tradeDate, @.symbol, @.identity, @.identitySource, @.exchange,
@.account, @.name, @.securityType, @.position, @.closingPrice,
@.expiry, @.optionStrikePrice, @.optionSide, @.optionMultiplier,
@.underlyingSymbol, @.underlyingIdentity, @.underlyingIdentitySource,
@.underlyingName, @.underlyingClosingPrice, @.underlyingDividendDate,
@.underlyingDividendPrice
END

-- clean up
CLOSE cPosition
DEALLOCATE cPosition

-- return everything good
RETURN 0

Job Error Output
------
Job 'Morning Batch Raw Export' : Step 3, 'Export Sage Positions' : Began
Executing 2003-10-24 09:09:30

Msg 2627, Sev 14: Violation of PRIMARY KEY constraint
'PK_ClearingPosition'. Cannot insert duplicate key in object
'ClearingPosition'. [SQLSTATE 23000]
Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]
Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007]
Msg 2627, Sev 14: Violation of PRIMARY KEY constraint
'PK_ClearingPosition'. Cannot insert duplicate key in object
'ClearingPosition'. [SQLSTATE 23000]
Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]

Manual Error Output
------
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.

(and so on for 51 times...)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Jason Callas (jaycallas@.hotmail.com) writes:
> I have a stored procedure that runs as a step in a scheduled job. For
> some reason the job does not seem to finish when ran from the job but
> does fine when run from a window in SQL Query.
> I know the job is not working because the number of rows that are
> inserted into the table (see code) is considerably less than the manual
> runnning of it.
> I have included the code for the stored procedure, the output from the
> job, and the output from the manual run.
> I know somebody will probably ask WHY I am using a cursor. We have no
> control over the possibility of having a PK conflict since the data
> comes from outside sources. If I do it as just a INSERT INTO..SELECT
> than nothing goes in when I have a violation. As a business rule we
> would rather have MOST of the data inserted into the historical tables
> with a log of the ones that did not make it. We can then go back and
> deal with the ones that did not go in.

So write it as:

INSERT tbl (keycol, ...)
SELECT keycol, ...
FROM src
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.keycol = src.keycol)

A SELECT ... WHERE EXISTS before that can be good to list the duplicates
if you like.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94202AE268E2Yazorman@.127.0.0.1>...
> So write it as:
> INSERT tbl (keycol, ...)
> SELECT keycol, ...
> FROM src
> WHERE NOT EXISTS (SELECT *
> FROM tbl
> WHERE tbl.keycol = src.keycol)
> A SELECT ... WHERE EXISTS before that can be good to list the duplicates
> if you like.

I thought about that but I have several problems with it.

1) First and foremost is that I need to log any row that does not get
inserted into historical table. That way I can go and manually deal
with those rows (find out what the conflict was, fix it, and then
insert them).

I guess I could do a COUNT(*) with a HAVING > 1 statement but that
would catch any conflicts from the current normalized view. I would
have do it twice - once to compare the view to the historical table
and once to compare it to itself. Then log any results I get.

2) That would only deal with FK violations but it not with any other
issues. As an example would be a column in the normalized view that is
null but should not be. I do my best to clean up the data in the
normalized view but I cannot control the data that is given to me. For
the historical table I did identify and mark those columns that cannot
have nulls.

3) This solution does not really deal with my underlying problem. Is
it a common (or maybe uncommon but something I need to watch out for)
issue that a particular stored procedure could not work (or at least
different results) in a scheduled job (SQLs scheduler) compare to
within a SQL Query window?

Problem with @@identity return in stored procedure insert.

I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.

CREATE PROCEDURE addMagicRecTest
(
@.theSequence int,
@.theSubject int,
@.theFirstName nvarchar(50)=null
@.theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@.theSequence,@.theSubject,@.theFirstName,@.theLastName)
SELECT @.@.identity AS finalNum
magicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure
 ' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@.theSequence").Value = "41833"
' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@.theSubject").Value = "1064"
' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theFirstName", SqlDbType.NVarChar, 50))
 magicCommand.SelectCommand.Parameters("@.theFirstName").Value = orderFirstName
' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@.theLastName").Value = orderLastName
DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If

I need finalMagicNum2I usually just have the stored proc return the @.@.identity like so:

CREATE PROCEDURE addMagicRecTest
(
@.theSequence int,
@.theSubject int,
@.theFirstName nvarchar(50)=null
@.theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@.theSequence,@.theSubject,@.theFirstName,@.theLastName)
Return @.@.Identity

Your stored proc is not returning finalnum and your code has no output param set up in it.

Sam|||Try this code in .NET

DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables(0).Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables(0).Rows(0).item("finalnum").toString
'OR
'finalMagicNum2 = DSMagic.Tables(0).Rows(0).item(0).toString
End If

Hope this help

Problem with @@IDENTITY in stored procedure

Hello !

I just can't access @.@.IDENTITY in my requests !
Here is my stored procedure : (I removed a few lines for clarity)

set ANSI_NULLSONset QUOTED_IDENTIFIERONGOALTER PROCEDURE [dbo].[AssignerLicence](@.Apprenantint = 0)ASBEGINSET NOCOUNT ON;DECLARE @.NBLICENCESintDECLARE @.COMMANDEIDintDECLARE @.LICENCEIDintSET @.NBLICENCES = (SELECTSUM(commande_licences_reste)FROM CommandeWHERE commande_valide = 1)IF@.NBLICENCES > 0BEGINUPDATE CommandeSET commande_licences_reste = (commande_licences_reste - 1)WHERE commande_id =(SELECT TOP 1 C_min.commande_idFROM CommandeAS C_minWHERE C_min.commande_licences_reste > 5ORDER BY C_min.commande_licences_resteASC,C_min.commande_dateASC);SET @.COMMANDEID =@.@.IDENTITYINSERT INTO Licence (licence_date_debut, commande_id)VALUES (GETDATE(), @.COMMANDEID)SET @.LICENCEID =@.@.IDENTITYUPDATE ApprenantSET licence_id = @.LICENCEIDWHERE apprenant_id = @.ApprenantENDEND

But it always throws an error saying that I can't insert null into commande_id (basically, I think @.@.IDENTITY isn't executed at all)

Any idea why it doen't works ?

Thanks !

@.@.IDENTITY returns the last identity value that the currently executing statement created. Since all you are doing with CommandeID is updating, you can't use @.@.Identity. You need to rework your code similar to the following:

SELECT @.COMMANDEID = (SELECT TOP 1 C_min.commande_idFROM CommandeAS C_minWHERE C_min.commande_licences_reste > 5ORDER BY C_min.commande_licences_resteASC,C_min.commande_dateASC);UPDATE CommandeSET commande_licences_reste = (commande_licences_reste - 1)WHERE commande_id = @.COMMANDEID

Then test if it works.

Problem with @@identity in SQL Server 6.5

In SQL Server 6.5, I have a table called employee
with two fields emp_id and emp_name where emp_id
is an identity column.

The below Stored Procedure is trying to select
the last inserted emp_id using @.@.identity.

CREATE PROCEDURE Employee AS

begin

SET IDENTITY_INSERT employee OFF

SET NOCOUNT ON

declare @.empid int

INSERT INTO employee(emp_name) VALUES
("Soundy")

SET @.empid = @.@.identity

return @.empid

end

But when compiling this procedure, SQL Server displays
an error saying "Incorrect syntax near @.empid".

Can i know where the problem is ?Q1 Can i know where the problem is ?

A1 Try 'Soundy' instead of "Soundy"? For example:

Use TempDB
Go

CREATE TABLE [employee] (
[emp_id] [int] IDENTITY (1, 1) NOT NULL ,
[emp_name] [nvarchar] (50) NULL)
Go

CREATE PROCEDURE ins_Employee
@.pEmpName nvarChar (50) AS
begin
SET IDENTITY_INSERT employee OFF
SET NOCOUNT ON
declare @.empid int
INSERT INTO employee(emp_name) VALUES
(@.pEmpName)
SET @.empid = @.@.identity
return @.empid
end

Go

DECLARE @.RC int
-- exec the Proc
EXEC @.RC = ins_Employee @.pEmpName = 'Soundy'
Select @.RC as '@.RC for ins_employee'
Go

SELECT emp_id, emp_name
FROM employee
Where
emp_id = (SELECT Max(emp_id)FROM employee)

Problem with &

Hai

Iam using asp.net 2.0 with sql server 2005 as back end..

i need to pass the data to stored procedure via parameter containg (&) symbol

for ex:

If i pass string like "sample&ss"

it gives the error xml parsing how to avoid this..welcome for your suggestion.

Regards

Senthil

hi,

ampersand doesn't seems to cause an error on Sql server as the statement below

executes succesfully

select 'sample&ss'

I think the error happens in dot net since

where & is an operator such as

a$='xxx'&'yyy'

regards,

joey

|||

If the error is in parsing XML try this: "sample&amp;ss"

'&' is a special character in XML, and if you catually want to use it you need to represent it as &amp;

|||Or...use ASCII...it works all the time sql

Problem with %....% in the Parameterized Queries

I am using MS SQL 2000.I am writing a simple search statement in the stored procedure which goes like this
Create Procedure.[dbo].[SelectSearch]
(
@.searchtext nvarchar(100)
)
SELECT * FROM TableName WHERE ColumnName LIKE @.searchtext

i want to enter the value in place of @.searchtext as '%strsearch%'
and i'm trying to enter the value of @.searchtext using Parameterized Queries like this

objcmd = new SqlCommand ("SelectSearch", objConn);
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.Add("@.searchtext", "'%" + strsearch + "%'");

but its not working out and giving the error message as
Procedure SelectSearch has no parameters and arguments were supplied

Is this the correct way to add the parameters ? Or
How to add' % on both sides of strsearch thro parameterized queries
Thank you very much in advance


You could simply append the %'s to the value.
strsearch = "%" + strsearch + "%";
objcmd = new SqlCommand ("SelectSearch", objConn);
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.Add("@.searchtext" );
Also I'd recommend adding the size of the paremeter to avoid problems later on.
|||

Thank you very much mate .

I was struggling to solve this problem atlast i got the answer . Thnx once again

Problem while using stored procedures with temporary tables in dataset

I am trying to generate a report using SQL Server Reporting Service. The dataset is passed the results from a stored procedure. The stored proc contains a temporary table. On exceuting of proc, it fetches the result but when I try to save dataset I get following error message

Invalid object name '#AdditionalParams'. (.Net SqlClient Data Provider)

And no colums are returned in the data set created.

Any help on this would be appreciated.

Thanks in advance

If possible, try using a table variable instead, or create a physical table first.

http://www.odetocode.com/Articles/365.aspx

Here are some workarounds for temp tables.

http://www.sql-server-performance.com/rd_temp_tables.asp

If you have to, try using set fmtonly off in stored procedure.

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

cheers,

Andrew

|||

I exceuted the proc as stored procedure. And latter I added a new field to same dataset, as it didnt had any field because it had thrown error. I refreshed the datset and I got all the dataset fields although initally it showed error and it worked.

But their is essentially problem the way datset are handled in reporting service.

Thanks

Problem while using stored procedures with temporary tables in dataset

I am trying to generate a report using SQL Server Reporting Service. The dataset is passed the results from a stored procedure. The stored proc contains a temporary table. On exceuting of proc, it fetches the result but when I try to save dataset I get following error message

Invalid object name '#AdditionalParams'. (.Net SqlClient Data Provider)

And no colums are returned in the data set created.

Any help on this would be appreciated.

Thanks in advance

If possible, try using a table variable instead, or create a physical table first.

http://www.odetocode.com/Articles/365.aspx

Here are some workarounds for temp tables.

http://www.sql-server-performance.com/rd_temp_tables.asp

If you have to, try using set fmtonly off in stored procedure.

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

cheers,

Andrew

|||

I exceuted the proc as stored procedure. And latter I added a new field to same dataset, as it didnt had any field because it had thrown error. I refreshed the datset and I got all the dataset fields although initally it showed error and it worked.

But their is essentially problem the way datset are handled in reporting service.

Thanks

problem while using sp_ExecuteSql

Hi all

I have a stored procedure as I placed below

create procedure ps_Select_Student
@.RollNo nvarchar(50),
@.Class int
AS
Begin
declare @.MainQuery nvarchar(4000)
set @.MainQuery = 'select StudentName where RollNo=@.RollNo and Class=@.Class'

exec sp_ExecuteSql @.MainQuery
End

Go

While executing this procedure in Query Analyser i am agetting an error that

must declare @.RollNo

What is the problem. Please help me out.

You have to declare those variables on param declaration parameter of sp_executesql

Code Snippet

create procedure ps_Select_Student

@.RollNo nvarchar(50),

@.Class int

AS

Begin

Declare @.MainQuery nvarchar(4000)

Declare @.ParamDecl nvarchar(4000)

set @.MainQuery = N'select StudentName where RollNo=@.RollNo and Class=@.Class'

set @.ParamDecl = N'@.RollNo nvarchar(50),@.Class int'

Exec sp_ExecuteSql @.MainQuery , @.ParamDecl, @.RollNo, @.Class

End

Go