I am probably a bit of dumb programmer but I am trying to get away from using cursors in my SQL stored procedures. I probably have my VB program code set in my mind but what is the alternative of doing something like this in T-SQL:
do while not recordset.eof
if condition is ok then
update blah blah blah set something = recordset("field")
end if
recordset.movenext
Loop
Can someone give me an example in T-SQl which can achieve the same thing? (Apart from calling SQL stored procedure using ADO within VB?)
SELECT ID, Firstname, Lastname FROM Table_1 WHERE (Firstname LIKE '2-%%' ESCAPE '-') ID FIRSTNAME LASTNAME 2 2% mohsen why statement above correct work but statement down correct do not work. table ID FIRSTNAME LASTNAME SELECT ID, Firstname, Lastname FROM Table_1 1 mohsen nafisi WHERE (Firstname LIKE '2-%' ESCAPE '-') 2 2% mohsen 3 25467 89 ID FIRSTNAME LASTNAME NULL NULL NULL
I tried searching for "escape character", "quotes", etc, but they didn't work.
I'm having troubling inserting data into my tables, if they have an apostrophe or double quotes. I know that MySQL's escape character is "", but I tried it for MS SQL and it didn't work. HOW DO I INSERT DATA INTO MY DB THAT HAS AN APOSTROPHE OR DOUBLE QUOTE? Thanks.
What should one pass as a field value into a table in the insertstatement if the value contained a percentage symbol (%) or theasterisk symbol (*), since both of these have special wildcardmeanings. What if I want to pass the special meanings and pass them asliterals? Is there any escape character that I must use? I am usingADO.NET v1.1 of the framework with VB.NET. The database is MicrosoftSQL Server 2000.
Is there a way to escape single quotes ' in a sql statement that uses a sql data source that is automated? I know you can do it with a string manip and replacing them with double single quotes. I am just looking for a simple way. Thanks Adam
The command below runs fine from within Management Studioexec sp_MSforeachtable @command1="exec sp_spaceused '?' "However, I'd like to run it via sqlcmd. I've tried variouscombinations of escaping the doulbe and single quotes but withoutsuccess.sqlcmd -E -Q "sp_MSforeachtable @command1="exec sp_spaceused '?'""Sqlcmd: 'exec sp_spaceused '?'""': Unexpected argument. Enter '-?'for help.Any ideas?Thanks,M
SET @SQL1 = 'SELECT * FROM ' + @NENTITY + ' WHERE ' + @NENTITY + '.' + @NFLAG + ' = ' + @FLAGVAR
SELECT @SQL1
OUTPUT: SELECT * FROM HouseDimension WHERE HouseDimension.HouseLastUpdateFlag = Y
where as I need an output with Y in single quotes ('Y') because when I run this query its gives me an error invalid colunm name Y. I tried to use escape sequence but of no use..
Hi, I need to convert from mssqlto Postgres and I need to export all MS-SQL table data to a CSV or TXT file (one file per table)
Presumably, all data per row (of a table) must be in one line. Then when you copy to another database, a new line of data means a new row in the table.
However, MS SQL is exporting a large varchar text field as multiple lines. The data itself is many lines, so exporting it causes the data for one row to fall onto many lines.
My question: How do I escape new lines? When MS SQL exports the data, I want to replace all NEW LINES / carriage returns by /n or by <br> tag (since the data will be for web use).
(pls note I am not actually handling the ms sql database, so any response would be greatly appreciated as I advise the person in charge of the mssql db accordingly).
What is the escape sequence in a stored procedure?
Here is what I'm trying to achieve:
ALTER PROCEDURE Test ( @Func VarChar(1000) )
AS DECLARE @SQL VarChar(8000) SELECT @SQL = 'SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE ' + @Func
Now, my goal is to add single quote (') before @Func and another one after that. For eg, if @Func is "Test", I want my query to be SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE 'Test'
and NOT SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE Test
alter proc [ProGeneral_College_Structure] @Year nvarchar(4) as begin DECLARE @SQLStatement nvarchar(1000)
Set @SQLStatement = 'SELECT School AS Level1Code, DIVISIONS.Div AS Level2Code, DIVISIONS.ProgArea AS Level3Code, DIVISIONS.progName AS LevelName , ' + SUBSTRING(@Year,1,2) + '/' + SUBSTRING(@Year,3,2) + ' AS AcademicYearID FROM DIVISIONS WHERE (((DIVISIONS.[' + @Year + '])=1)) ORDER BY DIVISIONS.School, DIVISIONS.Div, DIVISIONS.ProgArea'
EXEC(@SQLStatement) end
It's something to do with the / concatenation I think, is it an escape character or something tried // obviously and CHAR(47). before I get comments I know it's dynamic sql and it's not great but I can't edit the divisions table so have to use a dynamic column.
The apostrophe embedded in the name value is giving me headaches. I tried using double-quotes and [] to delineate the value but then I get complaints that a "Name" is not allowed in this context.
How do you turn the embedded characters into an escape character so they can be ignored by SQL Server and passed into the table field.
I have a problem setting some variables in a package using the /SET option of dtexec. Specifically when the value I want to set contains a semi-colon. I get an error like:
Argument ""Package.Variables[User::Delim].Properties[Value];^;"" for option "set" is not valid.
I am guessing that I will have to escape the semi-colons somehow, but with what?
STATIC Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor declare ll cursor global static for select name, salary from ag open ll fetch from ll
while @@FETCH_STATUS=0 fetch from ll update ag set salary=200 where 1=1
close ll deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
CSharp:SqlConnection Con = new SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True"); its give ErrorUnrecognized escape sequence
Not sure if this is the right forums to ask this question.. I have a website in which i have a text box that has multiline = true... So suppose if if the user enter absbsbcjd and then hits enter and then again enter jfkdjf in my database i been stored as abscbabc twoboxes (new line) and then jfdkf and then sometime later i am taking that plan name and sending it as a subject to an email but since it has a new line character in that field.. i get an error..
so how can escape the new line character.. thanks Karen
I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.
So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?
I've been installing SQL with a unattended install for awhile, and have the following settings in my install_configuration.ini file. I happened to come across a password with a quote in it today, what is the proper way to escape that quote? I've tried the standard backslash method, and found that to not work.
; SQL Server Agent Service Account AGTSVCACCOUNT="DomainUserName" AGTSVCPASSWORD="PasswordWith"AQuoteInIt" ; SQL Server Service Account SQLSVCACCOUNT="DomainUserName" SQLSVCPASSWORD="PasswordWith"AQuoteInIt" ; SSRS Service Account RSSVCACCOUNT="DomainUserName" RSSVCPASSWORD="PasswordWith"AQuoteInIt"
The cool thing is the install apparently tries a few time to authenticate before giving up, because it ends up locking out the account each time.
I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.
Query example:
Select * from myTable Where item like '%FSC%'
This works great with the exception of when FSC is surrounded by double quotes.
DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur DEALLOCATE DBCur
Part 2
SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is, how to join the part 1 n part 2? is there posibility?
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors
declare Q cursor for select systudentid from satrans
declare @id int
open Q fetch next from Q into @id while @@fetch_status = 0 begin
declare c cursor for
Select b.ssn, SaTrans.SyStudentID, satrans.date, satrans.type, SaTrans.SyCampusID, Amount = Case SaTrans.Type When 'P' Then SaTrans.Amount * -1 When 'C' Then SaTrans.Amount * -1 Else SaTrans.Amount END
From SaTrans , systudent b where satrans.systudentid = b.systudentid
