Help with code

I have this code in a DTS package which is:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

drop proc VerifyRequest
go
/*
* VerifyRequestTransfer - run a command that looks for @.filename in the output
*

*
* This proc looks for a file matching 'tbl_%' in the output of an ftp command.
* The output message reports success/failure of transfer.
* A return code of 1 indicates success
* Return code = 0 indicates failure.
*
* How it Works:
* ftp is executed using @.ftpcommandfile as input to the -s parameter.
* The output of ftp is written to a table
* The table is cleared of garbage records
* The count of records matching @.filename is checked
* if the count = 1 then there success!
*/
CREATE proc VerifyRequest
@.filename varchar(200),
@.ftpcommandfile varchar(1000)
as

declare @.rc int
declare @.rows int, @.errcode int, @.rows2 int
set @.rc = 0
set @.rows = -9998

set nocount on
-- build a table containing list of files in Request directory
if exists (select * from tempdb.dbo.sysobjects where name='RequestFiles' and type = 'U')
drop table tempdb.dbo.RequestFiles
create table tempdb.dbo.RequestFiles (
line_no int identity(1,1) Primary key clustered,
Filename varchar(200) NULL
)
declare @.cmd varchar(2000)

--Get list of remote files
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Insert into tempdb.dbo.RequestFiles (Filename)
Exec master.dbo.xp_cmdshell @.cmd
select @.rows = @.@.rowcount, @.errcode = @.@.error
if @.rows = 0 OR @.errcode != 0
begin
set @.rc = -1
goto done
end

-- remove non-files and already processed files ( there might have been old files on remotesystem )
Delete
From tempdb.dbo.RequestFiles
Where coalesce(Filename, '') not like '%tbl_%'
-- check count
select @.rows = (select count(*) from tempdb.dbo.RequestFiles
Where tempdb.dbo.RequestFiles.Filename like '%'+@.filename+'%' )

if @.rows = 1
set @.rc = 1
done:
return @.rc
go

Now the message I am getting is:

The task reported failure on execution. Procedure 'VerifyRequest' expects Parameter '@.filename', which was not supplied.

I don't know where to set this parameter.

I hope someone can help.

Thanks

LystraYou will need to supply both the @.filename and @.ftpcommandfile parameters when you call the procedure from your code:

VerifyRequest 'C:\Yourfile.nam', 'C:\YourCommandFile.nam'|||If that was THAT easy the error would have referenced @.ftpcommandfile parameter, not @.filename.|||THe vb scripts that is first started which is:

Function Main()
DTSGlobalVariables("PostDate") = month(now()) & "/" & day(now()) & "/" & year(now())
dim tmp
dim filename

tmp = right("0" & datepart("m", DTSGlobalVariables("PostDate")), 2)
tmp = tmp & right("0" & datepart("d", DTSGlobalVariables("PostDate")), 2)
tmp = tmp & datepart("yyyy", DTSGlobalVariables("PostDate"))
DTSGlobalVariables("Datestamp") = tmp
filename = DTSGlobalVariables("TransferFileNameRoot") & DTSGlobalVariables("Datestamp") & DTSGlobalVariables("FileExtension")
DTSGlobalVariables("TransferFilename") = DTSGlobalVariables("TransferFileDir") & "\" & DTSGlobalVariables("TransferFileNameRoot") & DTSGlobalVariables("Datestamp") & DTSGlobalVariables("FileExtension")

' use the output file name to generate an FTP command file
set oFSO = CreateObject("Scripting.FileSystemObject")
set oFile = oFSO.OpenTextFile( DTSGlobalVariables ("TransferFTPCommands").Value , 2, 1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "cd /fs11/infiles"
oFile.writeline "mput " & DTSGlobalVariables("TransferFilename").Value
oFile.writeline "quit"
oFile.Close
set oFile = nothing

' Generate an FTP command file to verify that transfer worked.
set oFile = oFSO.OpenTextFile( DTSGlobalVariables ("TransferFTPVerifyReq").Value , 2, 1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "cd /fs11/infiles" & vbCRLF & "ls -l " & vbCRLF & "quit"
oFile.Close
set oFile = nothing
set oFSO = nothing

' save the output filename into the transfer verification query
tmp = "Select count(*) from tempdb.dbo.RequestFiles " & vbCRLF & _
"Where tempdb.dbo.RequestFiles.Filename like '%"+ filename + "%'"
'Create a new query to look for files with the output filename
' find the task that counts the number of transferred files
set oTasks = DTSGlobalVariables.Parent.Tasks
for each task in oTasks
if task.Properties("Description") = "Evaluate File Count" then
' set the Query in DynamicProperties Task so that it checks for today's file
For Each oAssignment In task.CustomTask.Assignments
if instr( oAssignment.DestinationPropertyID , "'TransferredFileCount'" ) then
oAssignment.SourceQuerySQL = tmp
end if
next
end if
next
Main = DTSTaskExecResult_Success
End Function

Since I have created a ftp transfer file that lists the file names and should put the files in a temp table. I am having trouble with the ftp command to list the file in my file.

Thanking you in advance.

Lystra

help with a stored procedure

here is my sp

create proc [login]

(

@.email as varchar(200)

@.password as varchar(200)

)

as

begin

select customerid from customerlogin

where email = @.email

and password = @.password

End

print 'Welcome'

print (@.email)

End

This works fine, but if the email is wrong, what do I need to add to this to print that the email is wrong and not print 'welcome email'

Help, please.....

Hello,

I think what you want is:

create proc [login]

(

@.email as varchar(200)

@.password as varchar(200)

)

as

IF EXISTS (select customerid from customerlogin where email = @.email and password = @.password)

BEGIN

print 'Welcome'

print (@.email)

END

ELSE

BEGIN

print 'Bad login!'

print (@.email)

END

GO

As a side note, best practice is to NOT pass the users' password, but rather store a hash of the password and then when the user attempts to login, you simply create a hash of the entered password and compare it with the hashed value stored in the db.

Cheers

Rob

|||

create procedure sp_login

@.email as varchar(200)

@.password as varchar(200)

AS

Select CASE WHEN ((Select COUNT(*) from customerlogin where email = @.email and password = @.password) = 0)

THEN Print 'Invalid Login'

ELSE Print 'Welcome ' + @.email

END AS [EmailTest]

Adamus

Help with a list variable

I have made the following test stored proc that all it does right now
is accept a string variable that will be a list ie. :5,9,6,13. Right
now all the SP is doing is creating a temp table to store the values in
the list, then looping through the list and inserting the values into
the temp table, then selecting all the records from teh temp table.
It's WORKING however i must have done something wrong because it's not
inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
will get inserted, not the 13. if I pass in the list like this
"5,9,6,13," with an extra comma at the end, they all get inserted fine
but thats not how the strings will be coming in. Here's what I have so
far:

CREATE PROCEDURE sp_searchTaskTest

-- in params
@.strAssignedTovarchar (200) = NULL

AS

CREATE TABLE #tblAssignedTo (
strAssignedToIDvarchar(10)
)

-- initialize variables
DECLARE @.lengthOfString int
DECLARE @.startingPosition int
DECLARE @.parseString1 int
DECLARE @.strAssignedToID varchar(10)

SET @.startingPosition = 0
SELECT @.parseString1 = CHARINDEX (',', @.strAssignedTo,1)
WHILE ( @.parseString1 > 0 )
BEGIN
SELECT @.parseString1 = CHARINDEX (',',
@.strAssignedTo,@.startingPosition)
SET @.lengthOfString = @.parseString1 - @.startingPosition
IF @.lengthOfString > 0
BEGIN
SET @.strAssignedToID = SUBSTRING(@.strAssignedTo, @.startingPosition,
@.lengthOfString)
SET @.startingPosition = @.parseString1 + 1
END
ELSE
BEGIN
SET @.parseString1 = 0
SET @.strAssignedToID = ''
END
IF @.strAssignedToID != ''
BEGIN
INSERT #tblAssignedTo(strAssignedToID)
VALUES(@.strAssignedToID)
END
END

SELECT * FROM #tblAssignedTo
GOhi
looks like u need to make some changes in the code.

else what u can do is,

@.strAssignedTo = @.strAssignedTo + ','

in the first line of the SP. This is not correct but a quick fix

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||On 3 Aug 2005 10:38:43 -0700, Erich93063 wrote:

>I have made the following test stored proc that all it does right now
>is accept a string variable that will be a list ie. :5,9,6,13. Right
>now all the SP is doing is creating a temp table to store the values in
>the list, then looping through the list and inserting the values into
>the temp table, then selecting all the records from teh temp table.
>It's WORKING however i must have done something wrong because it's not
>inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
>will get inserted, not the 13. if I pass in the list like this
>"5,9,6,13," with an extra comma at the end, they all get inserted fine
>but thats not how the strings will be coming in. Here's what I have so
>far:

Hi Erich,

http://www.sommarskog.se/arrays-in-sql.html

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Help understanding Stored proc

hi guys! Can anybody please explain what does lines 4,6,8,13 and 26 does? Thanks in advance!

1 USE [PawnShoppeDB]
2 GO
3 /****** Object: StoredProcedure [dbo].[sp_Customer_AddCustomer] Script Date: 06/14/2007 16:50:07 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER Procedure [dbo].[sp_Customer_AddCustomer]
9 @.Customer_FirstName varchar(50),
10 @.Customer_LastName varchar(50),
11 @.Customer_MiddleInitial varchar(5),
12 @.Customer_Address varchar(100),
13 @.Identity int output
14 AS
15 Begin
16 Declare @.DateCreated DateTime
17 Set @.DateCreated = getDate()
18
19 Insert into RCPS_Customer
20 Values (@.Customer_FirstName,
21 @.Customer_MiddleInitial,
22 @.Customer_LastName,
23 @.Customer_Address,
24 '0',@.DateCreated)
25
26 Set @.identity = Scope_identity()
27 EndLol.

Check books online (SQL Server help) for:
ANSI_NULLS
QUOTED_IDENTIFIER
Scope_identity()
output parameters

Alter proc replaces an existing proc with the same name with the code you posted. That'll be in the help files too.

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!

help Stored proc

Hi,
Could any one please help me in creating sp.
It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100.
Thanks in advance.
-ssTry this:

create procedure p_odd_even(@.i int)
as
select number -1 + @.i from master..spt_values where type = 'P' and number % 2 = 1 and number <= 100|||Very clever trick - what is the purpose of this table in SQL|||too clever

you would need DISTINCT in there, vaxman, and also restrict number to between 0 and 100, not just less than or equal to 100

the use of master..spt_values is a hack

better to declare an integers table, because it will come in handy in so many other queries|||Not sure what spt_values is for. I have seen it used for things like this. In examining the table, it looks like type 'P' does return distinct integers, but as Rudy says, much better to have your own table. I create one like this:

select top 8000 id = identity(int,1,1) into Numbers from sysobjects s1, sysobjects s2, sysobjects s3

(8000 because I usually use it for parsing varchar strings but change for your needs)

Or you can just generate your 50 numbers on the fly. For 50 numbers this is probably more efficient (no I/O and 50 numbers is small enough not to generate a work table) but for larger counts a real table is better because statistics will be kept for it and indexes used.

create procedure p_odd_even(@.i int)
as

select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||You could always use:CREATE PROCEDURE pSSkris
@.arg INT = 1
AS

SELECT n
FROM (SELECT 1 + 10 * tens + ones AS n
FROM (SELECT 0 AS ones UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN (SELECT 0 AS tens UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS b) AS c
WHERE n % 2 = 2 - @.arg
ORDER BY n

RETURN
GO-PatP|||uh Pat, didn't I say that? (but you only need 50 numbers...)|||uh Pat, didn't I say that? (but you only need 50 numbers...)Sort of... You relied on another table outside of the problem definition, but my suggestion was self contained. I suspect that mine will be easier to explain too, but that's a relative kind of thing.

-PatP|||I think you missed this part:

create procedure p_odd_even(@.i int)
as
select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||Sorry, I was only looking at your first posting in my previous comment.

-PatP|||As an interesting wrinkle, you could also use:SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 0 AS b UNION SELECT 1) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n-PatP|||As an interesting wrinkle, you could also use:CREATE PROCEDURE pSSkris2
@.arg INT = 1
AS

SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 2 - @.arg AS b) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n

RETURN-PatP|||I wonder if you'll get an "A"...|||Who wants to explain it? Without an explanation, I'd expect that the code is worthless.

-PatP|||if it's for a school assignment, i would stringly suggest to sskris to submit the solution in post #2

that'll get an A+|||I would expect that any of the solutions that we've offered will get a trip to the Dean's office and an opportunity to use this experience in their upcomming ethics class (whether it was part of the curriculum before now or not). These solutions may help them think about the answer they want to give, but I can't imagine any of them being "safe" to turn in as they are.

-PatP|||Thanks to all of you..
I really appreciate that.
Kris

Help retrieving values from 2 similar stored procs

I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------
Row# 1 from current stored proc
Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------
100000 33455 200 300 345555 4455555 5666666 Arizona 56000
103400 22455 900 700 777555 3333555 5444666 Illinois 660000

The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
http://www.webfound.net/CurrentMonthCollections_AZ.txt
http://www.webfound.net/CurrentMonthCollections_IL.txt
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount
) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows

"I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows"

Union is what you want:

select 1
union
select 2

returns

1
2

It seems to me that you could parameterize the procedures and get back the two rows that you want, or at least have one procedure and pass in the state you are interested in...that is a lot of proc to deal with though...

|||what about union all ?|||

The difference between the 2 is that union removes duplicates and union all does not

union all is also faster because of that

select 1
union
select 2
union
select 2

select 1
union all
select 2
union all
select 2

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Sorry, I should have been clearer:

UNION ALL doesn't eliminate duplicates, UNION does.

UNION ALL is probably the more correct way to go in this case, but it won't really make a difference with such a small set.

|||in this case, there are no dups so it doesn't matter I guess then.|||so I thought UNION puts it all in one row? I must be wrong...