Passing A Parameter To A USE Command Within A Cursor
Hi everyone I am trying to pass a parameter to a USE command within the body of a cursor. I keep getting an error. Here is my code.
Code Snippet
DECLARE @SQLText AS NVARCHAR(4000)
Declare @SQLText2 as NVARCHAR (2000)
DECLARE Cursor_Trial CURSOR FOR SELECT name FROM sys.sysdatabases order by name
Declare @name as varchar(255)
Open Cursor_Trial
FETCH NEXT FROM Cursor_Trial INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Use @name --------This is where I'm getting the error
Set @SQLText2 = @name
select @SQLText2, * from sys.database_principals
where type_desc='DATABASE_ROLE'
and name = 'xxxxx'
FETCH NEXT FROM Cursor_Trial INTO @name
END
CLOSE Cursor_Trial
DEALLOCATE Cursor_Trial
As you can see I am trying to pass the next database name into the body of the cursor so that the query will pull the role information only for the current database in the loop. Any thoughts or tips? Is there a better way to accomplish what I'm trying to do here?
View Complete Forum Thread with Replies
Related Forum Messages:
Passing Parameter To OLE DB Command To Perform Update On Paradox Table
Dear All - iam facing a problem for passing a parameter to update paradox table statment in the OLE DB Command Briefly an OLE DB Command exist between OLE DB Source and OLE DB destination , all i want it to send the data that flow from the OLE DB Source to the update paradox table statment in OLE DB Command , i use the parameter annotation which is "?" to refer to the parameter in the update where clause and it sucess when i use the OLE DB Command to connect to SQL Server DB but when i use it with the Paradox Table an error appear saying that there is more than one data source column with the name "?" The data source column names must be unique. i dont know what that means is it means that "?" it consider "?" as a column or what or is there another way to send parameter to Update statment for the paradox table differ from the SQL Server Update statment which is the "? " i hope someone help me as soon as possible because i need it in work sOOon Thanks, Maylo
View Replies !
Passing Variables In An SQL Command
I have an SSIS package. In my control flow I have an Execute SQL Task and a data flow task. In my Execute SQL Task in the SQL Statement I have (select dbo.to_date(getdate()) as process_date) its a direct input with a result set. It gets Getdate as (processed_date) is declared. I have set no parameters, but I have set a Result Set. Also, I have set a global variable in the scope where I passes the date (processed_date) In my Data Flow, I have an OLE DB data source with the following SQL statement in my SQL Command. I am trying to pass down the variable as the ? . It works when I pass it only to: lr.processed_date = ? But I get an error when I pass it down to the : and ? between cav.begin_date and cav.end_date SELECT distinct acc.account_num, FROM cust_version_slow cvs inner join cust_acco_version cav on cus.cust_id = cav.cust_id and ? between cav.begin_date and cav.end_date inner join bia.dbo.acct acc on cav.acco_id = acc.acco_id inner join bia.dbo.account_version_slow avs on acc.account_id = avs.account_id and ? between avs.begin_date and avs.end_date inner join bia_org ('02','2,3,4,5,6,7,9,10') boh on cvs.branch_code = boh.branch_code inner join accou_version_profit accM4 on acc.acco_id = accM4.acco_id and ? between accM4.begin_date and accM4.end_date where lr.processed_date = ? I need to pass down the same variable (processed_date) which is ? in all the ? in red Thanks, Delovan
View Replies !
Passing '% Variable %' To SqlDataSource Through E.Command.Parameters
Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page. It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results. I've tried several variations of: string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'"; e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much!
View Replies !
Execute SQL Task Passing Parameters To A Restore Command
Hi, I'm very new to SSIS and I€™m trying to do the following in a SQL task RESTORE DATABASE @DatabaseName FROM DISK = @Backup WITH FILE = 1, MOVE @OldMDFName TO @NewMDFPath, MOVE @OldLDFName TO @NewLDFPath, NOUNLOAD, REPLACE, STATS = 10 I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@DatabaseName". How can i get my values to be substituted into the command? Many thanks Martin
View Replies !
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
View Replies !
Passing Command-line Options Through To Package.Execute()
Hi, I'm looking into the idea of building an enhanced version of dtexec.exe that builds in some extra logging features. My utility will execute packages using the Package.Execute() method. Thing is, I'd still want to support all of the command-line options that dtexec supports. For example, my utility should accept "/set package.variables[myvariable].Value;myvalue" and pass it through to the executing package but I can't find a way of doing it using Package.Execute(). Am I missing something or is this just not possible? Thanks Jamie [Microsoft follow-up]
View Replies !
Passing Status Back From Command Line Application To SQL Job
Hi, I have created a job in SQL Server 2005 in which one of the step executes a .NET console application which is created in .NET to update some status to database before the next step. i need some help in sending some status back to sql job when i come accross any problem in the console application for ex when there is a exception i need to send some status to the job, so the job gets failed permanently. i tried few other methods of updating some temporary status database with this error information and have another intermediate step in the job to check for the status... it worked but i dont like doing it. please let me know if there is any other method to do this. Thanks in advance Hariharan
View Replies !
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.
View Replies !
Passing Parameter
I have a stored procedure named "processInventory" like the following.Depending on the passed in parameters, I would like to add a WHEREclause for "select" action. For example, if any varchar type ofparameter is passed in, the where clause would use "LIKE" operator. Forexample, "Select * from Main where [s/n] like @Serial. All other typeswill use "=" operator. For example, "Select * from Main where MAKE =@Make and Type = @type".How could this be achieved? Thanks.CREATE PROCEDURE processInventory@Action varchar(7),@ControlNumber int = null,@AssetTag int = null,@Serial varchar(50) = null,@Description varchar(50) = null,@Make int = null,@Type int = null,@Model int = null,@Status int = null,@Networked bit = null,@LoginName varchar(50) = null,@Shared bit = null,@Org varchar(15) = null,@RecordDate datetime = null,@LastUpdate datetime = null,@ManufactureDate datetime = null,@Comment ntext = nullASdeclare @processError intset @processError = 0if @Action = 'Select' goto selectInventoryelseIf @Action = 'Update'beginif @ControlNumber = null return(1) --Required parameter value notspecifiedelsegoto updateInventoryendelseif @Action = 'Insert'beginif @Serial = null return(1) --Required parameter value notspecifiedelsegoto InsertInventoryendelseif @Action = 'Delete'beginif @ControlNumber = null return(1) --Required parameter valuenot specifiedelse goto deleteInventoryendselectInventory:if @Serial <> nullbeginselect * from Main where [S/N] like @Serialif @@Error<>0beginset @processError = @@Errorreturn @processErrorendendelseif @ControlNumber <> nullbeginselect * from Main where ControlNumber = @ControlNumberif @@Error <>0beginset @processError = @@Errorreturn @processErrorendendelseselect top 100* from MainupdateInventory:update MAINset [Org Asset Tag] = @AssetTag, [S/N] = @Serial, [Description]= @Description, Make = @Make, Type = @Type,Model = @Model, Status = @Status, Networked = @Networked,LoginName = @LoginName, Shared = @Shared,Org = @Org, [Date Of Record] = @RecordDate, [Date LastUpdated] = @LastUpdate, [Manuf Date] = @ManufactureDate,Comments = @Commentwhere ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelsereturn(0) -- successful updateinsertInventory:insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,Model, Status, Networked, LoginName, Shared,Org, [Date Of Record], [Date Last Updated], [ManufDate],Comments)values(@AssetTag, @Serial, @Description, @Make, @Type, @Model,@Status, @Networked, @LoginName, @Shared,@Org, @RecordDate, @LastUpdate, @ManufactureDate,@Comment)if @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful insertdeleteInventory:delete MAIN where ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful deleteGO
View Replies !
Passing Parameter To SP Using IN
Hi, I have a stored proc as follows: Create stored procedure sp_test @Cno nvarchar (1000) AS SELECT * FROM Contracts WHERE Cno IN (@Cno) How do I actually pass the parameters to the stored proc? sp_test "'cn01','cn02','cn03','cn04'" doesn't work. In VB.NET this doesn't work either: dim s as string = "'cn01','cn02','cn03','cn04'" cmd.parameters.add(new sqlparameter("@Cno",nvarchar,1000)) cmd.parameters("@Cno").value = s I don't want to parse the string inside the proc and then Exec the parsed string. TIA Jeremy Holt
View Replies !
Parameter Passing Of Chr Value
in my url i have set a parameter called EndCarrier = %FE. I want to be able to check for this value in my report parameter. I have tried Chr, Hex conversions but haven't found the right way to do it yet. Any suggestions as to how I would check the EndCarrier parameter in my report. The Parameter in the report is defined as a string.
View Replies !
Regarding Parameter Passing
Hi guys... As of now I'm working on a certian report which needs to pass a certain parameter to another dataset... I'm refering to the dataset of the same report. Can anyone tell me how this is done? I really need to know it to finish my report... Thanks a bunch in advance!
View Replies !
Passing A Parameter Value
Hi , I have this situation,I need to store results of a Select Count(*) from TableA to a parameter and only if the value of the count(*) is greater than 50,000,a notification mail would be sent out to the table owner.Currently this is what I have done: 1) Use SQL task to include "Select Count(*) from TableA" 2) In parameter mapping tab of SQL Task, define the following Parameter name : Table::Count Direction: Input Data Type: Numeric Parameter Name : @Count 3) I have connected the SQL Task to a Send Mail Task [ Only If @count>50 000, then a mail should be sent out] 4) The constraints has been set as Evaluation Operation : Expression and Constraints Value: Success Expression : @Count >"50000" In SSIS tab > Variable I have set the following Variable Name : Count DataType : Int32 Can someone advise me where I am going wrong, as when I execute the SQL Task turns greens and ends.Even when the count of tableA is greater than 50 000, no mails I sent out[send mail task does not get executed] Thank You in Advance
View Replies !
Passing Parameter To A URL
how can i pass report parameter via URL? my jump to URL link is: http://itreportsme096/Reports/Pages/Report.aspx?ItemPath=%2fDevelopment%2fhoneylette%2fdetailed+phone+calls and i want to pass two parameters: billingmonth and phoneno how can i do that?
View Replies !
Using A Cursor As A Function Parameter
I've created a function that converts the rows of a column into a delimited string using a passed cursor and delimiter character. In the past I did this in Oracle and called it as shown in the following example: SELECT Table1.ID, Table1.FirstName, Table1.LastName, fnDelimitRows(CURSOR(SELECT Table2.CourseName FROM Table2 WHERE Table2.StudentID = Table1.ID), ',') AS AssignedCourses FROM Table1 23 John Smith CS101,MT200,BIO100 43 Julio Johnson CS200,ENG100,MT300 How would I pass a cursor into a function in SS like I did above in Oracle? Thanks!
View Replies !
Sql Help - Passing An Operator As A Parameter Maybe?
My data table contains a column (a foreign key) with numbers from 0 and up. (it could be null or a number. I used ISNULL to convert it.) I want to be able to select in three ways. 1) equals zero (or is null)2) greater than zero (or is not null)3) equal to a specific number.How do I do this?My application setup is Presentation Layer, BLL, DAL and Sql Server.I don't want to create another Query because this is only part of a larger query with other parameters.If I could just do something like @IDOperator @IDValue in the Filter of the Sql it would be great.
View Replies !
Parameter Passing Problem
why this wont work?error: Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'test1' expects parameter '@cid', which was not supplied..................................................................................ASPX CODE: string cid12; cid12 = "user5"; // Connection ConnectionStringSettings mysettings; mysettings = System.Configuration.ConfigurationManager.ConnectionStrings["dbase1_connection"]; string myConnectionString = mysettings.ConnectionString; SqlConnection conn1 = new SqlConnection(myConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn1; cmd.CommandText = "test1"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter param = cmd.Parameters.Add("@cid", SqlDbType.NChar, 20); param.Direction = ParameterDirection.Input; param.Value = cid12; conn1.Open(); cmd.ExecuteNonQuery(); conn1.Close();.................................................................................STORED PROCEDURE:ALTER PROCEDURE dbo.test1 ( @cid nvarchar(20) )AS /* SET NOCOUNT ON */ select tech_id, customer_id, issue_main from [case] where customer_id = @cid --GO RETURN.................................................................................im using asp.net, C#, and sql expressforgive me. im new to this .netPLS HELP
View Replies !
Parameter Not Passing On Delete
Interesting problem, selecting and updating (with reference to a Form View) works great. Whenever I try to delete I get this error:Must declare the variable '@template_id'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@template_id'. Here is my code:<asp:SqlDataSource ID="SqlTemplateDS" runat="server"SelectCommand="SELECT [template_id], [client_id], [language], [label], [email_default], [email_template], [event_template] FROM [CMT] ORDER BY [template_id]"DeleteCommand="DELETE FROM CMT WHERE (template_id = @template_id)" ConnectionString="<%$ ConnectionStrings:BrandMSConnectionString %>"> </asp:SqlDataSource><asp:GridView ID="grdTemplates" runat="server" AutoGenerateColumns="False" DataKeyNames="template_id" DataSourceID="SqlTemplateDS" Font-Names="Verdana" Font-Size="Small" ForeColor="Black" Width="100%" OnSelectedIndexChanged="grdTemplates_SelectedIndexChanged" CellPadding="2"> <Columns> <asp:BoundField DataField="template_id" HeaderText="ID" ReadOnly="True" SortExpression="template_id" /><asp:BoundField DataField="client_id" HeaderText="Client" SortExpression="client_id" /><asp:BoundField DataField="language" HeaderText="Language" SortExpression="language" /><asp:BoundField DataField="label" HeaderText="Label" SortExpression="label" /><asp:CheckBoxField DataField="email_default" HeaderText="Default" SortExpression="email_default" /><asp:CheckBoxField DataField="email_template" HeaderText="Email" SortExpression="email_template" /><asp:CheckBoxField DataField="event_template" HeaderText="Event" SortExpression="event_template" /> <asp:TemplateField HeaderText="Options"><ItemTemplate> <asp:LinkButton ID="btnModify" CommandName="Select" runat="server" ForeColor="Blue">Edit</asp:LinkButton> | <asp:LinkButton ID="btnDelete" CommandName="Delete" runat="server" ForeColor="Blue">Delete</asp:LinkButton></ItemTemplate></asp:TemplateField> </Columns><HeaderStyle BackColor="Khaki" /><AlternatingRowStyle BackColor="Beige" /></asp:GridView>Any ideas?Chris
View Replies !
Aspx Vb Sql Parameter Passing
Hello, Can someone kindly point out what is wrong with the following code file. I'm trying to: - fill a dropdown from a db on page load (this works!) - when user selects from the list and hits a button, pass the dropdown value to a second query - use the second query to make another call to the db and fill a data grid In the code below, if I swap an actual value (eg '1005') into the command and comment out the .Parameter.Add statements, the dategrid is filled sucessfully. Otherwise, when the button is pressed, nothing is displayed. Thanks PS comments about my coding approach are welcome - I'm new to aspx... <%@ Language="VBScript" Debug="true"%> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.data.SqlClient" %> <html> <head> <script language="vb" runat="server"> Dim dbConnection As SqlConnection Dim ds_Teams,ds_Agents As DataSet Dim sqlCmd_Teams,sqlCmd_Agents As SqlDataAdapter Dim dbConn = "server=csacd01;uid=mpierce;pwd=cabledog;database=clearviewacd" Dim sql_select_teams = "" & _ "SELECT team_no, team_name " & _ "FROM dbo.team " & _ "WHERE (team_status = 'Curr')" Dim sql_select_agents = "" & _ "SELECT last_name + ', ' + first_name AS name, agent_no " & _ "FROM dbo.users " & _ "WHERE (team_no = @Team) AND (NOT (agent_no = '1029')) " & _ " AND (NOT (last_name IS NULL)) " & _ " AND (NOT (first_name IS NULL)) " & _ " AND (NOT (first_name = 'FirstName')) " & _ "ORDER BY last_name" Dim teamList = "teamList" Dim agentList = "agentList" Sub Page_Load(Sender As Object, E As EventArgs) if not (IsPostBack) ds_Teams = new DataSet() dbConnection = New SqlConnection(dbConn) sqlCmd_Teams = New SqlDataAdapter(sql_select_teams, dbConnection) sqlCmd_Teams.Fill(ds_Teams, teamList) dbConnection.close() dropdownlist_Teams.DataSource=ds_Teams.Tables(teamList).DefaultView dropdownlist_Teams.DataBind() end if End Sub sub Get_Agents(Sender As Object, E As EventArgs) ds_Agents = new DataSet() dbConnection = New SqlConnection(dbConn) sqlCmd_Agents = new SqlDataAdapter(sql_select_agents, dbConnection) sqlCmd_Agents.SelectCommand.Parameters.Add(new SqlParameter("@Team", SqlDbType.NVarChar,4)) sqlCmd_Agents.SelectCommand.Parameters("@Team").Value = dropdownlist_Teams.DataValueField sqlCmd_Agents.Fill(ds_Agents,agentList) dbConnection.close() datagrid_Agents.DataSource=ds_Agents.Tables(agentList).DefaultView datagrid_Agents.DataBind() end sub </script> </head> <body> <form runat="server"> <asp:DropDownList id="dropdownlist_Teams" runat="server" DataTextField="team_name" DataValueField="team_no"> </asp:DropDownList> <input type="submit" onserverclick="Get_Agents" value="Get Agents" runat="server"><br /> <ASP:DataGrid id="datagrid_Agents" runat="server" Width="500" BackColor="#ccccff" BorderColor="black" ShowFooter="false" CellPadding=3 CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" EnableViewState="false" /> </form> </body> </html>
View Replies !
Passing IN() Values As Parameter
Hi... I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter. eg: procedurename @fieldvalue varchar(100) as begin ... WHERE fieldname IN(@fieldvalue) while executing the procedure how to pass the value... or procedure itself has problem...? Help me... Tx in Advance...
View Replies !
Passing A Text Parameter
Is it possible to pass a text parameter to an SP. I was never successful in 6.5 and haven't yet been successful in 7.0. I'm hoping maybe I'm just doing something wrong.
View Replies !
Passing A Qry In As A Parameter To Proc?
possible to pass in a query as a parameter to a stored proc? Number of constraints right now would make it a lot easier if I could pass in a query that selects all the ID's, tried but couldn't come up w/anything, just have a simple proc that does the deletes on 1 ID @ a time...since there are up to 100 that will need to be deleted, the qry as a param would be much more convenient. below is the proc...Thx for any help. Code: CREATE PROCEDURE [dbo].[s_DeletePeople] @PeopleID int /* single id to be deleted, tried just passing * in a query that resembled a string and using it * but it didn't work either. */ AS Deletefrom tProjectManager whereManager_ID in (@PeopleID) Deletefrom tMerchandiser whereID in (@PeopleID) Deletefrom tProjectCall wheremerchandiser_id in (@PeopleID) Delete from tManager whereID in (@PeopleID) Deletefrom tDistrictManager whereID in (@PeopleID) Deletefrom tPeople whereID in (@PeopleID) ....and on and on GO
View Replies !
Passing A Table Name Through A Parameter
Hi there, I have this problem.. I created a function in MS SQL and through this fuinction I am also passing the table name... now when I call the table name through the function in a SELECT statement, MS SQL is giving me an error that I have to declare the variabel.... Can someone help me?? Thanks!!
View Replies !
Passing A Table Name Through A Parameter
Hi there.. I created the following FUNCTION and I am trying to pass as a parameter a variable name withe the TABLE name. MS SQL is not accepting it because it is asking me to declare it... can someone help me?? Thankyou "CREATE FUNCTION getNSR2 (@tablename varchar(30)) RETURNS decimal(9,0) AS BEGIN DECLARE @TB varchar(30) SET @TB = @tablename DECLARE @SR decimal(9,0) SET @SR= ( SELECT MAX(SysRef)+1 FROM @tablename ) RETURN(@SR) END "
View Replies !
Passing A Parameter To A SQL Task
Hi The genereal Properities of my SQL Task are ResultSet : None Conection Type : OLEDB SQLSourceType : Direct Input SQL Statement : Update NewFile Set CompanyID = 'S', CompanyName = 'SA', CustomerName = 'SA TEST', CustomerCode = ? BypassPrepare : True Parameter Mapping Variable Name Direction Data Type Parameter Name User::Variable2 Input LONG 0 When executing the SSIS Package I get the Following Error SSIS package "Test.dtsx" starting. Error: 0xC002F210 at Update Company ID and Name, Execute SQL Task: Executing the query "Update NewFile Set CompanyID = 'S', CompanyName = 'SA', CustomerName = 'SA TEST', CustomerCode = ? " failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Update Company ID and Name SSIS package "Test.dtsx" finished: Success. Please Help Many Thanks In Advance Que
View Replies !
Passing Parameter To Sql Statement
I am trying to pass a parameter to an sql statement that executes a stored proc. Here is my sql statement from the Execute SQL Task component. USE [master] GO DECLARE @return_value int EXEC @return_value = [dbo].[sp_sendSMTPmail] @To = N'john.r.carter@bankofamerica.com', @Subject = ?, @Body = NULL, @Importance = NULL, @Cc = NULL, @Bcc = NULL, @Attachments = NULL, @HTMLFormat = NULL, @From = N'bpm@bankofamerica.com' SELECT 'Return Value' = @return_value GO I have the ByPassPrepare set to True. Can anyone tell me why I can't get this to work. Thanks, JC
View Replies !
Parameter Passing Problem
why this wont work? error: Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'test1' expects parameter '@cid', which was not supplied. ................................................................................. ASPX CODE: string cid12; cid12 = "user5"; // Connection ConnectionStringSettings mysettings; mysettings = System.Configuration.ConfigurationManager.ConnectionStrings["dbase1_connection"]; string myConnectionString = mysettings.ConnectionString; SqlConnection conn1 = new SqlConnection(myConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn1; cmd.CommandText = "test1"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter param = cmd.Parameters.Add("@cid", SqlDbType.NChar, 20); param.Direction = ParameterDirection.Input; param.Value = cid12; conn1.Open(); cmd.ExecuteNonQuery(); conn1.Close(); ................................................................................. STORED PROCEDURE: ALTER PROCEDURE dbo.test1 ( @cid nvarchar(20) ) AS /* SET NOCOUNT ON */ select tech_id, customer_id, issue_main from [case] where customer_id = @cid --GO RETURN ................................................................................. im using asp.net, C#, and sql express forgive me. im new to this .net PLS HELP
View Replies !
Passing Parameter To MDX Query
Hi, I am passing Pool Name,From Date and Two date to this quey.I want to change the requirement a bit. I want to select From Date in following query as Last date of Privious Month.Any help on this?? Following query works fine ="Select crossjoin(union(except([From OTS STATUS].[OTS_STATUS].members,[From OTS STATUS].[OTS_STATUS].[All]),[From OTS STATUS].[OTS_STATUS].[All]), [measures].[Loan count] ) on 1,union(except([To OTS STATUS].[OTS_STATUS].members,[to OTS STATUS].[OTS_STATUS].[All]),{[To OTS STATUS].[OTS_STATUS].[All],[To OTS STATUS].[OTS_STATUS].[flat]}) on 0 from [Roll Rate Matrix] where (" &Parameters!FromDate.Value & "," &Parameters!ToDate.Value & "," &Parameters!Pool.Value & ")" I want to get my FromDate as DATEADD(dd,-(DAY(Parameters!FromDate.Value)),Parameters!FromDate.Value) But as soon as I replace From Date parameter with above line it says End Of statement Expected. Any help on this is appriciated. -Thanks, Digs
View Replies !
Passing Parameter Into Dataset
Hello Im trying to pass a integer parameter to my dataset query depending on the selection made from the user. It is suppose to get the Top number off records based on what number the user chooses I get the error Incorrect syntax near '@Param_TopNo, If i take the top @param_topno out the query works The cutdown version off the query is... Select Top @Param_TopNo SaleDate, Consultant, Productname From tblSales where product = 2000 Im not sure if the method above is even achievable !!!! does anyone know how i can pass a variable/parameter into my dataset and maybe provide an example off the code. thanks
View Replies !
Passing Parameter Value To Variable
Hello, I can't figure out how to pass values from report parameters to a variable in custom code. I have a Report Parameter called Parm1 of type string. It contains the following: Label: Value: Labell1 Value1 Label2 Value2 In custom code I have declared a variable thus: Public Dim ParmValue As string = Parameters!Parm1.Value What I want is this: When a user select a report, they will have to choose a parameter from a dropdown-list (created automatically when creating a paramete). The chosen parameter will be passed to the variable ParmValue which I use as one of the parametes in a text-box like this: =code.InstanceName.MethodName(ParmValue) InstanceName is the instance of a class from a dll I reference. It works fine if I hard-code the paramter like this: Public Dim ParmValue As string = "SomeString" instead of: Public Dim ParmValue As string = Parameters!Parm1.Value The error I get is this: [rsCompilerErrorInCode] There is an error on line 0 of custom code: [BC30469] Reference to a non-shared member requires an object reference. I don't know if I'm at all on the right track here. Can someone tell me what I'm doing wrong? Thanks /Peter
View Replies !
Passing Parameter To IN Statement
Hi, I try to pass a parameter to a IN statement in SSRS but couldn't get it work. It likes following sample: select * from T1 where name IN (@nameList) I passed @nameList like 'aa', 'bb', ''aa'', ''bb''(two single quotations), all didn't work. I couldn't even get it work on "Reporting -> Data" screen. What is the right way to pass a parameter to IN statement in SSRS? I am using C# and SSRS2005. Thanks!
View Replies !
Passing Report Parameter Through URL
I am experiencing a problem that has been posted a number of times but cannot find a solution. Passed linked report parameter value through URL: Jump to URL: Code Block="javascript:void(window.open('http://myservert/Reports/Pages/Report.aspx?ItemPath=/Sales/Enquiries/Order+Enquiry&rs:Command=Render&Cust="+Fields!Cust.Value+"'))" When I click the link I am taken to the report but it is waiting for me to enter a parameter. I can see the parameter in the url address and it looks correct. I also tried Parameter!Cust.value (which has the same value as Fields!Cust.Value) but get the same result. The target report has the correct name parameter: Cust. In any case, when I use "Jump to report" instead, it works correctly - although I don't see the parameter value in the url. Any advice appreciated. Thanks, SQL Servant
View Replies !
Passing Parameter To A Trigger
Hi, Im workin in a ASP.Net project. I have a database in SQL 2005 Express. I wrote a trigger for categories table, which makes a backup of record in xcategories table before update operation. The problem is How can i add the userid value into the xcategories table. the userid is available as session variable in my .net application. How to pass that userid to the xcategories table? Please Help........ Thankx in advance.
View Replies !
Passing Parameter To Trigger
How do i pass parameter to trigger.iam using trigger to delete from multiple tables here is the code Create trigger DeletePartyName on PartyMaster_Gen (@PartyName varchar(50)) for delete as DELETE FROM PartyMaster_Address WHERE PartyName=@PartyName DELETE FROM PartyMaster_DEPB WHERE Party_Name=@PartyName DELETE FROM PartyMaster_Gen WHERE Party_Name=@PartyName I want to pass @partyname parameter to this query. how do i do it.
View Replies !
Passing Parameter In Function
I have to pass 3parameters in function, @begindate,@enddate and @group_type.. but in @group_type should be - state,zipcode and country from salestable inview :vwstzipcont create view vwstzipcont as select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3 left outer join (select distinct stype,docdate from salesdisttable) s2 on s2.stype = s3.stype where s2.soptype = 2 go create function mystzipcont ( @begindate datetime, @enddate datetime, @group_type char(70)) RETURNS TABLE AS RETURN (Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from ( select distinct vs.docdate,vs.itemnmbr, p.index From Pubs P inner join vwstzipcont vs on vs.index = p.index Where (vs.docdate between @begindate and @enddate) and @group_type ) as t order by t.itemnmbr,t.docdate end how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country can anyone tell me? what condition should be in where clause for this variable? thanks
View Replies !
Error While Passing Parameter???
Hi I am getting error while passing parameter in SSIS package I am writing following query in sql command select * from Mytable where empid in (select empid from testtable where empid = ?) Error: Parameter cannot be extracted from the SQL Command. The Provider might not help to parse parameter information from the command. In that case, use the "SQL Command from variable" access mode, in which the entire SQL command is stored in a variable. can any body help me out Thanks
View Replies !
Using Cursor As OUT Parameter In Stored Procedure
Hi guys, I have a serious problem. I need to use my cursor as an out parameter, but the problem is, HOW CAN I CLOSE THE CURSOR??????If I dont close the cursor, my server is getting really slow because of the open cursors, cause I have more than 100 stored procedures, which have a cursor as an out-parameter.Here's one of my stored procedures : create or replace PACKAGE pkgResIS TYPE resType IS REF CURSOR RETURN res%ROWTYPE;END pkgRes; create or replace procedure res_sel_val(p_id in number,cs out pkgRes.resType)asBEGIN open cs for select * from res where res_id = p_id; --close cs;EXCEPTION when others then raise_application_error(-20970, 'record kan niet geselecteerd worden');END res_sel_val; How can I close my cursor? If I write the "close cursor" (which is in red at the code above), it returns an empty cursor, which is not my intention.Please help me with thisThanks in advance Morph 'n Nike
View Replies !
Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code' Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' but I can't seem to come up with the right statement. Any help greatly appreciated. Thanks, Judith
View Replies !
Passing Arrays As Parameter (SqlDataSource)
My sql-string looks like this: SelectCommand="SELECT * FROM Table1 WHERE Field1 IN @target" And my parameter looks like this: <asp:ControlParameter Name="target" ControlID="CheckBoxList1" PropertyName="SelectedValue" /> This code gives me a syntax error near @target. Someone got a solution?
View Replies !
Problems Passing Datetime Parameter To SQL
I'm developig an ASP.NET application and I'm trying to pass a datetime parameter into my stored procedure. The procedure is very simple "SET @sqlMessage = @parameterPassed" I have the @sqlMessage setup as out OUTPUT parameter and I'm trying to see what I get. So far no go. The datetime value is of type string, and it was retrived from a datetime column in an SQL database. Any ideas. Thanks.
View Replies !
Passing Comma Delimited Parameter To SP
Is this possible? I find it hard to believe that this could be sooo difficult. I have a simple select stored procedure that has one parameter. My application is passing a comma delimited string of values to be used in the IN clause. Ex: Where x In(@parametername) the x column is an integer. How can one work around this??? Thanks!
View Replies !
Parameter Passing To A Stored Procedure
Hey huys, I got this stored procedure. First of all: could this work? --start :) CREATE PROCEDURE USP_MactchUser @domainUserID NVARCHAR(50) , @EmployeeID NVARCHAR(50) , @loginType bit = '0' AS INSERT INTO T_Login (employeeID, loginType, domainUserID) Values (@EmployeeID, @loginType, @domainUserID) GO --end :) then I got this VB.Net code in my ASP.net page.... ---begin :) Private Sub matchUser() Dim insertMatchedUser As SqlClient.SqlCommand Dim daMatchedUser As SqlClient.SqlDataAdapter SqlConnection1.Open() 'conn openned daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1) daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text) daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output SqlConnection1.Close() 'conn closed End Sub --- If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
View Replies !
Passing A Parameter Into SQL OpenQuery, Revisited
Greetings. ASP.NET neophyte seeking a bit of advice. I am looking for the specific syntax that allows me to pass a local variable into an open query within SQL 2000 for a linked server, such as: SELECT [ticketno] from OpenQuery(JWS_GR, 'select ticketno from tksmisc where itemno = 1 AND ticketno = @scaleticketno') I included the variable @scaleticketno here where I was using an actual value. This doesn't work, returning the eqivalent of a database 'DUH?' message because the linked server database engine (Pervasive SQL 2000i) has no idea how to treat @scaleticketno. (Nor do I.) Any thoughts, suggestions or recommendations would be much appreciated and regarded with eternal gratitude.
View Replies !
|
TRACKER
