Passing of parameter

Need help for the passing of parameter in CR9.

How to pass the "record_id" from main report to the sql command of subreport. To clarify, i add a sql command in database expert in fmy subreport. This is my query....

For the main report..

Select record_id, distributor_name... from distributor

In the sql command of my subreport....

Select record_id, distributor_name, customer_id, sum(sales) as amount where record_id =2 group by record_id, distributor_name, customer_id
order by amount desc limit 20.

All i want to do is how to pass the record_id from the main report into the sql command of my subreport to look like this....

Select record_id, distributor_name, customer_id, sum(sales) as amount where record_id =("record_id from main_report") group by record_id, distributor_name, customer_id
order by amount desc limit 20.

My goal is how to view the top 20 accounts per distributor

Thanx a lot!!!1. Make main report
2. make sub report with same parameters.
3. In Design mode of main report, edit->subreport links.
Select main report parameter and link it to subreport parameter.

Passing error messages from stored procedure to osql command

I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:

IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN

If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.

Questions:

How can that be accomplished?

How to restart the stored procedure ones the error has been corrected?

Thank you for your help.Use "/b" with your OSQL, and use RAISERROR in your error trapper. In the batch file check "ERRORLEVEL 1":

osql ..... /b
if errorlevel 1 goto blah-blah|||Do you have a sample that I can look at? Thanks|||Watch out for word wrapping.
This one runs DBCC INDEXDEFRAG on specified server + database.

@.echo off
set server=%1
set db=%2
set uid=-U%3
set pwd=-P%4
if "%1"=="" goto ServerError
if "%2"=="" set db=master
if "%3"=="" set uid=-E
if "%4"=="" set pwd=
echo Checking for existence of a view on server %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -l 1 -Q"if object_id('dbo.vw_DBCC_INDEX_DEFRAG') is not null drop view dbo.vw_DBCC_INDEX_DEFRAG" -b
if errorlevel 1 goto LoginFailure
echo Creating a view on server %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -i"Create_vw_DBCC_INDEX_DEFRAG.SQL" -b
if errorlevel 1 goto CreateViewError
if "%uid%"=="-E" set uid=-T
echo Generating the final script for server %server% database %db%...
bcp %db%.dbo.vw_DBCC_INDEX_DEFRAG out %server%_%db%_DBCC_INDEX_DEFRAG.SQL -S %server% %uid% %pwd% -c
if "%uid%"=="-T" set uid=-E
echo Processing INDEXDEFRAG script on %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -i %server%_%db%_DBCC_INDEX_DEFRAG.SQL -h-1 -n -w 256 -o %server%_%db%_DBCC_INDEX_DEFRAG.LOG -b
if errorlevel 1 goto ScriptProcessingError
echo Check %server%_%db%_DBCC_INDEX_DEFRAG.LOG for any errors!
goto end
:ServerError
echo No server and/or database specified!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:LoginFailure
echo Failed to login to %server%!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:CreateViewError
echo Failed to create vw_DBCC_INDEX_DEFRAG!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:ScriptProcessingError
echo Failed to process the script: %server%_%db%_DBCC_INDEX_DEFRAG.SQL
echo Execution returned Error Code %ERRORLEVEL%
goto end
:end
@.echo on

Passing Command line arguments from Visual Studio

Hi all,

Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)

Any help would be greatly appreciated :-)

Steven

No. Since you are in a IDE/Debug environment with Visual Studio, I think it is a minor limitation that you cannot do this, you can just change and set anything your require in the IDE.

If you have external configuration information you wish to set all the time then /SET is probably not the best solution. Using the built in Configurations support in SSIS would be a better choice and this does work in VS. See the SSIS menu.

|||

I have the exact same issue. I just need a single parameter that needs to change every time I call my package, so it doesn't really warrant external configuration. The following article implies that CmdLineArguments are only taken into account whenever you use dtexec externally to execute the package and then attach to it to debug. See the last section ("Testing and Debugging your code") for details.

http://msdn2.microsoft.com/en-us/library/ms403356.aspx

Passing Command line arguments from Visual Studio

Hi all,

Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)

Any help would be greatly appreciated :-)

Steven

No. Since you are in a IDE/Debug environment with Visual Studio, I think it is a minor limitation that you cannot do this, you can just change and set anything your require in the IDE.

If you have external configuration information you wish to set all the time then /SET is probably not the best solution. Using the built in Configurations support in SSIS would be a better choice and this does work in VS. See the SSIS menu.

|||

I have the exact same issue. I just need a single parameter that needs to change every time I call my package, so it doesn't really warrant external configuration. The following article implies that CmdLineArguments are only taken into account whenever you use dtexec externally to execute the package and then attach to it to debug. See the last section ("Testing and Debugging your code") for details.

http://msdn2.microsoft.com/en-us/library/ms403356.aspx

sql

Passing a variable to the UPDATE Command

Hello everybody,

I have a problem in passing a variable into the Update command. This is my stored procedure.

CREATE PROCEDURE UpdateTable @.tsID INT, @.UpdateDesc varchar(1000)
AS

DECLARE @.TableNumber INT
DECLARE @.TableName NVARCHAR(100)
DECLARE @.SQL VARCHAR(100)

/* I have to get the name of the table to be updated by first getting the TableId
( an INT field) from a table TS_SUBTASKS and with that the table name from another table TS_TABLES */

SELECT @.TableNumber = TS_SUBTABLEID FROM TS_SUBTASKS
WHERE TS_SUBITEMID = @.tsID

SELECT @.TableName = TS_DBNAME FROM TS_TABLES
WHERE TS_ID = @.TableNumber

/* Which the Table name in the variable @.TableName i have to set one of its
fields, TS_DESCRIPTION to the value passed into the procedure
(@.UpdateDesc) */

SET @.SQL = 'update ' + @.TableName+ 'SET TS_DESCRIPTION = ' + @.UpdateDesc + ' WHERE TS_ID = ' + @.tsID

EXEC(@.SQL)

GO

--But i get this error

Syntax error converting the varchar value 'update USR_HUMAN_RESOURCESSET TS_DESCRIPTION = newdescription WHERE TS_ID = ' to a column of data type int.

It would be very helpful if somebody could throw light on this.
Thanks,
Krishna Murthy.

You should really rethink your approach of using dynamic SQL and this type of design. It requires lot of work in terms of giving permissions to users directly to update the table and coding the dynamic SQL. It is best to use static SQL wherever possible since it is more secure and easier to manage. The errors you are seeing are a result of incorrecly formed UPDATE statement. I believe it is missing some quotes. You should also consider using sp_executesql since that is easier to parameterize like below and you can avoid some of the SQL injection problems with using EXEC incorrectly:

DECLARE @.SQL nvarchar(1000)
SET @.SQL = N'update ' + QUOTENAME(@.TableName) +
N'SET TS_DESCRIPTION = @.Descr WHERE TS_ID = @.ID'
exec sp_executesql @.SQL, N'@.Descr varchar(1000), @.ID int', @.Descr = @.UpdateDesc, @.ID = @.tsID
|||Thank you Mr. Jayachandran, your correction has helped me a great deal. And coming to your suggestion of not taking this approach, I could not find any other way of doing the same. I shall again be very thankful to you if you can suggest a better way of doing this.

Thanks,
Krishna Murthy.|||I meant that you should avoid writing code that relies on dynamic schema elements. They are difficult to manage and code. In your case, you may want to consider generating some of the SP code during the creation at the time of definition of the row that represents a table. This approach also depends on your application and requirements.

Passing a selected row column value to the stored procedure

I have a simple Gridview control that has a delete command link on it.

If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...

The grid
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"
ReadOnly="True" SortExpression="member_id" />
<asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />
<asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />
<asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />
<asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"
SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"
DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure"
OldValuesParameterFormatString="original_{0}"
>

<DeleteParameters>

<asp:Parameter Name="member_id" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource
the SP

CREATE PROCEDURE renMemberDelete
@.member_id as int
As UPDATE [renMembers]
SET member_status=1
WHERE [member_id] = @.member_id
GO

Try:GridView2.DataKeyNames="member_id"

or

<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2" DataKeyNames="member_id">

Passing a parameter into a LIKE command in a stored procedure...

Hi All,
I am having quite a bit of trouble getting a certain stored procedure to
work properly. In my web page I have a list of checkboxes that post to the
url. When I retrieve the value for the "beds" checkbox list from the url it
looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
Proc and use a LIKE command to find any property where (beds LIKE
'[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
parameter gets passed into the stored procedure from the web page, it gives
no errors and does not retrieve any properties. Please take a look at my
stored procedure below and tell me where I may be going wrong. I've
simplified it so that it is less convoluded
CREATE PROCEDURE [sp_SearchResults_v2]
(@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
nvarchar(2), @.a3 nvarchar(2),
@.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
nvarchar(2), @.a9 nvarchar(2),
@.a10 nvarchar(2), @.Kids int)
AS
--declare area variables--
DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
@.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
-- check for null entries and convert to a select all entry Area --
IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null) AND
(@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND (@.a9
is null) AND (@.a10 is null))
BEGIN
set @.area1 = N'%'
set @.area2 = N''
set @.area3 = N''
set @.area4 = N''
set @.area5 = N''
set @.area6 = N''
set @.area7 = N''
set @.area8 = N''
set @.area9 = N''
set @.area10 = N''
END
ELSE
BEGIN
set @.area1 = @.a1
set @.area2 = @.a2
set @.area3 = @.a3
set @.area4 = @.a4
set @.area5 = @.a5
set @.area6 = @.a6
set @.area7 = @.a7
set @.area8 = @.a8
set @.area9 = @.a9
set @.area10 = @.a10
END
SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag, Archive_Flag,
Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
Property_Address, Property_Country,
Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
Dwelling_Code, Pet_Type_code,
Suite_Type_Code, Date_avalible, Property_Rental_Period,
Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
Property_phone2,
Property_ext, First_name, Last_name, Email_address,
CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
Property_houseno, Property_stdesignation,
Property_stname, cross_stno, cross_stname, cross_stdesignation,
CorrectedPhone1, CorrectedPhone2,
Property_ID
FROM dbo.Propeties
WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
AND
(Property_Bedrooms LIKE @.beds)
AND
(City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code =
@.area3 OR City_Area_Code = @.area4
OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code =
@.area7 OR
City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code =
@.area10)
AND
(Kids_results = 1)
AND
(Prop_Del_Flag <> 1)
ORDER BY Rent_Amount
GONow I don't have table descripts etc. so I made a very simple proc.
create proc dbo.NEW_Procedure @.input as varchar(250)
AS
select
ArtistName
,SongName
,Genre
,FileName
,Duration
,FirstLetter
,AlbumName
from RadioFreeLlloyd.dbo.SongInfo
where ArtistName like @.input
When I input any value with either wildcards or not it works. Perhaps you
could create a test table with the same columns as you have variables and
then insert a row when a call is made to the proc. It would seem that that
the values of the parameters are perhaps not what you think.
"Fabio Papa" <fapapa@.hotmail.com> wrote in message
news:8YfPb.2394$2j7.752@.clgrps13...

quote:

---

> Hi All,
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to

---

the

quote:

---

> url. When I retrieve the value for the "beds" checkbox list from the url

---

it

quote:

---

> looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
> Proc and use a LIKE command to find any property where (beds LIKE
> '[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
> LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
> parameter gets passed into the stored procedure from the web page, it

---

gives

quote:

---

> no errors and does not retrieve any properties. Please take a look at my
> stored procedure below and tell me where I may be going wrong. I've
> simplified it so that it is less convoluded
> CREATE PROCEDURE [sp_SearchResults_v2]
> (@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
> nvarchar(2), @.a3 nvarchar(2),
> @.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
> nvarchar(2), @.a9 nvarchar(2),
> @.a10 nvarchar(2), @.Kids int)
> AS
> --declare area variables--
> DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
> nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
> @.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
> -- check for null entries and convert to a select all entry Area --
> IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null)

---

AND

quote:

---

> (@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND

---

(@.a9

quote:

---

> is null) AND (@.a10 is null))
> BEGIN
> set @.area1 = N'%'
> set @.area2 = N''
> set @.area3 = N''
> set @.area4 = N''
> set @.area5 = N''
> set @.area6 = N''
> set @.area7 = N''
> set @.area8 = N''
> set @.area9 = N''
> set @.area10 = N''
> END
> ELSE
> BEGIN
> set @.area1 = @.a1
> set @.area2 = @.a2
> set @.area3 = @.a3
> set @.area4 = @.a4
> set @.area5 = @.a5
> set @.area6 = @.a6
> set @.area7 = @.a7
> set @.area8 = @.a8
> set @.area9 = @.a9
> set @.area10 = @.a10
> END
> SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag,

---

Archive_Flag,

quote:

---

> Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
> Property_Address, Property_Country,
> Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
> Dwelling_Code, Pet_Type_code,
> Suite_Type_Code, Date_avalible,

---

Property_Rental_Period,

quote:

---

kred">
> Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
> Property_phone2,
> Property_ext, First_name, Last_name, Email_address,
> CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
> Property_houseno, Property_stdesignation,
> Property_stname, cross_stno, cross_stname, cross_stdesignation,
> CorrectedPhone1, CorrectedPhone2,
> Property_ID
> FROM dbo.Propeties
> WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
> AND
> (Property_Bedrooms LIKE @.beds)
> AND
> (City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code

---

=

quote:

---

> @.area3 OR City_Area_Code = @.area4
> OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code =
> @.area7 OR
> City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code =
> @.area10)
> AND
> (Kids_results = 1)
> AND
> (Prop_Del_Flag <> 1)
> ORDER BY Rent_Amount
> GO
>

---

|||Fabio Papa (fapapa@.hotmail.com) writes:

quote:

---

> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
> the url. When I retrieve the value for the "beds" checkbox list from
> the url it looks something like "1,3,5,7". Now I want to pass this into
> a SQL Stored Proc and use a LIKE command to find any property where
> (beds LIKE '[1,3,5,7]'). When I use the query analyzer and manually
> type "WHERE beds LIKE '[1,3,5,7]'" it works fine and pulls up
> properties, but when the parameter gets passed into the stored procedure
> from the web page, it gives no errors and does not retrieve any
> properties. Please take a look at my stored procedure below and tell me
> where I may be going wrong. I've simplified it so that it is less
> convoluded

---

Maybe you simplifed too much? Save the spelling error for the table
it looks OK, but I don't know what values you are passing.
I would suggest that you trim the conditions until you get values
back. It may not be the LIKE expression that is causing you headache.

quote:

---

> CREATE PROCEDURE [sp_SearchResults_v2]

---

Ah, by the way, the sp prefix is reserved for system procedures and
SQL Server will first look for these names in master. You should not
use it for your own names.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Passing a parameter into a LIKE command in a stored procedure...

Hi All,
I am having quite a bit of trouble getting a certain stored procedure to
work properly. In my web page I have a list of checkboxes that post to the
url. When I retrieve the value for the "beds" checkbox list from the url it
looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
Proc and use a LIKE command to find any property where (beds LIKE
'[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
parameter gets passed into the stored procedure from the web page, it gives
no errors and does not retrieve any properties. Please take a look at my
stored procedure below and tell me where I may be going wrong. I've
simplified it so that it is less convoluded
CREATE PROCEDURE [sp_SearchResults_v2]
(@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
nvarchar(2), @.a3 nvarchar(2),
@.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
nvarchar(2), @.a9 nvarchar(2),
@.a10 nvarchar(2), @.Kids int)
AS
--declare area variables--
DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
@.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
-- check for null entries and convert to a select all entry Area --
IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null) AND
(@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND (@.a9
is null) AND (@.a10 is null))
BEGIN
set @.area1 = N'%'
set @.area2 = N''
set @.area3 = N''
set @.area4 = N''
set @.area5 = N''
set @.area6 = N''
set @.area7 = N''
set @.area8 = N''
set @.area9 = N''
set @.area10 = N''
END
ELSE
BEGIN
set @.area1 = @.a1
set @.area2 = @.a2
set @.area3 = @.a3
set @.area4 = @.a4
set @.area5 = @.a5
set @.area6 = @.a6
set @.area7 = @.a7
set @.area8 = @.a8
set @.area9 = @.a9
set @.area10 = @.a10
END
SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag, Archive_Flag,
Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
Property_Address, Property_Country,
Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
Dwelling_Code, Pet_Type_code,
Suite_Type_Code, Date_avalible, Property_Rental_Period,
Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
Property_phone2,
Property_ext, First_name, Last_name, Email_address,
CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
Property_houseno, Property_stdesignation,
Property_stname, cross_stno, cross_stname, cross_stdesignation,
CorrectedPhone1, CorrectedPhone2,
Property_ID
FROM dbo.Propeties
WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
AND
(Property_Bedrooms LIKE @.beds)
AND
(City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code = @.area3 OR City_Area_Code = @.area4
OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code = @.area7 OR
City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code = @.area10)
AND
(Kids_results = 1)
AND
(Prop_Del_Flag <> 1)
ORDER BY Rent_Amount
GONow I don't have table descripts etc. so I made a very simple proc.
create proc dbo.NEW_Procedure @.input as varchar(250)
AS
select
ArtistName
,SongName
,Genre
,FileName
,Duration
,FirstLetter
,AlbumName
from RadioFreeLlloyd.dbo.SongInfo
where ArtistName like @.input
When I input any value with either wildcards or not it works. Perhaps you
could create a test table with the same columns as you have variables and
then insert a row when a call is made to the proc. It would seem that that
the values of the parameters are perhaps not what you think.
"Fabio Papa" <fapapa@.hotmail.com> wrote in message
news:8YfPb.2394$2j7.752@.clgrps13...
> Hi All,
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
the
> url. When I retrieve the value for the "beds" checkbox list from the url
it
> looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
> Proc and use a LIKE command to find any property where (beds LIKE
> '[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
> LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
> parameter gets passed into the stored procedure from the web page, it
gives
> no errors and does not retrieve any properties. Please take a look at my
> stored procedure below and tell me where I may be going wrong. I've
> simplified it so that it is less convoluded
> CREATE PROCEDURE [sp_SearchResults_v2]
> (@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
> nvarchar(2), @.a3 nvarchar(2),
> @.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
> nvarchar(2), @.a9 nvarchar(2),
> @.a10 nvarchar(2), @.Kids int)
> AS
> --declare area variables--
> DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
> nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
> @.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
> -- check for null entries and convert to a select all entry Area --
> IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null)
AND
> (@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND
(@.a9
> is null) AND (@.a10 is null))
> BEGIN
> set @.area1 = N'%'
> set @.area2 = N''
> set @.area3 = N''
> set @.area4 = N''
> set @.area5 = N''
> set @.area6 = N''
> set @.area7 = N''
> set @.area8 = N''
> set @.area9 = N''
> set @.area10 = N''
> END
> ELSE
> BEGIN
> set @.area1 = @.a1
> set @.area2 = @.a2
> set @.area3 = @.a3
> set @.area4 = @.a4
> set @.area5 = @.a5
> set @.area6 = @.a6
> set @.area7 = @.a7
> set @.area8 = @.a8
> set @.area9 = @.a9
> set @.area10 = @.a10
> END
> SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag,
Archive_Flag,
> Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
> Property_Address, Property_Country,
> Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
> Dwelling_Code, Pet_Type_code,
> Suite_Type_Code, Date_avalible,
Property_Rental_Period,
> Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
> Property_phone2,
> Property_ext, First_name, Last_name, Email_address,
> CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
> Property_houseno, Property_stdesignation,
> Property_stname, cross_stno, cross_stname, cross_stdesignation,
> CorrectedPhone1, CorrectedPhone2,
> Property_ID
> FROM dbo.Propeties
> WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
> AND
> (Property_Bedrooms LIKE @.beds)
> AND
> (City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code
=> @.area3 OR City_Area_Code = @.area4
> OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code => @.area7 OR
> City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code => @.area10)
> AND
> (Kids_results = 1)
> AND
> (Prop_Del_Flag <> 1)
> ORDER BY Rent_Amount
> GO
>|||Fabio Papa (fapapa@.hotmail.com) writes:
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
> the url. When I retrieve the value for the "beds" checkbox list from
> the url it looks something like "1,3,5,7". Now I want to pass this into
> a SQL Stored Proc and use a LIKE command to find any property where
> (beds LIKE '[1,3,5,7]'). When I use the query analyzer and manually
> type "WHERE beds LIKE '[1,3,5,7]'" it works fine and pulls up
> properties, but when the parameter gets passed into the stored procedure
> from the web page, it gives no errors and does not retrieve any
> properties. Please take a look at my stored procedure below and tell me
> where I may be going wrong. I've simplified it so that it is less
> convoluded
Maybe you simplifed too much? Save the spelling error for the table
it looks OK, but I don't know what values you are passing.
I would suggest that you trim the conditions until you get values
back. It may not be the LIKE expression that is causing you headache.
> CREATE PROCEDURE [sp_SearchResults_v2]
Ah, by the way, the sp prefix is reserved for system procedures and
SQL Server will first look for these names in master. You should not
use it for your own names.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

pass variable with a space

Hi,

I am passing a filename to a command in sql.

The problem is that it gives an error if the @.FullFilePath has a space in it.

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];' + @.FullFilePath + '"'

print @.cmd

Thanks

As with everything, use quotes... ""|||

This is the stored procedure which passes the filename

uspCEMTradeExecutePackage 'd:\sysappl\CEM\SSIS\Imports\Trades\csa.dtsx', 'CSA data.csv'

Notice the filename, i.e. 'CSA data.csv'

It has a space in it.

Do you mean the call to the stored procedure should be:

uspCEMTradeExecutePackage 'd:\sysappl\CEM\SSIS\Imports\Trades\csa.dtsx', '"CSA data.csv"'

this does not seem correct.

Thanks

|||

That is correct, you need the single quotes outside of the double quotes. When you call DTEXEC with a variable or path that has spaces in it, it has to be wrapped in double quotes.

Or you could change the command line to include the double quotes:

Code Snippet

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"'

Pass UserName to the SQL Select ? (VB)

VB ASP.NET 2.0

How do I get the Authenticated UserName passed to a Select Command of a data control ?

I'm not clear on how to get the logged in username fromUser.Identity.Name as a string or how to pass it to the Select Command of a control.

I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL.

I want to show the user data from a database, based on who is logged in .

Help, greatly appreciated.

Chris

User.Identity.Name will hold the value of the currently authenticated user. How you deal with parameters depends on how you approach it. with a straightforward Sql command, the following is a guide:

Dim query As String = "Select * From Users WHERE UserName = @.UserName"
Dim conn As New SqlConnection(myconnectionstring)
Dim cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@.UserName",User.Identity.Name)
conn.Open()
Dim rdr As New SqlDataReader = cmd.ExecuteReader()
While rdr.Read()...

If you want to use a SqlDataSource, this article should help:http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

|||

valenumr:

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

But that'll get the SQL user, which probably won't be the same as the user logged on to the website..

Regards

Fredr!k

|||

Very true... it will work if your DB is using windows login's, and you don't have any impersonation going on in your web server... so I guess, I should have mentioned that.

|||

Thanks to all the rapid replies.

An error had lead me to believe that the User.Identity.Name did not return a string, but further research says it should.

I should be clearer about the approach I was attempting. Given that I'm struggling to learn .Net and ASP.Net I'm struggling to keep the issues clear.

I was trying to find a way to do the assignment in the Mark Up for the server controls.

I have found that you can also create a Parameter in the mark up for the SqlSourceControl section under the <SelectParameters> section that you can then use in the Select Command.

Still trying to tie that info together to do this.

BUT , your cmd.Parameters.AddWithValue method is eye opening. When I start working with code, I'm still trying to learn the models to understand whichevents to put such code.

(Anyone know a concise reference for this ? )

I want a dropdown box to be filled with a set of items from a database that are particular to the User that's logged on.

(ps. I find from my research that you can use My.User.Name , also to get the username)

Many Thanks for all the help !

Chris9876

|||

I'm not at my computer to verify, but try User.Identity.Name.tostring If that doesn't work, I will check when I get home as I do this exact thing, but with the userid and not the username.

|||

This is from memory, but you can create a parameter in your sqldatasource (Let's call it @.Username).

Then in the SqlDataSource1_Selecting event, just put:

e.Commad.Parameters("@.UserName").Value= My.User.Name

Then whenever the sqldatasource is about to do a select, you set the username parameter to whomever is currently logged it. Simple and easy.

If you are changing the output based on the persons roles, or if they are currently logged in, you might want to just use a loginview control.

|||

The following Markup and Event Code works great. (DropdownList is filled with data from MyField whereUserName=@.UserName)

Thank you all very much.

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"DataTextField="ClassCode"DataValueField="ClassCode">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [MyField] FROM [MyView] WHERE ([UserName] = @.UserName)">

<SelectParameters>

<asp:ParameterName="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

ProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)Handles SqlDataSource1.Selecting

e.Command.Parameters("@.UserName").Value =My.User.Name

EndSub

Pass Datasource Name as input parameter

hi,

how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?

Thanks,

shanthi

Hi,

Unfortunately this is not possible. What you can do is use a stored procedure which returns a different set based on a parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

The closest you can get is to use an expression-based data source connection string. An example is shown here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

Pass Datasource Name as input parameter

hi,

how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?

Thanks,

shanthi

Hi,

Unfortunately this is not possible. What you can do is use a stored procedure which returns a different set based on a parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

The closest you can get is to use an expression-based data source connection string. An example is shown here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

Partner works from mirror to principal, but not vice versa.

We were having problems setting up the mirroring, so I did it via command lines. I found out the "alter...set partnership" command works on the mirror server going to the principal, but gets a 1418 error when going from the principal to the mirror.

So if A is the principal and B is the mirror, A to B fails but B to A works.

If I reverse it so that B is the principal and A is the mirror, B to A fails and A to B works.

Any suggestions?

Here’s an update on our problem.

We are now doing it thru Management Studio. We set up the mirroring, but answer “no” when it asks to start mirroring and go back to the mirroring screen. One of the default names contains the domain but the other does not.

We replace the address field with the IP addresses for the Principal and Mirror Network Addresses (no witness). When we click on “start mirroring”, the Principal’s name gets changed back to the dns name, but the mirror name remains an IP address. This is true even when we switch which server is the principal and which the mirror.

It seems to me that because it always happens the same way for the mirror and principal addresses, regardless of the server, that this might be a bug.

|||

Fixed it!

We did two things:

- Went to C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys and set the administrators permission to Full Control and

- Edited the C:\WINDOWS\system32\drivers\etc\hosts file on each server to include

IP addressDNSname

For each server.

Partner works from mirror to principal, but not vice versa.

We were having problems setting up the mirroring, so I did it via command lines. I found out the "alter...set partnership" command works on the mirror server going to the principal, but gets a 1418 error when going from the principal to the mirror.

So if A is the principal and B is the mirror, A to B fails but B to A works.

If I reverse it so that B is the principal and A is the mirror, B to A fails and A to B works.

Any suggestions?

Here’s an update on our problem.

We are now doing it thru Management Studio. We set up the mirroring, but answer “no” when it asks to start mirroring and go back to the mirroring screen. One of the default names contains the domain but the other does not.

We replace the address field with the IP addresses for the Principal and Mirror Network Addresses (no witness). When we click on “start mirroring”, the Principal’s name gets changed back to the dns name, but the mirror name remains an IP address. This is true even when we switch which server is the principal and which the mirror.

It seems to me that because it always happens the same way for the mirror and principal addresses, regardless of the server, that this might be a bug.

|||

Fixed it!

We did two things:

- Went to C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys and set the administrators permission to Full Control and

- Edited the C:\WINDOWS\system32\drivers\etc\hosts file on each server to include

IP addressDNSname

For each server.