Hi,
I have microsoft sql server installed on fat32 partition and I want to convert it to ntfs.
Should I expect any problems with my DB while/after doing that ???? You aren't going to try to covert it with database files on it are you?sqlsql
Thursday, March 29, 2012
Converting Existing Data into Unicode Format?
Hi Everybody,
We have an application which accepts transactions in local regional languages. The SQL Database is in 7.0 and OS is NT, with all latest SPs. We are planning to move it SQL 2000. During our testing we found, that Crystal Reports 9.0 is not able extract data which is in Korean/Chinese Languages but Crystal Reports 8.0 is working fine.
So we planned to move all existing data in SQL 7.0 to Unicode data format. For New data, we can change the datatypes from varchar to nvarchar.
Existing Sort Order as follows,
Unicode data sorting -- Locale ID =3D 1033
case insensitive, kana type insensitive, width insensitive
Sort Order Description ---
---
--
Character Set =3D 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order =3D 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Characters, in Order ---
---
--
! " # $ % & ' ( ) * + , - . / : ; < =3D > ? @. [ \ ] ^ _ ` { | }
~ =A1 =A2 =A3 =A4 =A5 =A6 =A7 =A8 =A9 =AA =AB =AC =AD =AE =AF =B0 = =B1 =B2 =B3 =B4 =B5 =B6 =B7 =B8 =B9 =BA =BB =BC =BD =BE
=BF =D7 =F7 0 1 2 3 4 5 6 7 8 9 A=3Da =C0=3D=E0 =C1=3D=E1 =C2=3D=E2 = =C3=3D=E3 =C4=3D=E4 =C5=3D=E5 =C6=3D=E6 B=3Db C
=3Dc =C7=3D=E7 D=3Dd E=3De =C8=3D=E8 =C9=3D=E9 =CA=3D=EA =CB=3D=EB = F=3Df G=3Dg H=3Dh I=3Di =CC=3D=EC =CD=3D=ED =CE=3D=EE =CF=3D=EF J
=3Dj K=3Dk L=3Dl M=3Dm N=3Dn =D1=3D=F1 O=3Do =D2=3D=F2 =D3=3D=F3 = =D4=3D=F4 =D5=3D=F5 =D6=3D=F6 =D8=3D=F8 P=3Dp Q=3Dq R=3Dr S
=3Ds =DF T=3Dt U=3Du =D9=3D=F9 =DA=3D=FA =DB=3D=FB =DC=3D=FC V=3Dv = W=3Dw X=3Dx Y=3Dy =DD=3D=FD =FF Z=3Dz =D0=3D=F0 =DE=3D=FE
Can anybody help me, how to convert existing data (Korean/chinese) to the Unicode format in SQL 2000.
tks in advance,
vasumVasum,
Is this the same issue that is posted with the subject "UNICODE" in the
microsoft.public.sqlserver.programming group? If so, see the replies
there.
Thanks,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "vasum" <vasu22_madhavan@.yahoo.com.sg>
Sender: "vasum" <vasu22_madhavan@.yahoo.com.sg>
Subject: Converting Existing Data into Unicode Format?
Date: Sat, 5 Jul 2003 21:10:21 -0700
Lines: 64
Message-ID: <034501c34374$848f5880$a501280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Newsreader: Microsoft CDO for Windows 2000
Thread-Index: AcNDdISPYMUUigRbRNmnrEVJZrvb6Q==X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa09.phx.gbl
Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23000
NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi Everybody,
We have an application which accepts transactions in local
regional languages. The SQL Database is in 7.0 and OS is
NT, with all latest SPs. We are planning to move it SQL
2000. During our testing we found, that Crystal Reports
9.0 is not able extract data which is in Korean/Chinese
Languages but Crystal Reports 8.0 is working fine.
So we planned to move all existing data in SQL 7.0 to
Unicode data format. For New data, we can change the
datatypes from varchar to nvarchar.
Existing Sort Order as follows,
Unicode data sorting
--
Locale ID = 1033
case insensitive, kana type insensitive, width insensitive
Sort Order Description
---
---
--
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit
character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with
several We
stern-European languages including English, French,
and German
. Uses the ISO 8859-1 character set.
Characters, in Order
---
---
--
! " # $ % & ' ( ) * + , - . / : ; < = > ? @. [ \ ] ^ _
` { | }
~ ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º »
¼ ½ ¾
¿ × ÷ 0 1 2 3 4 5 6 7 8 9 A=a À=à Á=á Â=â Ã=ã Ä=ä Å=å
Æ=æ B=b C
=c Ç=ç D=d E=e È=è É=é Ê=ê Ë=ë F=f G=g H=h I=i Ì=ì Í=í
Î=î Ï=ï J
=j K=k L=l M=m N=n Ñ=ñ O=o Ò=ò Ó=ó Ô=ô Õ=õ Ö=ö Ø=ø P=p
Q=q R=r S
=s ß T=t U=u Ù=ù Ú=ú Û=û Ü=ü V=v W=w X=x Y=y Ý=ý ÿ Z=z
Ð=ð Þ=þ
Can anybody help me, how to convert existing data
(Korean/chinese) to the Unicode format in SQL 2000.
tks in advance,
vasum
We have an application which accepts transactions in local regional languages. The SQL Database is in 7.0 and OS is NT, with all latest SPs. We are planning to move it SQL 2000. During our testing we found, that Crystal Reports 9.0 is not able extract data which is in Korean/Chinese Languages but Crystal Reports 8.0 is working fine.
So we planned to move all existing data in SQL 7.0 to Unicode data format. For New data, we can change the datatypes from varchar to nvarchar.
Existing Sort Order as follows,
Unicode data sorting -- Locale ID =3D 1033
case insensitive, kana type insensitive, width insensitive
Sort Order Description ---
---
--
Character Set =3D 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order =3D 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Characters, in Order ---
---
--
! " # $ % & ' ( ) * + , - . / : ; < =3D > ? @. [ \ ] ^ _ ` { | }
~ =A1 =A2 =A3 =A4 =A5 =A6 =A7 =A8 =A9 =AA =AB =AC =AD =AE =AF =B0 = =B1 =B2 =B3 =B4 =B5 =B6 =B7 =B8 =B9 =BA =BB =BC =BD =BE
=BF =D7 =F7 0 1 2 3 4 5 6 7 8 9 A=3Da =C0=3D=E0 =C1=3D=E1 =C2=3D=E2 = =C3=3D=E3 =C4=3D=E4 =C5=3D=E5 =C6=3D=E6 B=3Db C
=3Dc =C7=3D=E7 D=3Dd E=3De =C8=3D=E8 =C9=3D=E9 =CA=3D=EA =CB=3D=EB = F=3Df G=3Dg H=3Dh I=3Di =CC=3D=EC =CD=3D=ED =CE=3D=EE =CF=3D=EF J
=3Dj K=3Dk L=3Dl M=3Dm N=3Dn =D1=3D=F1 O=3Do =D2=3D=F2 =D3=3D=F3 = =D4=3D=F4 =D5=3D=F5 =D6=3D=F6 =D8=3D=F8 P=3Dp Q=3Dq R=3Dr S
=3Ds =DF T=3Dt U=3Du =D9=3D=F9 =DA=3D=FA =DB=3D=FB =DC=3D=FC V=3Dv = W=3Dw X=3Dx Y=3Dy =DD=3D=FD =FF Z=3Dz =D0=3D=F0 =DE=3D=FE
Can anybody help me, how to convert existing data (Korean/chinese) to the Unicode format in SQL 2000.
tks in advance,
vasumVasum,
Is this the same issue that is posted with the subject "UNICODE" in the
microsoft.public.sqlserver.programming group? If so, see the replies
there.
Thanks,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "vasum" <vasu22_madhavan@.yahoo.com.sg>
Sender: "vasum" <vasu22_madhavan@.yahoo.com.sg>
Subject: Converting Existing Data into Unicode Format?
Date: Sat, 5 Jul 2003 21:10:21 -0700
Lines: 64
Message-ID: <034501c34374$848f5880$a501280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Newsreader: Microsoft CDO for Windows 2000
Thread-Index: AcNDdISPYMUUigRbRNmnrEVJZrvb6Q==X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa09.phx.gbl
Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23000
NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi Everybody,
We have an application which accepts transactions in local
regional languages. The SQL Database is in 7.0 and OS is
NT, with all latest SPs. We are planning to move it SQL
2000. During our testing we found, that Crystal Reports
9.0 is not able extract data which is in Korean/Chinese
Languages but Crystal Reports 8.0 is working fine.
So we planned to move all existing data in SQL 7.0 to
Unicode data format. For New data, we can change the
datatypes from varchar to nvarchar.
Existing Sort Order as follows,
Unicode data sorting
--
Locale ID = 1033
case insensitive, kana type insensitive, width insensitive
Sort Order Description
---
---
--
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit
character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with
several We
stern-European languages including English, French,
and German
. Uses the ISO 8859-1 character set.
Characters, in Order
---
---
--
! " # $ % & ' ( ) * + , - . / : ; < = > ? @. [ \ ] ^ _
` { | }
~ ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º »
¼ ½ ¾
¿ × ÷ 0 1 2 3 4 5 6 7 8 9 A=a À=à Á=á Â=â Ã=ã Ä=ä Å=å
Æ=æ B=b C
=c Ç=ç D=d E=e È=è É=é Ê=ê Ë=ë F=f G=g H=h I=i Ì=ì Í=í
Î=î Ï=ï J
=j K=k L=l M=m N=n Ñ=ñ O=o Ò=ò Ó=ó Ô=ô Õ=õ Ö=ö Ø=ø P=p
Q=q R=r S
=s ß T=t U=u Ù=ù Ú=ú Û=û Ü=ü V=v W=w X=x Y=y Ý=ý ÿ Z=z
Ð=ð Þ=þ
Can anybody help me, how to convert existing data
(Korean/chinese) to the Unicode format in SQL 2000.
tks in advance,
vasum
Converting empty string to Null when inserting/updating
I am using the following query to calculate date differences:
select ........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM ....
select ........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM ....
I have stored all my dates as NVARCHAR because of the issues with localization.
If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table areNULL and they produce the correct result.
Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.
I'd like to use it as a trigger when inserting or updating to convert empty strings to NULL
before the values are inserted.
Thanks guys.
You REALLY should store your dates as a datetime. There is no localization "Problem" with datetimes if you use them correctly, and you can't sort and/or generate good indexes if they are stored in a nvarchar field (Unless you specifically use the YYYYMMDD or YYYY-MM-DD format).
That aside, yes, try NULLIF() like:
INSERT INTO MyTable(col1) VALUES (NULLIF(@.val1,''))
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_3uhy.asp
Converting DTS to Stored procedure
Hi,
I created a DTS package for importing a textfile, parse the data and insert it into some tables.
I was planning to start this from a stored procedure so I could reach it from the outside... It turns out I don't have (and won't get) the permissions to do this...
Now I have to convert the package into a stored procedure instead.
SQL Querys are no problem but the importing of the textfile is.
How do I do it?
First step is to import only row 0 to a table, second step is to import row 1 -> n
Any examples would be nice... :-)bcp or bulk insert...
what's row 0?
Probably a header...
Can you create a "work" table for your own use?
How many rows are we talking about?
Where is the data coming from (it's Access isn't...grrrrrr)
What do you mean by parse (SUBSTRING)?
Does your dog have fleas?
Why is the sky blue?
What's the name of your dba...(just kidding)
Tell us what you're trying to do, what you can expect you can access to(not demanding to ask a dba for a work table), and I'm sure we can figure something out...|||Whats bcp?
The file contains a header in the first row and then tab separated data...
I have a rawtable that it is imported to now by DTS.
It's about 20000 rows, textfile...
By parsing i mean several queries that translates the data from the rawtable into other tables.
And no, my dog (Staffordshire Bullterrier, "Kim") does not have any fleas...
The sky is blue beacuse God have his blue underwear on...
My dba is named Sven.
--
I have tried to use xp_cmdshell but I'm not allowed too...
Seems like I have to do the import in my client.. written in C++.|||Hi,
You could try using the stored proceedure below to execute yr DTS, if the DTS is within the same DB then it shd work fine ... Hope this helps.
CREATE PROC dbo.DTSExecutePKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)
-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL
IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
RETURN 1
END
-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN 1
END
-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
RETURN 1
END
-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
RETURN 1
END
GO|||Got a:
Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 17
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
*** Create Package object failed
--
Seems like I have no permission to run sp_OACreate either...
Whats the point of having a flashy database if you're not allowed to use all of its finesses? Grrr.
I created a DTS package for importing a textfile, parse the data and insert it into some tables.
I was planning to start this from a stored procedure so I could reach it from the outside... It turns out I don't have (and won't get) the permissions to do this...
Now I have to convert the package into a stored procedure instead.
SQL Querys are no problem but the importing of the textfile is.
How do I do it?
First step is to import only row 0 to a table, second step is to import row 1 -> n
Any examples would be nice... :-)bcp or bulk insert...
what's row 0?
Probably a header...
Can you create a "work" table for your own use?
How many rows are we talking about?
Where is the data coming from (it's Access isn't...grrrrrr)
What do you mean by parse (SUBSTRING)?
Does your dog have fleas?
Why is the sky blue?
What's the name of your dba...(just kidding)
Tell us what you're trying to do, what you can expect you can access to(not demanding to ask a dba for a work table), and I'm sure we can figure something out...|||Whats bcp?
The file contains a header in the first row and then tab separated data...
I have a rawtable that it is imported to now by DTS.
It's about 20000 rows, textfile...
By parsing i mean several queries that translates the data from the rawtable into other tables.
And no, my dog (Staffordshire Bullterrier, "Kim") does not have any fleas...
The sky is blue beacuse God have his blue underwear on...
My dba is named Sven.
--
I have tried to use xp_cmdshell but I'm not allowed too...
Seems like I have to do the import in my client.. written in C++.|||Hi,
You could try using the stored proceedure below to execute yr DTS, if the DTS is within the same DB then it shd work fine ... Hope this helps.
CREATE PROC dbo.DTSExecutePKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)
-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL
IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
RETURN 1
END
-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN 1
END
-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
RETURN 1
END
-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
RETURN 1
END
GO|||Got a:
Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 17
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
*** Create Package object failed
--
Seems like I have no permission to run sp_OACreate either...
Whats the point of having a flashy database if you're not allowed to use all of its finesses? Grrr.
converting diagram to word
i want to convert my diagram that I have created in SQL
server 2000 to word an editable format e.g. jpg.
(not using print screen)
thanks to anyone who can helpYou can use a third-party product like Paint Shop Pro to capture the
image, edit it, and save it as a jpg.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 6 Nov 2003 05:19:27 -0800, "koby"
<anonymous@.discussions.microsoft.com> wrote:
>i want to convert my diagram that I have created in SQL
>server 2000 to word an editable format e.g. jpg.
>(not using print screen)
>thanks to anyone who can help|||I believe that screen capture utilities like SnagIt capture to jpg. =Perhaps that method would work for you.
-- Keith
"koby" <anonymous@.discussions.microsoft.com> wrote in message =news:044401c3a468$9a9f48b0$a001280a@.phx.gbl...
> i want to convert my diagram that I have created in SQL > server 2000 to word an editable format e.g. jpg.
> > (not using print screen)
> > thanks to anyone who can help
server 2000 to word an editable format e.g. jpg.
(not using print screen)
thanks to anyone who can helpYou can use a third-party product like Paint Shop Pro to capture the
image, edit it, and save it as a jpg.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 6 Nov 2003 05:19:27 -0800, "koby"
<anonymous@.discussions.microsoft.com> wrote:
>i want to convert my diagram that I have created in SQL
>server 2000 to word an editable format e.g. jpg.
>(not using print screen)
>thanks to anyone who can help|||I believe that screen capture utilities like SnagIt capture to jpg. =Perhaps that method would work for you.
-- Keith
"koby" <anonymous@.discussions.microsoft.com> wrote in message =news:044401c3a468$9a9f48b0$a001280a@.phx.gbl...
> i want to convert my diagram that I have created in SQL > server 2000 to word an editable format e.g. jpg.
> > (not using print screen)
> > thanks to anyone who can help
Converting Desktop Engine to SQL Server 2005
I have got SQL Server Desktop Engine running with 2 database and I need
to install the evaluation version of SQL Server 2005 instead. A
straight upgrade does not seem to be possible. Does someone know if
backing up the databases, uninstalling the desktop engine, installing
SQL Server 2005 and then importing/restoring the databases would be an
option to look into?
Here is an article on Upgradeing MSDE to SQL 2005 Express, you could follow this example then it is an easy upgrade to the Full SQL 2005 Product.
sqlsqlConverting delimited varchar @parameter for use in NOT IN()
I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:
> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:
> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?
Subscribe to:
Posts (Atom)