How to escape out single quotes from an T-SQL variable

How do I escape out the single quotes in the T-SQL Variable Assignment
appearing below around the comma for setting the FIELDTERMINATOR and the \n
setting the ROWTERMINATOR?
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
Thanks,
DavidTranslate each single quote into two single quotes:
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:B423BD8F-F0A8-4516-B720-C8990908BD1D@.microsoft.com...
How do I escape out the single quotes in the T-SQL Variable Assignment
appearing below around the comma for setting the FIELDTERMINATOR and the \n
setting the ROWTERMINATOR?
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
Thanks,
David|||David,
Double them.
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )'
AMB
"David Bowles" wrote:

> How do I escape out the single quotes in the T-SQL Variable Assignment
> appearing below around the comma for setting the FIELDTERMINATOR and the \
n
> setting the ROWTERMINATOR?
> SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
> WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
> Thanks,
> David|||By using two single quotes.
PRINT 'O''Leary';
"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:B423BD8F-F0A8-4516-B720-C8990908BD1D@.microsoft.com...
> How do I escape out the single quotes in the T-SQL Variable Assignment
> appearing below around the comma for setting the FIELDTERMINATOR and the
> \n
> setting the ROWTERMINATOR?
> SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
> WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
> Thanks,
> David

How to enumerate the databases on the server

Given that you have an interface with a connection to SQL Server Express, is there some T-SQL script or statement that can enumerate the available database names on the server ?

For example:

CString clQuery;

clQuery.Format("SELECT SERVERPROPERTY('Databases')");

m_db.Execute(clQuery);

or something of the sort ?

This will work in both SQL 2000 and SQL 2005:

Code Snippet

SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA

|||

Thanks, Arnie.

For some reason, that statement only yields about 16 'Master' records in the CATALOG_NAME column. I really need a way to enumerate all the databases on the server.

Any other ideas ?

Mike

|||

Sorry Mike, my mistake. That Information VIEW 'changed' between SQL 2000 and SQL 2005.

In SQL 2005, use:

SELECT name

FROM sys.databases

|||

Thanks, Arnie. That works perfectly.

BTW, I have had trouble responding as your server appears to be down quite a bit of the time.

Regards,

Mike

|||There seems to have been some 'replication' issues this week. We all appreciate everyone's patience/

How to enumerate my Instances?

Hello MVPs,
Is there any way to enumerate my Instances with T-SQL
or ...?
Thanks in Advance.Michas Konstantinos wrote:
> Hello MVPs,
> Is there any way to enumerate my Instances with T-SQL
> or ...?
> Thanks in Advance.
Not a MVP (yet...? :P) but will try to help you.
I am afraid that it is not possible without calling some external COM object
from within T-SQL. See the script by Umachandar Jayachandran:
DECLARE @.ServerName varchar( 128 ), @.SQLServer int, @.RetCode int,
@.Instances int, @.Count int, @.NumInstances int, @.Instance varchar(
128 )
EXEC @.RetCode = sp_OACreate 'SQLDMO.SQLServer2', @.SQLServer OUTPUT
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
EXEC @.RetCode = sp_OAMethod @.SQLServer, 'ListInstalledInstances', @.Instances
OUTPUT, @.ServerName
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
EXEC @.RetCode = sp_OAGetProperty @.Instances, 'Count', @.NumInstances OUTPUT
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
SET @.Count = 1
WHILE ( @.Count <= @.NumInstances )
BEGIN
EXEC @.RetCode = sp_OAGetProperty @.Instances, 'Item', @.Instance OUTPUT,
@.Count
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
SET @.Count = @.Count + 1
PRINT @.Instance
END
IF @.Instances > 0 EXEC sp_OADestroy @.Instances
IF @.SQLServer > 0 EXEC sp_OADestroy @.SQLServer
RETURN
Error_Handler:
IF @.Instances > 0 EXEC sp_DisplayOAErrorInfo @.Instances, @.RetCode
IF @.SQLServer > 0 EXEC sp_DisplayOAErrorInfo @.SQLServer, @.RetCode
IF @.Instances > 0 EXEC sp_OADestroy @.Instances
IF @.SQLServer > 0 EXEC sp_OADestroy @.SQLServer
You can see intances list on a machine by checking the registry key
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances.
See "ListSQLSvr.exe - Enumerate SQL Servers" for the non-TSQL approach
http://sqldev.net/misc/ListSQLSvr.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||By sysservers?
>--Original Message--
>Hello MVPs,
>Is there any way to enumerate my Instances with T-SQL
>or ...?
>Thanks in Advance.
>.
>|||Thanks. Sebastian and Michas. Both of you are quite help. I read the
article Michas sent to me. Now, I understand how the SQL Server should be
configured via a firewall.
Many thanks again, and have a nice day.
Chopin
"Michas Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8101c40069$221787b0$a601280a@.phx.gbl...
> Hello MVPs,
> Is there any way to enumerate my Instances with T-SQL
> or ...?
> Thanks in Advance.
>

How to do this the T-SQL way.

I have this code in VB...

OpenOrdSQL = "SELECT JPO, QtyDlv, OutStandVal FROM ActiveOrders WHERE JPO < '5000000' " & _
"ORDER BY JPO ASC"
Set OpenOrdRS = New ADODB.Recordset
OpenOrdRS.Open OpenOrdSQL, DB

Do Until OpenOrdRS.EOF
ActiveSQL = "SELECT * FROM fn_ActiveOrders('" & OpenOrdRS!JPO & "')"
Set ActiveRS = New ADODB.Recordset
ActiveRS.Open ActiveSQL, DB

If ActiveRS.EOF Then
SQL = "DELETE FROM ActiveOrders WHERE JPO = '" & OpenOrdRS!JPO & "'"
DB.Execute SQL
Else
If (Int(OpenOrdRS!QtyDlv) - Int(ActiveRS!QtyDlv)) Or (Int(OpenOrdRS!OutStandVal) - Int(ActiveRS!OpenVal)) Then
SQL = "UPDATE ActiveOrders SET OutStandVal = '" & ActiveRS!OpenVal & _
"', SQM = '" & ActiveRS!OpenSQM & "', QtyDlv = '" & ActiveRS!QtyDlv & _
"' WHERE JPO = '" & OpenOrdRS!JPO & "'"
DB.Execute SQL
End If
End If

OpenOrdRS.MoveNext
Loop

...that I'm trying to imitate in SQL server so that I can remove this module from my VB project and use SQL Server Agent. And this is what I've come up...

DECLARE @.JPO NUMERIC, @.OpenVal FLOAT, @.OpenSQM FLOAT, @.OpenQty NUMERIC
SELECT @.JPO = JPO,
QtyDlv,
OutStandVal
FROM ActiveOrders
WHERE CASE
WHEN NOT EXISTS (SELECT a.AUF_NR AS JPO,
SUM(a.RG_OFFEN * b.SUM_NETTO) AS OpenVal,
SUM(a.RG_OFFEN * b.VER_M2) AS OpenSQM,
SUM(a.RG_OFFEN) AS OpenQty
FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_POS b
WHERE a.AUF_NR = b.AUF_NR AND
a.AUF_POS = b.AUF_POS AND
a.RG_OFFEN != 0 AND
a.AUF_NR = @.JPO
GROUP BY a.AUF_NR) THEN (DELETE FROM ActiveOrders WHERE JPO = @.JPO)
WHEN EXISTS (SELECT a.AUF_NR AS JPO,
@.OpenVal = SUM(a.RG_OFFEN * b.SUM_NETTO) AS OpenVal,
@.OpenSQM = SUM(a.RG_OFFEN * b.VER_M2) AS OpenSQM,
@.OpenQty = SUM(a.RG_OFFEN) AS OpenQty
FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_POS b
WHERE a.AUF_NR = b.AUF_NR AND
a.AUF_POS = b.AUF_POS AND
a.RG_OFFEN != 0 AND
a.AUF_NR = @.JPO
GROUP BY a.AUF_NR) THEN (UPDATE ActiveOrders
SET OutStandVal = @.OpenVal,
QtyDlv = @.OpenQty,
SQM = @.OpenQty
WHERE JPO = @.JPO)
END
...Now this is just experimental. I've tried running it and it gave me errors. I was thingking of using cursors but I think that's not efficient enough. You might notice the "fn_ActiveOrders". It's a function I created and this is the content...

CREATE FUNCTION fn_ActiveOrders(@.JPO numeric)
RETURNS TABLE
AS RETURN
SELECT OrdStat.AUF_NR AS JPO,
SUM(OrdStat.RG_OFFEN * OrdPos.SUM_NETTO) AS OpenVal,

SUM(OrdStat.RG_OFFEN * OrdPos.VER_M2) AS OpenSQM,
SUM(OrdStat.RG_OFFEN) AS QtyDlv
FROM liorder..LIORDER.AUF_POS OrdPos INNER JOIN
liorder..LIORDER.AUF_STAT OrdStat ON

OrdPos.AUF_NR = OrdStat.AUF_NR AND
OrdPos.AUF_POS = OrdStat.AUF_POS
WHERE OrdStat.RG_OFFEN <> 0 AND
OrdStat.AUF_NR = @.JPO
GROUP BY OrdStat.AUF_NR
I hope that somebody could help me with this...I was thingking of using cursors but I think that's not efficient enough.
Hi

That is defo correct - you don't need cursors for this. I rewrote your middle block of code - it looks about right but I haven't tested. Please post DDL and sample data if it fails. Please test in a test environment of course :)


Delete
FROM ActiveOrders
WHERE NOT EXISTS (SELECT NULL
FROM liorder..LIORDER.AUF_STAT a
INNER JOIN liorder..LIORDER.AUF_POS b ON
a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS
WHERE a.RG_OFFEN <> 0
AND a.AUF_NR = ActiveOrders..JPO)
Update ActiveOrders
SET OutStandVal = SumA,
QtyDlv = SumB,
SQM = SumC
FROM (SELECT SUM(a.RG_OFFEN * b.SUM_NETTO) AS SumA,
SUM(a.RG_OFFEN * b.VER_M2) AS SumB,
SUM(a.RG_OFFEN) AS SumC,
a.AUF_NR AS JPO
FROM liorder..LIORDER.AUF_STAT a
INNER JOIN liorder..LIORDER.AUF_POS b ON
a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS
WHERE a.RG_OFFEN <> 0
Group BY
a.AUF_NR) AS c
INNER JOIN ActiveOrders ON
ActiveOrders.JPO = c.JPO
Group BY
c.JPO


HTH|||This what I understood from ur VB code.I havent tested with data,so no guaranty from my side :).Test it before applying to production server.

CREATE PROC Update_ActiveOrders_sp
as
declare @.err int
set nocount on
begin tran
-- creating temp table inserting record--
SELECT OrdStat.AUF_NR AS JPO,
SUM(OrdStat.RG_OFFEN * OrdPos.SUM_NETTO) AS OpenVal,
SUM(OrdStat.RG_OFFEN * OrdPos.VER_M2) AS OpenSQM,
SUM(OrdStat.RG_OFFEN) AS QtyDlv
INTO #TEMP
FROM
liorder..LIORDER.AUF_POS OrdPos INNER JOIN
liorder..LIORDER.AUF_STAT OrdStat ON
OrdPos.AUF_NR = OrdStat.AUF_NR AND
OrdPos.AUF_POS = OrdStat.AUF_POS INNER JOIN
ActiveOrders ON
ActiveOrders.JPO=OrdStat.AUF_NR
WHERE
OrdStat.RG_OFFEN <> 0 AND
ActiveOrders.JPO < '5000000'
GROUP BY
OrdStat.AUF_NR

--update ActiveOrders which exists in #TEMP table--
UPDATE ActiveOrders
SET OutStandVal = tm.OpenVal,
SQM =tm.OpenSQM ,
QtyDlv =tm.QtyDlv
FROM
#TEMP AS tm
WHERE
JPO =tm.JPO

set @.err=@.@.error
if(@.err <> 0) goto quitWithError

-- delete records not exists in #TEMP table and ActiveOrders.JPO<'5000000'--
DELETE ActiveOrders
where not exists (
select
null
from #TEMP
where ActiveOrders.JPO=#TEMP.JPO
)
and ActiveOrders.JPO<'5000000'
set @.err=@.@.error
if(@.err <> 0) goto quitWithError
-- drop #TEMP table
DROP TABLE #TEMP
goto EndSave
quitWithError:
if (@.@.trancount >0) rollback
return @.err
EndSave:
if (@.@.trancount >0) commit tran
return 0|||Hi

That is defo correct - you don't need cursors for this. I rewrote your middle block of code - it looks about right but I haven't tested. Please post DDL and sample data if it fails. Please test in a test environment of course :)


Delete
FROM ActiveOrders
WHERE NOT EXISTS (SELECT NULL
FROM liorder..LIORDER.AUF_STAT a
INNER JOIN liorder..LIORDER.AUF_POS b ON
a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS
WHERE a.RG_OFFEN <> 0
AND a.AUF_NR = ActiveOrders..JPO)
Update ActiveOrders
SET OutStandVal = SumA,
QtyDlv = SumB,
SQM = SumC
FROM (SELECT SUM(a.RG_OFFEN * b.SUM_NETTO) AS SumA,
SUM(a.RG_OFFEN * b.VER_M2) AS SumB,
SUM(a.RG_OFFEN) AS SumC,
a.AUF_NR AS JPO
FROM liorder..LIORDER.AUF_STAT a
INNER JOIN liorder..LIORDER.AUF_POS b ON
a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS
WHERE a.RG_OFFEN <> 0
Group BY
a.AUF_NR) AS c
INNER JOIN ActiveOrders ON
ActiveOrders.JPO = c.JPO
Group BY
c.JPO


HTH

Pootle,u missed one condition here , "ActiveOrders.JPO<'5000000'"|||Pootle,u missed one condition here , "ActiveOrders.JPO<'5000000'"You are right :o I think you took a bit more time about it - I didn't bother translating the vb solution - I just reworked his T-SQL stab at it. The 5000000 didn't make a showing there (in my defence :D )|||Thanks guys! Both your solutions worked!!! Now I've removed the module from my VB and all updates now work via SQL Server Agent. By the way, since we've talked about cursors, is there a best way to use it? I mean, I've only tried it twice and it takes shitload of time before it finishes execution. I'm sorry though this is off topic, but I just wanted to learn more about T-SQL as it helps me a lot in VB programming. And the stuff that I've learnt so far I got from forums as well.|||Use cursor when there is no other option.Most of time u can solve the problem without cursors.|||Cursors are useful in about two situations -
1) looping through system tables to perform commands on, for example, each table in the database. There are variations on this theme.
2) Camparing values in a column to values from different rows in the same column.

it takes shitload of time before it finishes execution
terribly perceptive - most cursor devotees don't appear to notice this rather important point.

how to do an addYears(-2) in T-SQL

I have a variable @.yr which represents the years column in my table, and there is also a Months column, which may or may not have all the months in each year, so I'm trying to get the # of months in a 2 year period - - -

Heres' where I'm stuck:

DECLARE @.yr AS Int

SELECT count(*) AS cnt, month, year
FROM Sales_history
WHERE year BETWEEN @.yr AND @.yr (addyears(- 2))

I put the addyears part because I know you can work with stuff like that in VB.net - - - -but I don't know how to construct this in T-SQL - - help!!

The T-SQL function you're looking for is DATEADD.

http://msdn2.microsoft.com/en-US/library/ms186819.aspx

So, DATEADD(year, -2, @.yr).|||I think I read your post too fast though. DATEADD needs a date for that third parameter. Since @.yr is an int already, why don't you just do your subtraction there. If you need mid year ranges (instead of January to December), you will most likely need to compose a date from your month/year data.|||

As Hous68 said, you can use DATEADD function in T-SQL. But if the year column in the table is INT, why not directly compare it with @.yr:year BETWEEN @.yr-2 AND @.yr ? If theyearcolumn is DATETIME, and since you only what to compare the year part of the datetime to @.yr, you can use YEAR() function to get th year part for comparation: YEAR(year) BETWEEN @.yr-2 AND @.yr .

|||

SELECT count(*) AS cnt, month, year
FROM Sales_history
WHERE year BETWEEN @.yr AND @.yr-2

GROUP BY month,year