Outer table inside stored procedure

Hi,
I am using SQL Server 2000
I need to select values from a table that belongs to other database from
within a stored procedure. Hardcoding table's fully qualified name is not
appropriate. Table's database name must be a parameter of a stored procedure
.
Is there any better way than using dynamic query string generation? If query
is a dynamic string it is difficult to mainain it.
Thanks in advance.You could create a view which includes the database name - then when you nee
d
to change the database just change the view and all the SPs will reference
tthe correct database.
"Alexander Korol" wrote:

> Hi,
> I am using SQL Server 2000
> I need to select values from a table that belongs to other database from
> within a stored procedure. Hardcoding table's fully qualified name is not
> appropriate. Table's database name must be a parameter of a stored procedu
re.
> Is there any better way than using dynamic query string generation? If que
ry
> is a dynamic string it is difficult to mainain it.
> Thanks in advance.|||Sorry I made a multipost. Was not intended to just missed the window :) .
From now on let's refer to "How to refer from a stored procedure to a table
in another database"
Thanks for your reply.
I can not do anything beforehand. In the runtime my procedure can get only
the name of a database of guaranteed structure. Thereis a way to change the
database name in the view from stored procedure in the runtime ALTER VIEW. I
s
that what you ment? I will try.|||Dynamic altering the view helped. Thanks.
A very simple example:
exec('ALTER VIEW ViewName AS SELECT * FROM ' + @.NM_DATABASE +
'.dbo.TableName')

Outer join runs differently on SQL Server and Oracle

Oracle 9i:
create table t(i integer)
Table created
insert into t values(1)
1 row inserted
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
0 rows selected
-- I beleive this is wrong
drop table t
Table dropped
the same query against MS SQL Server 2000:
create table t(i integer)
insert into t values(1)
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
i1 i2
-- --
1 NULL
-- I think this is correct
(1 row(s) affected)
drop table t
What do you thinkAK wrote:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
I think you're using an unpatched release of Oracle 9i, whichever
release that may be (9i says NOTHING about which release or patch level
you're using):
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL>
SQL> insert into t values(1);
1 row created.
SQL>
SQL>
SQL> select t1.i i1, t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
The results look exactly the same to me between the two. Care to try
again, this time with a patched release of Oracle?
David Fitzjarrell|||AK schrieb:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
>
Oracle ansi join implementation had some bugs ( much of them fixed in
recent versions/patches ), but in your case i definitely can't reproduce
your behaviour ( both on 9iR2 and 10gR1/R2)
oracle@.col-fc1-02:~/sql >sqlplus scott/tiger
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 11 19:19:29 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
Here is output for 10gR1
oracle@.wks01:~> sqlplus scott/tiger
SQL*Plus: Release 10.1.0.3.0 - Production on Do Aug 11 18:05:22 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0
3 /
I1 I2
-- --
1
SQL>
Best regards
Maxim|||yep, one more reason to patch up:
Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
Thanks for the feedback

Outer Join Problem

I am trying to gather two sets of data for a form. I want distinct values from the pricebook.productid and pricebook.productname to be displayed in two columns and the results of the cast statement in a third. problem is the cast statement may not contain all of the productid's or productnames from the first two columns but i still need them displayed with a zero in the third....

Can anyone steer me in the right direction?

SELECT pricebook.productid, pricebook.productname, CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2))
FROM ticket left outer JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productidOriginally posted by hellhound
I am trying to gather two sets of data for a form. I want distinct values from the pricebook.productid and pricebook.productname to be displayed in two columns and the results of the cast statement in a third. problem is the cast statement may not contain all of the productid's or productnames from the first two columns but i still need them displayed with a zero in the third....

Can anyone steer me in the right direction?

SELECT pricebook.productid, pricebook.productname, CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2))
FROM ticket left outer JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productid

Use ISNULL|||Could you elaborate? Something like this?

SELECT pricebook.productid, pricebook.productname,
(select distinct isnull(CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2)), '0') as SoldTons
from ticket WHERE (RUNDATE = '2004-02-17') AND (plantid = '00045') AND (prodid != '888')
group by prodid)
FROM ticket full JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND(pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productid

Originally posted by smasanam
Use ISNULL|||Select CAST(ISNULL(null, 0) AS Decimal(10, 2))

SELECT pricebook.productid, pricebook.productname,
CAST(ISNULL(ROUND(SUM(PRODQTY), 2), 0) AS Decimal(10, 2))
FROM ticket left outer JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productid|||I don't think I am being clear enough. Forgive me...

Basically i have data in two tables, one contains the prodid and name, the other contains the prodid and qty. i want one table with all the prodid's and names combined with the qty values from the other. Not all prodid's will have qty data, so i need those as '0'......

clear as mud?

thanks for your help.

Originally posted by r123456
Select CAST(ISNULL(null, 0) AS Decimal(10, 2))

SELECT pricebook.productid, pricebook.productname,
CAST(ISNULL(ROUND(SUM(PRODQTY), 2), 0) AS Decimal(10, 2))
FROM ticket left outer JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productid|||Originally posted by hellhound
I don't think I am being clear enough. Forgive me...

Basically i have data in two tables, one contains the prodid and name, the other contains the prodid and qty. i want one table with all the prodid's and names combined with the qty values from the other. Not all prodid's will have qty data, so i need those as '0'......

clear as mud?

thanks for your help.
If you mean

Product Id Product Name, QTY
-----------
1 abc 100
2 efg 50
3 effff NULL

And QTY is in the 2nd Table then I think r123456's Query should work. If not

Can you please give more explanation|||Originally posted by smasanam
If you mean

Product Id Product Name, QTY
-----------
1 abc 100
2 efg 50
3 effff NULL

And QTY is in the 2nd Table then I think r123456's Query should work. If not

Can you please give more explanation

ok you are very close there, but when I run r123456's Query, i get multiple Productid and product name values.

ex.

Product Id Product Name, QTY
-----------
1 abc 100
1 abc 50
1 abc NULL
2 efg 100
2 efg 50
2 efg NULL
3 effff 100
3 effff 50
3 effff NULL|||Can you sum them up?

Or you need induvidual records??|||Can you please tell us the relationship between those 2 tables??|||Originally posted by smasanam
Can you please tell us the relationship between those 2 tables??

Here is an example of the table setup.

pricebook

productid productname
--------
abc abc product
def def product
efff efff product

Ticket

Prodid qty
----------
abc 20
abc 100
abc 50
def 150
def 200


Here is what I need from these;

Productid Productname QTY
--------------
abc abc product 170
def def product 350
efff efff product 0

Make any better sense?

i only need one distinct listing for the productid as determined by the where clause, and a sum for the qty... or 0 if it is not in the qty table.|||The join condition between the two tables is not clear. In your example, Pricebook.ProductID = 'abc abc', however Ticket.productID = 'abc'.

select pb.productID, sum(NVL(t.QTY, 0))
from priceBook pb
LEFT OUTER JOIN
ticket t ON
pb.productID = t.productID;
group by pb.productid;|||Originally posted by r123456
The join condition between the two tables is not clear. In your example, Pricebook.ProductID = 'abc abc', however Ticket.productID = 'abc'.

select pb.productID, sum(NVL(t.QTY, 0))
from priceBook pb
LEFT OUTER JOIN
ticket t ON
pb.productID = t.productID;
group by pb.productid;

now that i look back that was confusing;;;

tblpricebook

productid | productname
--------------------
abc | abc product
def | def product
efff | efff product

tblTicket

Prodid | qty
---------------------
abc | 20
abc | 100
abc | 50
def | 150
def | 200


Here is what I need from these;

Result

Productid | Productname | QTY
------------------------
abc | abc product | 170
def | def product | 350
efff | efff product | 0

Basically some productid appears multiple times with differing qty's in the ticket tbl. All productid's appear once in the pricebook table.
I need all the productid's from pricebook listed with appropriate sums from the ticket table and where there are no sums from the ticket table a null or zero int the result.

I really appreciate your patience on this.|||OK, this works...

select distinct pb.productid, isnull(sum(t.prodQTY), 0) as SOLDTONS
from priceBook pb left outer JOIN
ticket t ON
pb.productID = t.prodID
where pb.pricegroupid = '00045' /*and t.rundate = '2004-02-18'*/
group by pb.productid

only problem is if i narrow down the selection with the rundate in the where clause, I then limit my results to only what is contained in the ticket table....

Is Having my answer?|||move the condition on the t column into the ON clause for the left outer join

oh, and you do not need DISTINCT when you use GROUP BY, groups are distinct by definition:select pb.productid
, isnull(sum(t.prodQTY), 0) as SOLDTONS
from priceBook pb
left outer
join ticket t
on pb.productID = t.prodID
and t.rundate = '2004-02-18'
where pb.pricegroupid = '00045'
group
by pb.productid|||Originally posted by r937
move the condition on the t column into the ON clause for the left outer join

oh, and you do not need DISTINCT when you use GROUP BY, groups are distinct by definition:select pb.productid
, isnull(sum(t.prodQTY), 0) as SOLDTONS
from priceBook pb
left outer
join ticket t
on pb.productID = t.prodID
and t.rundate = '2004-02-18'
where pb.pricegroupid = '00045'
group
by pb.productid

EXCELLENT! Thanks for all your help!

mb

Out of range datetime value error when inserting using select...union

Hi all,
I am getting this error when insert values from one table to another in
the first table the values are varchar (10). In the second they are
datetime. The format of the data is mm/dd/yyyy to be easily converted
to dates. The conversion in this case is implicit as indicated in SQL
Server documentation. Here is my query:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description
UNION
SELECT program_id + 100000, program_name, start_date, end_date,
campaign_id AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.programs
UNION
SELECT execution_id + 200000, execution_name, start_date,
end_date, program_id + 100000 AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.executions
UNION
SELECT wave_id + 300000, wave_name, start_date, end_date,
mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL AS
ListID
FROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executions
WHERE mysqlfactiva.dbo.waves.execution_id =
mysqlfactiva.dbo.executions.execution_id

I am referencing programs table two times. If I just select this all I
get all data I need. When doing insert I get a message:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. The statement has been terminated.

If I execute just first part of the query before first union, I insert
data fine:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description

As soon as I use union I get the above error. This is very strange
since even when I execute the query using first union where the dates
come from the same table 'programs' I get the error. Why I can insert
from programs first time and can's second time?

Any help will be appreciated.

Thanks,
Stan(suslikovich@.yahoo.com) writes:
> I am getting this error when insert values from one table to another in
> the first table the values are varchar (10). In the second they are
> datetime. The format of the data is mm/dd/yyyy to be easily converted
> to dates. The conversion in this case is implicit as indicated in SQL
> Server documentation. Here is my query:

Note that the interpretation is dependent on the settings for DATEFORMAT
and LANGUAGE.

> INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
> ListID)
> SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
> mysqlfactiva.dbo.campaigns.campaign_name AS Name,
> MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,
> MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
> NULL AS ParentID,
> NULL AS ListID
> FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs

So the data type of mysqlfactiva.dbo.programs.start_date is varchar(10)?

Excuse me, if I am nosy, but a MIN on that columns appears somewhat
funny to me. If there is a date in early January you will use that
date, no matter which year it is in?

Better would be

MIN (CASE WHEN isdate(start_date) = 1 THEN start_date END

> As soon as I use union I get the above error. This is very strange
> since even when I execute the query using first union where the dates
> come from the same table 'programs' I get the error. Why I can insert
> from programs first time and can's second time?

In the first query, you are only taking the first value, in the
second query you are taking all the values. This query should give
you the bad data:

SELECT * FROM mysqlfactiva.dbo.programs.start_date
WHERE isdate(start_date) = 0 OR isdate(end_date) = 0

By the way, I seem recall that MySQL has a very liberal view on what
is a good date...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

OSQL output

> Is there a way to get the output of the OSQL as tabular values so I can passn">
> the query (without OPENROWSET inside the query) using OSQL, get the result
s
> in tabular format (like a table) and then save the results in main table.
The output of osql.exe is text. You typically need an intermediate
program/script to parse it and reformat it before feeding the result into a
table(s).
Linchi
"Alex" wrote:

> Hi,
> I run a script on all of our SQL Servers (+300 servers) on a regular basis
> to collect information about different instances, version, port and so on
> for each SQL Server using a loop than runs OSQL and passes a query to it f
or
> each member server. The passed query then collects this information on eac
h
> server and saves the results in main server using OPENROWSET.
> Due to different SQL Server and MDAC versions on each member SQL server,
> sometimes OPENROWSET fails to saves its results.
> Is there a way to get the output of the OSQL as tabular values so I can pa
ss
> the query (without OPENROWSET inside the query) using OSQL, get the result
s
> in tabular format (like a table) and then save the results in main table.
> This way, I will avoid using costly OPENROWSET.
> My main server is SQL Server 2000 SP4 and we have different range of SQL
> Servers and SPs.
> Thanks
>
>Hi,
I run a script on all of our SQL Servers (+300 servers) on a regular basis
to collect information about different instances, version, port and so on
for each SQL Server using a loop than runs OSQL and passes a query to it for
each member server. The passed query then collects this information on each
server and saves the results in main server using OPENROWSET.
Due to different SQL Server and MDAC versions on each member SQL server,
sometimes OPENROWSET fails to saves its results.
Is there a way to get the output of the OSQL as tabular values so I can pass
the query (without OPENROWSET inside the query) using OSQL, get the results
in tabular format (like a table) and then save the results in main table.
This way, I will avoid using costly OPENROWSET.
My main server is SQL Server 2000 SP4 and we have different range of SQL
Servers and SPs.
Thanks|||> Is there a way to get the output of the OSQL as tabular values so I can passn">
> the query (without OPENROWSET inside the query) using OSQL, get the result
s
> in tabular format (like a table) and then save the results in main table.
The output of osql.exe is text. You typically need an intermediate
program/script to parse it and reformat it before feeding the result into a
table(s).
Linchi
"Alex" wrote:

> Hi,
> I run a script on all of our SQL Servers (+300 servers) on a regular basis
> to collect information about different instances, version, port and so on
> for each SQL Server using a loop than runs OSQL and passes a query to it f
or
> each member server. The passed query then collects this information on eac
h
> server and saves the results in main server using OPENROWSET.
> Due to different SQL Server and MDAC versions on each member SQL server,
> sometimes OPENROWSET fails to saves its results.
> Is there a way to get the output of the OSQL as tabular values so I can pa
ss
> the query (without OPENROWSET inside the query) using OSQL, get the result
s
> in tabular format (like a table) and then save the results in main table.
> This way, I will avoid using costly OPENROWSET.
> My main server is SQL Server 2000 SP4 and we have different range of SQL
> Servers and SPs.
> Thanks
>
>

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Fvrier')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Fvrier become Fvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Fvrier')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Fvrier become Fvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Fvrier')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Fvrier become Fvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Fvrier')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Fvrier become Fvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Fvrier')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Fvrier become Fvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome."Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Fvrier')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Fvrier become Fvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Février')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Février become FÚvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome."Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Février')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Février become FÚvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> I've an SQL file which contain accent characters to fill my default
>> values like this:
>> insert into mtytable... values('Février')
>> and also I create storedprocedures which execute some insert / update
>> command with the same values.
>> The SQL file is correct.
>> I've a batch file (.bat) which execute this command :
>> osql -E -i "MyScript.sql" -d MyDatabase
>> but the result is:
>> the accents disappeared !
>> Février become FÚvrier in the database!
>> My stored procedure has changed during the OSQL execution.
>> How can I solve the problem?
>> The storeprocedure (and other commands) are correct in the .sql file.
>> thanks.
>> Jerome.
>>
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>