I was having some issues with converting this @BeginDate(which is passed in as a datetime) correctly and someone suggested the syntax below. It enloses it in triple single quotes. That seemed to work, but it also seems to work with single quotes too. Can someone explain the use of triple single quotes in stored procedures?
I had a procdure in SQL 7.0 in which I am using both single quote and double quotes for string values. This proceudreused to work fine in SQL 7.0 but when I upgraded SQL 7.0 to SQL 2000, this proceudre stopped working. When I changed the double quotes to single quotes, it worked fine.
I have 2 SQL 6.5 databases on separate servers. Server A replicates a text field into a table on server B.
On server A the field contains text similar to THIS IS FRED'S HOUSE. After replication to Server B it looks like THIS IS FRED''S HOUSE. The distribution database also has it as THIS IS FRED''S HOUSE. Using ODBC trace I cannot see the value being passed in the text field as it is displayed as a question mark e.g. ?.
I am having a problem with updating a password within a table. Below is the old and new password that needs to be updated. Old2"3("-"3#@(%P="''_DD@D* New :"I+)#]*1#E,%R-''7^,TEA$X&^O_-68DIK,@
Below is the query that I am trying to run. I believe my problem is becuase of the Single Quotes found within both the old and new password.
I have tried to escape the single quotes, @local_variable, CHAR(39) and other ways of playing with the string. I cannot seem to find a solution.
If you were to ignore the update statement, the SELECT statement would return no results even though the PRINT statement displays the required data. This is what baffles me and why I cannot run the UPDATE statement, becuase of course, nothing would be updated since no results were returned.
-- 2"3("-"3#@(%P="''_DD@D* ///// The old password.
SET @OLD_PASSWORD = '2"3("-"3#@(%P="' + CHAR(39) + CHAR(39) + '_DD@D*'
-- :"I+)#]*1#E,%R-''7^,TEA$X&^O_-68DIK,@ ///// The new password.
SET @NEW_PASSWORD = ':"I+)#]*1#E,%R-' + CHAR(39) + CHAR(39) + '7^,TEA$X&^O_-68DIK,@'
PRINT @OLD_PASSWORD PRINT @NEW_PASSWORD
SELECT * FROM Credentials WHERE Is_Active = 1 AND [Password] = @OLD_PASSWORD
/* Attempt to update the password table. */ UPDATE Credentials SET [Password] = @NEW_PASSWORD WHERE [ID] IN (SELECT [ID] FROM Credentials WHERE Is_Active = 1 AND [Password] = @OLD_PASSWORD )
When users enter text into a textbox, to be INSERTed into my table, SQL Server throws an error if their text contains a single quote. For example, if they enter "It's great!" then it causes this error:Error: Incorrect syntax near 's'. Unclosed quotation mark after the character string ''. How can I allow text with single quotes to be inserted into the table?
My code results in SQL statements like the following one - and it gives an error because of the extra single-quotes in 'it's great': UPDATE Comments SET Comment='it's great' WHERE UserID='joe' AND GameID='503' Here's the error I get when I try this code in SQL Server: Msg 102, Level 15, State 1, Line 1Incorrect syntax near 's'.Msg 105, Level 15, State 1, Line 1Unclosed quotation mark after the character string ''. I need to know how I can insert a string such as 'it's great' - how do I deal with the extra quotes issue? is there a way to ecape it like this 'it/'s great' ? This doesn't seem to work. Here's the code that generates the SQL. I'm using a FCKeditor box instead of a TextBox, but I got the same error when I was using the TextBox: string strUpdate = "UPDATE Comments SET Comment='";strUpdate = strUpdate + FCKeditor1.Value;//strUpdate = strUpdate + ThisUserCommentTextBox.Text;strUpdate = strUpdate + "' WHERE UserID='";strUpdate = strUpdate + (string)Session["UserID"];strUpdate = strUpdate + "'";strUpdate = strUpdate + " AND GameID='";strUpdate = strUpdate + Request.QueryString["GameID"];strUpdate = strUpdate + "'"; SqlConnection myConnection = new SqlConnection(...);SqlCommand myCommand = new SqlCommand(strUpdate, myConnection); try{myCommand.Connection.Open();myCommand.ExecuteNonQuery();}catch (SqlException ex){ErrorLabel.Text = "Error: " + ex.Message; }finally{myCommand.Connection.Close();}
I'm using SQL Server 2005 and ASP.NET 2.0 Much thanks
I'm trying to build a dynamic sql statement in a stored procedurre and can't get the like statement to work. My problem is with the single quote. for example LIKE '%@searchvalue%'. This works fine: select * from view_searchprinters where serialnumber like '%012%' But I can't figure out how to create this in a stored procedure. What syntax should I use for the line in bold?SET NOCOUNT ON;DECLARE @sn varchar(50)SET @sn = N'012'DECLARE @sql nvarchar(4000)SELECT @sql = 'select top 10 * from view_searchprinters' + ' WHERE 1 = 1 'SELECT @sql = @sql + ' 'IF @sn IS NOT NULL SELECT @sql = @sql + 'and serialnumber LIKE ''' + '%' + '@sn' + '%' + ''' 'EXEC sp_executesql @sql, N'@sn varchar(50)', @sn
I have a problem with inserting a string with single quotes. For instance, string testme = "we don't have anything"; insert into tableone (buff) values ("'" + testme + "'"); I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks. blumonde
I have a problem with inserting a string with single quotes. For instance, string testme = "we don't have anything"; insert into tableone (buff) values ("'" + testme + "'"); I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it is okay. Is is a bug in sql 2005? Please help. Thanks. blumonde
I'm constructing a SQL string that needs single quotes in the WHERE clause. How do I encapsulate them in a string variable. I looked into ESCAPE and SET QUOTED_IDENTIFIER, but i don't really see any examples using string Concatenation. I'm trying to filter out the zls (0 length strings)
I'm trying to pass through a SQL statement to an Oracle database usingOPENROWSET. My problem is that I'm not sure of the exact syntax I needto use when the SQL statement itself contains single quotes.Unfortunately, OPENROWSET doesn't allow me to use parameters so I can'tget around the problem by assigning the SQL statement to a parameter oftype varchar or nvarchar as inSELECT *FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',@chvSQL)I tried doubling the single quotes as inSELECT *FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword','SELECT *FROM AWHERE DateCol > To_Date(''2002-12-01'', ''yyyy-mm-dd'')')But that didn't work. Is there a way out of this?Thanks,Bill E.Hollywood, FL
Hi,Don't worry about the vars, they are defined,the following line give me an err of "Incorrect syntax near '.'."Goal: to rename nonstardard column name.EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')','COLUMN';Thanks.
SELECT * FROM sale WHERE id =1 AND caldate >='10/1/2015' AND caldate <='10/31/2015' and I don't know how to write this in SP CREATE PROCEDURE [dbo].[sale] @id int,
[Code] ...
Is this a correct way to enclose a variable in single quotes?
Hi All, I am facing quotes problem. Without using the quotes my query is running fine, but I need to use IIF condition so for that I need quotes adjustment. I didn't figured it out how to adjust them, try several techniques but no success. I am using dotnetnuke. {IIF,"[frmradio,form]=text"," SELECT Docs.FileName, Dept_LegalLaw.MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, Dept_LegalLaw.LawDate, Dept_LegalLaw.Agreement, Dept_LegalLaw.Name, Dept_LegalLaw.NameSearch, Dept_LegalLawType.LawType, Dept_LegalLaw.LawNo, Dept_LegalMinistries.RegID, Dept_LegalLaw.IssueNo, Dept_LegalLaw.Attachment, Dept_LegalLaw.Amendment, Dept_LegalLaw.Scanned, Dept_LegalLaw.Html, Dept_LegalMinistries.Description FROM OPENQUERY(LEGALDBSERVER, 'SELECT Filename FROM SCOPE() WHERE Contains('" @FilterAnyWrd ")' ) AS Docs INNER JOIN Dept_LegalLaw ON Docs.FileName = Dept_LegalLaw.FileName INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID ", " "} {IIF,"'[frmradio,form]'='title'"," SELECT MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, LawDate, Agreement, Name, NameSearch, Dept_LegalLawType.LawType, LawNo, Dept_LegalMinistries.RegID, IssueNo, Attachment, Amendment, Scanned, Html, Dept_LegalMinistries.Description, Dept_LegalLaw.FileName FROM Dept_LegalLaw LEFT JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID COLLATE DATABASE_DEFAULT = Dept_LegalMinistries.RegID COLLATE DATABASE_DEFAULT INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID COLLATE DATABASE_DEFAULT = Dept_LegalLawType.LawID COLLATE DATABASE_DEFAULT WHERE @FilterLawNo AND @FilterLawID AND @FilterRegID AND @FilterIssueNo AND @FilterFromDate AND @FilterToDate AND @FilterNtContNew AND @FilterAgreement AND @FilterAllWrdNew AND @FilterExWrdNew AND @FilterAnyWrdNew ORDER BY [SORTTAG] ", " "} Thanks for any help
Hi: Got a newbie question that's been giving me fits! Basically I'm replicating what's going on here on this board...creating a "posting" interface that takes the "message" and inserts it into a table using an ADODB connection (using INSERT INTO table name,tablecells and VALUES)
However, if someone types in a single or double quote in the body of the message, I get an error similar to this:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'.
/test.asp, line 29
I think I understand why it's happening (SQL is interpreting the quote mark as a string-end), but what am I supposed to do to get around it?
I have this pivot table (I only post the static version as the problem only regards the single quotes)
SELECT * from( select DATEPART(year,DeliverydatePackingSlip) as Year, CASE WHEN DiffPromiseDateFirst < 0 Then '1 - too early' WHEN DiffPromiseDateFirst = 0 Then '2 - on time' ELSE '3 - too late' END as Delivery from iq4bisprocess.FactOTDCustomer WHERE OTD_Exclusion = 0)a PIVOT ( COUNT(Year) For Year
in ([2012],[2013],[2014],[2015])) as pvtNow, packing everything in a string parameter I always stumble over the single quotes. I tried to replace them with CHAR(39), I tried to define a parameter for each occurrence, but always get a syntax error. What am I doing wrong?declare @sql nvarchar(max)
declare @title1 nvarchar(20) declare @title2 nvarchar(20) declare @title3 nvarchar(20) set @title1 = '1 - too early' set @title2 = '2 - on time' set @title3 = '3 - too late'
[Code] .....
exec sp_executesql @sqlThis would throw:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'early'.
I've got a flat file data source, that is to large to edit with most Windows apps on my server that contains both single and double quote characters that I need to load in a varchar column.
So I attempted to do it with a Replace in data transformation, but I can't get SSIS to allow me to use a variable or pair of single or double quotes within the replace.
If I don't replace the single quote characters with a pair then the records containing these characters all end up in my failed records output file.
Here are 5 example property legal descriptions from my FLAT FILE data source:
COM 441'6" N OF SW/C OF NW4 OF SEC 22-29-20 ELY1340' N200' CROSSING THE CNTR OF TR AT 100 WLY1240' S200' TO POB CONTAINING 6 3/10 ACRE MOL
N 50' OF S 330' OF W 122' OF E 735' OF SW4 OF NE4 OF SEC 28/28/18 A/K/A LOT
I've a weird problem in my application. In of the pages, while trying to update the text box "Name", when I enter Linda's test, it gets saved as Linda''s test. I'm not sure if this is a problem due to SQL server. When I look at the stored procedure, I don't anything different. Also, when I update the table directly in SQL Server, the result is displayed in single quote. But if I update the field thro' the application, the returned name is with double quotes instead of single quote. Has any of you faced problems like this? What am I missing? What do I need to do to get the name saved the way I entered (with single quotes) instead of double quotes?
I have a table with several fields. I would like to ensure that that each triple combination from the three first fields is unique.
In other words, instead of ensuring unicity based on a single field, I would like to ensure unicity on a triple of fields (equality being defined as a field-wise equality for each one of the three fields).
below is the code of my sp it's giving my wrong result: 1 instead 0 and vice versa could you please check what's wrong with it: i think there is something related to @var2 in the code colored in orange.
Thanks!
Code:
ALTER PROCEDURE sp_name ( @param1 int = NULL, @param2 int = NULL, @Result int OUTPUT )
AS
DECLARE @var1 AS varchar(255) DECLARE @var2 AS varchar(255) DECLARE @x AS varchar(1000)
IF @param1 IS NOT NULL BEGIN SET @var1 = ( SELECT t1col1 FROM tabl1 WHERE t1col2 = @param1)
SET @var2 = ''
DECLARE crsr CURSOR FOR
SELECT t2col1 FROM tabl2 WHERE t2col2 = @var1
OPEN crsr FETCH NEXT FROM crsr INTO @x
WHILE @@FETCH_STATUS = 0 BEGIN SET @var2 = @var2 + '''' + @x + '''' + ', '
FETCH NEXT FROM crsr INTO @x END
CLOSE crsr DEALLOCATE crsr
SET @var2 = SUBSTRING(@var2, 1, LEN(@var2) - 1)
END ELSE BEGIN SET @var2 = (SELECT t2col1 FROM t2 WHERE tb2col3 = @param2) SET @var2 = '''' + @var2 + '''' END
PRINT @var2
IF EXISTS (SELECT * FROM tabl3 WHERE t3col1 IN (@var2)) OR EXISTS (SELECT * FROM tabl4 WHERE t4col1 IN (@var2)) BEGIN SET @Result = 1 END ELSE BEGIN SET @Result = 0 END
Your help would be appreciated if you could give me some idea about why SQL 2000 takes single quote(') as a default for string searching instead of double quotes("). I have couple of stored procedures which were developed with string comparison with double quotes in SQL 6.5. If any one knows how I can change default as a double quotes(") instead of single quote(') in SQL 2000, would be great. Thanks
select * from tblCalls where TNum = [NUMBER] and Street like '[TEXT]'
The problem is that the Street field contains street names with quotes (ex. DE L'ACADIE). When I run this query using this street name, it doesn't work because of the ' between the L and the A. How can I tell SQL to "ignore" the ' in the text?
Hi, I am creating a flat file connection to a .csv file In the columns section of the flatt file connection manager editor, I am not sure why the texts in the .csv file are shown with double quotes arouond them. They do not have "" in the .csv file. Thanks