ExecSQL Task Truncates Ouput Parameter

I am successfully passing several parameters into a SP using the ExecSQL Task. I have a single parameter that is an OUTPUT parameter in the SP.

I always recieve the first character of the OUTPUT string. I have tried chaning the data type of the parameter mapping [AnsiString, String, etc.] and I have tried changing the data type of the OUTPUT parameter [varchar, char, nvarchar, nchar]

In each case, only the frist character of the output value is returned and subsequently passed to the next SSIS component / task.

Any ideas why this value is truncated?It is a String.

One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.

John|||

jloper wrote:

One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.

John

Ignore that, I was wrong. Sorry!

Note to self: Check out your answers before posting them!

-Jamie

exec sp with output parameter

I have the following sp:
ALTER PROCEDURE myspPrepareTextForHTML
@.MessageContent nvarchar(1400),
@.returnval nvarchar(1400) output
AS
BEGIN
SET NOCOUNT ON;
SET @.returnval='something'
RETURN @.returnval
END

When I try this:

EXEC myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnvalOUTPUT

print @.returnval

I get the error:
Must declare the scalar variable "@.returnval".

How can I get this to work?

You need to set the value of an output parameter. In this case, you jus want to pass in your current variable.

Just like other parameters, the left side of = means the name of the parameter, right side means the value, or return value (return variable).

EXEC myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnval = @.returnvalOUTPUT

|||now I get the error:

Msg 245, Level 16, State 1, Procedure myspPrepareTextForHTML, Line 17

Conversion failed when converting the nvarchar value 'something' to data type int.

|||

Peter Smith:

now I get the error:

Msg 245, Level 16, State 1, Procedure myspPrepareTextForHTML, Line 17

Conversion failed when converting the nvarchar value 'something' to data type int.

Most likely, the calling proc or SQL statement declaredits@.returnval as an int, rather than an nvarchar of the same size as declared in the myspPrepareTextForHTML proc.

|||This is everything I have so far...I dont see why I still get that int error:

ALTER

PROCEDURE [dbo].[myspPrepareTextForHTML]

@.MessageContent

nvarchar(1400),

@.returnval

nvarchar(1400)output

AS

BEGIN

SETNOCOUNTON;SET @.returnval='something'RETURN @.returnval

END

declare

@.returnvalnvarchar(1400)

EXEC

myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnval= @.returnvalOUTPUT

print

@.returnval|||Because anything you return with RETURN can only be of INT type. So SQL Server is trying to convert the 'something' to int. If you are returning the value through the OUTPUT parameter you dont need to use the RETURN statement. So remove theRETURN @.returnval statement in your proc and you should be fine.|||

Sorry, I missed the fact that you are trying to return an output value. Any value returned with the RETURN statement must be an int. This is why there are OUTPUT variables, which allow us to essentially 'return' values of any type (like using reference/ByRef variables in .NET).

Change your code to the following, and save the RETURN statement to return something similar to an error code:

ALTER PROCEDURE [dbo].[myspPrepareTextForHTML] @.MessageContentnvarchar(1400), @.returnvalnvarchar(1400)output ASBEGIN SET NOCOUNT ON;SET @.returnval='something'RETURN 0-- no errorEND

|||like a charm!

Exec Proc with date parameter

Hi,
I have spent whole night to figure this out and right now i am hands up..
please help.
I have stored procedure (dsTPint_GLIntegrationRun) which have nested cursors
and cursors call external stored procedure to execute.
dsTPint_GLIntegrationRun have parameter of companyid which is database id. i
want to run stored procedure residing in the database which is equal to the
database passed as parameter.
Now one of the the parameters for the external store procedure is date, i am
passing date and keep getting on error incorrect syntax near '1'. I changed
the date parameter and supplied constant value for debugging and now i am
getting error cannot convert nvarchar into int. all the external stored
procedure looks fine, source table is hundered percent accurate, i am not
sure whats wrong. following is the syntax:
Exec ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + ' OUTPUT
, ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' + @.TrnxDate
+ ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period + ', ' +
@.Year + ', ''' + @.Currency + '''')
-- Exec dsTPint_GLIntegrationRun 'WSZM'
this Exec statement is producing the error and the parameter which is
producing the error is @.TrnxDate.
Thanks
DhariwalHave you tried replacing EXEC with PRINT to check for syntax errors?
PRINT ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
OUTPUT
, ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
@.TrnxDate
+ ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period +
', ' +
@.Year + ', ''' + @.Currency + '''')
Also, you may want to use sp_executeSQL instead; it handles paramaters
much better than building a string.|||Remember that you are building a character string for your SQL statement.
Consequently, all of the variables and expressions must be character types.
Also, because you are passing a string containing constants to EXEC, you
can't have output parameter values. You need to use sp_executesql to return
output values from dynamic SQL. Below is an example you can tweak to
specify your actual datatypes and variable names.
DECLARE @.SQL nvarchar(4000),
@.dbname sysname,
@.oErrString varchar(50),
@.NEXTJNLNBR int,
@.GPBatchNumber int,
@.TrnxDate datetime,
@.Reference int,
@.Ortrxnum int,
@.Period int,
@.Year int,
@.Currency int
SET @.dbname = N'MyDatabase'
SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
@.oErrString output,
@.NEXTJNLNBR output,
@.GPBatchNumber,
@.TrnxDate,
@.Reference,
@.Ortrxnum,
@.Period,
@.Year,
@.Currency'
EXEC sp_executesql @.SQL,
N'@.oErrString varchar(50) output,
@.NEXTJNLNBR int output,
@.GPBatchNumber int,
@.TrnxDate datetime,
@.Reference int,
@.Ortrxnum int,
@.Period int,
@.Year int,
@.Currency int',
@.oErrString OUTPUT,
@.NEXTJNLNBR OUTPUT,
@.GPBatchNumber = @.GPBatchNumber,
@.TrnxDate = @.TrnxDate,
@.Reference = @.Reference,
@.Ortrxnum = @.Ortrxnum,
@.Period = @.Period,
@.Year = @.Year,
@.Currency = @.Currency
Hope this helps.
Dan Guzman
SQL Server MVP
"K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
> Hi,
> I have spent whole night to figure this out and right now i am hands up..
> please help.
> I have stored procedure (dsTPint_GLIntegrationRun) which have nested
> cursors
> and cursors call external stored procedure to execute.
> dsTPint_GLIntegrationRun have parameter of companyid which is database id.
> i
> want to run stored procedure residing in the database which is equal to
> the
> database passed as parameter.
> Now one of the the parameters for the external store procedure is date, i
> am
> passing date and keep getting on error incorrect syntax near '1'. I
> changed
> the date parameter and supplied constant value for debugging and now i am
> getting error cannot convert nvarchar into int. all the external stored
> procedure looks fine, source table is hundered percent accurate, i am not
> sure whats wrong. following is the syntax:
> Exec ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
> OUTPUT
> , ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
> @.TrnxDate
> + ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period + ', '
> +
> @.Year + ', ''' + @.Currency + '''')
> -- Exec dsTPint_GLIntegrationRun 'WSZM'
> this Exec statement is producing the error and the parameter which is
> producing the error is @.TrnxDate.
> Thanks
> Dhariwal|||Hi Stu,
Thanks for response.
I did try Print to see the statement, PRINT produces error that int cannot
be converted to string as there are parameters with int , date and varchar.
I will check sp_executeSQL to see if that helps.
Thanks
Dhariwal
"Stu" wrote:

> Have you tried replacing EXEC with PRINT to check for syntax errors?
> PRINT ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
> OUTPUT
> , ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
> @.TrnxDate
> + ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period +
> ', ' +
> @.Year + ', ''' + @.Currency + '''')
> Also, you may want to use sp_executeSQL instead; it handles paramaters
> much better than building a string.
>|||Dan and STU , you guys are stars. sp_executesql did the magic.
Thanks for your precious time, i really appreciated your help.
regards,
Dhariwal
"Dan Guzman" wrote:

> Remember that you are building a character string for your SQL statement.
> Consequently, all of the variables and expressions must be character types
.
> Also, because you are passing a string containing constants to EXEC, you
> can't have output parameter values. You need to use sp_executesql to retu
rn
> output values from dynamic SQL. Below is an example you can tweak to
> specify your actual datatypes and variable names.
> DECLARE @.SQL nvarchar(4000),
> @.dbname sysname,
> @.oErrString varchar(50),
> @.NEXTJNLNBR int,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int
> SET @.dbname = N'MyDatabase'
> SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
> @.oErrString output,
> @.NEXTJNLNBR output,
> @.GPBatchNumber,
> @.TrnxDate,
> @.Reference,
> @.Ortrxnum,
> @.Period,
> @.Year,
> @.Currency'
> EXEC sp_executesql @.SQL,
> N'@.oErrString varchar(50) output,
> @.NEXTJNLNBR int output,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int',
> @.oErrString OUTPUT,
> @.NEXTJNLNBR OUTPUT,
> @.GPBatchNumber = @.GPBatchNumber,
> @.TrnxDate = @.TrnxDate,
> @.Reference = @.Reference,
> @.Ortrxnum = @.Ortrxnum,
> @.Period = @.Period,
> @.Year = @.Year,
> @.Currency = @.Currency
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
> news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
>
>|||Hi Dan,
One small issue still.
I cant retrieve values from OUTPUT parameters, they are NULL.
Following is the syntax:
-- build the exec query for the database specified in input parameter
SET @.SQLString = ('Exec ' + @.dbname + '..daiGLHeaderInsert @.oErrString
OUTPUT, @.NEXTJNLNBR OUTPUT, @.GPBatchNumber, @.TrnxDate, @.Reference,
@.Ortrxnum, @.Period, @.Year, @.Currency')
SET @.ParmDefinition = N'@.oErrString varchar(255) OUTPUT, @.NEXTJNLNBR int
OUTPUT, @.GPBatchNumber char(15), @.TrnxDate datetime, @.Reference char(31),
@.Ortrxnum char(13), @.Period smallint, @.Year smallint, @.Currency char(10) '
execute sp_executesql @.SQLString , @.ParmDefinition ,
@.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT , @.GPBatchNumber = @.GPBatchNumber
, @.TrnxDate = @.TrnxDate , @.Reference = @.Reference , @.Ortrxnum = @.Ortrxnum ,
@.Period = @.Period , @.Year = @.Year , @.Currency = @.Currency
PRINT @.NEXTJNLNBR
(This print returns nothing at all)
Also i noted that i had to declare @.NEXTJNLNBR before the above code else i
get error that varialbe needs to be declared.
Following is the PRINT of the above statement:
Exec WSZM..daiGLHeaderInsert @.oErrString OUTPUT, @.NEXTJNLNBR OUTPUT,
@.GPBatchNumber, @.TrnxDate, @.Reference, @.Ortrxnum, @.Period, @.Year, @.Currency
Thanks
Dhariwal
"Dan Guzman" wrote:

> Remember that you are building a character string for your SQL statement.
> Consequently, all of the variables and expressions must be character types
.
> Also, because you are passing a string containing constants to EXEC, you
> can't have output parameter values. You need to use sp_executesql to retu
rn
> output values from dynamic SQL. Below is an example you can tweak to
> specify your actual datatypes and variable names.
> DECLARE @.SQL nvarchar(4000),
> @.dbname sysname,
> @.oErrString varchar(50),
> @.NEXTJNLNBR int,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int
> SET @.dbname = N'MyDatabase'
> SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
> @.oErrString output,
> @.NEXTJNLNBR output,
> @.GPBatchNumber,
> @.TrnxDate,
> @.Reference,
> @.Ortrxnum,
> @.Period,
> @.Year,
> @.Currency'
> EXEC sp_executesql @.SQL,
> N'@.oErrString varchar(50) output,
> @.NEXTJNLNBR int output,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int',
> @.oErrString OUTPUT,
> @.NEXTJNLNBR OUTPUT,
> @.GPBatchNumber = @.GPBatchNumber,
> @.TrnxDate = @.TrnxDate,
> @.Reference = @.Reference,
> @.Ortrxnum = @.Ortrxnum,
> @.Period = @.Period,
> @.Year = @.Year,
> @.Currency = @.Currency
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
> news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
>
>|||Dhariwal,
Your declaration of @.ParmDefinition is probably too short to hold the
complete parameter definition.
before you execute sp_executesql, try PRINT @.ParmDefinition to
see, or change its definition to
DECLARE @.ParmDefinition(1000)
for example.
Steve Kass
Drew University
K M Dhariwal wrote:
>Hi Dan,
>One small issue still.
>I cant retrieve values from OUTPUT parameters, they are NULL.
>Following is the syntax:
> -- build the exec query for the database specified in input parameter
> SET @.SQLString = ('Exec ' + @.dbname + '..daiGLHeaderInsert @.oErrString
>OUTPUT, @.NEXTJNLNBR OUTPUT, @.GPBatchNumber, @.TrnxDate, @.Reference,
>@.Ortrxnum, @.Period, @.Year, @.Currency')
> SET @.ParmDefinition = N'@.oErrString varchar(255) OUTPUT, @.NEXTJNLNBR int
>OUTPUT, @.GPBatchNumber char(15), @.TrnxDate datetime, @.Reference char(31),
>@.Ortrxnum char(13), @.Period smallint, @.Year smallint, @.Currency char(10) '
> execute sp_executesql @.SQLString , @.ParmDefinition ,
> @.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT , @.GPBatchNumber = @.GPBatchNumbe
r
>, @.TrnxDate = @.TrnxDate , @.Reference = @.Reference , @.Ortrxnum = @.Ortrxnum ,
>@.Period = @.Period , @.Year = @.Year , @.Currency = @.Currency
>PRINT @.NEXTJNLNBR
>(This print returns nothing at all)
>Also i noted that i had to declare @.NEXTJNLNBR before the above code else
i
>get error that varialbe needs to be declared.
>Following is the PRINT of the above statement:
>Exec WSZM..daiGLHeaderInsert @.oErrString OUTPUT, @.NEXTJNLNBR OUTPUT,
>@.GPBatchNumber, @.TrnxDate, @.Reference, @.Ortrxnum, @.Period, @.Year, @.Currenc
y
>Thanks
>Dhariwal
>
>"Dan Guzman" wrote:
>
>|||>>@.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT
try this
1. you should declare all out parameters twice once in exec scope(param def)
and other in your calling code and then
@.oErrString = @.oErrString,@.NEXTJNLNBR = @.NEXTJNLNBR to assign the values
print @.NEXTJNLNBR
Regards
R.D
"Steve Kass" wrote:

> Dhariwal,
> Your declaration of @.ParmDefinition is probably too short to hold the
> complete parameter definition.
> before you execute sp_executesql, try PRINT @.ParmDefinition to
> see, or change its definition to
> DECLARE @.ParmDefinition(1000)
> for example.
> Steve Kass
> Drew University
> K M Dhariwal wrote:
>
>|||Hi Steve & RD.
The problem was in the logic of external proc and the statement wasn't even
getting to the line where it must populate the value of output parameter.
Thanks for the help guys.
Regrads,
Dhariwal
"R.D" wrote:
> try this
> 1. you should declare all out parameters twice once in exec scope(param de
f)
> and other in your calling code and then
> @.oErrString = @.oErrString,@.NEXTJNLNBR = @.NEXTJNLNBR to assign the values
> print @.NEXTJNLNBR
> Regards
> R.D
> "Steve Kass" wrote:
>

EXEC / Sp_executesql with Dynamic SQL string exceeding 4000 - SQL Server 2000

This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure.

I have my SQL string exeeding more than 4000 characters.
The sp_executesql expects its parameters to be declared as nvarchar/ntext.
ntext cannot be declared for a local variable and nvarchar has a maximum limit of 4000 characters.

The following was the original approach.
===============
DECLARE @.sql nvarchar(4000),

--
select @.sql=@.sql+'select.................'
--
--
INSERT INTO #Temp
EXEC sp_executesql @.SQL,@.paramlist

===============================

The above sql was split into two sql variables and tried in the following way.
==============================
DECLARE @.sql nvarchar(4000), @.sql1 nvarchar(4000),@.paramlist nvarchar(4000)

--
select @.sql=@.sql+'select.................'
select @.sql1=@.sql1+'from table1, table2.........'
--
--

INSERT INTO #Temp
exec('EXEC sp_executesql ''' + @.sql + @.sql1 + ''',@.paramlist')
====================

However, it compiles correctly but during the execution, the error indicates as syntax error.
I ran the resulting SQL string (@.sql + @.sql1) separately, and it runs fine without any syntax errors returning rows, but when execute through
sp_executesql, it gives me error

Appreciate if anyone can suggest a workaround for this situation.

Try the other version of executing dynamic SQL the simple one....

EXEC(@.sql + @.Sql1)

Because i have used this 2 one place where my Dynamic SQl even exceeds 1lakh characters....|||

Thanks Mandip. It's working. Appreciate your help.

Excluding filter via report parameter

Hi,

How to exclude a filter on a dataset such that I may either apply the filter or not?

I would like to control that through a boolean report parameter.

Edmund

try creating a stored procedure as your dataset. in the sp write an If statement. this lets you set up logic so thet a different query can be run e.g if "true" is selected query A is run filter applied

if "false" is selected query b can be run with no filter

Excluding empty parameters

I would like to exclude any parameter that is empty from the SELECTcommand? How do I do this? This is part of a storedprocedure.
SELECT PersonID FROM Persons WHERE
(FirstName = @.firstname) AND
(LastName = @.lastname) AND
(SSN = @.ssn) AND
(AddressID = @.addressid) AND
(DOB = @.dob) AND
(Middle = @.middle)
THanks
One way to do that is to pass a NULL value for the parameters to be excluded, and then structure your query like this:

SELECT PersonID FROM Persons WHERE

(FirstName = ISNULL(@.firstname,FirstName)) AND

(LastName = ISNULL(@.lastname,LastName)) AND

(SSN = ISNULL(@.ssn,ssn)) AND

(AddressID = ISNULL(@.addressid,AddressID)) AND

(DOB = ISNULL(@.dob,DOB)) AND

(Middle = ISNULL(@.middle,Middle))

|||Thank you Teri - As always you answer me SQL questions!

EXCEPTION_ACCESS_VIOLATION using ntext

I get the following error when i execute the SQL below.
It works if the sp parameter is text rather than ntext or if the update uses
the full key.
(Windows 2K sp4, sql 2k sp3 no hot fixes)
================================================== === BugCheck Dump
================================================== ===
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current time is 11:23:50 09/21/04.
1 Intel x86 level 15, 3 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
MemoryLoad = 80%
Total Physical = 1021 MB
Available Physical = 196 MB
Total Page File = 1326 MB
Available Page File = 645 MB
Total Virtual = 2047 MB
Available Virtual = 948 MB
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0170.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 09/21/04 11:23:50 spid 54
*
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 82 bytes -
* exec spStoreCompanyTest 1, 2, 3, N'test'
================Test.sql=========================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCompanyTest]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblCompanyTest]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spStoreCompanyTest]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spStoreCompanyTest]
GO
CREATE TABLE [dbo].[tblCompanyTest] (
[source] [int] NOT NULL ,
[feed] [int] NOT NULL ,
[code] [int] NOT NULL ,
[sourceData] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompanyTest] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompanyTest] PRIMARY KEY CLUSTERED
(
[source],
[feed],
[code]
) ON [PRIMARY]
GO
CREATE PROCEDURE dbo.spStoreCompanyTest
@.source int,
@.feed int,
@.code int,
@.sourceData ntext --text
AS
IF NOT EXISTS(SELECT * FROM tblCompanyTest WHERE source = @.source AND feed
= @.feed AND code = @.code)
BEGIN
INSERT INTO tblCompanyTest
VALUES(@.source, @.feed, @.code, @.sourceData)
END
ELSE
BEGIN
UPDATE tblCompanyTest
SET sourceData = @.sourceData
WHERE source = @.source
AND feed = @.feed
--and code = @.code
END
RETURN
GO
exec spStoreCompanyTest 1, 2, 3, N'test'
GO
exec spStoreCompanyTest 1, 2, 3, N'test'
GO
> I get the following error when i execute the SQL below.
> It works if the sp parameter is text rather than ntext or if the update
uses
> the full key.
> (Windows 2K sp4, sql 2k sp3 no hot fixes)
> ================================================== === BugCheck Dump

> ================================================== ===

> This file is generated by Microsoft SQL Server 8.00.760

> upon detection of fatal unexpected error. Please return this file,

> the query or program that produced the bugcheck, the database and

> the error log, and any other pertinent information with a Service
Request.
>

> Computer type is AT/AT COMPATIBLE.

> Current time is 11:23:50 09/21/04.

> 1 Intel x86 level 15, 3 Mhz processor(s).

> Windows NT 5.0 Build 2195 CSD Service Pack 4.

>
> Memory
> MemoryLoad = 80%
> Total Physical = 1021 MB
> Available Physical = 196 MB
> Total Page File = 1326 MB
> Available Page File = 645 MB
> Total Virtual = 2047 MB
> Available Virtual = 948 MB
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0170.txt

> *
>
************************************************** **************************
*
> **

> *

> * BEGIN STACK DUMP:

> * 09/21/04 11:23:50 spid 54

> *

> * Exception Address = 00000000

> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION

> * Access Violation occurred reading address 00000000

> * Input Buffer 82 bytes -

> * exec spStoreCompanyTest 1, 2, 3, N'test'

>
> ================Test.sql=========================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblCompanyTest]') and OBJECTPROPERTY(id,
N'IsUserTable') =
> 1)
> drop table [dbo].[tblCompanyTest]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[spStoreCompanyTest]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[spStoreCompanyTest]
> GO
> CREATE TABLE [dbo].[tblCompanyTest] (
> [source] [int] NOT NULL ,
> [feed] [int] NOT NULL ,
> [code] [int] NOT NULL ,
> [sourceData] [ntext] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCompanyTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCompanyTest] PRIMARY KEY CLUSTERED
> (
> [source],
> [feed],
> [code]
> ) ON [PRIMARY]
> GO
>
> CREATE PROCEDURE dbo.spStoreCompanyTest
> @.source int,
> @.feed int,
> @.code int,
> @.sourceData ntext --text
> AS
> IF NOT EXISTS(SELECT * FROM tblCompanyTest WHERE source = @.source AND
feed
> = @.feed AND code = @.code)
> BEGIN
> INSERT INTO tblCompanyTest
> VALUES(@.source, @.feed, @.code, @.sourceData)
> END
> ELSE
> BEGIN
> UPDATE tblCompanyTest
> SET sourceData = @.sourceData
> WHERE source = @.source
> AND feed = @.feed
> --and code = @.code
> END
> RETURN
> GO
> exec spStoreCompanyTest 1, 2, 3, N'test'
> GO
> exec spStoreCompanyTest 1, 2, 3, N'test'
> GO
>
Paste the whole sqldmpxxxx.txt and I'll give it a go.
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Excel OmitFormulas DeviceInfo parameter:

Is there a way I can set OmitFormulas DeviceInfo parameter to true for a whole site so that when a user exports the data to Excel, they only get the values?

Joe

Yes, this is possible in RS 2005 by modifying the RSReportServer.config file.

You could either modify the original "Excel" entry or add a new one.

<Render>
<Extension Name="Modified Excel" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
<OverrideNames>
<Name Language="en-US">Modified Excel</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<OmitFormulas>true</OmitFormulas>
</DeviceInfo>
</Configuration>
</Extension>
...
</Render>
Additional Excel deviceinfo settings:
http://msdn2.microsoft.com/en-us/library/ms155069.aspx

-- Robert