The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Please help me with this if you can. I have one table with CustomerID and some other data. In other table i have CustomerID(the link with the first table) and Agent The relation of the first with the second one is ONE TO MANY. I want something like this: Customer,'Agent1,Agent2,Agent3'
I would like to concatenate a String value to an Int value using an SQL statement. At the moment it reads like this:
SELECT 'website.com/shop/product.cfm?ProductID=' + Products.ProductID AS Product_URL
But unfortunately I am getting the error: "Conversion failed when converting the varchar value 'website.com/shop/product.cfm?ProductID=' to data type int."
Any idea how to get around this at all just using an SQL query statement?
Hi all I need some help in concatenatng a string in T-SQL. Having used the Command Microsoft Access inside the 'SQL View' window and typed the following it worked perfectly.
Code Snippet
UPDATE tblValidUsers SET blocked_users = blocked_users + 'name_123@hotmail.com;' WHERE userid='Onam'
However attempting the same command in T-SQL I get the following error:
Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals text.
Reason for having this command is I want to be able to add something to the end of the field "blocked_users" without actually overwriting the fields contents.
So for instance if I had the items: "Item1, Item2, Item3" in blocked_users and I updated it with "Item4" then the value "Item4" would be added to the end thus the use of "+" is used to concatenate. Is there a way of doing this in T-SQL?
As I build a record set in an SP I need to add in a string containing a list derived from a second query. I need the results of the sub query to be presented as a single string in the first query, separated by a single space.
I have no idea how to do this in t-sql, and am doing it on the web server at the moment, but becase the dataset is quite large, I'm getting 20 - 40 second processing times which is far too long.
I have found reference to the xp_sprintf function but this is not supported by my host, so not a solution.
I'm no expert in t-sql, so I imagine there's a way somewhere, and would be grateful for any advice available.
To start, I am NOT a SQL programmer. I have to do some minimal SQL administration (DB Creation, Backups, Security) on spatial databases that are for the most part managed by a 3rd party program. My experience with T-SQL is mostly simple tasks (i.e. Select and Update statements)..However I have been requested to calculate an ID Field using the values of two other fields. Per the request, I need to convert one field to Hex and concatenate with the second field.
ex. Field 1 + Field 2(hex string) = Field 3 Field 1 = 'FF02324323' Field 2 = 'Smith Creek' Field 3 = 'FF02324323536D69746820437265656B'
Field 1 VarChar(10) (Code) Field 2 VarChar(65) (Common Name) Field 3 VarChar(max) (ResourceID)
Spent half the day searching and have tried various forms of CAST, CONVERT, fn_varbintohexstr and others but have unable to come up with the correct combination to get what I need.
I've been trying to return hex data in a way that can be concatenated. I need the actual hex info (e.g. 0x6E3C070) as displayed since it contains info about the path to a file. So I can turn it into D:6E3C 7 to get the path to the file. In searching around I have come across a way to do this but can't figure out how to get it to run through a column and either display or insert into a table multiple results.
-Here's the user function that converts an integer into a hex string-
CREATE FUNCTION udf_hex_string (@i int) RETURNS varchar(30) AS BEGIN DECLARE @vb varbinary(8) SET @vb = CONVERT(varbinary(8),@i) DECLARE @hx varchar(30) EXEC master..xp_varbintohexstr @vb, @hx OUT RETURN @hx END GO
--'PageStoreId' contains the data that needs to be converted into the editable hex string --'HexString' is where I'd like it to go so I can parse it later.
--I can run the below select and get the hex string. But am stuck on how to run a select or update that would run through the 'XPages.PagestoreId' column and insert the hex string into the 'XPages.Hexstring' column. 'XPages.PagestoreId' could have 100's of entries that need to be converted and placed in the relevant the 'XPages.Hexstring' column.
col1 Â Â Â col2 Â Â Â col3 1 Â Â Â Â Â 0 Â Â Â Â Â 0 1 Â Â Â Â Â 0 Â Â Â Â Â 1 1 Â Â Â Â Â 1 Â Â Â Â Â 1 0 Â Â Â Â Â 1 Â Â Â Â Â 0
I am expecting output asÂ
col1 Â Â Â col2 Â Â Â col3 Â Â Â NewCol 1 Â Â Â Â Â 0 Â Â Â Â Â 0 Â Â Â Â Â Â SL 1 Â Â Â Â Â 0 Â Â Â Â Â 1 Â Â Â Â Â Â SL,PL 1 Â Â Â Â Â 1 Â Â Â Â Â 1 Â Â Â Â Â Â SL,EL,PL 0 Â Â Â Â Â 1 Â Â Â Â Â 0 Â Â Â Â Â Â EL
condition if col>0 then SL else '', Â if col2>0 EL else '', if col3>0 PL else ''
I have a large table that looks like this. (ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10)) 1, 1, p12, 1, p23, 2, p34, 2, p45, 3, p56, 3, p67, 4, p78, 5, p19, 5, p210,5, p83 i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner. can anyone help? i have to use temporary tables. (not cursors-with cursors i know how to di it, but i want with temporary table) thanks in advance
i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.
ACCOU NAME     NAME TODATE                           ID    EDUCAT   EXPIRYDATE 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
Need to get it like
ACCOU NAME     NAME TODATE                           ID    EDUCAT   EXPIRYDATEstring 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.
Hi, I need to concatenate a string with an int variable on a stored procedure; however, i looks like i am lost in single and double quotes. Does any one know the right comination of quotes for this please? My Code is below: 1 @Price int 2 3 DECLARE @SqlPrice varchar(50) 4 5 if (@Price is not null) 6 7 set @sqlPrice = 'AND price' + '' > '' + '' + @Price + '' 8
I'm having trouble using paramaters in a full-text search with FormsOf(INFLECTIONAL:DECLARE @SearchWord nvarchar(4000) SET @SearchWord = 'tax' SELECT listing_id, RANK, name, address, city, zip, heading, phone FROM listings a, FREETEXTTABLE(listings, *, 'FormsOf(INFLECTIONAL, @SearchWord') WHERE [KEY] = a.listing_id ORDER BY RANK DESC, name
This returns no results. It treats the paramater as a NULL value. But this...
SELECT listing_id, RANK, name, address, city, zip, heading, phone FROM listings a, FREETEXTTABLE(listings, *, 'FormsOf(INFLECTIONAL, tax') WHERE [KEY] = a.listing_id ORDER BY RANK DESC, name
returns over 500 results. I've been banging my head against this for 2+hours. Google has been no help as every example shows the result hard-coded in like the second example.PLEASE HELP!!!!!!
Hi,I have been able to JOIN ON CONTAINS(column, 'whatever'), but can't seem toget the syntax right to join on one field containing a related field.The query below is what I am working with... As written, everything insidethe single quotes is interpreted as a literal string. I.e. "FormsOf" doesn'tact as a function, but just a bunch of letters. Any ideas?Thanks!SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ONCONTAINS(dbo.Dolch.vchWord, 'FORMSOF(INFLECTIONAL,dbo.CombinedLexicons.vchWord)')WHERE (dbo.CombinedLexicons.vchWord IS NULL)
How can I get the UserID to pass to the sql statement? The date is being passed but nothing else Thank you. My code:Public Function GetEmployeeByID(ByVal Today As Date, ByVal UserID As String) As SqlDataReaderDim dtToday As DateTime dtToday = DateTime.Today 'Create connectionDim con As New SqlConnection(_connectionString) 'Create commandDim cmd As SqlCommand = New SqlCommand()With cmd .Connection = con .CommandText = "SELECT UserID FROM ATTTblAttendance WHERE UserID = @UserID And Today = " & dtToday" -->The UserID no showing up here .CommandType = CommandType.Text 'Add Parameters.Parameters.AddWithValue("@UserID", UserID) .Parameters.AddWithValue("@Today", Today) End With 'Return DataReader con.Open()Return cmd.ExecuteReader() End Function
Help! I'm trying to figure out how to pass a string variable to my sqldatasource's "selectcommand" attribute. I'm trying to construct my SQL dynamically to adjust some things. My code is something like this, <%@ Page Language="VB" AutoEventWireup="false" CodeFile="report.aspx.vb" Inherits="_Default" EnableEventValidation="false" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><%@ Import Namespace="System.IO" %><html xmlns="http://www.w3.org/1999/xhtml"><head id="Head1" runat="server"></head><body style="font-family: Arial,Verdana; font-size: 8"> <% Dim selectCommandVar as String = "select column from mytable" %> <form runat="server"> <asp:SqlDataSource ID="SqlChild" runat="server" ConnectionString="<%$ ConnectionStrings:mynnectionString %>" ProviderName="<%$ ConnectionStrings:gmConnectionString.ProviderName %>" selectcommand="selectCommandVar"> </asp:SqlDataSource></form></body></html> What's the best way to get the contents of my selectCommandVar variable into the SelectCommand attribute of my sqldatasource? The above syntax doesn't work and I've tried several permutations that do not work either. The examples of sqldatasource I keep finding have the SQL statement hardcoded in the <asp:sqldatasource> section, which won't work for me. HELP!
We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.
My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?
Will I have to create Dynamic SQL to do this?...or... can I do something like this...
@ConcatenatedString --eg. 1,2,3,4,5,6,7
SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @ConcatenatedString
In c# - how to pass uid,pwd,dbname and servername as input parameters from vs 2003 windows application (am calling the rdl file from reporting service 2005 web service) to sql server 2005 rdl files.
Hi, I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this.
my query is:
"select username from registration where username like '" + TextBox1.Text + "%'";
It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.
I want to run an update command where the user types in a CSV value and the query runs. If I simulate 1 number it works, but if I put in 2 variable it returns nothing (but doesn't fail).
declare @SITE_ID int declare @txtSchedule varchar (500) set @SITE_ID=1 set @txtSchedule='5,6' select * from Schedules WHERE SITE_ID=@SITE_ID and WEEK IN(@txtSchedule .
We have 2 SQL Server 2005 machines. One has the CTP version installed,the other is the RTM.When running the query below, results are returned for the CTP versionbut not the RTM version.select * from table t1inner join containstable(table, column, 'formsof(inflectional,''bücher'')') t2on t1.[id] = t2.[key]I have checked that the same data is present in the tables on bothmachines and also that the FTI has been fully populated with a statusof "idle".Please note that the word used "bücher" is a German word and shouldreturn "buch" as a match.Is this a bug with the RTM version of SQL Server 2005??
In my .NET app I have a search user control. The search control allows the user to pick a series of different data elements in order to further refine your display results. Typically I store the values select in a VarChar(5000) field in the DB. One of the items I recently incorporated into the search tool is a userID (GUID) of the person handling the customer. When I go to pass the selected value to the stored proc
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = strCriteria; I get: Failed to convert parameter value from a String to a Guid. Ugh! It's a string, dummy!!! I have even tried: objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString(); and
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString("B"); but to no avail. How can I pass this to the stored proc without regard for it being a GUID in my app?
Hey folks, the question is fairly simple, unfortunately the answer has proven rather elusive.
Is it possible to declare a variable which would then be used to identify either a column or table in an SQL statement?
Here's a basic idea of what I'd like to do:
DECLARE @myVar AS NVARCHAR(50)
SELECT * FROM @myVar
or
DECLARE @myVar AS NVARCHAR(50)
SELECT @myVar FROM MyTable
I'm probably looking for some sort of built in function that will accept an argument here... like COLUMN(@myVar) or something of the like. I just don't know where to look...
Hi, Using SSIS 2005 how is it possible to loop through a folder on the network, look at each file, pass the data inside each file as a string into a stored procedure. Thanks
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 apologize in advance for posting yet another connection failure issue. I went through quite a few posts and could not find the actual answer so here is the issue. I have a main SSIS package to call five other packages. This seems to work fine in my BIDS workstation; however, when I copied it, including the bat file that was created by dtexecui utility, to the production environment (which runs on 64 bit - probably has nothing to do with the 64 bit platform), the main package executes fine but the child packages failed with this error: Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote conne ctions.".
It seems that the child packages are still using the old connection strings from my workstation. My question is how can I pass the connection string from the batch file to all the child packages that the parent (main) package is using.
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005! I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string. I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why? create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max) as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
