Hello,
Does anyone know if its possible to have multiple package configuations in a SSIS package. That you can control via a parameter in some way?
For example, one configuration for each country.
Thankful for any help!
//Patrick
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.
In my project source is Oracle and I am using ODBC to connect oracle for lading.I have create 2 project parameter for connection string one for connection and another for password..when I am making expression on ODBC connection it is showing error like below I can't establish a connection because our legacy driver doesn't support 'Password' as a connection string attribute.
when I am passing expression like
@[$Package::V_Constring]+ "PWD=faster1" on odbc connection it working fine.
When I use just the ConnectionString property on the ODBC connection manager and use a 'pwd' attribute; all is well. E.g., "uid=<user>;pwd=<password>;Dsn=<dsn name>;". But as soon as I
flip the sensitive attribute, I'm getting the classic error:
The expression will not be evaluated because it contains sensitive parameter variable..The sensitive parameter is desired, of course. I don't want the password in the clear.
I have defined a variable Var_Query_SQL and passed the below query using expression but it is showing error. where am i going wrong.
"SELECT
sample_id ,
sample_time ,
trans_date ,
product = mh.[identity] ,
comments = s.m_smp_comment
[URL] ...
I am using a lookup component in a SSIS data flow. The lookup is a select to a foxpro table. THe lookup works fine with full cache selected. I cannot get the lookup to work with a partial or no cache. I have the latest Foxpro OLE DB driver installed which I understand to support paramaterized queries. Has anyone had success with using cached lookup to Foxpro? Does anyone know how to set the lookup properties of sqlcommand and sqlcommandparam? I am unable to find any examples in BOL or on the web.
Here are some details. IF I go with "use a table or a view" option with the default cache query I get initialization errors
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E14 Description: "Command contains unrecognized phrase/keyword.".
In the advanced editor I see
SQLCommand set to
"select * from `kcf`"
and SQLCommandParam set to
"select * from
(select * from `kcf`) as refTable
where [refTable].[patkey] = ? and [refTable].[dayof_stay] = ? and [refTable].[modifier] = ? and [refTable].[kcf_code] = ? and [refTable].[source] = ? and [refTable].[kcf_time] = ?"
I believe the above error is because Foxpro V7 does not support the inner subselect . In addition the query contains CRLF without a continuation character (";").
If I remove the CRLF in the sqlcommandparam query, using the advanced editor, I get this design time error "OLE D error occurred while loading column metadata. Check the sqlcommand and sqlcommandparam properties". The designer requires both properties to be set, its unclear to me how the interact.
I cannot find any examples in BOL or on the web on how to set these 2 properties. Can someone give me a few guidelines?
I can get past the design errors by changing sqlcommandparam to a plain select that is VFP 7 compatible ( I removed the subselect and the square brackets):
select * from kcf as refTable where refTable.patkey = ? and refTable.dayof_stay = ? and refTable.modifier = ? and refTable.kcf_code = ? and refTable.source = ? and refTable.kcf_time = ?
But then I get a runtime error
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E46. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E46 Description: "One or more accessor flags were invalid.".
[lkp_lab_worst_value [6170]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.
Any idea on what I should try next ?
Hi,
Urgent Help required..........
Can anyone explain me steps how to parameterized query to send oracle.
If you know any other control which help to do this rather than OLEDB source.
Please let me know.
THanks
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 :)
View 1 Replies View RelatedI am trying to create a list of values for an IN clause. How does one do it with parameterized queries?
If I use var = "val','val2','val3"
then the ' are replaced with '' - so how?
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
View 7 Replies View RelatedCan someone please help me with this parameterized query? Its is not working.
builder.Append("select datepart(dd, datetime) as 'Day', datepart(hh,datetime) as 'Hour', count(*) as 'Count' ");
builder.Append("from @table_name with (nolock) ");
builder.Append("where datetime > @date_time ");
builder.Append("group by datepart(dd, datetime), datepart(hh, datetime) ");
builder.Append("order by datepart(dd, datetime), datepart(hh, datetime");
dateTime = DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongTimeString();
cmd.CommandType = CommandType.Text;
cmd.CommandText = builder.ToString();
cmd.Parameters.Add("@table_name", tableName);
cmd.Parameters.Add("@date_time", dateTime);
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.
View 3 Replies View Relatedcan someone plz explain to me wats a parameterized view or may be a link somewhere on the web or a few examples of parameterized view. Thanks
View 4 Replies View RelatedI'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.
View 1 Replies View RelatedIf 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?
Thanks,
My report needs to show Top (1-100) violators.... how do i put in this parameter?
Hello..
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?
Thanks in advance.
Just getting started using SSce and having a few problems
What I want to do is something like this...
Dim Code As Integer
Dim Description As String = txtDescription.Text.Trim
Dim conn As SqlCeConnection = ConnectToLocalDatabase()
Dim ssql As New System.Text.StringBuilder
ssql.AppendLine("INSERT INTO T_Titles (Description)")
ssql.AppendLine("VALUES(@Description)")
ssql.AppendLine("SELECT @Code = @@IDENTITY")
Dim cmd As New SqlCeCommand(ssql.ToString, conn)
Dim sqlCode As New SqlCeParameter("@Code", 0)
sqlCode.Direction = ParameterDirection.InputOutput
cmd.Parameters.Add(sqlCode)
cmd.Parameters.Add(New SqlCeParameter("@Description", Description))
cmd.ExecuteNonQuery()
Code = CInt(sqlCode.Value)
**********************************************************************
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.
Thanks
Hi,
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.
plz give example or link
thanks
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.
Thanks for your help.
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?
Thanks,
Aaron
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.
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("outIDlist").Value', @outIDlist
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
Alan
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?
Thanks,
Patrick Kafka
All,
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?
-Kevin
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?
Hello,
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?
Thanks!
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.
Hi,
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.
Thanks!
Adam Cataldo