Showing posts with label passing. Show all posts
Showing posts with label passing. Show all posts

Friday, March 9, 2012

HELP w/ Stored Procedure

Can someone explain what I'm doing wrong?

I am passing values to the SPROC and am trying to do the following:

1.) I check to see if the record already exists in the MEMBERS table
NO -> I want to insert a new record and then set @.MEMBER_ID = SCOPE_IDENTITY
YES-> I want to skip the insert and set @.MEMBER_ID to the existing MEMBER_ID

2.) I check to see if the record already exists in the SW_REQUESTS table
NO -> I want to insert a new record (Insert @.MEMBER_ID)
YES -> I want to skip the insert and close out the SPROC

Here is my code that doesn't work. I get an error when setting @.MEMBER_ID = MEMBER_ID:


CREATE PROCEDURE b4b_sw_request

@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0'

AS
SET NOCOUNT ON
IF EXISTS
(SELECT MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SET @.MEMBER_ID = MEMBER_ID
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
VALUES
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
SELECT @.MEMBER_ID=SCOPE_IDENTITY()
END

IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.MEMBER_ID)
BEGIN
Print ''
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.MEMBER_ID, @.Send_Updates)
END
GO

You need to declare @.Member_ID as an OUTPUT parameter, if you expect to get it back:


CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0' OUTPUT

MEMBERS does have a IDENTITY column, correct?

This section of code:


IF EXISTS
(SELECT MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SET @.MEMBER_ID = MEMBER_ID
END

should be like this:


IF EXISTS
(SELECT * from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SELECT @.Member_ID=MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip
SET @.MEMBER_ID = MEMBER_ID
END

You need to actually select the @.Member_ID value somewhere. Alternately, you could do the select where @.Member_ID=Member_ID outside the IF and then check IsNull(@.Member_ID,0)<>0|||I do have a IDENTITY column in the table MEMBERS. It is MEMBER_ID. When I made the changes you suggested, I still get an error message "Invalid Column Name MEMBER_ID"

Here is the code:


CREATE PROCEDURE b4b_sw_request

@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0' OUTPUT

AS
SET NOCOUNT ON
IF EXISTS

(SELECT * from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)

BEGIN

SELECT @.Member_ID=MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip

SET @.MEMBER_ID = MEMBER_ID

END

ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
VALUES
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
SELECT @.MEMBER_ID=SCOPE_IDENTITY()
END

IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.MEMBER_ID)
BEGIN
Print ''
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.MEMBER_ID, @.Send_Updates)
END
GO

|||Sorry. Loose this line:


SET @.MEMBER_ID = MEMBER_ID

If you still get an error, please specify exactly what line gived the error.|||Thanks Doug. I had just removed it and tested it when I got your message. Works great!

Sunday, February 26, 2012

Help talking to stored proc

Can someone please lend a hand.
I am a total noob at this .NET/ASP stuff. I simply need help passing and returning values to a stored procedure.
Here’s my sp.

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here @.sessionUservarchar(25)OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

So when the user logs into to the default page (windows authentication) I capture their domain\login in a session variable (sessionUser). On the next page, when the page loads I need to pass this to my stored proc and get the idUser in return.
Here is what I have for the page load so far.

try{ SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output; cmdReturnID.Connection.Open(); cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = cmdReturnID.Parameters["idUser"].Value;}catch (Exception ex){ lblStatus.Text = ex.Message;}

Of course this fails cause I don’t know what I’m doing. My error label shows the following: "An SqlParameter with ParameterName 'idUser' is not contained by this SqlParameterCollection."

Can someone point me to what I’m doing wrong?

Hmm.. You mixed up every thing here..

First you need to a pass an input parameter and expect an out put paremeter. So, your stored procedure should have two paramets defined in it.. You have only one

And also, in your c# code, you need to assign the value of session variable to the input parameter, which you have not.

Anyway, try this..

ALTER PROCEDURE [dbo].[returnIdUser]
-- Add the parameters for the stored procedure here

@.sessionUservarchar(25),
 @.idOUT varchar(25) out

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
set idOUT = (SELECT idUserFROM tblUsersWHERE domainUser = @.sessionUser)

return @.idOUT

END

and your code to

cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Input;
command.Parameters["@.sessionUser"].Value =  Session["UserName"]
 cmdReturnID.Parameters.Add("@.idOUT", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output;
SqlDataReader reader = cmdReturnID.ExecuteReader();
 reader.close();
 Session["sessionUserID"] = cmdReturnID.Parameters["idOUT"].Value;
 
Even though, the code looks real, its not... I typed by hand, so beware of small errors... 
|||

Hi , in your example I guess you want to pass a session user into the stored procedure and return idUser. In this case your stored procedure should be :

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here (@.sessionUservarchar(25))ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

And here is the modified code:

try { SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25); cmdReturnID.Connection.Open(); SqlDataReader sqldr = cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = sqldr.GetString(0); }catch (Exception ex) { lblStatus.Text = ex.Message; }
Here I use sessionUser as an input value and read idUser with SqlDataReader.Hope this helps.|||it took some alterations, but finally got it to work. Thanks to both of you!

Friday, February 24, 2012

help setting up and using SQLXML web service ?

Hi,
I have an app that connects to a remote SQL database using 1433. It
connecting using a locked down user then runs a stored procedure passing it
XML.
Roger Walter pointed me in the direction of SOAP, SQLXML web service.
I installed:
SoapToolkit 2.0
SoapToolkit 3.0
SQLXML 3.0 sp2
Now this is done i don't see any amendments to IIS (restarted) . What should
i see ?
How do i talk to the webservice ?
What should be install in services ?
Was hoping i can just pass user/pass/xml to webservice to run stored
procedures in same way i am at the moment just with slighty connection
changes.
Please help.
ScottI found this:
1.. Download SqlXml 3.0 (this test was done with Sp2 Beta 1).
2.. Run the installation program. You will need to also install the SOAP
toolkit if you want to use the SqlXml SOAP features.
3.. Open the IIS Virtual Directory Management tool under Start ->
Programs -> SqlXml 3.0 -> Configure IIS.
4.. Create a new virtual directory. Enter the information on the General
tab and the Security tab. When you go to the Data Source tab you should be
able to browse the databases on the server. If you can't then you need to
check your security settings.
5.. Turn on Allow sql= queries so that you can test your setup. Make sure
you turn this off when you are done testing.
I am finding however that i cant browse to my index page in the new virual
directory. This index page is basic aspx for login, no sql connection
required.

help setting up and using SQLXML web service ?

Hi,
I have an app that connects to a remote SQL database using 1433. It
connecting using a locked down user then runs a stored procedure passing it
XML.
Roger Walter pointed me in the direction of SOAP, SQLXML web service.
I installed:
SoapToolkit 2.0
SoapToolkit 3.0
SQLXML 3.0 sp2
Now this is done i don't see any amendments to IIS (restarted) . What should
i see ?
How do i talk to the webservice ?
What should be install in services ?
Was hoping i can just pass user/pass/xml to webservice to run stored
procedures in same way i am at the moment just with slighty connection
changes.
Please help.
ScottI found this:
1.. Download SqlXml 3.0 (this test was done with Sp2 Beta 1).
2.. Run the installation program. You will need to also install the SOAP
toolkit if you want to use the SqlXml SOAP features.
3.. Open the IIS Virtual Directory Management tool under Start ->
Programs -> SqlXml 3.0 -> Configure IIS.
4.. Create a new virtual directory. Enter the information on the General
tab and the Security tab. When you go to the Data Source tab you should be
able to browse the databases on the server. If you can't then you need to
check your security settings.
5.. Turn on Allow sql= queries so that you can test your setup. Make sure
you turn this off when you are done testing.
I am finding however that i cant browse to my index page in the new virual
directory. This index page is basic aspx for login, no sql connection
required.

Sunday, February 19, 2012

Help regarding passing parameters in SQL and Lookup

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

2) Before loading my target I need to define a Lookup . My requirement is if say consumer key matches in fact table then update it else insert.

2 kinds of lookup are available in SSIS dataflow tools. Simple Lookup for exact matching and Fuzzy Lookup for matching based on probability.

Neither of it supports my requirement? Can i put a select and insert query directly in Lookup or will need to call it from a file as a stored procedure?

Please suggest a solution for this too.

Thanks in advance.

Regards,

Aman Anand

aman anand wrote:

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

It'd be alot easier to work out what's going on if you told us:

The full error message|||

Jamie,

Thanks for your reply. My query is mentioned below:

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, ?, GETDATE(), GETDATE(), ?, ?, 0, 0, GETDATE(), GETDATE(), 'LR')

Audit table is being maintained to keep track of ETL parameters.

MASTER_SEQ_ID

SOURCE_RECORD_COUNT and

LOAD_RECORD_COUNT

are the 3 variables defined as int32 and the scope is at package level.

They are mapped to 3 parameters which are also defined with datatype as int.

error is something like it says unable to parse the parameters in the query!

will look at the links mentioned in your reply.

thanks again.

Regards,

Aman

|||

aman anand wrote:

error is something like it says unable to parse the parameters in the query!

I meant to copy and paste the error message!

But regardless, you should be able to achieve this using an expression. The links I provided earlier will help.

-Jamie

|||

Jamie,

Sorry for not copy pasting the error before here goes the entire stuff.

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, @.P_MASTER_SEQ_ID, GETDATE(), GETDATE(), @.P_SOURCE_RECORD_COUNT, @.P_LOAD_RECORD_COUNT, 0, 0, GETDATE(), GETDATE(), 'LR')

Error:

TITLE: SQL Task

The query failed to parse. Must declare the scalar variable "@.P_MASTER_SEQ_ID".


Variables have been defined as int64 and while mapping them to parameters datatype has been given as Long.

Regards,

Aman

|||

Again, try using an expression.

-Jamie

|||

Sometimes it works even though the parser says opposite

Another solution is to build the complete query in a varaible

Turn Evaluate as an expression to True and build it like

"SELECT Dato, Kurs from [" + @.[User::Kurstype] + "$] WHERE (NOT (Kurs IS NULL))"

|||

Hi,

Thanks for your invaluable replies jamie and cgpl.

I tried another workaround for my problem. Used ADO.NET connection manager for the query which was posing problems and passed the parameters as @.P_......,

Now this issue has been sorted.

Thanks again.

Regards,

Aman