No Inner Joins?

Hello:

I'm relatively new to MSQL - I come from a MySQL background and have a crap load of experience with Access/Jet. I tried executing a query in MSQL last night, and got an error. Here's my query:

update inv_mast inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid SET inv_mast.short_code = 'Entrelec' WHERE inv_loc.primary_supplier_id = '100086'

I got the error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

So I tried changing the syntax to:

UPDATE inv_mast
SET inv_mast.short_code = 'Entrelec'
FROM inv_mast, inv_loc
WHERE inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
AND inv_loc.primary_supplier_id = '100086'

And didn't get an error, but I didn't get any results (0 row(s) affected).

What am I doing wrong?

If my queries don't give enough of an explanation of the layout of the data, let me know, I'll gladly explain more.

Thanks all! :)

-jimyour update statment probably should have looked like:
update inv_mast
SET inv_mast.short_code = 'Entrelec'
from inv_mast
inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
WHERE inv_loc.primary_supplier_id = '100086'

and if you like aliases:
update im
SET im.short_code = 'Entrelec'
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
WHERE il.primary_supplier_id = '100086'

as to why you didn't update anything, maybe your keys don't line up or you have nothing that satisfies your where clause. what does the following produce?
select im.inv_mast_uid,il.primary_supplier_id
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
order by 1,2|||Paul,

Thanks a TON for your response! The last query you posted lists all the item master ID's with their vendor IDs. So I guess the keys are good.

I'm going to try your update query now...

Dude - I owe you big time!! It worked like a charm - how can I thank you?

-jim

No field delimiters using bcp command

Hi,

I am using a bcp command to load data into a text file . The command is below:

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.tx
t -c -U sax -S KARAFOKAS -C 1252 -P passsax

The command runs fine , the problem is , the output in the text file is with tab delimited form. I want the format NOT to have tab delimited form but the values actually to have a continuation. That is, nothing to split one value from the other.

This is the output with tab delimited format.

vvvXXXXXXXXXXXXXXXXXXXXXX 20071112 h 0000000000005555,70 066,50000
abcXXXXXXXXXXXXXXXXXXXXXX 19000101 y 0454545454523456,45 077,30000
xyzcccXXXXXXXXXXXXXXXXXXX 19000101 x 0000000000003456,00 077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX 20030302 6 0000000000232323,45 005,00000

I want the output to have to tabs , as shown below:

vvvXXXXXXXXXXXXXXXXXXXXXX20071112h0000000000005555 ,70066,50000
abcXXXXXXXXXXXXXXXXXXXXXX19000101y0454545454523456 ,45077,30000
xyzcccXXXXXXXXXXXXXXXXXXX19000101x0000000000003456 ,00077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX2003030260000000000232323 ,45005,00000

Columns values should not be seperated by tabs. Any thoughts?

Thank you
George

Quote:

Originally Posted by karafokas

Hi,

I am using a bcp command to load data into a text file . The command is below:

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.tx
t -c -U sax -S KARAFOKAS -C 1252 -P passsax

The command runs fine , the problem is , the output in the text file is with tab delimited form. I want the format NOT to have tab delimited form but the values actually to have a continuation. That is, nothing to split one value from the other.

This is the output with tab delimited format.

vvvXXXXXXXXXXXXXXXXXXXXXX 20071112 h 0000000000005555,70 066,50000
abcXXXXXXXXXXXXXXXXXXXXXX 19000101 y 0454545454523456,45 077,30000
xyzcccXXXXXXXXXXXXXXXXXXX 19000101 x 0000000000003456,00 077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX 20030302 6 0000000000232323,45 005,00000

I want the output to have to tabs , as shown below:

vvvXXXXXXXXXXXXXXXXXXXXXX20071112h0000000000005555 ,70066,50000
abcXXXXXXXXXXXXXXXXXXXXXX19000101y0454545454523456 ,45077,30000
xyzcccXXXXXXXXXXXXXXXXXXX19000101x0000000000003456 ,00077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX2003030260000000000232323 ,45005,00000

Columns values should not be seperated by tabs. Any thoughts?

Thank you
George

looks like you want a fixed-length output. have you tried passing -t "" ? or something like that? -t is the bcp parameter for field terminator.sql

No Factory?

I've tried all the things needed to load a provider. It does create an entry by putting this into App.config:

<system.data>
<DbProviderFactories>
<add name="Sql Everywhere Data Provider"
invariant="System.Data.SqlServerCe"
description=".NET Framework Data Provider for Microsoft SQL
Everywhere CTP Edition 3.1"
type="System.Data.SqlServerCe.SqlServerCeFactory,
System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>

However, it fails to load when I call DbProviderFactories.GetFactory("System.Data.SqlServerCe");

The root cause: In the SQL Everywhere dll, System.Data.SqlServerCe.dll,
there appears to be no class such as SqlServerCeFactory. Nor anything
like unto it was found in the object browser.

Is this true? If so, it really sucks. What happened to SQL Everywhere
being compatible with the full .Net 2.0 framework?

Nathan

The type in the above Machine.Config entry is wrong. Please have a look at http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=674426&SiteID=17

Thanks,

Laxmi

NLB and SQL2005

Hi,
early on in SQL2005 (Yukon's) life, there was talk for Network Load
Balancing support.
Now that it's (more or less) out, the documentation makes no mention of
support for NLB.
There's talk of clusters, mirroring and replication and while mirroring
seems to come close, it's still and active/passive configuration (with the
addition of a watcher!).
Hopefully this is just buried in the docs somewhere on the install CD (I'm
waiting for the Dec MSDN DVDs, which presumably will include SQL2005 RTM),
but I'd have thought it would be online somewhere.
Anyone spotted it?
Thanks
CraigThere is no "NLB" or other form of transparent SQL scale-out in SQL Server
2005 (other than the Distributed Partitioned Views carried over from SQL
Server 2000). Mirroring does allow the mirror copy to be accessed read-only
for reporting. And there is a shared read-only reporting database feature.
In some cases those address the need for scale-out.
There is the notion of a Service-Oriented Database Architecture (SODA) in
SQL Server 2005 that can be used to achieve scale-out. It is transparent at
the level of a service interface, not at the level of SQL. It isn't
explicitly talked about in BOL. I wrote a white paper about it a year ago,
but haven't seen Microsoft publish it yet. It was supposed to be available
at launch. Dave Campbell has given presentations on it, at launch and I
believe also at PDC (and perhaps PASS).
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Craig Humphrey" <CraigHumphreyHatesSpam@.newsgroup.nospam> wrote in message
news:uWpmgmX6FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> early on in SQL2005 (Yukon's) life, there was talk for Network Load
> Balancing support.
> Now that it's (more or less) out, the documentation makes no mention of
> support for NLB.
> There's talk of clusters, mirroring and replication and while mirroring
> seems to come close, it's still and active/passive configuration (with the
> addition of a watcher!).
> Hopefully this is just buried in the docs somewhere on the install CD (I'm
> waiting for the Dec MSDN DVDs, which presumably will include SQL2005 RTM),
> but I'd have thought it would be online somewhere.
> Anyone spotted it?
> Thanks
> Craig
>|||In addition to Hal's comments:
For mirroring, you will probably be able to define a database snapshot of a mirrored database and
use that snapshot as a read-only database. However, you will work against a snapshot which will be
up to as old as the interval for creating the snapshots.
Also, you can have several database instances accessing the same read-only data using the SSB
thinking: http://support.microsoft.com/?kbid=910378
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig Humphrey" <CraigHumphreyHatesSpam@.newsgroup.nospam> wrote in message
news:uWpmgmX6FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> early on in SQL2005 (Yukon's) life, there was talk for Network Load Balancing support.
> Now that it's (more or less) out, the documentation makes no mention of support for NLB.
> There's talk of clusters, mirroring and replication and while mirroring seems to come close, it's
> still and active/passive configuration (with the addition of a watcher!).
> Hopefully this is just buried in the docs somewhere on the install CD (I'm waiting for the Dec
> MSDN DVDs, which presumably will include SQL2005 RTM), but I'd have thought it would be online
> somewhere.
> Anyone spotted it?
> Thanks
> Craig
>