error in updating field thru stor_proc

I'm trying to update 'password' field in a table thru a stored procedure which is called from my program.

I'm passing the values for password and the key fields.

While debugging I can see the values in each parameter, but I get an error that the 'value for password was not passed'.

Here's the program snippet for calling the stored proc.

All three parameters are 'varchar' type

dbConn.Open();

dbConn.CreateParameters(3);

dbConn.AddParameters(0, Pwd, newPwd);

dbConn.AddParameters(1, Login, pwd_Login);

dbConn.AddParameters(2, IdNum, pwd_IdNum);

result = dbMgr.ExecuteNonQuery(CommandType.StoredProcedure, SP_UPDPWD);

--

EXEC @.return_value = [dbo].[usp_UsrMstUpdPwdParaPwdEmailCaseNum]

@.Pwd = N'3Sg7vqowIBRdfgdfgrgdjykFTjTFt5hfHfhFtFghzIG1haWRliBuYW1lPw==',

@.Login = N'xyz@.abc.com',

@.IdNumber = N'00009'

--

What am I missing?

Is Pwd a variable or constant set to "@.Pwd", login to "@.Login", IdNum to "@.IdNum", and "SP_UPDPWD" to the appropriate string for your stored procedure?

Also, is this using an IDBManager pattern?

Thanks,

John

|||

Never mind. found the problem

The parameter for Password was not spelt right!

Error in the Stored Procedure

I am trying to swap two rows in a table .. I am stuck with this error since a long time.. can anyone guess where the problem is ? create procedure was working fine in query analyzer but when used it in the stored procedure. I am getting these .. can anyone help me out please ... Your help will be greatly appreciated.. UpdateRowsReorderUp is my storedprocedure ... and i am using MS Sql 2000 .. am I doing something really wrong which i'm not supposed to ???

Thanks friends..

Procedure 'UpdateRowsReorderUp' expects parameter '@.nextlowestsortID', which was not supplied.

CREATE PROCEDURE [dbo].[UpdateRowsReorderUp]

(

@.intsortID int,

@.nextlowestsortID int,

@.MemberID int

)

AS

Select @.nextlowestsortID=(Select Top 1 SortID from SelectedCredits where SortID<@.intsortID order by SortID DESC)

UPDATE SelectedCredits SET SortID= CASE

WHEN SortID = @.nextlowestsortID then @.intsortID

WHEN SortID = @.intsortID then @.nextlowestsortID ELSE SortID End

WHERE MemberID = @.MemberID

SELECT * FROM SelectedCredits WHERE MemberID= @.MemberID ORDER BY SortID

GO

**************

// this is my script on the page

void moveup(Object s, DataListCommandEventArgs e) {

objcmd= new SqlCommand("UpdateRowsReorderUp",objConn);

objcmd.CommandType = CommandType.StoredProcedure;

objcmd.Parameters.Add("@.intsortID",intsortID);

objcmd.Parameters.Add("@.MemberID",Session["MemberID"]);

objRdr= objcmd.ExecuteReader();

dlSelCredits.DataSource = objRdr;

dlSelCredits.DataBind();

objRdr.Close();

objConn.Close();

BindData();

}

You are missing the @.nextlowestsortid parameter in your code. Add it between the other two.
|||

Thanks for your reply . I'm storing a value in the @.nextlowestsortid using a SELECT statement.I am not assigning any value outside so that i can pass it into the parameter. for example @.intsortid i'm assiging intsortid through the code but what can i assign to this? Sorry to ask like this i am a newbie in this field..

Can i write the SELECT statement in different way so that there is no need add any parameter through the code ??

|||If the @.nextlowestsortid parameter is an internal parameter, remove itfrom the parameter list and declare it after the AS, like this:
DECLARE @.nextlowestsortid int
Then you stored procedure will only have two parameters that you supply, and the error will go away.
Sam
|||

Thank you very much Sam .. I am really grateful to you ..

You solved my problem .. that was the perfect solution ..

cheers mate

Error in Stored Procedure while working with temp. table

Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :

String or binary data would be truncated.The statement has been terminated.

The procedure i created is as :

ALTER PROCEDUREfetchpersondetails

AS

CREATE Table#tempperson (personIDint,FirstNamenvarchar(200),LastNamenvarchar(250),titlenvarchar(150),Professionnvarchar(200),StreetAddressnvarchar(300),

StateAddressnvarchar(200),CityAddressnvarchar(200),CountryAddressnvarchar(200),ZipAddressnvarchar(200),Telephonenvarchar(200),Mobilenvarchar(200),

Faxnvarchar(200),Emailnvarchar(250),NotesPubntext,Affiliationnvarchar(200),Categorynvarchar(200))

Insert into#tempperson

SELECTdbo.tblperson.personID,ISNULL(dbo.tblperson.fName, N'') +' '+ISNULL(dbo.tblperson.mName, N'')ASFirstName, dbo.tblperson.lnameASLastName,

dbo.tblperson.honorASTitle, dbo.tblperson.titleASProfession, dbo.tblperson.street +' '+ISNULL(dbo.tblperson.suite, N'')ASStreetAddress,

dbo.tblperson.cityAScityaddress, dbo.tblperson.stateASstateaddress, dbo.tblperson.postalCodeASzipaddress,

dbo.tblperson.Phone1 +','+ISNULL(dbo.tblperson.Phone2, N'') +','+ISNULL(dbo.tblperson.Phone3, N'')ASTelephone,

dbo.tblperson.mobilePhoneASmobile, dbo.tblperson.officeFax +','+ISNULL(dbo.tblperson.altOfficeFax, N'') +','+ISNULL(dbo.tblperson.altOfficeFax2,

N'')ASFax,ISNULL(dbo.tblperson.Email1, N'') +','+ISNULL(dbo.tblperson.Email2, N'') +','+ISNULL(dbo.tblperson.Email3, N'')ASEmail,

dbo.tblperson.notesASNotesPub, dbo.tblOrganizations.orgNameASAffiliation, dbo.tblOrganizations.orgCategoryASCategory,

dbo.tblCountry.countryNameFullAScountryaddress

FROMdbo.tblpersonINNER JOIN

dbo.tblOrganizationsONdbo.tblperson.orgID = dbo.tblOrganizations.orgIDINNER JOIN

dbo.tblCountryONdbo.tblperson.countryCode = dbo.tblCountry.ISOCode

please let me know the solurion of this error.

What the error msg means is that you are trying to insert a value that is larger than what the column can take. Check your column lengths in the temp table and the data coming in from your SELECT statment. Apparently, you have crossed the limit somewhere.

|||

I made it working.Its not the column length that creating problem.

Error in stored procedure that updates a row

I have the following stored procedure:

CREATE PROCEDURE user1122500.sp_modifyOrganization
(
@.Name nvarchar(100)
,@.Location nvarchar(50)
,@.Url nvarchar (250)
,@.Org_Type nvarchar (50)
,@.Par_Org_Id uniqueidentifier
,@.Row_Id uniqueidentifier
,@.Error_Code int OUTPUT
,@.Error_Text nvarchar(768) OUTPUT
)
AS
DECLARE @.errorMsg nvarchar(512)
DECLARE @.spName sysname

SELECT @.spName = Object_Name(@.@.ProcID)
SET @.Error_Code = 0

IF @.Url > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET URL = @.Url
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'URL', @.Url
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Org_Type > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET ORG_TYPE = @.Org_Type
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'ORG_TYPE', @.Org_Type
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Par_Org_Id IS NOT NULL
BEGIN
UPDATE USER1122500.ORGANIZATION
SET PAR_ORG_ID = @.Par_Org_Id
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'PAR_ORG_ID', @.Par_Org_Id
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Name > ' ' OR @.Location > ' '
BEGIN

IF EXISTS (SELECT ROW_ID FROM USER1122500.ORGANIZATION WHERE NAME = @.Name AND LOCATION = @.Location)
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'NAME', @.Name,
N'LOCATION', @.Location
SET @.Error_Code = 55004 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
-- RAISERROR(@.Error_Text, 10, 1, @.spName, @.Error_Code, 'ORGANIZATION', @.errorMsg)
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'sp_name',@.spName))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'err_cd',@.Error_Code))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'tbl_name','ORGANIZATION'))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'err_msg',@.errorMsg))
RETURN(@.Error_Code)
END

IF @.Name > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET NAME = @.Name
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'PAR_ORG_ID', @.Name
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Location > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET LOCATION = @.Location
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'LOCATION', @.Location
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

END
GO

This is the code that runs it:

string strSP = "sp_modifyOrganization";

SqlParameter[] Params =new SqlParameter;

string strParOrgID =null;

if (this.ddlParentOrg.SelectedItem.Value != "")

{

strParOrgID =this.ddlParentOrg.SelectedItem.Value;

}

Params[0] =new SqlParameter("@.Name", txtName.Text);

Params[1] =new SqlParameter("@.Location",this.txtLocation.Text);

Params[2] =new SqlParameter("@.Url",this.txtURL.Text);

Params[3] =new SqlParameter("@.Org_Type",this.txtOrgType.Text);

//Params[4] = new SqlParameter("@.Par_Org_Id", strParOrgID);

Params[4] =new SqlParameter("@.Par_Org_Id", "CA1FBC83-D978-48F1-BCBC-E53AD5E8A321".ToUpper());

Params[5] =new SqlParameter("@.Row_Id", "688f2d10-1550-44f8-a62c-17610d1e979a".ToUpper());

// Params[5] = new SqlParameter("@.Row_Id", lblOrg_ID.Text);

Params =new SqlParameter("@.Error_Code", -1);

Params[7] =new SqlParameter("@.Error_Text", "");

Params[4].SqlDbType = SqlDbType.UniqueIdentifier;

Params[5].SqlDbType = SqlDbType.UniqueIdentifier;

Params.Direction = ParameterDirection.Output;

Params[7].Direction = ParameterDirection.Output;

try

{

this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);

if (Params.Value.ToString() != "0")

{

lblError.Text = "There was an error: " + Params.Value.ToString()+ "###" + Params[7].Value.ToString();

lblError.Visible =true;

}

}

//catch (System.Data.SqlClient.SqlException ex)

catch (System.InvalidCastException inv)

{

lblError.Text = lblOrg_ID.Text + "<br><br>" + inv.ToString() + inv.Message + inv.StackTrace + inv.HelpLink;

lblError.Visible =true;

}

catch (Exception ex)

{

lblError.Text = lblOrg_ID.Text + "<br><br>" + ex.ToString();

lblError.Visible =true;

// return false;

}

This is the exception being generated:

System.InvalidCastException: Invalid cast from System.String to System.Guid.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, 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 Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\Program Files\_vsNETAddOns\Microsoft Application Blocks for .NET\Data Access v2\Code\VB\Microsoft.ApplicationBlocks.Data\SQLHelper.vb:line 542
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\Program Files\_vsNETAddOns\Microsoft Application Blocks for .NET\Data Access v2\Code\VB\Microsoft.ApplicationBlocks.Data\SQLHelper.vb:line 458
at development.youthleadercert.com.share.ascx.organizationForm.btnAdd_Click(Object sender, EventArgs e) in c:\documents and settings\mark rubin\vswebcache\development.youthleadercert.com\share\ascx\organizationform.ascx.cs:line 352

I have no idea what field is even causing the error, nor do I see that I'm even using a GUID field. I've been stuck on this for 2 days. Any help?

I would guess it's here. You're passing them as strings. You may want to explicitly type them as UniqueIdentifiers. Or, change your proc temporarily and define your parameters as varchars and see what happens. I bet that even though they may look like GUIDs, SQL doesn't see them that way when they're passed in. Just a guess, but that would be where I would start.

Params[4] =new SqlParameter("@.Par_Org_Id", "CA1FBC83-D978-48F1-BCBC-E53AD5E8A321".ToUpper());

Params[5] =new SqlParameter("@.Row_Id", "688f2d10-1550-44f8-a62c-17610d1e979a".ToUpper());

|||

In my code, I already am setting the db type a few rows down...

Params[4].SqlDbType = SqlDbType.UniqueIdentifier;

Params[5].SqlDbType = SqlDbType.UniqueIdentifier;

Error in Stored Procedure Debugger

Hi,
SP debugger on my machine has suddenly stopped running
with the following message
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug,
Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to
connect to debugger on SWINDEV-SQL-01\DEV1 (Error =
0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on L000646.
Debugging disabled for connection 73.
I am running on SP3 and have also run exec
sp_sdidebug 'legacy_on' . It still gives this error.
Any more ideas any one ?
Have you checked out this link:
http://msdn.microsoft.com/library/de...tools_5cfm.asp
Cheers,
James Goodman
"Anoop Agarwal" <agarwala@.halcrow.com> wrote in message
news:f92301c43e55$7e541b40$a601280a@.phx.gbl...
> Hi,
> SP debugger on my machine has suddenly stopped running
> with the following message
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug,
> Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to
> connect to debugger on SWINDEV-SQL-01\DEV1 (Error =
> 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on L000646.
> Debugging disabled for connection 73.
> I am running on SP3 and have also run exec
> sp_sdidebug 'legacy_on' . It still gives this error.
> Any more ideas any one ?
|||I have tried all the things listed in the Article and still get the same
error.
Anoop
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Error in stored procedure

I have a stored procedure that will copy data from one table to another, but
I am having problem doing so from a 'text' field from table 1 to table 2.
Here is my SP:
<%
Dim Addtemplates__FirmId
Addtemplates__FirmId = "4"
if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("FirmId")
Dim Addtemplates__CaseId
Addtemplates__CaseId = "0"
if(Request.Querystring("caseid") <> "") then Addtemplates__CaseId =
Request.Querystring("caseid")
Dim Addtemplates__SecLtr
Addtemplates__SecLtr = "0"
if(Request.Querystring("caseid") <> "") then Addtemplates__SecLtr =
Request.Querystring("caseid")
Dim Addtemplates__LtrName
Addtemplates__LtrName = "0"
if(Templatesfiltered("LtrName") <> "") then Addtemplates__LtrName =
Templatesfiltered("LtrName")
Dim Addtemplates__LtrBody
Addtemplates__LtrBody = "0"
if(Templatesfiltered("LtrBody") <> "") then Addtemplates__LtrBody =
Templatesfiltered("LtrBody")
%>
<%
set Addtemplates = Server.CreateObject("ADODB.Command")
Addtemplates.ActiveConnection = MM_eimmigration_STRING
Addtemplates.CommandText = "dbo.Addtemplatestocase"
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.RETURN_VALUE",
3, 4)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.FirmId", 3,
1,4,Addtemplates__FirmId)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.CaseId", 3,
1,4,Addtemplates__CaseId)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.SecLtr", 200,
1,50,Addtemplates__SecLtr)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrName", 200,
1,100,Addtemplates__LtrName)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrBody", 200,
1,16000,Addtemplates__LtrBody)
Addtemplates.CommandType = 4
Addtemplates.CommandTimeout = 0
Addtemplates.Prepared = true
Addtemplates.Execute()
%>
LtrBody is the field wich is 'text'. If I change it to be nvarchar 4000 it
works fine, but I need more space that is why I need it to be text.
This is the error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
Line # 128 is: Addtemplates.Execute()
Any clues with the information above ?
AleksBlob must be handled differently than regular string. See these for some
info.
HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/d_efau...b;en-us;1949_75
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/d_efau...;EN-US;q258_038
--
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I have a stored procedure that will copy data from one table to another,
>but I am having problem doing so from a 'text' field from table 1 to table
>2.
> Here is my SP:
> <%
> Dim Addtemplates__FirmId
> Addtemplates__FirmId = "4"
> if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("FirmId")
> Dim Addtemplates__CaseId
> Addtemplates__CaseId = "0"
> if(Request.Querystring("caseid") <> "") then Addtemplates__CaseId =
> Request.Querystring("caseid")
> Dim Addtemplates__SecLtr
> Addtemplates__SecLtr = "0"
> if(Request.Querystring("caseid") <> "") then Addtemplates__SecLtr =
> Request.Querystring("caseid")
> Dim Addtemplates__LtrName
> Addtemplates__LtrName = "0"
> if(Templatesfiltered("LtrName") <> "") then Addtemplates__LtrName =
> Templatesfiltered("LtrName")
> Dim Addtemplates__LtrBody
> Addtemplates__LtrBody = "0"
> if(Templatesfiltered("LtrBody") <> "") then Addtemplates__LtrBody =
> Templatesfiltered("LtrBody")
> %>
> <%
> set Addtemplates = Server.CreateObject("ADODB.Command")
> Addtemplates.ActiveConnection = MM_eimmigration_STRING
> Addtemplates.CommandText = "dbo.Addtemplatestocase"
> Addtemplates.Parameters.Append
> Addtemplates.CreateParameter("@.RETURN_VALUE", 3, 4)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.FirmId", 3,
> 1,4,Addtemplates__FirmId)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.CaseId", 3,
> 1,4,Addtemplates__CaseId)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.SecLtr",
> 200, 1,50,Addtemplates__SecLtr)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrName",
> 200, 1,100,Addtemplates__LtrName)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrBody",
> 200, 1,16000,Addtemplates__LtrBody)
> Addtemplates.CommandType = 4
> Addtemplates.CommandTimeout = 0
> Addtemplates.Prepared = true
> Addtemplates.Execute()
> %>
>
> --
> LtrBody is the field wich is 'text'. If I change it to be nvarchar 4000 it
> works fine, but I need more space that is why I need it to be text.
> This is the error:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
> Line # 128 is: Addtemplates.Execute()
> Any clues with the information above ?
> Aleks
>|||I get this with your link: The system cannot find the file specified.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
> Blob must be handled differently than regular string. See these for some
> info.
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> --
>
>
>
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||Also there is nothing on how to handle it with a stored procedure.
A
"oj" <nospam_ojngo@.home.com> wrote in message
news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
> Blob must be handled differently than regular string. See these for some
> info.
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> --
>
>
>
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||hmmm...blame it on copy & paste from archive via google. ;)
here are the corrected urls.
http://support.microsoft.com/kb/194975
http://support.microsoft.com/kb/258_038
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eo1h9AKUFHA.1796@.TK2MSFTNGP15.phx.gbl...
>I get this with your link: The system cannot find the file specified.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
>|||If you want to manipulate blob inside a sql stored proc, you would want to
use updatetext/writetext.
http://msdn.microsoft.com/library/e..._ua-uz_6i2c.asp
9" target="_blank">http://groups.google.co.uk/group/co...fbdf01
9
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eL9prBKUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Also there is nothing on how to handle it with a stored procedure.
> A
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
>

Error in stored procedure

I have a stored procedure to which I pass the following parameters
@.Date smalldatetime,
@.Amount decimal(15,3)
@.Exg_Rate decimal(5,3)
Inside this stored procedure I call another one passing to it those parameters like that
EXECUTE dbo.[Opening_Balance_AfterInsert] @.Date, @.Amount*@.Exg_Rate
I receive an error at the above line saying: Incorrect syntax near '*'
if I try to put the expression between rounded brackets I receive the error Incorrect syntax near '('
How can I pass that expression?create procedure myproc
@.Date smalldatetime,
@.Amount decimal(15,3)
@.Exg_Rate decimal(5,3)
as
declare @.combo float
set @.combo = @.Amount*@.Exg_Rate
execute do.opening_balance_afterInsert @.date, @.combo
Nicksql

Error in stored procedure

When I'm trying to execute my stored procedure I'm getting the following code Line 35: Incorrect syntax near'@.SQL'.

Here is my procedure. Could someone tell me what mistake I'm doing.

Alterprocedure [dbo].[USP_SearchUsersCustomers_New]

@.UserIDINT

,@.RepName VARCHAR(50)

,@.dlStatus VARCHAR(5)=''

as

Declare

@.Criteria VARCHAR(500)

,@.SQL VARCHAR(8000)

SELECT @.Criteria=''

SET NOCOUNTON

if(@.dlStatus<>'ALL'AND(LEN(@.dlStatus)>1))

BEGIN

if(@.dlStatus='ALA')

SET @.Criteria='AND dbo.tbl_Security_Users.IsActive=1'

else

SET @.Criteria='AND dbo.tbl_Security_Users.IsActive=0'

END

--If the user is an Admin, select from all users.

if(dbo.UDF_GetUsersRole(@.UserID)= 1)

BEGIN

@.SQL='SELECT U.UserID

--,U.RoleID

,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR

INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleID

Where UR.UserID = U.UserID), 'Unassigned') as 'RoleName'

,U.UserName

,U.Name

,U.Email

,U.IsActive

,U.Phone

FROM dbo.tbl_Security_Users U

--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID

WHERE U.NAME LIKE @.RepName

AND U.UserTypeID < 3'+ @.Criteria

END

In your dynamic sql string, you need to escape the single quote by using it twice, i.e.: 'RoleName' should be ''RoleName''.

Also, before you build this string. make sure you test out the actual query first.

|||

I tried it still I get the same error "Incorrect Syntax near @.SQL". The query works fine when I execute it alone.

Here is the code again.

if(dbo.UDF_GetUsersRole(@.UserID)= 1)

BEGIN

@.SQL='SELECT U.UserID

--,U.RoleID

,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR

INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleID

Where UR.UserID = U.UserID), ''Unassigned'') as ''RoleName''

,U.UserName

,U.Name

,U.Email

,U.IsActive

,U.Phone

FROM dbo.tbl_Security_Users U

--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID

WHERE U.NAME LIKE @.RepName

AND U.UserTypeID < 3'+ @.Criteria

END

|||

SET @.SQL = '-- code here'

|||

I did that. Now when I click compile and execute it doesn't show any error. But when I execute the stored procedure it shows an error "Must declare @.UserID". But it's already declared.

|||

You dont need to use dynamic SQL in your scenario. Try something like this with your regular SELECT statement:

ANDdbo.tbl_Security_Users.IsActive= (CASEWHEN@.dlStatus='ALL'THENdbo.tbl_Security_Users.IsActive

WHEN@.dlStatus='ALA'Then1ELSE0END )

|||

Thanks! It worked and I liked the simplicity of the code while achieving the desired task.

Error in SQL-Server 2005 with varchar 8000

Hello,

We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

We use the following .Net Code to execute the stored procedure this stored procedure:

aParams[2] = sSerDatos.GetParameter("@.DominiosMenu", DbType.String, 8000);

aParams[2].Direction = ParameterDirection.Output;

sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

When we invoque the sqlcommand we get this sqlexception:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@.DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.

If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

Any help would be appreciated.

Thanks,

Sam

Hello Sam,

You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

I hope you find this information helpful. Please let me know if I can be of any other help.

Thanks

Stoyko Kostov

|||

Hello,

I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

Thanks, Arek

|||

If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

System.Data.SqlClient.SqlConnection conn;

System.Data.SqlClient.SqlCommand cmd;

System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

p1.SqlDbType = SqlDbType.NVarChar;

p1.Size = -1;

p1.ParameterName = "name";

or

System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

Please let me know if you have any questions about this example.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error in SQL-Server 2005 with varchar 8000

Hello,

We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

We use the following .Net Code to execute the stored procedure this stored procedure:

aParams[2] = sSerDatos.GetParameter("@.DominiosMenu", DbType.String, 8000);

aParams[2].Direction = ParameterDirection.Output;

sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

When we invoque the sqlcommand we get this sqlexception:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@.DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.

If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

Any help would be appreciated.

Thanks,

Sam

Hello Sam,

You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

I hope you find this information helpful. Please let me know if I can be of any other help.

Thanks

Stoyko Kostov

|||

Hello,

I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

Thanks, Arek

|||

If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

System.Data.SqlClient.SqlConnection conn;

System.Data.SqlClient.SqlCommand cmd;

System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

p1.SqlDbType = SqlDbType.NVarChar;

p1.Size = -1;

p1.ParameterName = "name";

or

System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

Please let me know if you have any questions about this example.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error in sql stored procedure

I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks.

ALTERprocedure [dbo].[USP_Account_Search_Mod]

@.ClientCode VARCHAR(7)=''

,@.DebtorName VARCHAR(25)=''

,@.DebtorNumberINT= 0

,@.AccountNumber VARCHAR(30)=''

,@.ReferenceNumber VARCHAR(30)=''

,@.TierINT= 0

,@.Status VARCHAR(5)=''

,@.UserIDINT

,@.MonthDateTime=NULL

,@.FromDateDateTime=NULL

,@.ToDateDateTime=NULL

,@.OriginalMindecimal= 0

,@.OriginalMaxdecimal= 0

,@.CurrentMindecimal= 0

,@.CurrentMaxdecimal=0

,@.lstAmountSelect VARCHAR(3)

,@.IsActivebit= 1

AS

DECLARE

@.SQLTier1Select VARCHAR(2000)

,@.SQLTier2Select VARCHAR(2000)

,@.Criteria VARCHAR(2000)

,@.SQL VARCHAR(8000)

,@.CRI1 VARCHAR(100)

,@.CRI2 VARCHAR(100)

,@.CRI3 VARCHAR(100)

,@.CRI4 VARCHAR(100)

,@.CRI5 VARCHAR(100)

,@.CRI6 VARCHAR(200)

,@.CRI7 VARCHAR(500)

,@.CRI8 VARCHAR(500)

,@.CRI9 VARCHAR(500)

SELECT @.CRI1=''

,@.CRI2=''

,@.CRI3=''

,@.CRI4=''

,@.CRI5=''

,@.CRI6=''

,@.CRI7=''

,@.CRI8=''

,@.CRI9=''

,@.Criteria=''

SELECT @.DebtorName=REPLACE(@.DebtorName,'''','''''');

Print @.DebtorName

if(SELECT UserTypeIDFROM dbo.tbl_Security_UsersWhere UserID= @.UserID)= 3AND @.ClientCode=''

return(-1)

IFLEN(@.DebtorName)> 0

SET @.CRI1=' AND Name like '+'''%'+ @.DebtorName+'%'''

IF @.DebtorNumber> 0

SET @.CRI2=' AND Number = '+CAST(@.DebtorNumberAS VARCHAR(7))

IFLEN(@.AccountNumber)> 1

SET @.CRI3=' AND AccountNumber like '+'''%'+ @.AccountNumber+'%'''

IFLEN(@.ReferenceNumber)> 0

SET @.CRI4=' AND Account like '+'''%'+ @.ReferenceNumber+'%'''

IFLEN(@.ClientCode)> 1

SET @.CRI5=' AND Customer = '+''''+ @.ClientCode+''''

SET @.Status=RTRIM(@.Status)

IF((@.StatusNotIN('ALL','ALA','ALI'))AND(LEN(@.Status)>1))

BEGIN

IF(@.Status='PAID')

SET @.CRI6=''

IF(@.Status='CANC')

SET @.CRI6=' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'

END

--PRINt @.CRI6

IFLEN(CONVERT(CHAR(8), @.Month, 112))> 0

BEGIN

IF(LEN(CONVERT(CHAR(8), @.FromDate, 112))> 0ANDLEN(CONVERT(CHAR(8), @.ToDate, 112))> 0)

BEGIN

SET @.CRI7=' AND Received BETWEEN '+''''+CONVERT(CHAR(8), @.FromDate, 112)+''''+' AND '+''''+CONVERT(CHAR(8), @.ToDate, 112)+''''

END

ELSE

BEGINSET @.CRI7=' AND DATEPART(mm, Received) = DATEPART(mm, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''+') AND DATEPART(yy, Received) = DATEPART(yy, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''

END

END

IF @.lstAmountSelect='ALL'

SET @.CRI8=''

elseIF @.lstAmountSelect='DR'

BEGIN

SET @.CRI8=' AND OriginalBalance >= '+convert(Varchar,@.OriginalMin)+'AND OriginalBalance<='+convert(Varchar,@.OriginalMax)+' AND CurrentBalance >= '+convert(Varchar,@.CurrentMin)+'AND CurrentBalance<='+convert(Varchar,@.CurrentMax)

END

ELSEIF @.lstAmountSelect='OLC'

BEGIN

SET @.CRI8=' AND OriginalBalance < CurrentBalance '

END

ELSEIF @.lstAmountSelect='OGC'

BEGIN

SET @.CRI8=' AND OriginalBalance > CurrentBalance '

END

ELSEIF @.lstAmountSelect='OEC'

BEGIN

SET @.CRI8=' AND OriginalBalance = CurrentBalance '

END

SELECT @.Criteria= @.CRI1+ @.CRI2+ @.CRI3+ @.CRI4+ @.CRI5+ @.CRI6+ @.CRI7+ @.CRI8

--PRINT @.Criteria

--PRINT @.CRI7

if @.Status='ALL'OR @.Status='ALA'OR @.Status='ALI'--All Period

BEGIN

if(@.Status='ALL')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'

END

if(@.Status='ALA')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'

END

if(@.Status='ALI')--All Inactive

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'

END

END

ELSEIF @.Status='PAID'

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'

END

ELSE

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

END

SELECT @.SQL=CASE @.Tier

WHEN 0THEN @.SQLTier1Select+' UNION '+ @.SQLTier2Select+'ORDER BY NAME ASC'

WHEN 1THEN @.SQLTier1Select+'ORDER BY NAME ASC'

WHEN 2THEN @.SQLTier2Select+'ORDER BY NAME ASC '

END

PRINT @.SQL

--SELECT @.SQL

EXEC(@.SQL)

Could you help us help you better by opening your stored procedure up in a query analyzer (like SQL 2005 QA or SQL Express Managment Studio) and parsing the query to determine what line number and what the syntax error is?

That is a lot of code and would be difficult for us to go over line by line to determine what the issue is.

|||

I do not have SQL Query Analyzer. Here is the error I get when I saw in event viewer

Transaction (Process ID 64) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction., Error Source:.Net SqlClient Data Provider.

|||

there's a lock on one of the tables your trying to do an insert on or update on. Talk with your DBA about that error and let him/her know what the Process ID is to help track it down.

Out of curiosity, what environment are you writing your stored procedures in?

|||

I'm writing my stored procedure in SQL Server Management Studio Express

|||

In SQL Server Management Studio Express you can parse your query (stored procedure syntax) and it will either tell you the syntax error along with the line number or tell you that the command was successful, which means you can run (execute) it and thus create your stored procedure.

Error in SP using Dreamweaver

Hi,
I am calling a stored procedure from DreamWeaver, but I am getting an error,
perhaps someone can help (It has to do with the last field (text), if I use
nvarchar instead works fine.
SP:
CREATE procedure Addtemplatestocase
@.FirmId int ,@.CaseId int , @.SecLtr nvarchar(50), @.LtrName nvarchar(100),
@.LtrBody text
as
INSERT
INTO LetterMrgs (FirmId ,CaseId,SecLtr ,LtrName,LtrBody)
VALUES (@.FirmId ,@.CaseId, @.SecLtr , @.LtrName, @.LtrBody)
GO
Error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
Any help is appreciated.
AleksI use Dreamweaver all the time and use the integrated environment. Are you
using DW to try to create an SP? or calling an update sp? I would not
recomend this.
for this question:you seem to be attempting to insert a value that is larger
than the field size.
thanks
kes
"Aleks" wrote:

> Hi,
> I am calling a stored procedure from DreamWeaver, but I am getting an erro
r,
> perhaps someone can help (It has to do with the last field (text), if I us
e
> nvarchar instead works fine.
> SP:
> CREATE procedure Addtemplatestocase
> @.FirmId int ,@.CaseId int , @.SecLtr nvarchar(50), @.LtrName nvarchar(100),
> @.LtrBody text
> as
> INSERT
> INTO LetterMrgs (FirmId ,CaseId,SecLtr ,LtrName,LtrBody)
> VALUES (@.FirmId ,@.CaseId, @.SecLtr , @.LtrName, @.LtrBody)
> GO
>
> --
> Error:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
> Any help is appreciated.
> Aleks
>
>|||I know.
The stored procedure is in the Database (MS SQL 2000) I am using DW to call
the SP and pass on the paramters.
I am copying from a text field to a text field, so this should not happen. I
don't know what I am doing wrong.
Aleks
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>I use Dreamweaver all the time and use the integrated environment. Are you
> using DW to try to create an SP? or calling an update sp? I would not
> recomend this.
> for this question:you seem to be attempting to insert a value that is
> larger
> than the field size.
> thanks
> kes
> "Aleks" wrote:
>|||ok, let's try and break it down.
1. can you do the same operation out side of dream weaver?
"Aleks" wrote:

> I know.
> The stored procedure is in the Database (MS SQL 2000) I am using DW to cal
l
> the SP and pass on the paramters.
> I am copying from a text field to a text field, so this should not happen.
I
> don't know what I am doing wrong.
> Aleks
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>
>|||sorry this this posted back auto...
1. try it out of deamweaver
a. try it in wisql
b. create a cfm or asp.net quick template using the sqlname space
2. if they work it may have to do with the odbc driver. I've found that the
odbc namespace does not work as well as oledb or sqldb, but you don't have a
choice with DW. This is philosophy, but vs.net does work better with
asp.net.
I'm not a MS only person, i've used DW since it was HomeSite and still do
for all my CFM stuff and some asp.net where the code was not created in
vs.net, it works ok, but does not connect to sql as well.
thanks
kes
"Aleks" wrote:

> I know.
> The stored procedure is in the Database (MS SQL 2000) I am using DW to cal
l
> the SP and pass on the paramters.
> I am copying from a text field to a text field, so this should not happen.
I
> don't know what I am doing wrong.
> Aleks
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>
>

error in parameters when using stored procedure...

hi, all
I met a problem, I use OleDB to connect database, and using stored procedure with 3 parameters to getting data, including 2 datetime parameters named DateFrom and DateTo.

Everything is fine in my desktop, but in my customer's side, there is an error-"DateFrom is not a parameter for procedure XXXX", I don't know why is this happened, is it related to the date format thing? I'm not sure what the enviroment is in my customer's side, does anyone ever met this error?

Many thanks!!!

Might be a format issue. They might be entering the date as 11-14-06. It might be interpreting it as a string datatype.

Adamus

|||

Adamus Turner wrote:

Might be a format issue. They might be entering the date as 11-14-06. It might be interpreting it as a string datatype.

Adamus

but, i notice that in the report screenshot which my customer sent me, the DateFrom and DateTo parameter had been converted to the standard format "9/1/2006 12:00:00 AM" and "11/1/2006 12:00:00 AM", and because I set these two parameters to DateTime in report, if the user input the invalid string, it shouldn't be passed anyway.

thanks.

|||

Then the problem is in the SQL sp_

I'm curious, did you test the sp_ by using EXEC in query analyzer/management studio or through a front end form?

Please post the sp_ instantiation along with the parameter declarations.

Adamus

|||

Adamus Turner wrote:

Then the problem is in the SQL sp_

I'm curious, did you test the sp_ by using EXEC in query analyzer/management studio or through a front end form?

Please post the sp_ instantiation along with the parameter declarations.

Adamus

Yes, I've tried and it works. the stored procedure is,,,

CREATE PROCEDURE [dbo].[usp_XXXX]
(
@.DateFrom datetime,
@.DateTo datetime,
@.ParentItem varchar(50)
)
AS
......

thank you.

|||

I see. Well I'm trying to remember where I've experience this issue. I believe it was in ASP.net where I used ADO.net to set the command text to execute the sp_ but set the command type incorrectly (i.e. cmdquery not cmdsp)

This is probably the case if you did not design the front end form. The client designed the form to execute a non-parameterized query such as a View. So when they try to pass parameters, it says, "Your command type doesn't support parameterized execution. Please use the correct command type." ...which in your case would be a stored procedure not plain T-SQL.

You might want to consult the ASP.net forum or ADO.net for a resolution to your problem.

Adamus

|||

Thank you, Adamus!

I got the reason, it is the connection type's problem, when using OLE DB, everything is ok...

Error in MS Stored Procedure

I am current creating a Oracle membership provider in dotnet based on the MS membership provider. One of the stored procedures that need converting is shown below. The variable UserId (highlighted blue) is declare and never assigned to but is used within the update query also highlighted in blue. If this is an oversight then I presume that this part of the stored procedure is never actually executed. Can somebody put an eye over this code to confirm.

Thanks.

ALTER PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
@.ApplicationName nvarchar(256),
@.UserName nvarchar(256),
@.CurrentTimeUtc datetime,
@.UpdateLastActivity bit = 0
AS
BEGIN
DECLARE @.UserId uniqueidentifier

IF (@.UpdateLastActivity = 1)
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, @.CurrentTimeUtc, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@.UserName) = u.LoweredUserName AND u.UserId = m.UserId

IF (@.@.ROWCOUNT = 0) -- Username not found
RETURN -1

UPDATE dbo.aspnet_Users
SET LastActivityDate = @.CurrentTimeUtc
WHERE @.UserId = UserId
END
ELSE
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@.UserName) = u.LoweredUserName AND u.UserId = m.UserId

IF (@.@.ROWCOUNT = 0) -- Username not found
RETURN -1
END

RETURN 0
END

It is a bug in the code. The code is wrong and there are other problems with the logic. You should file a bug at http://connect.microsoft.com.

error in image field when using CASE statement

I've this Stored procedure on a SQLserver 2000 SP3:

SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra

this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..

--------
Microsoft SQL-DMO (ODBC SQLState: 42000)
--------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
--------
OK
--------

An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??So, are you saying that "sometimes" it works and sometimes it doesn't?

This should work "always":

SELECT *,CASE WHEN immagine IS NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra

But exclude the image field from the SELECT list, unless you really intend to use it.|||i must use it!!!

Error in executing stored procedure:Not a valid identifier

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

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

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

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

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

|||

First off, I'm not sure why you're constructing a dynamic select inside your procedure...the procedure should be the select statement, using any input parameters you defined.

But to solve the problem, you need to change

exec @.sqlstr

to

exec(@.sqlstr)

I'd rewrite the entire piece of code...

|||

This is a duplicate post.

Please see answer in your other posting.

|||

Use the following satement to execute the SP,

Code Snippet

Exec [GSU_Site_ReterieveActiveSitesOnSearch]' where GSUStatus.GSUStatusID=1and site.Sitename like''lakshmisite'' '

|||

Kusuma,

Instead passing the this value " where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' ", use:

' where GSUStatus.GSUStatusID=1 and site.Sitename like ''lakshmisite'''

Notice that I am using two apostrophes per each one inside the string.

As you can see, you are setting QUOTED_IDENTIFIER to on, when creating the sp, so anything enclosed by double quote will be interprete as an identifier (name of a column, table, etc.), so when you pass that value to the sp, it will look like

...

SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID +

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and there is not such identifier in your db.

you can set QUOTED_IDENTIFIER to OFF, but I prefer to leave it as ON and use the other method to escape apostrophes.

AMB

|||

If you call it from any UI, the single quote will be automatically taken care by the providers/ADO classes. (since it is a parameter)

But when you test the sp, you have to use either escape sequence or as AMB sujest use the QUOTED_IDENTIFER OFF config.

|||

Thanks Mani :-)

Now it is working.

There were two problems. One

1)setQUOTED_IDENTIFIERON should be OFF

2)exec@.sqlstr should be exec(@.sqlstr)

Kusuma

|||

Hai Dalej,

Sorry for posting two times.

I need dynamic query for a searching -sitenames,Businessunit etc......... ( searching based on columns in a table)

Now the problem is solved by giving exec(@.sqlstr) instead of exec @.sqlstr.

Thanks for your help :-)

Kusuma

Error in ExecuteNonQuery()

Hi,I am developing a small application where in I need to take a few data from the user and put it on the DB. I have stored procs for the same. I am getting an error when I execute the ExecuteNonQuery() command. the error is as follows:

System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Intranet_New.leaveForm.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\intranet_new\leaveform.aspx.cs:line 138

Snippet of code:

try

{

con =

new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SqlCon"]);

cmd =

new SqlCommand();

cmd.Connection = con;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "SP_InsertIntoLeave";

cmd.Parameters.Add("@.empid", SqlDbType.Char, 20);

cmd.Parameters["@.empid"].Value = txtEmplyId.Text;

cmd.Parameters.Add("@.empName", SqlDbType.NVarChar, 50);

cmd.Parameters["@.empName"].Value = txtName.Text;

cmd.Parameters.Add("@.LeaveFrom", SqlDbType.DateTime);

string str_LeaveFrom = ddlDay.SelectedValue + "/" +ddlMonth.SelectedValue + "/" + ddlYear.SelectedValue;

DateTime LF =

new DateTime();

LF = DateTime.Parse(str_LeaveFrom);

string LeaveFrom1 = (LF.ToShortDateString());

cmd.Parameters["@.LeaveFrom"].Value = LeaveFrom1;

cmd.Parameters.Add("@.LeaveTo", SqlDbType.DateTime);

string str_LeaveTo = ddltoDay.SelectedValue + "/" + ddltoMonth.SelectedValue + "/" + ddltoYear.SelectedValue;

DateTime LT =

new DateTime();

LT = DateTime.Parse(str_LeaveTo);

string LeaveTo1 = (LT.ToShortDateString());

cmd.Parameters["@.LeaveTo"].Value = LeaveTo1;

cmd.Parameters.Add("@.TotalDays", SqlDbType.BigInt);

cmd.Parameters["@.TotalDays"].Value = txtNoofDays.Text;

cmd.Parameters.Add("@.TypeOfLeave", SqlDbType.NVarChar, 50);

cmd.Parameters["@.TypeOfLeave"].Value = rbtnType.SelectedValue;

cmd.Parameters.Add("@.ReasonOfLeave", SqlDbType.NVarChar, 1000);

cmd.Parameters["@.ReasonOfLeave"].Value = txtReason;

con.Close();

con.Open();

cmd.ExecuteNonQuery();

con.Close();

Stored proc is as follows:

ALTER PROCEDUREdbo.SP_InsertIntoLeave

(

@.empidas char(20), @.empNameas nvarchar(50), @.totalLeaveas decimal(9) = 12, @.LeaveFromas datetime,

@.LeaveToas datetime, @.TotalDaysas bigint, @.TypeOfLeaveas nvarchar(50), @.ReasonOfLeaveas nvarchar(1000),

@.RemainigLeaveas decimal(9)

)

/*

(

@.parameter1 datatype = default value,

@.parameter2 datatype OUTPUT

)

*/

AS

/* SET NOCOUNT ON */

INSERT INTOLeave_Table

(

emp_id, emp_Name, Total_Leave, Leave_From, Leave_To, Total_no_of_Days, Type_of_Leave, Reason_of_Leave,

Leave_Remaining

)

VALUES

(

@.empid, @.empName, @.totalLeave, @.LeaveFrom, @.LeaveTo, @.TotalDays, @.TypeOfLeave, @.ReasonOfLeave,

@.RemainigLeave

)

RETURN

Thanks in Advance.

just a thought

could it be this line?

cmd.Parameters["@.TotalDays"].Value = txtNoofDays.Text;

you defined the Parameter @.TotalDays as a big int but you are supplying it a string. try parsing txtNoofDays.Text to a int.

int.Parse(txtNoofDays.Text.ToString());

hth,
mcm

Error in Creating Stored Procedure from VS 2005

When I create a stored procedure in VS 2005 using C# and deploy it to the server I can't execute it there and here is the error message:

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

A .NET Framework error occurred during execution of user defined routine or aggregate 'GetAll':

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 StoredProcedures.GetAll()

and the code for creating the stored procedure is:

SqlConnection connDB = new SqlConnection(@."Initial Catalog=MDB;Data Source=Server1;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = connDB;

cmd.CommandText = "SELECT * FROM Modifier";

connDB.Open();

SqlDataReader rdr = cmd.ExecuteReader();

SqlContext.Pipe.Send(rdr);

rdr.Close();

connDB.Close();

your help is appreciated...

What is the permission_set that you assigned for the assembly? This is the one in the CREATE ASSEMBLY. You need to set it to EXTERNAL_ACCESS due to use of SqlConnection that is accessing remote resource. Additionally, you will have to enable TRUST_WORTHY bit (use with care) or do the recommended key based login creation & assign external access assembly permission to it and use that user as owner of the assembly. If you download the new version of SQL Server 2005 Books Online it should contain updated topics that show how to do this. If you need some examples, please post back and I will try to locate a sample for you.|||

I am getting the same error when trying to debug my stored procedure. How do I get around int?

Here's my code:

Try

Dim conn As SqlConnection = New SqlConnection

conn.ConnectionString = "Data Source=XXX-XXXX\SQLSERVER2005;Initial Catalog=MotorFleetConversion;User ID=xxxx;password=xxxx"

conn.Open()

command = New SqlCommand(sqlAction)

'command.Parameters.AddWithValue("@.rating", rating)

command.Connection = conn

' Execute the command and send the results directly to the client

'SqlContext.Pipe.ExecuteAndSend(command)

Dim drUnitCode As SqlDataReader = command.ExecuteReader()

While drUnitCode.Read

If drUnitCode.Item("CompanyCode").ToString <> prevCompanyCode Then

agencySysNo = 0

If drUnitCode.Item("CompanyCode").ToString <> "" Then

agencySysNo = InsertAgency(drUnitCode.Item("CompanyCode").ToString, drUnitCode.Item("UC_DEPARTMENT_DESC").ToString, _

Convert.ToBoolean(drUnitCode.Item("NCAS")), drUnitCode.Item("UC_BILLING_CODE").ToString)

End If

End If

If agencySysNo > 0 Then

InsertDivision(agencySysNo, drUnitCode.Item("UC_DIVISION_DESC").ToString, drUnitCode.Item("UC_SHORT_DEPT_DIV").ToString, _

drUnitCode.Item("UC_CODE_NUMBER").ToString)

End If

prevCompanyCode = drUnitCode.Item("CompanyCode").ToString

End While

Catch ex As Exception

End Try

Thanks!

Error in Creating Stored Procedure from VS 2005

When I create a stored procedure in VS 2005 using C# and deploy it to the server I can't execute it there and here is the error message:

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

A .NET Framework error occurred during execution of user defined routine or aggregate 'GetAll':

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 StoredProcedures.GetAll()

and the code for creating the stored procedure is:

SqlConnection connDB = new SqlConnection(@."Initial Catalog=MDB;Data Source=Server1;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = connDB;

cmd.CommandText = "SELECT * FROM Modifier";

connDB.Open();

SqlDataReader rdr = cmd.ExecuteReader();

SqlContext.Pipe.Send(rdr);

rdr.Close();

connDB.Close();

your help is appreciated...

What is the permission_set that you assigned for the assembly? This is the one in the CREATE ASSEMBLY. You need to set it to EXTERNAL_ACCESS due to use of SqlConnection that is accessing remote resource. Additionally, you will have to enable TRUST_WORTHY bit (use with care) or do the recommended key based login creation & assign external access assembly permission to it and use that user as owner of the assembly. If you download the new version of SQL Server 2005 Books Online it should contain updated topics that show how to do this. If you need some examples, please post back and I will try to locate a sample for you.|||

I am getting the same error when trying to debug my stored procedure. How do I get around int?

Here's my code:

Try

Dim conn As SqlConnection = New SqlConnection

conn.ConnectionString = "Data Source=XXX-XXXX\SQLSERVER2005;Initial Catalog=MotorFleetConversion;User ID=xxxx;password=xxxx"

conn.Open()

command = New SqlCommand(sqlAction)

'command.Parameters.AddWithValue("@.rating", rating)

command.Connection = conn

' Execute the command and send the results directly to the client

'SqlContext.Pipe.ExecuteAndSend(command)

Dim drUnitCode As SqlDataReader = command.ExecuteReader()

While drUnitCode.Read

If drUnitCode.Item("CompanyCode").ToString <> prevCompanyCode Then

agencySysNo = 0

If drUnitCode.Item("CompanyCode").ToString <> ""Then

agencySysNo = InsertAgency(drUnitCode.Item("CompanyCode").ToString, drUnitCode.Item("UC_DEPARTMENT_DESC").ToString, _

Convert.ToBoolean(drUnitCode.Item("NCAS")), drUnitCode.Item("UC_BILLING_CODE").ToString)

EndIf

EndIf

If agencySysNo > 0 Then

InsertDivision(agencySysNo, drUnitCode.Item("UC_DIVISION_DESC").ToString, drUnitCode.Item("UC_SHORT_DEPT_DIV").ToString, _

drUnitCode.Item("UC_CODE_NUMBER").ToString)

EndIf

prevCompanyCode = drUnitCode.Item("CompanyCode").ToString

EndWhile

Catch ex As Exception

EndTry

Thanks!

Error in create sp

hi every one

when i want to create a stored procedure that contain character " with a ado component , i receive this error message :

'Parameter object is improperly defined. inconsistent or incomplete information was provided.'

but if i create this procedure from query analyzer , this sp creates successfuly.

whyyyyyyyyyyyyyyyy? :mad:My guess would be that the client side (probably VB) code doesn't properly escape the quotation mark, and since the Transact-SQL doesn't need to escape the quote it isn't a problem there. I'd suggest that you post the VB code you are using so that we can see if that is your problem.

-PatP|||It's the QUOTED_IDENTIFIER setting on connection object vs. your QA. On the client side set this setting to be the same as in QA (in Connection Options menu item in QA).