Output column size limit on query analyzer

We are using SQL 2K with Sp4.
I am doing a Select statement where it contains a column that composes of a
concatenation of various strings and many fields. At time, the output of thi
s
column will contain a very very long output string that part the output data
is being truncated. I assume it exceeds the limit of an output column in
the result panel would allow. Is there a setting in SQL I can change to
imcrease the size of a column in the result panel in query anaylzer?
WingmanA way to get around this is to use Excel and exeucte a database query and
return the results to a sheet. The data will not be truncated. It may
work by sending query results to a file from query analyzer but I haven't
tried that.
Walter
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> We are using SQL 2K with Sp4.
> I am doing a Select statement where it contains a column that composes of
> a
> concatenation of various strings and many fields. At time, the output of
> this
> column will contain a very very long output string that part the output
> data
> is being truncated. I assume it exceeds the limit of an output column in
> the result panel would allow. Is there a setting in SQL I can change to
> imcrease the size of a column in the result panel in query anaylzer?
> Wingman|||Thank for the idea but I can't use Excel. Acutally the SQL statement is
coming from our web application so we need to solve this within SQL or
adjusting SQL settings.
"Walter Mallon" wrote:

> A way to get around this is to use Excel and exeucte a database query and
> return the results to a sheet. The data will not be truncated. It may
> work by sending query results to a file from query analyzer but I haven't
> tried that.
> Walter
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
>
>|||In Query analyzer, go to the menu to Tools, then Options.
On the results tab, increase the Maximum characters per
column setting.
-Sue
On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:

>We are using SQL 2K with Sp4.
>I am doing a Select statement where it contains a column that composes of a
>concatenation of various strings and many fields. At time, the output of th
is
>column will contain a very very long output string that part the output dat
a
>is being truncated. I assume it exceeds the limit of an output column in
>the result panel would allow. Is there a setting in SQL I can change to
>imcrease the size of a column in the result panel in query anaylzer?
>Wingman|||Thanks. It is getting closer.
The select statement is coming from a web application, how do I change the
settings you mention in a server-wise setup so all connections to that
database has the same setting.
Wingman
"Sue Hoegemeier" wrote:

> In Query analyzer, go to the menu to Tools, then Options.
> On the results tab, increase the Maximum characters per
> column setting.
> -Sue
> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
> <Wingman@.discussions.microsoft.com> wrote:
>
>|||Those settings are for query analyzer only - it won't apply
to other applications. If data is being truncated on the
application end, you need to look at the code in the
application.
-Sue
On Thu, 27 Apr 2006 14:56:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks. It is getting closer.
>The select statement is coming from a web application, how do I change the
>settings you mention in a server-wise setup so all connections to that
>database has the same setting.
>Wingman
>"Sue Hoegemeier" wrote:
>

Output column size limit on query analyzer

We are using SQL 2K with Sp4.
I am doing a Select statement where it contains a column that composes of a
concatenation of various strings and many fields. At time, the output of this
column will contain a very very long output string that part the output data
is being truncated. I assume it exceeds the limit of an output column in
the result panel would allow. Is there a setting in SQL I can change to
imcrease the size of a column in the result panel in query anaylzer?
WingmanA way to get around this is to use Excel and exeucte a database query and
return the results to a sheet. The data will not be truncated. It may
work by sending query results to a file from query analyzer but I haven't
tried that.
Walter
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> We are using SQL 2K with Sp4.
> I am doing a Select statement where it contains a column that composes of
> a
> concatenation of various strings and many fields. At time, the output of
> this
> column will contain a very very long output string that part the output
> data
> is being truncated. I assume it exceeds the limit of an output column in
> the result panel would allow. Is there a setting in SQL I can change to
> imcrease the size of a column in the result panel in query anaylzer?
> Wingman|||Thank for the idea but I can't use Excel. Acutally the SQL statement is
coming from our web application so we need to solve this within SQL or
adjusting SQL settings.
"Walter Mallon" wrote:
> A way to get around this is to use Excel and exeucte a database query and
> return the results to a sheet. The data will not be truncated. It may
> work by sending query results to a file from query analyzer but I haven't
> tried that.
> Walter
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> > We are using SQL 2K with Sp4.
> >
> > I am doing a Select statement where it contains a column that composes of
> > a
> > concatenation of various strings and many fields. At time, the output of
> > this
> > column will contain a very very long output string that part the output
> > data
> > is being truncated. I assume it exceeds the limit of an output column in
> > the result panel would allow. Is there a setting in SQL I can change to
> > imcrease the size of a column in the result panel in query anaylzer?
> >
> > Wingman
>
>|||In Query analyzer, go to the menu to Tools, then Options.
On the results tab, increase the Maximum characters per
column setting.
-Sue
On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>We are using SQL 2K with Sp4.
>I am doing a Select statement where it contains a column that composes of a
>concatenation of various strings and many fields. At time, the output of this
>column will contain a very very long output string that part the output data
>is being truncated. I assume it exceeds the limit of an output column in
>the result panel would allow. Is there a setting in SQL I can change to
>imcrease the size of a column in the result panel in query anaylzer?
>Wingman|||Thanks. It is getting closer.
The select statement is coming from a web application, how do I change the
settings you mention in a server-wise setup so all connections to that
database has the same setting.
Wingman
"Sue Hoegemeier" wrote:
> In Query analyzer, go to the menu to Tools, then Options.
> On the results tab, increase the Maximum characters per
> column setting.
> -Sue
> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
> <Wingman@.discussions.microsoft.com> wrote:
> >We are using SQL 2K with Sp4.
> >
> >I am doing a Select statement where it contains a column that composes of a
> >concatenation of various strings and many fields. At time, the output of this
> >column will contain a very very long output string that part the output data
> >is being truncated. I assume it exceeds the limit of an output column in
> >the result panel would allow. Is there a setting in SQL I can change to
> >imcrease the size of a column in the result panel in query anaylzer?
> >
> >Wingman
>|||Those settings are for query analyzer only - it won't apply
to other applications. If data is being truncated on the
application end, you need to look at the code in the
application.
-Sue
On Thu, 27 Apr 2006 14:56:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>Thanks. It is getting closer.
>The select statement is coming from a web application, how do I change the
>settings you mention in a server-wise setup so all connections to that
>database has the same setting.
>Wingman
>"Sue Hoegemeier" wrote:
>> In Query analyzer, go to the menu to Tools, then Options.
>> On the results tab, increase the Maximum characters per
>> column setting.
>> -Sue
>> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
>> <Wingman@.discussions.microsoft.com> wrote:
>> >We are using SQL 2K with Sp4.
>> >
>> >I am doing a Select statement where it contains a column that composes of a
>> >concatenation of various strings and many fields. At time, the output of this
>> >column will contain a very very long output string that part the output data
>> >is being truncated. I assume it exceeds the limit of an output column in
>> >the result panel would allow. Is there a setting in SQL I can change to
>> >imcrease the size of a column in the result panel in query anaylzer?
>> >
>> >Wingman
>>

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 with multiple tables and a plus sign?

I am trying to select specific columns from multiple tables based on a
common identifier found in each table.

For example, the three tables:

PUBACC_AC
PUBACC_AM
PUBACC_AN

each have a common column:

PUBACC_AC.unique_system_identifier
PUBACC_AM.unique_system_identifier
PUBACC_AN.unique_system_identifier

What I am trying to select, for example:

PUBACC_AC.name
PUBACC_AM.phone_number
PUBACC_AN.zip

where the TABLE.unique_system_identifier is common.

For example:

--------------
PUBACC_AC
=========
unique_system_identifier name
1234 JONES

--------------
PUBACC_AM
=========
unique_system_identifier phone_number
1234 555-1212

--------------
PUBACC_AN
=========
unique_system_identifier zip
1234 90210

When I run my query, I would like to see the following returned as one
blob, rather than the separate tables:

--------------------
unique_system_identifier name phone_number zip
1234 JONES 555-1212 90210
--------------------

I think this is an OUTER JOIN? I see examples on the net using a plus
sign, with mention of Oracle. I'm not running Oracle...I am using
Microsoft SQL Server 2000.

Help, please?

P. S. Will this work with several tables? I actually have about 15
tables in this mess, but I tried to keep it simple (!??!) for the above
example.

Thanks in advance for your help!

NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
ADDRESS.

Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
(Rhetorical question only.)You do the following:

SELECT PUBACC_AC.Name + PUBACC_AM.Phone_number + PUBACC_AN.zip
FROM PUBACC_AC
INNER JOIN PUBACC_AM
ON PUBACC_AM.unique_col = PUBACC_AC.unique_col
INNER JOIN PUBACC_AN
ON PUBACC_AN.unique_col = PUBACC_AC.unique_col

That's it - you do inner join, if you want only those records, for which
unique_col value exists in all 3 tables.
Or, if you replace "INNER JOIN" with "FULL JOIN", which is the same as OUTER
JOIN in Oracle, you will get
as meny records as number of unique_col values.

Thats it!

Hope it helped,
Andrey aka Muzzy

"TeleTech1212" <tele_tech1212DELETE_THIS@.yahoo.com> wrote in message
news:Xns9556E99BEE9B4teletech1212DELETETH@.207.115. 63.158...
> I am trying to select specific columns from multiple tables based on a
> common identifier found in each table.
> For example, the three tables:
> PUBACC_AC
> PUBACC_AM
> PUBACC_AN
> each have a common column:
> PUBACC_AC.unique_system_identifier
> PUBACC_AM.unique_system_identifier
> PUBACC_AN.unique_system_identifier
>
> What I am trying to select, for example:
> PUBACC_AC.name
> PUBACC_AM.phone_number
> PUBACC_AN.zip
> where the TABLE.unique_system_identifier is common.
>
> For example:
> --------------
> PUBACC_AC
> =========
> unique_system_identifier name
> 1234 JONES
> --------------
> PUBACC_AM
> =========
> unique_system_identifier phone_number
> 1234 555-1212
> --------------
> PUBACC_AN
> =========
> unique_system_identifier zip
> 1234 90210
>
> When I run my query, I would like to see the following returned as one
> blob, rather than the separate tables:
> --------------------
> unique_system_identifier name phone_number zip
> 1234 JONES 555-1212 90210
> --------------------
>
> I think this is an OUTER JOIN? I see examples on the net using a plus
> sign, with mention of Oracle. I'm not running Oracle...I am using
> Microsoft SQL Server 2000.
> Help, please?
> P. S. Will this work with several tables? I actually have about 15
> tables in this mess, but I tried to keep it simple (!??!) for the above
> example.
> Thanks in advance for your help!
> NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
> ADDRESS.
> Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
> (Rhetorical question only.)

Outer Join Syntax Problems (Multiple Tables)

Hello all--
I'm trying to run a SELECT on 3 tables:Class,Enrolled,Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I knowto be incorrect. Furthermore, it appears that the counts are beingmultiplied together (in one instance, enrolled should be 14, waitingshould be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts areaccurate. The problem only occurs when I try to pull counts from boththe tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
Run this query and you'll see what it's doing:
SELECT Class.Name, Enrolled.StudentID AS EnrolledCount, Waiting.StudentID AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
Something like this will work:
SELECT c.Name, e.EnrolledCount, w.WaitingCount
FROM Class c
LEFT OUTER JOIN
(select classid, COUNT(*) AS EnrolledCount
from Enrolled
group by classid) e
on c.classid = e.classid
LEFT OUTER JOIN
(select classid, COUNT(*) AS WaitingCount
from Waiting
group by classid) w
on c.classid = w.classid
There's other ways to do it with subqueries. Something like this would also work
select c.classname, (select count(*) from enrolled where classid = c.classid) as Enrolled, (select count(*) from waiting where classid = c.classid) as Waiting
from Class c|||Thanks, PDraigh. I used your subquery example and it worked great.
Thanks!
--Jeremy

Outer join problem

This is the query I m executing.

SELECT M.MetroName Metro, R.Description Bank,
B.BranchCode + ' - ' + B.BranchName Branch, E.EmployeeLastName
Name FROM Metro M, Region R, Branch B, Employee E where
E.BranchID *= B.BranchID AND B.RegionID *= R.RegionID AND
R.MetroID *= M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber
Is Not Null

The error message I m getting is
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.

Try to help me asap.

Thx in advance
KKPTry to replace *= with LEFT OUTER JOIN. The issue there deals with the optimizer complaining about your request to LEFT JOIN a table that has already been LEFT JOINed.|||Originally posted by rdjabarov
Try to replace *= with LEFT OUTER JOIN. The issue there deals with the optimizer complaining about your request to LEFT JOIN a table that has already been LEFT JOINed.

Hi Rdja,

I tried that I made this as it is

SELECT M.MetroName Metro, R.Description Bank,
B.BranchCode + ' - ' + B.BranchName Branch, E.EmployeeLastName
Name FROM Metro M, Region R, Branch B, Employee E where
E.BranchID LEFT OUTER JOIN B.BranchID AND B.RegionID LEFT OUTER JOIN R.RegionID AND
R.MetroID LEFT OUTER JOIN M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber
Is Not Null

It gives back me the error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'LEFT'.

Can u solve this?

Thx
KKP|||You need to look up and review the syntax of SELECT statements in Books Online.

SELECT M.MetroName Metro,
R.Description Bank,
B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName Name
FROM Metro M
left outer join Region R on M.MetroID = R.MetroID
left outer join Branch B on R.RegionID = B.RegionID
left outer join Employee E on B.BranchID = E.BranchID
where E.ProductionNumber = '0000'
AND E.ProductionNumber Is Not Null

blindman|||You'd code it like this

SELECT M.MetroName Metro,
R.Description 'Bank',
B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName 'Name'
FROM Metro AS M
LEFT OUTER JOIN Region AS R ON R.MetroID = M.MetroID
LEFT OUTER JOIN Branch AS B ON B.RegionID = R.RegionID
LEFT OUTER JOIN Employee AS E ON E.BranchID = B.BranchID
WHERE E.ProductionNumber = '0000'
AND E.ProductionNumber IS NOT NULL

But that WHERE clause basically undoes the effect of all those LEFT OUTER JOINS so I not sure what you're trying to accomplish here. I mean it seems pointless to use LEFT OUTER to preserve the rows in the Metro, Region, and Branch tables that don't have matching Employee rows if you're going to turn around and throw them away with the where clause. Besides, the two conditions E.ProductionNumber = '0000' and E.ProductionNumber IS NOT NULL are redundant. You don't need the later, as if E.ProductionNumber is '0000', it is of necessity not null.

Can you explain your desired result? That might help us translate it into the correct SQL, as we really don't know what it is you're trying to get.|||Yes, logically you should either convert your outer joins to inner joins, or move the criteria to the join as well:

SELECT M.MetroName Metro,
R.Description Bank,
B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName Name
FROM Metro M
left outer join Region R on M.MetroID = R.MetroID
left outer join Branch B on R.RegionID = B.RegionID
left outer join Employee E on B.BranchID = E.BranchID
and E.ProductionNumber = '0000'
and E.ProductionNumber Is Not Null|||Steve and Blindman

Thx for the reply.
Steve : My objective of the query is to select Metroname, Description,
Lastname and Branch code , name from four diff tables based on the
criterias MetroIds, RegionIds and BranchIds. Reg the production number
it is a four digit number in Employee table with diff values.
I should take both factors '0000' and null so I added both the constraints in the where clause.

You see my first query and try to tell me the error.

SELECT M.MetroName Metro, R.Description Bank, B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName Name FROM Metro M, Region R, Branch B, Employee E WHERE
E.BranchID *= join B.BranchID AND B.RegionID *= R.RegionID AND R.MetroID *= M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber Is Not Null

Error mesg : Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.

so I added left outer joins. Lemme know if I have confused you more.
waiting for ur reply.

Thx
KK|||AH, OK, I think I have it now.

I think you need IS NULL instead of IS NOT NULL. Also, since you're using LEFT OUTER joins, those selection criteria need to be in the JOIN clause. I think you want something like tihis

SELECT M.MetroName Metro,
R.Description 'Bank',
B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName 'Name'
FROM Metro AS M
LEFT OUTER JOIN Region AS R ON R.MetroID = M.MetroID
LEFT OUTER JOIN Branch AS B ON B.RegionID = R.RegionID
LEFT OUTER JOIN Employee AS E ON E.BranchID = B.BranchID
AND (E.ProductionNumber = '0000'
OR E.ProductionNumber IS NULL)

Whether to put a condition like column IS NULL in the WHERE or JOIN clause depends on what you're trying to achieve. To use an OUTER JOIN to find rows that don't have matches in another table, you typically put the condition in a WHERE clause and use a column that won't naturally have NULLs. If, as I think you are saying you need, you only want to consider joining rows that have NULL (or possibly other values as well) but still need to preserve the previous tables even if there isn't a qualifying match, then you need to put the condition in the JOIN clause. This will make the OUTER JOIN function correctly and return nulls for the final table, even if it has rows that would typically match a normal join, but don't match you extra criteria.

OUTER JOIN PROBLEM

My SQL Statement as follows:

SELECT S.SessionID,S.SessionName,T.number
FROM Sessions S LEFT OUTER JOIN (SELECT SessionID,COUNT(*) AS number
FROM EventLog
WHERE MachineID = @.machineID
GROUP BY SessionID) AS T
ON S.SessionID = T.SessionID
ORDER BY S.SessionID

The result sets T.number as NULL if there is no record related to SessionName. How to change NULL to ZERO?
Thanks a lot.ISNULL(T.number, 0)|||SELECT S.SessionID,S.SessionName,isnull(T.number,0)

outer join oracle query translate in sql server

Hi,
I have this oracle query with outer join situation. how can i convert it into sql server query.

SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
PRES_ITEM.RET_PERC
FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
AND CP.DB_CONTRACT = AE.DB_CONTRACT
AND CP.pc_code = 1

Any Help will be appreciated.I thhink this is it

SELECT DISTINCT
ae.dB_CONTRACT
, CP.PC_CODE
, BID_ITEM.ITEM_NO
, 'N'
, BID_ITEM.PRICE_WORDS
, OFF_ITEM.DESCPT
, OFF_ITEM.UNITS
, OFF_ITEM.TYPE_ITEM
, PRES_ITEM.RET_PERC
FROM BID_TOTAL
JOIN BID_ITEM
ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
JOIN OFF_ITEM
ON BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
LEFT JOIN PRES_ITEM
ON OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO
JOIN AE_CONTRACT AE
ON AE.DB_CONTRACT = BID_TOTAL.DB_cONTRACT
JOIN CONTRACT_PC CP
ON CP.DB_CONTRACT = AE.DB_CONTRACT
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND CP.pc_code = 1

Test it out...|||Hi,
thank you very much..it worked excellent

Originally posted by Brett Kaiser
I thhink this is it

SELECT DISTINCT
ae.dB_CONTRACT
, CP.PC_CODE
, BID_ITEM.ITEM_NO
, 'N'
, BID_ITEM.PRICE_WORDS
, OFF_ITEM.DESCPT
, OFF_ITEM.UNITS
, OFF_ITEM.TYPE_ITEM
, PRES_ITEM.RET_PERC
FROM BID_TOTAL
JOIN BID_ITEM
ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
JOIN OFF_ITEM
ON BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
LEFT JOIN PRES_ITEM
ON OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO
JOIN AE_CONTRACT AE
ON AE.DB_CONTRACT = BID_TOTAL.DB_cONTRACT
JOIN CONTRACT_PC CP
ON CP.DB_CONTRACT = AE.DB_CONTRACT
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND CP.pc_code = 1

Test it out...|||if your oracle install is 9i
the ansi join syntax actually works
after 20 years!!!!!

Outer join difficulty

Can someone please help me with the following join ? I have two tables that I need to join. The select statement for the first part is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The table that is produced is as follows:-

Rundate Accno Sharename NotAtHome
20031128 260869 PANGBOURNE 123200
20031128 260869 PARAMOUNT 221100
20031128 260869 PRISAIB. 221100

The table I wish to join on, is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The result is as follows:-

Rundate Accno Sharename NotAtHome
20031103 260869 PANGBOURNE 123200

Note that Paramount and Prisaib are missing from the second table.

I need to create an output table showing all three records with the missing two having 0 under "NotAtHome".

I'd much appreciate any assistance with this..

thanks!(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q1
LEFT OUTER JOIN
(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q2
ON q1.AccNo = q2.AccNo;

Assuming AccNo is a key.

The default value for non-matching rows is Null, however if you do indeed want a numerical then,

I am not entirely sure of MSSQL syntax however I believe it's the following for Case statements.

select attributes, (case NotAtHome If Null Then 0 Else NotAtHome END) As "NotAtHome"
from
(
Above query
)|||you would not need ORDER BY in the subqueries, and in fact the query is a bit neater without subqueries

the NotAtHome column is weird, i left it as t1.NotAtHome as alkemac specified, but i think he/she might have meant t2.NotAtHome select t1.Rundate
, t1.Accno
, t1.Sharename
, case when t2.AccNo is null
then 0
else t1.NotAtHome
end as NotAtHome
from dbo.Holdings t1
left outer
join dbo.Holdings t2
on t1.AccNo = t2.AccNo
and t2.Rundate = '20031103'
and t2.Sharename like 'P%'
where t1.Accno = '260869'
and t1.Rundate = '20031128'
and t1.Sharename like 'P%'rudy
http://r937.com/

Outer join bug

In a SELECT query with a left join, I have an ON clause
with two conditions -- one compares a column from each
table and the other compares a column in the left table to
a constant value. The results should be equivalent to
putting the second comparison (to a constant) in a WHERE
clause and in the execution plan, the second comparison
appears in the WHERE condition of the join. The results
include rows that should have been filtered out.
Example:
select table1.A, table1.B, table2.A
from table1 left outer join table2
on table1.A=table2.A AND table1.B=1
table1
Row 1: A=1, B=1
Row 2: A=1, B=2
table2
Row 1: A=1, B=1
results
Row 1: 1, 1, 1
Row 2: 1, 2, <null>
The second row of the results should not be there. It was
also not joined with the row in table2.
(Is this the correct newsgroup for this question?)This is a multi-part message in MIME format.
--=_NextPart_000_0100_01C35B4D.9E3AD110
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This is not a bug - it's a trap. If you subscribe to SQL Server =Professional, you can check out my article on this at:
http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0F792E503632AF9A852568E2=
007AD8BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John VMT" <jbeckett@.vmtsoftware.com> wrote in message =news:0de601c35b6e$622b4940$a601280a@.phx.gbl...
In a SELECT query with a left join, I have an ON clause with two conditions -- one compares a column from each table and the other compares a column in the left table to a constant value. The results should be equivalent to putting the second comparison (to a constant) in a WHERE clause and in the execution plan, the second comparison appears in the WHERE condition of the join. The results include rows that should have been filtered out.
Example:
select table1.A, table1.B, table2.A
from table1 left outer join table2
on table1.A=3Dtable2.A AND table1.B=3D1
table1
Row 1: A=3D1, B=3D1
Row 2: A=3D1, B=3D2
table2
Row 1: A=3D1, B=3D1
results
Row 1: 1, 1, 1
Row 2: 1, 2, <null>
The second row of the results should not be there. It was also not joined with the row in table2.
(Is this the correct newsgroup for this question?)
--=_NextPart_000_0100_01C35B4D.9E3AD110
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

This is not a bug - it's a trap. =If you subscribe to SQL Server Professional, you can check out my article on =this at:
http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/0F792E503632AF9A852568E2007AD8BC?open&login=-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John VMT" = wrote in message news:0de601c35b6e$62=2b4940$a601280a@.phx.gbl...In a SELECT query with a left join, I have an ON clause with two =conditions -- one compares a column from each table and the other compares a =column in the left table to a constant value. The results should be =equivalent to putting the second comparison (to a constant) in a WHERE clause =and in the execution plan, the second comparison appears in the WHERE =condition of the join. The results include rows that should have been =filtered out.Example:select table1.A, table1.B, table2.Afrom =table1 left outer join table2on table1.A=3Dtable2.A AND table1.B=3D1table1Row 1: A=3D1, B=3D1Row 2: A=3D1, B=3D2table2Row 1: A=3D1, B=3D1resultsRow 1: 1, =1, 1Row 2: 1, 2, The second row of the results should not be there. It was also not joined with the row in =table2.(Is this the correct newsgroup for this question?)

---

--=_NextPart_000_0100_01C35B4D.9E3AD110--|||Thanks for the quick feedback and correction. It's a bad
habit I got into several years ago with Jet databases (it
improved query performance).
It was unfortunate that the execution plan showed the
condition (column=<constant>) in the join's WHERE clause.
So the plan was as I expected but the execution was not.
Life is a series of disappointments when you hope for what
you want instead of what you get.
>--Original Message--
>This is not a bug - it's a trap. If you subscribe to SQL
Server Professional, you can check out my article on this
at:
>http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0F792E503
632AF9A852568E2007AD8BC?open&login
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"John VMT" <jbeckett@.vmtsoftware.com> wrote in message
news:0de601c35b6e$622b4940$a601280a@.phx.gbl...
>In a SELECT query with a left join, I have an ON clause
>with two conditions -- one compares a column from each
>table and the other compares a column in the left table
to
>a constant value. The results should be equivalent to
>putting the second comparison (to a constant) in a WHERE
>clause and in the execution plan, the second comparison
>appears in the WHERE condition of the join. The results
>include rows that should have been filtered out.
>Example:
>select table1.A, table1.B, table2.A
>from table1 left outer join table2
>on table1.A=table2.A AND table1.B=1
>table1
>Row 1: A=1, B=1
>Row 2: A=1, B=2
>table2
>Row 1: A=1, B=1
>results
>Row 1: 1, 1, 1
>Row 2: 1, 2, <null>
>The second row of the results should not be there. It
was
>also not joined with the row in table2.
>(Is this the correct newsgroup for this question?)
>

Out put of Select Statement Stored in a variable to another Variab

Hi Guys,
I need a solution in SQL Server 2000. I wanted to store the output of a
select query into a variable. The Select Statement is stored in a variable
.
the sample is given below.
declare @.Tmp Varchar(100),
@.Tmp1 Varchar(100),
@.Tmp2 Varchar(15)
set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
serialno=5'
set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
exec(@.Tmp1)
print @.Tmp2
Do any one have the idea.
Thanks in advance
Regds
Kag"Kag" <Kag@.discussions.microsoft.com> wrote in message
news:304B2B5C-AE55-491A-9D26-88F205861757@.microsoft.com...
> Hi Guys,
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
>
You will need to use a Temp table. The variables are only valid within the
scope in which they are defined. Temp tables are part of your session.
DECLARE @.tmp varchar(100),
@.tmp1 varchar(100)
CREATE TABLE #ReturnValue (tmp2 varchar(15) NULL)
SET @.tmp = 'columntitle FROM bworksheetdtl WHERE Worksheetcode=1 AND
serialno = 5'
SET @.tmp1 = 'INSERT #ReturnValue (tmp2) SELECT ' + @.tmp
EXECUTE (@.tmp1)
SELECT tmp2 FROM #ReturnValue
DROP TABLE #ReturnValue
Rick Sawtell
MCT, MCSD, MCDBA|||The @.Temp2 variable does not exist (is not declared) inside the context of
your EXECUTE statement.
Take a look at sp_executesql in Books Online. It accepts parameters and
really simplifies execution of dynamic SQL.
On the other hand, if you post DDL and sample data and specify what you're
actually trying to achieve, we can help you find a more efficient solution.
ML|||Hi Kag,
Would you like to try this?
DECLARE @.Tmp Varchar(100),
@.Tmp1 Varchar(100),
@.Tmp2 Varchar(15)
SELECT @.tmp2 = columntitle FROM bworksheetdtl WHERE Worksheetcode = 1 and
serialno = 5
Leo
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Hi Kag,
This may be what you want:
Declare @.tmp varchar(15)
SELECT @.temp = columntitle FROM bworksheetdtl WHERE Worksheetcode=1 and
serialno=5
PRINT @.tmp
Leo
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100), columntitle from bworksheetdtl where Worksheetcode=1
and
> serialno=5
> @.Tmp2 Varchar(15)
> set @.Tmp = ''
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Pl try code below
declare @.Tmp nVarchar(100), -- varchar changed to nvarchar
@.Tmp1 nVarchar(100), -- varchar changed to nvarchar
@.Tmp2 Varchar(15)
set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
serialno=5'
set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
exec sp_executesql @.Tmp1, N'@.Tmp2 Varchar(15) output', @.Tmp2 output -- alter
ed
print @.Tmp2
Rakesh
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Thanks Rakesh and ML. I tried the solution you have given with
sp_executesql and it was the one i was searching for. Special thanks to
Rakesh for the exact example. Thanks again guys..
Kag
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kagsql

Out of Memory Error when running a Select Statement

Hi all,

In the object Explorer in SQL Server 2005 Management Studio, if I right click on the table I want to open and choose open, I eventually get an out of memory error with an error source: MSCORLIB.

This table contains 40m records, but when it opens the table it goes past the 40m and wants to open 83m records, and then eventually comes up with an out of memory error.

The properties of the table indicates that there are 40m records, but when you open the table, at the bottom, where it says retrieving data, it is counting of numbe rof records that surpass the 40m total.

Is it because the database is too big, or is there a way to configure SQL Server 2005 to use X amount of memory?

Regards

Mark:

You might want to temper opening large tables with Management Studio or other tools. If your table contains 40 million rows of data and each row of data is on average 1000 bytes per row, opening this table might take 40 GB of memory to open and display the data. Be careful about viewing all rows of large tables.

Dave

|||

Cheers Dave,

I have used Management Studio, but you are probably right about the memory requirement.

Thanks

osql with script trouble...

I have a sql file that contains:
if EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name ='newShopcart')
DROP DATABASE [newShopcart];
CREATE DATABASE [newShopcart] ON (NAME ='newShopcart_dat', FILENAME ='C:\MSSQL7\DATA\newShopcart.mdf' , SIZE = 10, FILEGROWTH =
10%) LOG ON (NAME ='newShopcart_log', FILENAME ='C:\MSSQL7\DATA\newShopcart.ldf' , SIZE = 10, FILEGROWTH = 10%);
USE [newShopcart];
GO
And I call this using osql in this way:
osql -D TLC -U sa -P thepassword -i MakeDB.sql -o MakeDB.log -n
I get the following in the resulting log:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'i'.
I've noticed that changing the first line to USE [master] changes the log to:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'U'.
This leads me to believe that it sees the first character of the file as the name of a Stored Procedure.
Any idea how to get around this?
Thanks in advance.
Mandy wrote:
> I have a sql file that contains:
> if EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name
> ='newShopcart')
> DROP DATABASE [newShopcart];
> CREATE DATABASE [newShopcart] ON (NAME ='newShopcart_dat', FILENAME
> ='C:\MSSQL7\DATA\newShopcart.mdf' , SIZE = 10, FILEGROWTH = 10%) LOG
> ON (NAME ='newShopcart_log', FILENAME
> ='C:\MSSQL7\DATA\newShopcart.ldf' , SIZE = 10, FILEGROWTH = 10%);
> USE [newShopcart];
> GO
> And I call this using osql in this way:
> osql -D TLC -U sa -P thepassword -i MakeDB.sql -o MakeDB.log -n
> I get the following in the resulting log:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'i'.
> I've noticed that changing the first line to USE [master] changes the
> log to:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'U'.
> This leads me to believe that it sees the first character of the file
> as the name of a Stored Procedure.
> Any idea how to get around this?
> Thanks in advance.
Does this code work from Query Analyzer? I see on your last line you
are using a semi-colon and a go. Does OSQL understand "go"? What's the
"n" option for and do you need it?
David Gugick
Imceda Software
www.imceda.com
|||It does work under QA. -n removes line numbers from results.

> Does this code work from Query Analyzer? I see on your last line you
> are using a semi-colon and a go. Does OSQL understand "go"? What's the
> "n" option for and do you need it?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Are you sure this is an odbc data source and not just a regular SQL Server
connection? What happens if you remove the -D option and value? You could
also try adding the option -e to echo the commands as they are read, for
troubleshooting.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mandy" <clafarge@.NOSPAMgraphiced.com> wrote in message
news:eAn4RG%232EHA.1192@.tk2msftngp13.phx.gbl...
>I have a sql file that contains:
> if EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name
> ='newShopcart')
> DROP DATABASE [newShopcart];
> CREATE DATABASE [newShopcart] ON (NAME ='newShopcart_dat', FILENAME
> ='C:\MSSQL7\DATA\newShopcart.mdf' , SIZE = 10, FILEGROWTH =
> 10%) LOG ON (NAME ='newShopcart_log', FILENAME
> ='C:\MSSQL7\DATA\newShopcart.ldf' , SIZE = 10, FILEGROWTH = 10%);
> USE [newShopcart];
> GO
> And I call this using osql in this way:
> osql -D TLC -U sa -P thepassword -i MakeDB.sql -o MakeDB.log -n
> I get the following in the resulting log:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'i'.
> I've noticed that changing the first line to USE [master] changes the log
> to:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'U'.
> This leads me to believe that it sees the first character of the file as
> the name of a Stored Procedure.
> Any idea how to get around this?
> Thanks in advance.
>
|||I changed to specifying -S and ip address. When I added -e, it shows only one line of output:
I
So it still appears to only see the first character of the file.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:%23PyX8MA3EHA.1396@.tk2msftngp13.phx.gbl...
> Are you sure this is an odbc data source and not just a regular SQL Server
> connection? What happens if you remove the -D option and value? You could
> also try adding the option -e to echo the commands as they are read, for
> troubleshooting.
|||-S is usually followed by the server name.
What version of SQL Server? Are you running osql remotely or locally?
Can you verify the version of osql?
How did you create the .sql file?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mandy" <clafarge@.NOSPAMgraphiced.com> wrote in message
news:Ogr83FH3EHA.2312@.TK2MSFTNGP15.phx.gbl...
>I changed to specifying -S and ip address. When I added -e, it shows only
>one line of output:
> I
> So it still appears to only see the first character of the file.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23PyX8MA3EHA.1396@.tk2msftngp13.phx.gbl...
>
|||I tried -S with ip and also server name.
MSSQL Version 7
osql on my machine, Server on network
osql version 2000.80.194.0
created script by Right-click on existing DB in Enterrprise and "Generate SQL Script", then changing names as needed for new
database.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:u0bXMoH3EHA.1300@.TK2MSFTNGP14.phx.gbl...
> -S is usually followed by the server name.
> What version of SQL Server? Are you running osql remotely or locally?
> Can you verify the version of osql?
> How did you create the .sql file?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Mandy" <clafarge@.NOSPAMgraphiced.com> wrote in message
> news:Ogr83FH3EHA.2312@.TK2MSFTNGP15.phx.gbl...
>
|||Mandy wrote:
> I tried -S with ip and also server name.
> MSSQL Version 7
> osql on my machine, Server on network
> osql version 2000.80.194.0
> created script by Right-click on existing DB in Enterrprise and
> "Generate SQL Script", then changing names as needed for new database.
>
You really should patch your client tools. You are running the retail
release of OSQL.EXE from years ago. Apply the latest SQL Server SP3a
service pack to your client PC.
Also, try running a very simple script like "Select * from sysobjects"
from a script file and see if that works.
David Gugick
Imceda Software
www.imceda.com
|||Thanks for the pointer...
When I changed to a Select, I get:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'S'.
Is there any reason why it would only see the first character?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:uBnghHJ3EHA.524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Mandy wrote:
|||Mandy wrote:[vbcol=seagreen]
> Thanks for the pointer...
> When I changed to a Select, I get:
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'S'.
> Is there any reason why it would only see the first character?
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uBnghHJ3EHA.524@.TK2MSFTNGP09.phx.gbl...
What format are you saving the file in? Is it unicode? Maybe OSQL cannot
read unicode files and requires an ANSI formatted file.
David Gugick
Imceda Software
www.imceda.com

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.
That should work. Can you post code which we can run that displays this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegro ups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>
|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:

> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.That should work. Can you post code which we can run that displays this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegroups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:

> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.That should work. Can you post code which we can run that displays this?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegroups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql syntax

Hi
I am running osql utility like this...
C:\Temp>osql -U sa -P 5a -S %ServerName% -d pubs -Q "select title, notes
from titles" -o .\out.txt -s"," -w300 -h-1
And it produces the following in "out.txt":
== start of output ==
The Busy Executive's Database Guide
,An overview of available database systems with emphasis on common business
applications. Illustrated.
...
...
...
Sushi, Anyone?
,Detailed instructions on how to make authentic Japanese sushi in your spare
time.
(18 rows affected)
== end of output ==
Can anybody tell me
(a) how to remove the "(18 rows affected)" comment
(b) how to remove the %FieldValue% right padding. Ideally I'd like the
output to be
== start of output ==
%FieldValue%,%FieldValue%
%FieldValue%,%FieldValue%
== end of output ==
Thanks for any tips.
PS My actual queries will be bigger than bcp allows. I'd prefer not to have
to write the query as:
select '"' + title + '", "' + notes + '"' from titles
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieF...ew.msnw?&pps=k
Neil
to avoid the rowcount, you can use -Q "set nocount on select title, notes
from titles". As far as I understand, you can't avoid the fixed width nature
of the output, apart from the concatenation solution you propose.
HTH,
Paul Ibison

osql syntax

Hi
I am running osql utility like this...
C:\Temp>osql -U sa -P 5a -S %ServerName% -d pubs -Q "select title, notes
from titles" -o .\out.txt -s"," -w300 -h-1
And it produces the following in "out.txt":
== start of output ==
The Busy Executive's Database Guide
,An overview of available database systems with emphasis on common business
applications. Illustrated.
...
...
...
Sushi, Anyone?
,Detailed instructions on how to make authentic Japanese sushi in your spare
time.
(18 rows affected)
== end of output ==
Can anybody tell me
(a) how to remove the "(18 rows affected)" comment
(b) how to remove the %FieldValue% right padding. Ideally I'd like the
output to be
== start of output ==
%FieldValue%,%FieldValue%
%FieldValue%,%FieldValue%
== end of output ==
Thanks for any tips.
PS My actual queries will be bigger than bcp allows. I'd prefer not to have
to write the query as:
select '"' + title + '", "' + notes + '"' from titles
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=kNeil
to avoid the rowcount, you can use -Q "set nocount on select title, notes
from titles". As far as I understand, you can't avoid the fixed width nature
of the output, apart from the concatenation solution you propose.
HTH,
Paul Ibison

osql syntax

Hi
I am running osql utility like this...
C:\Temp>osql -U sa -P 5a -S %ServerName% -d pubs -Q "select title, notes
from titles" -o .\out.txt -s"," -w300 -h-1
And it produces the following in "out.txt":
== start of output ==
The Busy Executive's Database Guide
,An overview of available database systems with emphasis on common business
applications. Illustrated.
...
...
...
Sushi, Anyone?
,Detailed instructions on how to make authentic Japanese sushi in your spare
time.
(18 rows affected)
== end of output ==
Can anybody tell me
(a) how to remove the "(18 rows affected)" comment
(b) how to remove the %FieldValue% right padding. Ideally I'd like the
output to be
== start of output ==
%FieldValue%,%FieldValue%
%FieldValue%,%FieldValue%
== end of output ==
Thanks for any tips.
PS My actual queries will be bigger than bcp allows. I'd prefer not to have
to write the query as:
select '"' + title + '", "' + notes + '"' from titles
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieFamily/_whatsnew.msnw?&pps=kNeil
to avoid the rowcount, you can use -Q "set nocount on select title, notes
from titles". As far as I understand, you can't avoid the fixed width nature
of the output, apart from the concatenation solution you propose.
HTH,
Paul Ibison

osql script

I have two Osql scripting questions for windows?
1. How can I get my OSQL script to output to a CVS file. Such as "select *
from itemtable"
2. How can I create a osql backup script to will name the backup filename
system_date.bck. This way I have a backup for each day by date.
Thanks
Dan
hi Dan,
Danny wrote:
> I have two Osql scripting questions for windows?
> 1. How can I get my OSQL script to output to a CVS file. Such as "select *
> from itemtable"
SET NOCOUNT ON;
USE pubs;
GO
DECLARE @.fullcmd varchar(1000);
DECLARE @.cmd varchar(1000);
SET @.cmd = 'SELECT * FROM pubs.dbo.authors';
SET @.fullcmd = 'osql -S(Local) -E -q "' + @.cmd + '" -o c:\authors.txt';
-- this will result in a tab delimited columns output..
EXEC master..xp_cmdshell @.fullcmd ;
-- but you can prepare a format file as required, see BOL
PRINT 'PREPARE a format file';
SET @.fullcmd = 'bcp pubs.dbo.authors format nul -c -f c:\f.txt -T'
--EXEC master..xp_cmdshell @.fullcmd ;
PRINT 'MANUALLY MODIFY THE format file setting the "," as column separator
instead of the tab char';
SET @.fullcmd = 'bcp "' + @.cmd + '" queryout "c:\bcpAuthors.txt" -f
c:\f.txt -T';
SELECT @.fullcmd;
EXEC master..xp_cmdshell @.fullcmd;

> 2. How can I create a osql backup script to will name the backup
> filename system_date.bck. This way I have a backup for each day by
> date.
DECLARE @.db sysname;
DECLARE @.dir varchar(500);
DECLARE @.file varchar(256);
SELECT @.db = 'pubs', @.dir = 'c:\', @.file = CONVERT(varchar(8), GETDATE(),
112) + '_' + @.db + '.bak';
DECLARE @.fullpath varchar(1000);
SET @.fullpath = @.dir + @.file;
SELECT @.fullpath;
BACKUP DATABASE @.db
TO DISK = @.fullpath
WITH INIT;
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply