Identifier! [problem In Passing A Lengthy String To An SP]
Sep 5, 2006
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
Hi, I test the following sql statement, finding that an error ocurs:
Msg 7630, Level 15, State 2, Line 3 Syntax error near '"' in the full-text search condition '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfds'.
DECLARE @searchTerm NVARCHAR(40) SET @searchTerm = '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf"'; SELECT [JobTitle], [JobDes], [OpenDate], j.[URLRef], c.[CompanyName], c.[URLRef], c.[URLSource] FROM JobWanted AS j INNER JOIN Company AS c ON c.CompanyID = j.CompanyID WHERE CONTAINS((JobTitle, JobDes), @searchTerm)
It seems too lengthy string will cause an error for full-text engine. I find the sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf is truncated as shown in error message.
How to avoid this issue? Could I configre this limination?
I have a function in VBA (Access) that passes a sql string into a passthrough query that is server side executed
I changed the inner joins and left joins to where statements but this particular sql string crashes with the titled error folowed by the multi-part identifier "task_backup.activityid" could not be bound (#4104) and then it repeats that one more time in the same message
Which i think the second "sub message" is for the null criteria
INSERT INTO NewTasksBeingAdded ( [Activity ID], [WBS Code], [Activity Name], Area, Line) SELECT [TASK Excel Data].[Activity ID],[TASK Excel Data].[WBS Code], [TASK Excel Data].[Activity Name], [TASK Excel Data].Area, [TASK Excel Data].Line FROM [TASK Excel Data] where [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID] and TASK_BackUP.[Activity ID] Is Null
I am wanting to get the job name based on sys.sysProcesses.[Program_name] column. Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?
SELECTCASE WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name ELSE RTRIM([program_name]) END ProgramName , Val1.UqID , Val1.UqIDStr
I am working on report where I am using a table to disply my data.There is a column called "Control Description" , data for this column can be of max 300 characters. And my report is pretty big it has 22 coulmns so each column's size is very less. Right now height of the cell is increasing to display the whole 300 char text it looks very odd.
I want to know if this is possible? when the report is displayed to user I can only show portion of the text in the tabel's cell and if user wants to see the whole thing he/she can click on it(or roll over mouse on it) and it would display entire text?
I'm working on a stored procedure, and I'm nearing completion. The procedure takes a while to run (it's a very large dataset), but that's not my issue. The problem is that I include several print statements, but I don't get to see any of them until the entire procedure has finished. Is there anything I can do to be able to see them as they are printed? Using sql server 2000 with management studio for 2005.
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
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
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----------------------------------------
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
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>
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
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
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 ?
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?
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.
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.
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.
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?
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.
I'm running into this error message when passing in a few records in particular to a function, the only difference I could find is that these recods have about 60k characters on the field that I'm passing to a function.
is there a max lenght for passing to a function?
select function ( field) as results
It's been working fine until today and all of the related fields are declared as nvarchar(max)
Does anyone know the column name for the system row identifier. For example Informix uses rowid, for Ingres its tid, in MS SQL server its _______ (fill in the blank).
I'm trying to create a unique identifier number that meets the following criteria. The unique identifier needs to be a concatenation of two values submitted from a form and the identity value (primary key) for the new record that is inserted into the database.
So, if the first field is the year and the second field is a objnumber, the unique identifier number would have the format: ("YR" + "objnumber" + primary key value), where the year and object number are what the user selected in the form.
I have a stored procedure that I use to handle the insert, which also returns @@identity for the purpose of passing that value into another stored procedure that inserts child records.
So, within my stored procedure, is there a way I can create the unique identifier number and return that value back to the application? I'm not sure how to accomplish this?
