Hello,
How do I pass a string parameter to a Stored Procedure with an 'in' clause?
ex.
Select * FROM Persons
WHERE PersonID IN (1,2,3,4,5,20,56,80)
How do I define my Store Procedure so I can pass the values between () as a string (nvarchar) parameter to the SqlCommand?
Thanks,
W
Is it possible to pass a report parameter that is defined as a string to the following SQL statement that is using an "IN" clause ?
WHERE (ANALYST.User_Bemsid IN (@Report_Parameter_Bemsid))
If I pass a single value (I.E. A) it works okay, but once I try to pass multiple values (I.E. A,B or 'A','B') it returns no data.
Using Crystal reports I can pass multiple values via a report prompt into the SQL "IN" clause and seems that SQL Reporting Services should also have this feature. What do I need to do to get it working ?
Thanks for any help...
Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A in the dbif radiobutton value select = 2, it will select from column B in the dband so on... Am i attempting to do the impossible?Thanks All,
View 4 Replies View RelatedHi,
I have a requirement where i have to pass OrderBy clause to an SP as an parameter. I dont want to use dynamic sql query so i am running into problem.
Below is my procedure..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[USP_GetData]
@sortBy varchar(100),
as
BEGIN
SELECT *
FROM
(SELECT CC.C1, CC.C2, CC.C3, CC.C4,
ROW_NUMBER() OVER(ORDER BY @sortBy) as RowNum
FROM T1 CC
) as e
END
The thing is, if i execute the procedure as
EXEC USP_Get_Data ('C1 asc'), it runs without any error but it gives me unsorted result.
I am not able to figure out why.
Any help in this will be appriciated.
Regards,
Salim.
Hi everyone I am new to this site I have a major issue I cant figure out seeing how im fairly new to asp, and vb, but i have 5 years php mysql experience.
Im pulling the correct data into a grid. Then i need to make a button or some sort of link that will take the value of one field in the record set and replace it with @transid in the where statement I can enter in the value of transid into form field with that name and it will run the rest of the script correctly, I just cant get past this hurdle. If anyone can help that would be great. I tried to get this to work with java script but then realized thats not possible to transfer varaibles to asp from it.
///javascript
function DisplayReciept(transactionnum)
{
recieptdis = transactionnum;
}
////field in grid
<asp:BoundField htmlEncode=false DataFormatString="<a href=javascript:DisplayReciept{0}>Display</a>" DataField="transid" HeaderText="Show Reciept" SortExpression="transid" />
//////////////query//////////////
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbsgcConnectionString %>"
SelectCommand="SELECT [fulldata] FROM [data] WHERE ([transid] = @transid)">
<SelectParameters>
<asp:FormParameter FormField="transid" Name="transid" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Hi All :)
I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @CustCode). The parameter is passed using a DECommand object in VB6.
I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@CustCode1, @CustCode2,...etc), but do not want to limit the number of customers.
If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.
I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:
KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)
and
'KA1001', 'KA1002'
Both fail :(
Any ideas?
Regards
Xo
I have a stored proc that I am trying to use for sorting and paging below is a portion of it. When I hard code "Stan" into the Where clause I get all the records back that where posted by "Stan" but when I try to pass in "Stan" as a variable it tells me something about an invalid column name.
Here is the working version. When I hard code "Stan" into the Where Clause it works. I am using 2 single quotes on each side. See Where clause.
SET @sql ='SELECT [ThreadName],[PostID],[PostTypeID],[LanguageID],[PostAccessID],[UserID],[ThreadID],[PostParentID],[VoteSummaryID],
[Subject],[Body],[PostAuthor],[PostDate],[IsApproved],[TotalViews],[FormattedBody],[IPAddress],[PostCount],[ArticleCount],
[TrackbackCount],[IsSticky],[StickyDate]
FROM
(SELECT t.[ThreadName], p.[PostID],p.[PostTypeID],p.[LanguageID],p.[PostAccessID],p.[UserID],p.[ThreadID],p.[PostParentID],p.[VoteSummaryID],
p.[Subject],p.[Body],p.[PostAuthor],p.[PostDate],p.[IsApproved],p.[TotalViews],p.[FormattedBody],p.[IPAddress],p.[PostCount],p.[ArticleCount],
p.[TrackbackCount],p.[IsSticky],p.[StickyDate],ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
FROM syl_Posts p
INNER JOIN syl_Threads t ON t.[ThreadID] = p.[ThreadID]
WHERE t.[PostAuthor] = ''Stan'')
AS syl_TPInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'
Not working when I try to pass in a variable into the Where clause.
SET @sql ='SELECT [ThreadName],[PostID],[PostTypeID],[LanguageID],[PostAccessID],[UserID],[ThreadID],[PostParentID],[VoteSummaryID],
[Subject],[Body],[PostAuthor],[PostDate],[IsApproved],[TotalViews],[FormattedBody],[IPAddress],[PostCount],[ArticleCount],
[TrackbackCount],[IsSticky],[StickyDate]
FROM
(SELECT t.[ThreadName], p.[PostID],p.[PostTypeID],p.[LanguageID],p.[PostAccessID],p.[UserID],p.[ThreadID],p.[PostParentID],p.[VoteSummaryID],
p.[Subject],p.[Body],p.[PostAuthor],p.[PostDate],p.[IsApproved],p.[TotalViews],p.[FormattedBody],p.[IPAddress],p.[PostCount],p.[ArticleCount],
p.[TrackbackCount],p.[IsSticky],p.[StickyDate],ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
FROM syl_Posts p
INNER JOIN syl_Threads t ON t.[ThreadID] = p.[ThreadID]
WHERE t.[PostAuthor] = ' + @PostAuthor + ')
AS syl_TPInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'
I have a table that looks like this:
RecordId
PictureId
KeywordId
111
212
313
421
522
623
725
817
932
1044
I need to run a query where I pass in an unknown number of KeywordIds that returns the PictureId. The 'IN' clause will not work because if a KeyWordId gets passed into the Stored Procudure the PictureId must have a record with each KeyWordId being passed in. For example, lets say you need to see the result of all PictureIds that have both 1 and 2, the correct result set should only be PictureId 1 and PictureId 2.
Im going crazy trying to find a simple solution for this. Please advise.
Table : incident
----------------
incident_id usr_id item_id Inc_Date
10059926 191 61006 8-22-2015
10054444 222 3232 6-7-2015
Table: act_reg
--------------
act_reg_id act_type_id incident_id usr_id act_type_sc
454244 1 10059926 191 ASSIGN
471938 115 10059926 191 TRAVEL TIME
473379 40 10059926 191 FOLLOW UP
477652 115 10059926 191 TRAVEL TIME
489091 504 10059926 191 ADD_ATTCHMNTS
477653 504 10054444 222 ADD_ATTCHMNTSParameter: @attach (value=1, Label=Yes & Value=0, Label=No)
Result (While I am selecting 'Yes' in dropdown)
----------------------------------------------
incident_id usr_id item_id
10059926 191 61006
10054444 222 3232
SELECT incident.incident_id,incident.usr_id,incident.item_id
FROM incident
where exists (How i can write query here to check the act_type_sc=ADD_ATTCHMNTS is exists)
Hi,
i m tring to pass my xml string to my sql server but i receive empty value even tho my xml have it's value.
here's my code,
insert user(userName)
select xml_user
from OPENXML(@xmlHandle,'/NewDataSet',1)
with (xml_user nvarchar(20) 'table/user_name')
my XML code
<NewDataSet>
<table>
<user_name>dorris</user_name>
</table>
</NewDataSet>
Please advice on this matter.Thanks
hi there,
i am trying to pass a string which contains a string,
here is the code which is wrong :
{string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)";
OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString());
cmd.Parameters.Add(p1);
}
the problem is that the sql compailer doesnt take the parameter (@p1) as a string
if someone could help me with that it would be great ! tnx
Hey,I've inherited a project from my office and am stuck.I'm trying to take input from multiple souces (DropDownLists, TextBoxes, etc) and depending on which ones are used, update a SELECT string with additional AND statements. <script language=VB runat=server>
Dim resultsql As String
Public Sub Button_Click(ByVal client As String, ByVal state As String)
Dim dv As String
resultsql = resultsql & "SELECT ClientName, Address1, City, State FROM tblClient"
dv &= ""
If (StrComp(client, dv) <> 0) Then
resultsql &= "AND ClientName = " & client
End If
If (StrComp(state, dv) <> 0) Then
resultsql &= "AND State = " & state
End If
resultsql &= " ORDER BY ClientName ASC"
End Sub
</script>Now when I got to display the results of this new string in a GridView I am recieving errors trying to pass my variable "resultsql" into SelectCommand. <asp:GridView ID="Results" runat="server" AutoGenerateColumns="False" DataKeyNames="ClientNumber"
DataSourceID="SqlDataSource3">
<Columns>
<asp:BoundField DataField="ClientName" HeaderText="ClientName" SortExpression="ClientName" />
<asp:BoundField DataField="Address1" HeaderText="Address1" SortExpression="Address1" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:SQLConnectionString %>"
SelectCommand=resultsql> </asp:SqlDataSource> I've scoured the web without any success. Any suggestions are appreciated.
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
View 1 Replies View Related
Hello
I am pretty new to reporting services. Now i have something like this. on the .cs file, i have a fullName lets say
String fullName = "John Smith",
Now I want to pass this string to the report's textBox and show this value on this textbox.
How can i do this?
1.How should i write in .cs file?
2. How should i set the expression for the textbox?
Thank you very much. It is very urgent! Please help!
I thought I would post this solution. I searched a long time and didnt see anything about how to solve my problem. After avoiding this and simply building strings I decided to dig in my heels and try and figure this out. Well, maybe I am just slow. :) Anyhow, here is some code that should help a lot of folks with this question...
Function GetProductCategories(ByVal departmentID) As DataSet
'set the connection string (comes from a property in this case)
Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringWeb"))
'set the sql text string notice the @DepartmentID is my parameter. protected from sql injections
Dim strSQL As String = "SELECT * FROM ProductCategory WHERE DepartmentID = @DepartmentID"
'set new command object to strsql and the connection. required
Dim command As New SqlCommand(strSQL, connection)
'set parameters to pass to through with the strsql. required for parameters. you can take this a set further. See commented fields below.
command.Parameters.Add(New SqlParameter("@DepartmentID", departmentID))
'additional set up for parameters if you like...
'command.Parameters.Add("@departmentID", SqlDbType.Int, 4)
'command.Parameters("@departmentID").Value = departmentID
'set SQLDataAdapter to your previously created command object
'this enables your adapter access to your strSQL, connection and parameters
Dim da As New SqlDataAdapter(command)
'set proper name of table for data set based upon departmentID
If departmentID = 1 Then
Dim ds As New DataSet()
da.Fill(ds, "dtCarriers")
Return ds
End If
'set proper name of table for data set based upon departmentID
If departmentID = 2 Then
Dim ds As New DataSet()
da.Fill(ds, "dtProducts")
Return ds
End If
End Function
Hello, I'm trying to pass a simple string value to a stored procedure and just can't seem to make it work. I'm baffled since I know how to pass an integer to a stored procedure. In the example below, I don't get any compile errors or page load errors but my repeater doesn't populate (even though I know for certain the word "hello" is actually in the BlogTxt field in the db. If I change the stored procedure to say...WHERE BlogTxt LIKE '%hello%'then the results do indeed show up in the repeater.I ultimately would like to pass text from a textbox control or maybe even a querystring to the stored procedure. Then I'll move on to passing multiple "keywords" to it. :)My relevant code is below. Thanks in advance for any help.*******************ViewData.ascx.vb file*******************Private strSearch As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadTry Dim objBlogController As New BlogController 'for testing purposes strSearch = "hello" repeaterSearchResults.DataSource = objBlogController.SearchBlog(strSearch) repeaterSearchResults.DataBind()Catch exc As Exception ProcessModuleLoadException(Me, exc)End TryEnd Sub----------------------------------------
*******************Controller.vb file*******************
Public Function SearchBlog(ByVal strSearch As String) As ArrayList Return CBO.FillCollection(DataProvider.Instance().SearchBlog(strSearch), GetType(BlogInfo)) End Function----------------------------------------
******************* DataProvider.vb file*******************
Public MustOverride Function SearchBlog(ByVal strSearch As String) As IDataReader----------------------------------------
*******************SqlDataProvider.vb file*******************
Public Overrides Function SearchBlog(ByVal strSearch As String) As IDataReader Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SearchBlog", strSearch), IDataReader)End Function----------------------------------------
*******************Stored Procedure*******************
CREATE PROCEDURE dbo.SearchBlog @strSearch varchar(8000)AS
SELECT ItemID, PortalID, ModuleID, UserID, BlogTxt, DateAdd, DateModFROM BlogWHERE BlogTxt LIKE '%@strSearch%'GO----------------------------------------
I have created a function with:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
WHERE item = @item AND week = @week
to work correctly please?
WHERE item = '@item' AND week = @week
won't work and
WHERE item = @item AND week = @week
won't work.
I want to do something like this in a stored proc:------Create Procedure dbo.GetPatients@PatientIdList varchar(200) -- comma separated list of PatientIDsAsSelect *From PatientsWhere PatientId In (@PatientIdList)------I know the above won't work, but of course what I want is if@PatientIdList = '1,2,3' then I want Patient records with PatientIds1, 2, and 3 returned.It looks like the only way to do this is to build the SQL statement asa string within the stored procedure ... which pretty much defeats theusefulness of using precompiled sprocs as I understand it (better offbuilding a dynamic query against a View in that case).Thoughts?Joel Thornton ~ <groups@joelpt.eml.cc>
View 1 Replies View Related
exec sp_SearchProductAdvanced "(name LIKE '%a%' Or name LIKE '%b%' Or name LIKE '%c%' Or name LIKE '%d%' Or name LIKE '%e%' Or name LIKE '%f%')and (salesprice between 3 and 10) ",null
after executing i got this error:
The identifier that starts with '(name LIKE '%a%' Or name LIKE '%b%' Or name LIKE '%c%' Or name LIKE '%d%' Or name LIKE '%e%' Or name LIKE '%f%')and (salesprice ' is too long. Maximum length is 128.
how can i solve this problem. so that i can use more values .
my lenght exceeds 128. is there any way to get rid of this.
in my SP i have declared the parameter variable as
@criteria nvarchar(1000)
Hi,
I have a stored proc which accepts a varchar(255) as a parameter and when I call the proc using a concatenised string I get an error i.e.
-- Proc
CREATE PROCEDURE #proc_param_test
@p_param1 varchar(40) = NULL
, @p_param2 varchar(40) = NULL
AS
BEGIN
SELECT @p_param1, @p_param2
END
EXEC #proc_param_test 'test', 'test 2'
returns
---------------------------------------- ----------------------------------------
test test 2
but EXEC #proc_param_test 'test', 'test 2' + ' - the rest'
gives a Incorrect syntax near '+'. error
The solution must be a real doddle but it's a 'mare to find anywhere.
Cheers,
John
I have a multivalued parameters populated from a dataset with country codes: US,CA,HK etc... I tried to pass the parameter to the stored procedured but that did not work. I did some research and found out that I won't be able to do that.
Writing Queries that Map to Multivalued Report Parameters
http://msdn2.microsoft.com/en-us/library/ms155917.aspx
You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN statement to specify the parameter.
If I am not able to pass the parameter that way, what is the best way to accomplish my goal?
Thanks,
Elie
Hello, I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product. I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here: http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:
WHERE (OrderStatus IN ((select value from dbo.fn_Split(@StatusList,','))) OR @StatusList IS NULL) but now I don't know how to assign the string value to the parameter, say to '@solution_id'.my current select statement which was generated by Visual Studio 2005 looks like this: SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @solution_ID) ...but this only pulls results for the first item checked in the checkboxlist.Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.
Ok, so I'm a JSP guy and thing it should be easy to replace "@color" with t_color after I initialized it to red by String t_color = "red";and then calling the insert SqlDataSource1.Insert();here is insert command: InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" I've tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "+ t_color+")" Ive tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "<%$ t_color %>" )" Is there any easy way to do this? or Can I set it like @color = t_color? Thanks in advance for ANY help JSP turning ASP (Maybe)Dan
View 4 Replies View RelatedI want to declare a variable that will serve as my where clause. The variable will be passed in from our website. Does anyone have any information or can guide me in the right direction to do this?
example:
@variable varchar(1000)
SELECT *
FROM table
WHERE @variable
(The @variable would reflect the where clause string)
Thanks!
I want to bring a string parameter to where clause like this:
select .. from ...
Where Code IN (@Code)
When @Code is 'ABC' it works but when @code is 'ABC, XYZ' it won't work. I know for IN statement we should use IN ('ABC', 'XYZ') and I tried make @code = '''ABC'', ''XYZ''' still not working. I tried
Print '''ABC'', ''XYZ'''
I got 'ABC', 'XYZ' but why it does not work in @Code?
Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.
I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?
Thanks.
More of a general "Streams" question than broker specific, but this is all being done in the context of Broker passing the messages around. The use of Streams & Encoding seems to be my problem and I'm not as familiar with Streams as I am with other areas of the Framework... Any advice would be appreciated.
At this point, I've created my own objects/stored procedures based loosely on the ServiceBrokerInterface class provided in the SQL Server samples. Some of this was done for simplification and as a learning exercise, but also to ensure that all of the SQL operations are being done via Stored Procedures and not inline SQL. This was done to adhere to our existing security policy used on this project.
In this "interface" I've built, I have a [BrokerMessage.cs] class which is meant to have a few additional pieces of functionality beyond what the MS provided version had supplied.
1st... A constructor for accepting either String or XmlDocument as the "content"
2nd... Methods to return either a XmlDocument or a simple String.
Since all of the Broker functionality is defined as using VARBINARY(MAX) in my stored procedures, I don't believe I have any problems at that level. It's simply a binary blob to Broker.
In my constructor for accepting String or XmlDocuments, I attempted to use the following...
public BrokerMessage(string type, XmlDocument contents)
{
m_type = type;
m_contents = new MemoryStream(EncodingProvider.GetBytes(contents.InnerXml));
}
My understanding was that MemoryStream is derived from Stream so I can implicitly cast it. The "EncodingProvider" is a static member set as follows:
public static Encoding EncodingProvider = Encoding.Unicode;
This way I ensure that internal & external code can all be set to use the same encoding and easily changed if necessary. I was hoping to avoid using Unicode since the rest of the application does not require it, but from my understanding all Xml documents in SQL Server are Unicode based, so this should be a better encoding choice for any processing that may potentially occur within SQL Server itself.
In my methods to return the various forms of the "Stream", I have the following code... The ToBytes() method is what is used to pass intot he stored procedure parameter that is defined as VarBinary and expecting a byte array. One area of concern is that the Read method accepts an INT for the length, but the actual Length property is a LONG. I'm sure there's a better way to handle this and I would welcome any advise there.
/// <summary>
/// Used to convert from a Stream back to a simple Byte array.
/// </summary>
/// <returns></returns>
public virtual byte[] ToBytes()
{
byte[] results = new byte[this.Contents.Length];
this.Contents.Read(results, 0, (int)this.Contents.Length);
return results;
}
/// <summary>
/// Used to convert from a Stream back to a simple String.
/// </summary>
/// <returns></returns>
public new string ToString()
{
byte[] buffer = this.ToBytes();
String results = EncodingProvider.GetString(buffer);
return results;
}
/// <summary>
/// Used to convert from a Stream back to a simple XmlDocument.
/// </summary>
/// <returns></returns>
public virtual XmlDocument ToXmlDocument()
{
XmlDocument results = new XmlDocument();
results.InnerText = this.ToString();
return results;
}
Hello,
I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.
Thanks a lot for any feedback!
I am trying to write where clause after pivot but I could not know how?
DECLARE @PivotQuery AS NVARCHAR(MAX)
I am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.
Thanks for any help! Aaron B.
i defined 3 pkg scope user variables of type string in the ssis variables window, typed null as their value, and tried passing them (thru exec sql task) to an sp who expects 3 varchar(23) params.
The sp is blowing up because their values arent null.
The sql task command reads exec sp_name ?,?,?. In the sql task editor's param mapping window, I have each param listed with direction "input", data type varchar and the individual sp params in the parameter name column. I think the plumbing is set up correctly because I'm fine when I send the System Variable StartTime as a 4th param to the sp with data type DATE on the ssis end and datetime on the sp end.
Does anyone know if ssis string and engine varchar are incompatible or perhaps if null in the variables window doesnt initialize variables?
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
[Code] ....
Now I have to pass multiple param values to it. I was trying this but didnt get any success
exec
[SP_test]'LOC','LOP'
I have create packages which loads the data from flat file to sql server table, now I want to make my destination table connection dynamic what is format of connection string. I also need to pass user name and password for sql server dynamically in this case, what is the format for the connection string.
Also  in package i used ADO.net  as source for  *.mdb files how i can set the commection to .mdb files dynamically which is used as source in my package.