I execute dynamic SQL with a return parameter like this:
SET @lSqlStr = ' SELECT @oRsltCount = COUNT(*) FROM foo';
EXECUTE sp_executesql @lSqlStr, N'@oRsltCount int out' @oRsltCount out;
The sp_executesql @stmt parameter is type nVarChar. Occationally, the size of @lSqlStr may be greater than 4000 bytes, which exceed the maximum allowable size of type nVarchar.
So, I may execute dynamic SQL like this:
EXECUTE ( @lSqlStr );
where the type of the argument is VarChar(MAX), and size of @lSqlStr may be up to 2^31 -- but cannot parameterize (or bind, if you will) the query result.
How can I execute dynamic query with string size greater than 4k bytes AND bind the query result?
I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.
Query is sort of
Select field1,fiel2... from table 1 where id = @id
@id is each ID from lookup
Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.
Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.
I'm trying to build ssis package that select rows from SQL Server table and
according to Conditional Split cube results I should execute oracle stored
procedure with in and out parameters.
The in parameters should come from the SQL select and the output parameters
should be inline parameter that I should declare on in the OleDB Command
selecet statement.
Here is the sql statement I wrote in the OleDB Command:
declare @out_return_status as int declare @out_return_msg as varchar (1000) begin exec clickace.cns_clk_sst_iw_pkg.update_instal_warranty_dates ?,?,?,?,?,
@out_return_status,@out_return_msg end
When I click on the Refresh button in the ssis GUI I get the following error:
An OLE DB error has occured. Error Code : 0x80040E51.
An OLE DB record is available. Source: OraOLEDB Hresult : 0x80040E51.
Description :"provider cannot provide parameter information and
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
When I try to add a parameter called findby to the order by part of a query like this: dim q1 as string="select store+' '+customer+' '+left(customer,len(customer))" q1=q1+"+replicate('.',30-len(customer))+' '+cdate as a" q1=q1+" from tblcustomers" q1=q1+" where store='65' and customer like @lookfor" 'eventually want @findby where this says customer q1=q1+" order by @findby" with command1.parameters: .Add(New SQLParameter("@lookfor", textbox1.text+"%")) .Add(New SQLParameter("@findby", dropdownlist2.text)) 'dropdownlist2.text="customer" which is the name of a column end with I get this server error: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. Are parameters not good for names of things in a query but ok for values of what those names represent? If not, what am I doing wrong? Thank you very much.
I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes. Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click Dim ACSelections As Boolean = False Dim ACItem As ListItem For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSelections = True End If Next If ACSelections = True Then Dim ACSqlString As String ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE " For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4) ACSqlString &= "ORDER By Customers.CustomerName" sql_CustomerSearchGrid.SelectCommand = ACSqlString End IfEnd Sub
Hello,I have an add stored procedure in Yukon (would work in 2000 too), where I select the ID from the table to make sure that it doesn't already have the data. So it looks like:create procedure ....set transaction isolation level serializablebegin transactiondeclare @ID intselect @ID = rowid from tblBusinessInformation where Name = @Name and Rules = @Rulesif ( @ID is NULL ) begin insert into tblBusinessInformation (..) values (@Name, @Rules) endcommit transactionThe problem is the values could be:Name RulesNULL 'Test''Test' NULL'Test' 'Test'When one of the values was NULL, it would never select the ID, unless I changed it to "where Name is @Name", and then it worked, because where Name is NULL, which is correct in SQL; so how do I allow for both; I can use the CLR, but would like to avoid rewriting the proc if possible, and I thought that was to work...Thanks.
I've got this SP:CREATE PROCEDUREEWF_spCustom_AddProfiles_CompanyYear@prmSchoolYear char(11)ASSELECTContactIDFROMdbo.EWF_tblCustom_CompanyProfileWHERESchoolYear = @prmSchoolYearI'd like to be able to reference that in the where clause of anotherSP. Is that possible?I'd like to end up with something like this:CREATE PROCEDUREMyNewProc@prmSchoolYear2 char(11)ASSELECTContactID, SomeOtherFieldsFROMtblContactWHEREContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear@prmSchoolYear2)How would I make that happen?If this isn't possible, what else might I try?Thanks much for any pointers.JeremyPS: I accidentally crossposted this in another group(http://tinyurl.com/gksq4) thinking it was this one. Sorry for that.
If I bring up a report in the report server (http://reportserver/Reports/Pages/.... and I set the parameters to see the information I want to see and say to myself, hey, my boss would really like to see this, how would I go about sending him the link to the report with the parameters already set. Ideally when I view the report, the parameters would show in the URL automatically as other reporting products do, however, SSRS does not provide this. I do not even see a function in the report designer to display the link on the report itself so I can cut and paste from it. Anyone know how to do this without having to custom craft a URL or set a one time subscription?
Is there a way to use parameterized queries with RDA method? I write a program for WinCE5.0 and when I submit a query I use hardcoded date format and this causes problems in different systems.There's a solution for this?
The above code doesnt work. Firstly I am not sure if I can execute the two statements in one go. Secondly, I am not sure if output parameters are supported.
I have been working with SQL Server since 6.5 but have always used sprocs and am feeling a little lost here without them. Any help getting started would be greatly appreciated.
I am new to Parameterize query ... and i want to use above for inserting XML data and to retrive the same as it contains some special character so by using string it is changed.
can anyone show me where i've done wrong in my coding? because i can't seems to find the error. I've looked through forums and google but just can't understand what they are on about as i'm kind of a beginner. Please help me...thanks in advance...(i dont have any stored pocedure, just using a connectionstring called connectionstringnews)HERES THE ERRORParameterized Query '(@newsid nvarchar(4000),@author nvarchar(5),@date datetime,@arti' expects parameter @newsid, which was not supplied. 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: Parameterized Query '(@newsid nvarchar(4000),@author nvarchar(5),@date datetime,@arti' expects parameter @newsid, which was not supplied.Source Error:
Line 37: txtMessage.Text)Line 38: con.Open()Line 39: cmd.ExecuteNonQuery()Line 40: con.Close()Line 41: 1 Imports System.Web.Configuration 2 Imports System.Data.SqlClient 3 Partial Class News_Articles_Default 4 Inherits System.Web.UI.Page 5 6 Protected Sub btnPost_Click( _ 7 ByVal sender As Object, _ 8 ByVal e As System.EventArgs) _ 9 Handles btnPost.Click 10 11 Dim cs As String 12 cs = WebConfigurationManager _ 13 .ConnectionStrings("ConnectionStringNews") _ 14 .ConnectionString 15 Dim insertNews As String 16 insertNews = "INSERT news " _ 17 + "(newsid, author, date, articles) " _ 18 + "VALUES(@newsid, @author, @date, @articles);" 19 20 Dim con As SqlConnection 21 con = New SqlConnection(cs) 22 Dim cmd As SqlCommand 23 cmd = New SqlCommand(insertNews, con) 24 25 Dim newsid As String 26 newsid = Request.QueryString("news") 27 28 cmd.CommandText = insertNews 29 cmd.Parameters.Clear() 30 cmd.Parameters.AddWithValue("newsid", _ 31 newsid) 32 cmd.Parameters.AddWithValue("author", _ 33 txtAuthor.Text) 34 cmd.Parameters.AddWithValue("date", _ 35 DateTime.Now) 36 cmd.Parameters.AddWithValue("articles", _ 37 txtMessage.Text) 38 con.Open() 39 cmd.ExecuteNonQuery() 40 con.Close() 41 42 End Sub 43 44 45 End Class 46
Hey fellas. Here's my situation. I have two textboxes where the user enters a "start" date and an "end" date. I want to search a table to find records who's "expired" column date is between those two dates provided by the user. The tricky part is, if the user just puts a start date in but no end date, I want it to search from whatever start date the user entered to the future and beyond. Essentially, I think I'm looking for a SQL statement along the lines of: SELECT Request.RequestID, Request.URL, ActionProvider.Name, Request.CurrentStageID, Request.Decision, Request.SubmissionDate, Request.ExpirationDate FROM Request INNER JOIN RequestSpecificActionProvider ON Request.RequestID = RequestSpecificActionProvider.RequestID INNER JOIN ActionProvider ON RequestSpecificActionProvider.ActionProviderID = ActionProvider.ActionProviderID INNER JOIN RoleActionProvider ON ActionProvider.ActionProviderID = RoleActionProvider.ActionProviderID INNER JOIN Role ON RoleActionProvider.RoleID = Role.RoleID WHERE
CASE WHEN @BeginDate is not null AND @BeginDate <> '' THEN Request.ExpirationDate > @BeginDate END
AND
CASE WHEN @EndDate is not null AND @EndDate <> '' THEN Request.ExpirationDate > @EndDate END
AND (Role.Description = 'Requestor')
I realize my code isn't correct and there's still a floating "AND" out there I would have to put some logic around. Anyway, how do I do this? Do I need to build three separate queries in my tableadapter (one for if both dates are provided, one for if start date is provided, one for if end date is provided) and build the logic in my application code or can I tackle it with SQL? If I can tackle it with SQL, where have I gone astray? I'm currently getting the error: "Error in WHERE clause near '>'. Unable to parse query text." Thanks for the help everyone!
How would I debug such a query. I have a sqlCommand to which I add several parameters for an insert statement. if the statement fails, for some reason, I would like to copy the final sql with all values inserted as text and use this in e.g. TOAD to see where the error is coming from. Is this possible?
Hi, I need to use parameters with the IN clause in a SQL statement like: select * from tableX where field IN (1,2,3,4) I don't know how to do that. I'm using SQLServer and OleDB.
Hi, Below are two methods o passing a parameterized query, are these the same, or is one open to sql injection attacks more than the other?Option 1 - through code behindDim testDataSource As New SqlDataSource()testDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionStringName").ToString()testDataSource.UpdateCommandType = SqlDataSourceCommandType.TexttestDataSource.InsertCommand = "INSERT INTO test(id) VALUES (@id1)"testDataSource.InsertParameters.Add("@id1", TextBox1.Text) Option 2: through sqldatasource on page and control parameters<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringName %>"InsertCommand="INSERT INTO test(id) VALUES (@id1)" <InsertParameters><asp:ControlParameter ControlID="TextBox1" Name="id1" Type="Int32" /></InsertParameters></asp:SqlDataSource> Feedback would be great, thanks!
I am trying to use the following SQL query to return a set of values:SELECT id, submit_date, company_name, request_type, status FROM tblRequestForms WHERE request_type IN (@RequestType) AND status IN (@Status) ORDER BY id ASCI have tried passing an array of string values to both @RequestType and @Status, but It does not work. Is there any way to pass multiple values like this using parameters?
I want to create a stored proc that might or might not recieve a value like below.
create proc cmine @vdate datetime = null as select * from table where mydate > @vdate
However if the value is null,( no value was supplied) I would not want to use the where clause. Should (or can you) use an if statement to check and see if no value was given to change the where clause? Thanks
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface. I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ... FROM ... WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure. But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);). The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
How i can create parameterized sql query .This is my basic idea to implement.
select * from dimemployee
where name = ?
or
exec proc sp_para_employee ?
how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.
Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?
I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?
Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?
I'm creating a data flow task to export a set of records that were created within a specific time frame. The date offsets I'm using are read into user variables that are of type Int32. I have an OLEDB source connected to a SQL Server 2005 database using the following query to get the records I want:
select * from claim where date_created > dateadd(day,?,getdate)
I've mapped Parameter0 to my offset variable, which has a value of -7. When I hit OK to close out of the OLE DB Source editor, I get a message saying "Argument data type datetime is invalid for argument 2 of dateadd function." I can't figure out why it keeps talling me this even though the variable I'm passing in is an integer, not a datetime. I've done a lot of searching and found some instances of other people having this problem, but so far no answers. I could just go ahead and try to create an equivalent query using datediff or something, but I'd like to know what's going on here. Is this a bug in Integration Services itself, or is there another explanation?
For my report I want to have the days listed as a dropdown with simple dates during the month (3/27/2007), but there may be many records for that date as it is using the time as well. Unfortunately I don't even know where to begin
Ultimately the user will be given a dropdown listbox showing the simple dates only for the current month and on selection user will view only that days report. How do I basically treat each day as a group in the parameter?
Can you create one connection manager that accepts the Server name and database name at run time and constantly reuses the server and datbase name at runtime when executing one SSIS solution with Multiple packages.
I'm a Transact-SQL newbie, and I would like to create a procedure that creates a table with a parameterized name. I wrote the following, which I thought should do the job:
CREATE PROCEDURE procedure_AddFund @FundName varchar(10) AS BEGIN CREATE TABLE @FundName (Date smalldatetime, Price money) END GO
But I am getting the following error in Management Studio:
Msg 102, Level 15, State 1, Procedure procedure_AddFund, Line 5 Incorrect syntax near '@FundName'.
Presumably, I need to convert the @FundName to some appropriate string type. My question is what is the string type, and how do I do the conversion.
This fails with the error "cannot convert varchar to numeric". I believe since ColumnA is numeric, its trying to convert the dynamic paramter to numeric leading to the failure.
has someone implemented an In clause as a parameter? Please do not tell me that I can append the values as string and construct a dynamic query. I want to use a parameterized version. I will be calling this repeatedly and dont want recompile overhead.
I am developing a website for multiple clients, each with their own separate database on SQL Server 2005. The database structures are identical for all clients. I like to use SQL stored procedures for the security advantages (i.e., don't need to grant access to the tables, only exec permissions on the stored procedures), but maintaining and deploying many sp's across all databases is becoming unwieldy and error-prone.
Is there a way to use parameterized queries (SqlCommand, SqlParameter) in C# code (which could be reused for all databases by changing the connection string) without having to grant access to the tables?
