Extract color formatted text from code editor?

Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
txThis is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing th
is
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>sql

Extract color formatted text from code editor?

Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
tx
This is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx
|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing this
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>

External SQL functions exported by DLLs

We have a few rather advanced calculation routines
developed in C++. Currently we are using a Sybase ASA
database for our app. and these routines are accessible
by so called external functions exported by a DLL
(dynamic link library) that we wrote in C++. Sybase
supports this quite nicely.
The client app can call such routines by:
"select get_roof_angle() as angle from dummy;"
or
":angle = call get_roof_angle();"
Now we are checking if other databases can provide us
with similar abilities. So I just wonder if SQLServer/MSDE
supports the ability to call a routine exported by a
DLL? If yes, does anyone know the SQL syntax to declare
it in SQLServer/MSDE
Best regards
Peter Sullvan
hi Peter,
Peter Sullvan wrote:
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if SQLServer/MSDE
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in SQLServer/MSDE
> Best regards
> Peter Sullvan
you can write so called "extended stored procedures" externded by dll... at
http://www.sqldev.net/xp.htm you can find some examples as long as usefull
links..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

External SQL functions exported by DLLs

We have a few rather advanced calculation routines
developed in C++. Currently we are using a Sybase ASA
database for our app. and these routines are accessible
by so called external functions exported by a DLL
(dynamic link library) that we wrote in C++. Sybase
supports this quite nicely.
The client app can call such routines by:
"select get_roof_angle() as angle from dummy;"
or
":angle = call get_roof_angle();"
Now we are checking if other databases can provide us
with similar abilities. So I just wonder if MS-SQLServer
supports the ability to call a routine exported by a
DLL? If yes, does anyone know the SQL syntax to declare
it in MS-SQLServer.
Best regards
Peter SullvanCurrently only as (extended) stored procedures.
EXEC xp_procname -- which really is a function in a DLL file.
SQL Server 2005 will implement CLR inside SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>|||In SQL Server 2000, you can create an extended stored procedure. See the
Books Online for details. For scalar functions, you can encapsulate an
extended stored procedure in a Transact-SQL scalar function like the example
below.
CREATE FUNCTION get_roof_angle()
RETURNS int
AS
BEGIN
DECLARE @.RoofAngle int
EXEC master..xp_get_roof_angle @.RoofAngle OUT
RETURN @.RoofAngle
END
GO
SELECT dbo.get_roof_angle()
GO
The planned SQL Server 2005 version will allow you to develop functions
using CLR managed code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>|||To clarify my response, you can create extended stored procedures in all
versions of SQL Server. However, Transact-SQL functions are available only
in SQL 2000+.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23YTFuOkJFHA.1280@.TK2MSFTNGP09.phx.gbl...
> In SQL Server 2000, you can create an extended stored procedure. See the
> Books Online for details. For scalar functions, you can encapsulate an
> extended stored procedure in a Transact-SQL scalar function like the
> example below.
> CREATE FUNCTION get_roof_angle()
> RETURNS int
> AS
> BEGIN
> DECLARE @.RoofAngle int
> EXEC master..xp_get_roof_angle @.RoofAngle OUT
> RETURN @.RoofAngle
> END
> GO
> SELECT dbo.get_roof_angle()
> GO
> The planned SQL Server 2005 version will allow you to develop functions
> using CLR managed code.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
> news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server only supports Extended Stored Procedures, no Extended Functions,
but you could wrap an extended stored procedure inside a user defined T-SQL
function and get the same behavior, but you need to use output parameters in
your extended stored procedure to return values, you can not return a result
set.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>

Extended Stored Procedure

When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. When
I add the same to a stored proc my app hangs and I get a timeout.
Any help is greatly appreciated. I really don't want to create a job that
runs this.
EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) ENDHi
At a guess as the application hangs it probably means it can't find the
object, which implies it is not being run in the same context and/or possibl
y
a lack of permissions. You also need to be symin to run sp_OACreate.
John
"Mark - HYPERe" wrote:

> When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. Wh
en
> I add the same to a stored proc my app hangs and I get a timeout.
> Any help is greatly appreciated. I really don't want to create a job that
> runs this.
> EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
> IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) END

Expressions in queries

I'm new to SQL Server and converting a VB app from Access back end to
SQL Server back end. In this app I use a lot of expressions in
queries
e.g. SELECT iif(isnull([MyText],"",[MyText]) as sRemoveNull
or SELECT [SaleDate]>=#01-Jan-2004# as bUseSale
I can't get anything of the sort to work in SQL Server. I've written a
few UDFs to cope with some more common expressions, but for some cases
it would be much easier to be able to use a complex expression within
the query.
Stored Procedures are not really appropriate as queries are built in
VB and the SQL string passed to SQL Server.
Am I missing something obvious?
Thanks,
MattThese two examples may help. You can check out the full syntax of these
expressions in SQL Server Books Online.
SELECT COALESCE(mytext,'') AS sRemoveNull
SELECT CASE WHEN saledate>='20040101' THEN 1 ELSE 0 END AS bUseSale

> queries are built in
> VB and the SQL string passed to SQL Server.
In the world of SQL Server that's generally a really bad idea. Standard
practice is to use SPs for all data access unless you have an exceptional
reason not to. SPs make the most sense from the point of view of
performance, security and maintainability. If you just want to continue
using SQL the way you've always used Access then you can't expect to realize
all the benefits of the platform change. You may well be better off sticking
with your Access backend.
David Portas
SQL Server MVP
--|||Thanks David, that'll get me started.
Re your further comments, I was also going to post the
"How do I do SELECT * FROM @.tablename" question last night,
but did some reading here first and found numerous responses along the
same lines as yours. I've read two articles from www.sommarskog.se
about dynamic SQL and it seems I have to accept that I'll have to do
things quite differently in the future than I have been (sticking with
Access is not an option). The upside is that I'm learning heaps and
enjoying doing it.
Thanks for your comments.
Matt
On Sun, 30 Jan 2005 18:03:56 -0000, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>These two examples may help. You can check out the full syntax of these
>expressions in SQL Server Books Online.
>SELECT COALESCE(mytext,'') AS sRemoveNull
>SELECT CASE WHEN saledate>='20040101' THEN 1 ELSE 0 END AS bUseSale
>
>In the world of SQL Server that's generally a really bad idea. Standard
>practice is to use SPs for all data access unless you have an exceptional
>reason not to. SPs make the most sense from the point of view of
>performance, security and maintainability. If you just want to continue
>using SQL the way you've always used Access then you can't expect to realiz
e
>all the benefits of the platform change. You may well be better off stickin
g
>with your Access backend.
>--
>David Portas
>SQL Server MVP

Express, Can I really redistribute this with our app?

SQL 2005 Express, Can I really redistribute this with our app?
Background: We sell a software program to Fortune 1000, always previously
required clients to use or obtain at least SQL 2000 Standard, always a
tedious process on their end, ie budgets, licensing, IT folks, etc.
Our typical client site has about 10 users who use our program, and the SQL
DB on some nearby host server.
Our DB requirements fit Express such as size < 4GB and Ram < 1GB. We don't
do anything too fancy with the DB, about 70 tables, 70 triggers, 140 stored
procedures. We use an embedded ID & PW passed by application, so SQL Login
has to be in there, but we can probably be flexible here.
Is SQL Express really a deployable solution for us? I assume that we can
script stuff like DB creation along with Tables and other needed objects,
allowing a typcial small group of users to get up and running quickly.
Can the DB host be an XP o/s in this scenerio? Do we need Management
Studio Express ?
From what I've read up at ms.com, all of this seems possible, I just can't
believe it would be this easy.
Any and all comments will be greatly appreciated."John" <John@.discussions.microsoft.com> wrote in message
news:936531F0-26FF-432E-A783-AD8264CE486A@.microsoft.com...
> SQL 2005 Express, Can I really redistribute this with our app?
> Background: We sell a software program to Fortune 1000, always previously
> required clients to use or obtain at least SQL 2000 Standard, always a
> tedious process on their end, ie budgets, licensing, IT folks, etc.
> Our typical client site has about 10 users who use our program, and the
> SQL
> DB on some nearby host server.
> Our DB requirements fit Express such as size < 4GB and Ram < 1GB. We
> don't
> do anything too fancy with the DB, about 70 tables, 70 triggers, 140
> stored
> procedures. We use an embedded ID & PW passed by application, so SQL
> Login
> has to be in there, but we can probably be flexible here.
> Is SQL Express really a deployable solution for us? I assume that we can
> script stuff like DB creation along with Tables and other needed objects,
> allowing a typcial small group of users to get up and running quickly.
> Can the DB host be an XP o/s in this scenerio? Do we need Management
> Studio Express ?
> From what I've read up at ms.com, all of this seems possible, I just can't
> believe it would be this easy.
> Any and all comments will be greatly appreciated.
>
Yep, it's that easy. You need to register for redistribution rights
(http://www.microsoft.com/sql/editions/express/redistregister.mspx).
Of course your customers might want to deploy your app on a higher SKU of
SQL Server, and they can always upgrade later.
David|||Thanks David for confirming its that easy.
How about the Windows XP host PC? Is that OK with say about 10 users
accessing data?
And Management Studio Express ? Is a gold release out yet or still CTP?
I'm figuring we'd need to use this for deployment or at least make it
available for clients just in case.
Again, your comments are valuable and appreciated!
John|||"John" <John@.discussions.microsoft.com> wrote in message
news:08D225AB-CB1E-44E4-BF80-D831FA3D57F8@.microsoft.com...
> Thanks David for confirming its that easy.
> How about the Windows XP host PC? Is that OK with say about 10 users
> accessing data?
It will install fine, but SQL Server is a server product, and you should
really run it on a server OS. Even though you don't have to pay a lot for
it you should take the deployment decisions seriously. Especially with
Fortune 1000 customers, their XP desktops are probably managed in a way that
makes them unsuitable for running a server product. Run Windows 2003
Server.
> And Management Studio Express ? Is a gold release out yet or still CTP?
> I'm figuring we'd need to use this for deployment or at least make it
> available for clients just in case.
For development get your staff MSDN or SQL Server Developer Edition. This
will give you full access to all the tools. You can use Management Studio
Express manage your deployed instances.
David

Express, Can I really redistribute this with our app?

SQL 2005 Express, Can I really redistribute this with our app?
Background: We sell a software program to Fortune 1000, always previously
required clients to use or obtain at least SQL 2000 Standard, always a
tedious process on their end, ie budgets, licensing, IT folks, etc.
Our typical client site has about 10 users who use our program, and the SQL
DB on some nearby host server.
Our DB requirements fit Express such as size < 4GB and Ram < 1GB. We don't
do anything too fancy with the DB, about 70 tables, 70 triggers, 140 stored
procedures. We use an embedded ID & PW passed by application, so SQL Login
has to be in there, but we can probably be flexible here.
Is SQL Express really a deployable solution for us? I assume that we can
script stuff like DB creation along with Tables and other needed objects,
allowing a typcial small group of users to get up and running quickly.
Can the DB host be an XP o/s in this scenerio? Do we need Management
Studio Express ?
From what I've read up at ms.com, all of this seems possible, I just can't
believe it would be this easy.
Any and all comments will be greatly appreciated.
"John" <John@.discussions.microsoft.com> wrote in message
news:936531F0-26FF-432E-A783-AD8264CE486A@.microsoft.com...
> SQL 2005 Express, Can I really redistribute this with our app?
> Background: We sell a software program to Fortune 1000, always previously
> required clients to use or obtain at least SQL 2000 Standard, always a
> tedious process on their end, ie budgets, licensing, IT folks, etc.
> Our typical client site has about 10 users who use our program, and the
> SQL
> DB on some nearby host server.
> Our DB requirements fit Express such as size < 4GB and Ram < 1GB. We
> don't
> do anything too fancy with the DB, about 70 tables, 70 triggers, 140
> stored
> procedures. We use an embedded ID & PW passed by application, so SQL
> Login
> has to be in there, but we can probably be flexible here.
> Is SQL Express really a deployable solution for us? I assume that we can
> script stuff like DB creation along with Tables and other needed objects,
> allowing a typcial small group of users to get up and running quickly.
> Can the DB host be an XP o/s in this scenerio? Do we need Management
> Studio Express ?
> From what I've read up at ms.com, all of this seems possible, I just can't
> believe it would be this easy.
> Any and all comments will be greatly appreciated.
>
Yep, it's that easy. You need to register for redistribution rights
(http://www.microsoft.com/sql/edition...tregister.mspx).
Of course your customers might want to deploy your app on a higher SKU of
SQL Server, and they can always upgrade later.
David
|||Thanks David for confirming its that easy.
How about the Windows XP host PC? Is that OK with say about 10 users
accessing data?
And Management Studio Express ? Is a gold release out yet or still CTP?
I'm figuring we'd need to use this for deployment or at least make it
available for clients just in case.
Again, your comments are valuable and appreciated!
John
|||"John" <John@.discussions.microsoft.com> wrote in message
news:08D225AB-CB1E-44E4-BF80-D831FA3D57F8@.microsoft.com...
> Thanks David for confirming its that easy.
> How about the Windows XP host PC? Is that OK with say about 10 users
> accessing data?
It will install fine, but SQL Server is a server product, and you should
really run it on a server OS. Even though you don't have to pay a lot for
it you should take the deployment decisions seriously. Especially with
Fortune 1000 customers, their XP desktops are probably managed in a way that
makes them unsuitable for running a server product. Run Windows 2003
Server.

> And Management Studio Express ? Is a gold release out yet or still CTP?
> I'm figuring we'd need to use this for deployment or at least make it
> available for clients just in case.
For development get your staff MSDN or SQL Server Developer Edition. This
will give you full access to all the tools. You can use Management Studio
Express manage your deployed instances.
David

Express, Can I really redistribute this with our app?

SQL 2005 Express, Can I really redistribute this with our app?
Background: We sell a software program to Fortune 1000, always previously
required clients to use or obtain at least SQL 2000 Standard, always a
tedious process on their end, ie budgets, licensing, IT folks, etc.
Our typical client site has about 10 users who use our program, and the SQL
DB on some nearby host server.
Our DB requirements fit Express such as size < 4GB and Ram < 1GB. We don't
do anything too fancy with the DB, about 70 tables, 70 triggers, 140 stored
procedures. We use an embedded ID & PW passed by application, so SQL Login
has to be in there, but we can probably be flexible here.
Is SQL Express really a deployable solution for us? I assume that we can
script stuff like DB creation along with Tables and other needed objects,
allowing a typcial small group of users to get up and running quickly.
Can the DB host be an XP o/s in this scenerio? Do we need Management
Studio Express ?
From what I've read up at ms.com, all of this seems possible, I just can't
believe it would be this easy.
Any and all comments will be greatly appreciated."John" <John@.discussions.microsoft.com> wrote in message
news:936531F0-26FF-432E-A783-AD8264CE486A@.microsoft.com...
> SQL 2005 Express, Can I really redistribute this with our app?
> Background: We sell a software program to Fortune 1000, always previously
> required clients to use or obtain at least SQL 2000 Standard, always a
> tedious process on their end, ie budgets, licensing, IT folks, etc.
> Our typical client site has about 10 users who use our program, and the
> SQL
> DB on some nearby host server.
> Our DB requirements fit Express such as size < 4GB and Ram < 1GB. We
> don't
> do anything too fancy with the DB, about 70 tables, 70 triggers, 140
> stored
> procedures. We use an embedded ID & PW passed by application, so SQL
> Login
> has to be in there, but we can probably be flexible here.
> Is SQL Express really a deployable solution for us? I assume that we can
> script stuff like DB creation along with Tables and other needed objects,
> allowing a typcial small group of users to get up and running quickly.
> Can the DB host be an XP o/s in this scenerio? Do we need Management
> Studio Express ?
> From what I've read up at ms.com, all of this seems possible, I just can't
> believe it would be this easy.
> Any and all comments will be greatly appreciated.
>
Yep, it's that easy. You need to register for redistribution rights
(http://www.microsoft.com/sql/editio...stregister.mspx).
Of course your customers might want to deploy your app on a higher SKU of
SQL Server, and they can always upgrade later.
David|||Thanks David for confirming its that easy.
How about the Windows XP host PC? Is that OK with say about 10 users
accessing data?
And Management Studio Express ? Is a gold release out yet or still CTP?
I'm figuring we'd need to use this for deployment or at least make it
available for clients just in case.
Again, your comments are valuable and appreciated!
John|||"John" <John@.discussions.microsoft.com> wrote in message
news:08D225AB-CB1E-44E4-BF80-D831FA3D57F8@.microsoft.com...
> Thanks David for confirming its that easy.
> How about the Windows XP host PC? Is that OK with say about 10 users
> accessing data?
It will install fine, but SQL Server is a server product, and you should
really run it on a server OS. Even though you don't have to pay a lot for
it you should take the deployment decisions seriously. Especially with
Fortune 1000 customers, their XP desktops are probably managed in a way that
makes them unsuitable for running a server product. Run Windows 2003
Server.

> And Management Studio Express ? Is a gold release out yet or still CTP?
> I'm figuring we'd need to use this for deployment or at least make it
> available for clients just in case.
For development get your staff MSDN or SQL Server Developer Edition. This
will give you full access to all the tools. You can use Management Studio
Express manage your deployed instances.
David

Express cannot convert different data types automaticly?

I have a SQL Server 2000 database which works like a charm in my asp app. Joining tables by id columns which are of different data types (eg. int in one table and varchar in the other) is no problem with the SQL Server engine, but Native Client refuses to do so and hands me back an error. Even if columns have a slightly different collation (like Slovenian_CI_AS and SQL_Server_1250_CI_AS) SQL Express native client returns an ERROR?!!! Is it possible that Native client cannot resolve this on its own? Must I change the colattion and datatype on all my tables? Please help!!!!

Peca, developer from Serbia

If you have two columns in two tables, and one is, say PK in one table and FK on the other, then they really should be of the same datatype.

If possible, you should make changes where necessary.

I'm not familiar with Slovenian collations, but different collations may me incompatible between eachother, so there's a chance there could be errors when different collations are used at the same time.

/Kenneth

|||Yes Kenneth, you are right, but the same query works with the SQL SERVER 2000 engine and does not work with EXPRESS database engine. And the problem is that PK's cannot be of the same type in this case. "OLD" sql 2000 engine does not complain but the new one does. I just want to know if this is "solid rock rule" or I can change this behaviour and make him accept these querys?|||

Ah, I see... I haven't tried out those circumstances, so I'm sorry that I don't have a definite answer. Perhaps someone else will chime in with some insights.

/Kenneth

|||

All what you need is to specify collation explicitly in your queries while comparing charater data which collattion differ.

See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm

|||

Yes, I know that, but thank you anyway... With the collation I can live, but joining tables with different data types as pk's is a different story. If I say - join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").This really works in SQL Server 2000 but not in Express (returns an error). I am developing databases for years now and I've been working on databases with millions of records and hundreds of tables. This database cannot be normalised more than it is...I'll just stick to 2000 and forget about Express...A BIG thanks to all that replied to y question!!!

|||

>>join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").

Joining two tables throug int field and varchar field, MSSQL would try to convert varchar values into integer value and would generate an error as soon as 'aaa' value reached, unless you specify otherwise in your query.

|||

Agreed. That example has never worked in SQL 2000 either.
the conversion will follow the rules for datatype precedence, and the varchar will be converted to int when implicit conversion comes into play.

So, this is not some new behaviour by Express.

Small demo below from SQL Server 2000: (this is the expected behaviour)

create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go
insert #a select 1, 'a'
insert #b select 1, 'b'
insert #b select 'aaa', 'b'
go
select *
from #a join #b
on #a.pk = #b.pk
go

pk a pk b
-- - - -
1 a 1 b

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'aaa' to a column of data type int.

/Kenneth

|||

Kenneth, did I mention that I have the WHERE clausule? Ok, let's end this now and for all ! These are table definitions and bellow is the query that works in SQL 2000 (I can send screenshots to all that do not believe me) and DOES NOT work with EXPRESS (both your and my example).

Your example a bit rewriten:
create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go

insert #a select 1, 'a'
insert #a select 2, 'b'
insert #b select 'aaa', 'be'
insert #b select '1', 'c'
insert #b select '2', 'd'

go

select * from #a ,#b
where #a.pk = #b.pk and (#b.b='c' or #b.b='g') and #a.pk in (1)
go

My example that does the same

table :KOMP_REF pk:SIFRA varchar(50)
table :VESTI pk:ID int

they are not referenced in any way (there is no explicit reference between them e.g. constraints, referencial integrity etc.)

query VESTI:
select id,headline,text,date from vesti where id=11
result:
11 heder <FONT face="Courier New".. 2005-12-23 11:51:13.107

query KOMP_REF:
select * from komp_ref
result:
...
lentro 7 4 43 11 81 1 1
11 7 4 127 -1 22 1 1
...

values 81 and 22 are of column SIFRA_KOMP so I believe that row with the value 81 should be excluded from the join by the where clausule in the query bellow

query:
select vesti.id from vesti,komp_ref
where vesti.id=komp_ref.sifra and
(komp_ref.sifra_komp=22 or komp_ref.sifra_komp=64 or komp_ref.sifra_komp=65 or
komp_ref.sifra_komp=91 or komp_ref.sifra_komp=92 or komp_ref.sifra_komp=93)
and vesti.id IN (11)

Express error msg:
Conversion failed when converting the varchar value 'lentro ' to data type int.

Database is copied from sql2000 into express (just copied files without any import procedure etc.)without any changes (I just changed the conn string in my ASP app). I understand all you said but WHY does 2000 do not report an error? Maybe that is the right question? Why is OR operator so confusing for Express? Without the or #b.b='g' even your example works in express but when I add it only SQL2000 returns a recordset? Any comments?!?

|||Sorry KeWin, I wrote Kenneth by mistake :)|||

:) No worries, Kenneth is what my mother calls me also.
(sry for the late reply)

Unfortunately I don't have the ability to try this on Express at the moment, but...
What if you tried rewriting the join ANSI style instead of the old legacy syntax.

select vesti.id
from vesti
join komp_ref
on vesti.id = komp_ref.sifra
and komp_ref.sifra_komp in ('22', '64', '65', '91', '92', '93')
and vesti.id = 11

..see if anything changes..?

/Kenneth

|||unfortunately...no... still reporting the same error... But that is to be expected since the exapmle I gave above has a 100% valid SQL syntax and there are no reasons why it would not work... And did I mention that it works with 2000? ;) (less than 2000 times daaaaa) ... If I accidentaly found a bug (hope not because it would be a huuuuuuuuge one) the boys and girls from MS owe me a beer :) thanx 4 all Kewin!

Express cannot convert different data types automaticly?

I have a SQL Server 2000 database which works like a charm in my asp app. Joining tables by id columns which are of different data types (eg. int in one table and varchar in the other) is no problem with the SQL Server engine, but Native Client refuses to do so and hands me back an error. Even if columns have a slightly different collation (like Slovenian_CI_AS and SQL_Server_1250_CI_AS) SQL Express native client returns an ERROR?!!! Is it possible that Native client cannot resolve this on its own? Must I change the colattion and datatype on all my tables? Please help!!!!

Peca, developer from Serbia

If you have two columns in two tables, and one is, say PK in one table and FK on the other, then they really should be of the same datatype.

If possible, you should make changes where necessary.

I'm not familiar with Slovenian collations, but different collations may me incompatible between eachother, so there's a chance there could be errors when different collations are used at the same time.

/Kenneth

|||Yes Kenneth, you are right, but the same query works with the SQL SERVER 2000 engine and does not work with EXPRESS database engine. And the problem is that PK's cannot be of the same type in this case. "OLD" sql 2000 engine does not complain but the new one does. I just want to know if this is "solid rock rule" or I can change this behaviour and make him accept these querys?|||

Ah, I see... I haven't tried out those circumstances, so I'm sorry that I don't have a definite answer. Perhaps someone else will chime in with some insights.

/Kenneth

|||

All what you need is to specify collation explicitly in your queries while comparing charater data which collattion differ.

See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm

|||

Yes, I know that, but thank you anyway... With the collation I can live, but joining tables with different data types as pk's is a different story. If I say - join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").This really works in SQL Server 2000 but not in Express (returns an error). I am developing databases for years now and I've been working on databases with millions of records and hundreds of tables. This database cannot be normalised more than it is...I'll just stick to 2000 and forget about Express...A BIG thanks to all that replied to y question!!!

|||

>>join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").

Joining two tables throug int field and varchar field, MSSQL would try to convert varchar values into integer value and would generate an error as soon as 'aaa' value reached, unless you specify otherwise in your query.

|||

Agreed. That example has never worked in SQL 2000 either.
the conversion will follow the rules for datatype precedence, and the varchar will be converted to int when implicit conversion comes into play.

So, this is not some new behaviour by Express.

Small demo below from SQL Server 2000: (this is the expected behaviour)

create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go
insert #a select 1, 'a'
insert #b select 1, 'b'
insert #b select 'aaa', 'b'
go
select *
from #a join #b
on #a.pk = #b.pk
go

pk a pk b
-- - - -
1 a 1 b

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'aaa' to a column of data type int.

/Kenneth

|||

Kenneth, did I mention that I have the WHERE clausule? Ok, let's end this now and for all ! These are table definitions and bellow is the query that works in SQL 2000 (I can send screenshots to all that do not believe me) and DOES NOT work with EXPRESS (both your and my example).

Your example a bit rewriten:
create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go

insert #a select 1, 'a'
insert #a select 2, 'b'
insert #b select 'aaa', 'be'
insert #b select '1', 'c'
insert #b select '2', 'd'

go

select * from #a ,#b
where #a.pk = #b.pk and (#b.b='c' or #b.b='g') and #a.pk in (1)
go

My example that does the same

table :KOMP_REF pk:SIFRA varchar(50)
table :VESTI pk:ID int

they are not referenced in any way (there is no explicit reference between them e.g. constraints, referencial integrity etc.)

query VESTI:
select id,headline,text,date from vesti where id=11
result:
11 heder <FONT face="Courier New".. 2005-12-23 11:51:13.107

query KOMP_REF:
select * from komp_ref
result:
...
lentro 7 4 43 11 81 1 1
11 7 4 127 -1 22 1 1
...

values 81 and 22 are of column SIFRA_KOMP so I believe that row with the value 81 should be excluded from the join by the where clausule in the query bellow

query:
select vesti.id from vesti,komp_ref
where vesti.id=komp_ref.sifra and
(komp_ref.sifra_komp=22 or komp_ref.sifra_komp=64 or komp_ref.sifra_komp=65 or
komp_ref.sifra_komp=91 or komp_ref.sifra_komp=92 or komp_ref.sifra_komp=93)
and vesti.id IN (11)

Express error msg:
Conversion failed when converting the varchar value 'lentro ' to data type int.

Database is copied from sql2000 into express (just copied files without any import procedure etc.)without any changes (I just changed the conn string in my ASP app). I understand all you said but WHY does 2000 do not report an error? Maybe that is the right question? Why is OR operator so confusing for Express? Without the or #b.b='g' even your example works in express but when I add it only SQL2000 returns a recordset? Any comments?!?

|||Sorry KeWin, I wrote Kenneth by mistake :)|||

:) No worries, Kenneth is what my mother calls me also.
(sry for the late reply)

Unfortunately I don't have the ability to try this on Express at the moment, but...
What if you tried rewriting the join ANSI style instead of the old legacy syntax.

select vesti.id
from vesti
join komp_ref
on vesti.id = komp_ref.sifra
and komp_ref.sifra_komp in ('22', '64', '65', '91', '92', '93')
and vesti.id = 11

..see if anything changes..?

/Kenneth

|||unfortunately...no... still reporting the same error... But that is to be expected since the exapmle I gave above has a 100% valid SQL syntax and there are no reasons why it would not work... And did I mention that it works with 2000? ;) (less than 2000 times daaaaa) ... If I accidentaly found a bug (hope not because it would be a huuuuuuuuge one) the boys and girls from MS owe me a beer :) thanx 4 all Kewin!

Express and AspNet_RegSql.exe

Can the Asp Net exe sql script be used to add the aspnetdb database to an Sq
l
Express?
I am doing this for running the App Blocks.
The error that comes back is "can not connect".
Thaks,
MarcCheck to see if you need to specify the instance name for the connection. I
know that when I installed a CTP version of 2005, the "standard" installatio
n
created a non-default instance.
"Marc" wrote:

> Can the Asp Net exe sql script be used to add the aspnetdb database to an
Sql
> Express?
> I am doing this for running the App Blocks.
> The error that comes back is "can not connect".
> Thaks,
> Marc
>

Exporting XML file format issue

Hello
I am trying to generate an XML file from SQL Server which gets pushed out to
a third party app using DTS. So Far I have created a sproc like so.
CREATE PROC querystrCR
AS
SELECT 1 as Tag,
NULL as Parent,
'<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
'<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
'<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
'<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
'<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
'<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
'<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
FROM CR
FOR XML EXPLICIT
It needs the CDATA fields as they contain several odd characters. Then I
export the file as part of a DTS package like so;
EXEC sp_makewebtask
@.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
@.query = 'EXEC querystrCR',
@.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
The template file looks like
<?xml version="1.0" encoding="utf-8" ?>
<DB>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</DB>
The problem is with the format of the output file. SQL Server spits it out
in rows which don't correspond to the tags e.g. a line end like
</CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
Obviously this means the third party app. will not parse it and a web
browser will not open the file e.g. Firefox reports "XML Parsing Error: not
well-formed". I need the output in the exported file to look
<CPB>
<CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
<CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
<CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
<CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
<CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
<PRIO><![CDATA[ "1" ]]></PRIO>
<MTIME><![CDATA[ "999999999" ]]></MTIME>
</CPB>
Any Help would be appreciated.
Cheers
Matt
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOADA couple of points:
1. Instead of manually constructing the <![CDATA[ use the !cdata directive.
2. However, the CDATA section should not give you anything that you cannot
achieve otherwise. In particular it does not help with invalid characters
(what are your odd characters?). So you may not need to use it.
3. FOR XML results per default return fragments. In order to export it as a
document, add the root node (there are a couple of ways depending on the API
used).
4. If you want to expose the XML, you should use either the respective
stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked XML
in approx 4kBytes blocks that you need to merge yourself.
HTH
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:4207e650$1@.news01.wxnz.net...
> Hello
> I am trying to generate an XML file from SQL Server which gets pushed out
> to a third party app using DTS. So Far I have created a sproc like so.
> CREATE PROC querystrCR
> AS
> SELECT 1 as Tag,
> NULL as Parent,
> '<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
> '<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
> '<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
> '<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
> '<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
> '<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
> '<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
> FROM CR
> FOR XML EXPLICIT
>
> It needs the CDATA fields as they contain several odd characters. Then I
> export the file as part of a DTS package like so;
> EXEC sp_makewebtask
> @.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
> @.query = 'EXEC querystrCR',
> @.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
> The template file looks like
> <?xml version="1.0" encoding="utf-8" ?>
> <DB>
> <%begindetail%>
> <%insert_data_here%>
> <%enddetail%>
> </DB>
> The problem is with the format of the output file. SQL Server spits it out
> in rows which don't correspond to the tags e.g. a line end like
> </CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
> Obviously this means the third party app. will not parse it and a web
> browser will not open the file e.g. Firefox reports "XML Parsing Error:
> not well-formed". I need the output in the exported file to look
> <CPB>
> <CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
> <CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
> <CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
> <CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
> <CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
> <PRIO><![CDATA[ "1" ]]></PRIO>
> <MTIME><![CDATA[ "999999999" ]]></MTIME>
> </CPB>
> Any Help would be appreciated.
> Cheers
> Matt
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>|||I have everything I want in my XML file the only thing I need to format now
is the CR. I know this because when I open the example XML that the third
party app will parse, in notepad I see the square cube CR character. If I
manually edit my XML file in notepad and "paste" this char in after every
closing TAG the XML I generated is parsed and imported to the app.
I'm pulling the XML over HTTP using this script; Can I format this XML here?
var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
xml.async = false;
xml.load("http://localhost/routingtwo/template/test.xml");
xml.save("testoutput.xml")
or can I do it in my template test.xml
<?xml version="1.0" encoding="UTF-8"?>
<CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
exec CSM.dbo.querystrCR
</sql:query>
</CSM>
or is it in the actual SQL query?
Cheers
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>A couple of points:
> 1. Instead of manually constructing the <![CDATA[ use the !cdata
> directive.
> 2. However, the CDATA section should not give you anything that you cannot
> achieve otherwise. In particular it does not help with invalid characters
> (what are your odd characters?). So you may not need to use it.
> 3. FOR XML results per default return fragments. In order to export it as
> a document, add the root node (there are a couple of ways depending on the
> API used).
> 4. If you want to expose the XML, you should use either the respective
> stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
> or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked
> XML in approx 4kBytes blocks that you need to merge yourself.
> HTH
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:4207e650$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD|||Why would you want a carriage return added? An XML parser always replaces a
CR (or a CR/LF sequence) with a LF. So where do you need to send the CR to?
Does it also work without the CR present?
Thanks
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420a7ee3$1@.news01.wxnz.net...
>I have everything I want in my XML file the only thing I need to format now
>is the CR. I know this because when I open the example XML that the third
>party app will parse, in notepad I see the square cube CR character. If I
>manually edit my XML file in notepad and "paste" this char in after every
>closing TAG the XML I generated is parsed and imported to the app.
> I'm pulling the XML over HTTP using this script; Can I format this XML
> here?
> var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
> xml.async = false;
> xml.load("http://localhost/routingtwo/template/test.xml");
> xml.save("testoutput.xml")
> or can I do it in my template test.xml
> <?xml version="1.0" encoding="UTF-8"?>
> <CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <sql:query>
> exec CSM.dbo.querystrCR
> </sql:query>
> </CSM>
> or is it in the actual SQL query?
> Cheers
> Matt
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>|||My error it was an encoding problem - I was saving my XML template as ANSI
not Unicode.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Why would you want a carriage return added? An XML parser always replaces
> a CR (or a CR/LF sequence) with a LF. So where do you need to send the CR
> to? Does it also work without the CR present?
> Thanks
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:420a7ee3$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD|||Cool (well, now it is :-)).
Best regards
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420be8dd$1@.news01.wxnz.net...
> My error it was an encoding problem - I was saving my XML template as ANSI
> not Unicode.
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>

Exporting XML file format issue

Hello
I am trying to generate an XML file from SQL Server which gets pushed out to
a third party app using DTS. So Far I have created a sproc like so.
CREATE PROC querystrCR
AS
SELECT 1 as Tag,
NULL as Parent,
'<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
'<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
'<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
'<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
'<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
'<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
'<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
FROM CR
FOR XML EXPLICIT
It needs the CDATA fields as they contain several odd characters. Then I
export the file as part of a DTS package like so;
EXEC sp_makewebtask
@.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
@.query = 'EXEC querystrCR',
@.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
The template file looks like
<?xml version="1.0" encoding="utf-8" ?>
<DB>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</DB>
The problem is with the format of the output file. SQL Server spits it out
in rows which don't correspond to the tags e.g. a line end like
</CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
Obviously this means the third party app. will not parse it and a web
browser will not open the file e.g. Firefox reports "XML Parsing Error: not
well-formed". I need the output in the exported file to look
<CPB>
<CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
<CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
<CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
<CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
<CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
<PRIO><![CDATA[ "1" ]]></PRIO>
<MTIME><![CDATA[ "999999999" ]]></MTIME>
</CPB>
Any Help would be appreciated.
Cheers
Matt
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
A couple of points:
1. Instead of manually constructing the <![CDATA[ use the !cdata directive.
2. However, the CDATA section should not give you anything that you cannot
achieve otherwise. In particular it does not help with invalid characters
(what are your odd characters?). So you may not need to use it.
3. FOR XML results per default return fragments. In order to export it as a
document, add the root node (there are a couple of ways depending on the API
used).
4. If you want to expose the XML, you should use either the respective
stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked XML
in approx 4kBytes blocks that you need to merge yourself.
HTH
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:4207e650$1@.news01.wxnz.net...
> Hello
> I am trying to generate an XML file from SQL Server which gets pushed out
> to a third party app using DTS. So Far I have created a sproc like so.
> CREATE PROC querystrCR
> AS
> SELECT 1 as Tag,
> NULL as Parent,
> '<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
> '<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
> '<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
> '<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
> '<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
> '<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
> '<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
> FROM CR
> FOR XML EXPLICIT
>
> It needs the CDATA fields as they contain several odd characters. Then I
> export the file as part of a DTS package like so;
> EXEC sp_makewebtask
> @.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
> @.query = 'EXEC querystrCR',
> @.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
> The template file looks like
> <?xml version="1.0" encoding="utf-8" ?>
> <DB>
> <%begindetail%>
> <%insert_data_here%>
> <%enddetail%>
> </DB>
> The problem is with the format of the output file. SQL Server spits it out
> in rows which don't correspond to the tags e.g. a line end like
> </CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
> Obviously this means the third party app. will not parse it and a web
> browser will not open the file e.g. Firefox reports "XML Parsing Error:
> not well-formed". I need the output in the exported file to look
> <CPB>
> <CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
> <CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
> <CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
> <CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
> <CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
> <PRIO><![CDATA[ "1" ]]></PRIO>
> <MTIME><![CDATA[ "999999999" ]]></MTIME>
> </CPB>
> Any Help would be appreciated.
> Cheers
> Matt
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>
|||I have everything I want in my XML file the only thing I need to format now
is the CR. I know this because when I open the example XML that the third
party app will parse, in notepad I see the square cube CR character. If I
manually edit my XML file in notepad and "paste" this char in after every
closing TAG the XML I generated is parsed and imported to the app.
I'm pulling the XML over HTTP using this script; Can I format this XML here?
var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
xml.async = false;
xml.load("http://localhost/routingtwo/template/test.xml");
xml.save("testoutput.xml")
or can I do it in my template test.xml
<?xml version="1.0" encoding="UTF-8"?>
<CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
exec CSM.dbo.querystrCR
</sql:query>
</CSM>
or is it in the actual SQL query?
Cheers
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>A couple of points:
> 1. Instead of manually constructing the <![CDATA[ use the !cdata
> directive.
> 2. However, the CDATA section should not give you anything that you cannot
> achieve otherwise. In particular it does not help with invalid characters
> (what are your odd characters?). So you may not need to use it.
> 3. FOR XML results per default return fragments. In order to export it as
> a document, add the root node (there are a couple of ways depending on the
> API used).
> 4. If you want to expose the XML, you should use either the respective
> stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
> or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked
> XML in approx 4kBytes blocks that you need to merge yourself.
> HTH
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:4207e650$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
|||Why would you want a carriage return added? An XML parser always replaces a
CR (or a CR/LF sequence) with a LF. So where do you need to send the CR to?
Does it also work without the CR present?
Thanks
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420a7ee3$1@.news01.wxnz.net...
>I have everything I want in my XML file the only thing I need to format now
>is the CR. I know this because when I open the example XML that the third
>party app will parse, in notepad I see the square cube CR character. If I
>manually edit my XML file in notepad and "paste" this char in after every
>closing TAG the XML I generated is parsed and imported to the app.
> I'm pulling the XML over HTTP using this script; Can I format this XML
> here?
> var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
> xml.async = false;
> xml.load("http://localhost/routingtwo/template/test.xml");
> xml.save("testoutput.xml")
> or can I do it in my template test.xml
> <?xml version="1.0" encoding="UTF-8"?>
> <CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <sql:query>
> exec CSM.dbo.querystrCR
> </sql:query>
> </CSM>
> or is it in the actual SQL query?
> Cheers
> Matt
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>
|||My error it was an encoding problem - I was saving my XML template as ANSI
not Unicode.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Why would you want a carriage return added? An XML parser always replaces
> a CR (or a CR/LF sequence) with a LF. So where do you need to send the CR
> to? Does it also work without the CR present?
> Thanks
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:420a7ee3$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
|||Cool (well, now it is :-)).
Best regards
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420be8dd$1@.news01.wxnz.net...
> My error it was an encoding problem - I was saving my XML template as ANSI
> not Unicode.
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>