Invoking or sending SQL queries using dOS

Hello Folks,
I want to create a simple batch DOS script to query mi SQL Server 2000 database. How I could do this?
I just want to run a select for a table but I dont want people to interct with sql query analyzer or enterprise manager to avoid any issues.
Regards,Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.|||Originally posted by Paul Young
Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.

UID stands for? PWD I assume is the pasword, isnt.

How I can redirect the results to a txt file,

OSQL -S OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b >> query.txt

Am I right?

Thanks for your prompt reply and help|||UID = User ID

You could pipe the output to a text file but the -o parm would be more usefull.|||How I can get the instance name. I have tested in my environment and I got the next error:

[DBNETLIB] Sql Server does not exist or access denied
[DBNETLIB] ConnectionOpen <Connect(())

As per this result I checked the server name I used typing osql -L. I got the names and used them but with the same result.

When I open the Sql query analyzer I connect using windows authentication option to connect to my databases.

Please suggest..|||For Windows Security I think you need to use -E option instead of -U and -P options.

Tim Ssql

invalid column name ref: calculated columns

hello

we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.
we are trying to use a already selected feild in a calculation to return another feild or column as they like to be known. this as we have found is not allowed!! is there anyone who knows of a clever work around so we dont have to write the calculation out 10000000000... times..
below is the code with the problem areas separted ta
cq

Alter Procedure AXACCBodereaux

(
@.ReportsPrintMenuHoldCoveredText73 datetime
)

As

SELECT QuoteRegister.QuoteNumber, QuoteRegister.PolicyNumber, QuoteRegister.Datecoverfrom, QuoteRegister.Datecoverto,
QuoteRegister.Broker, QuoteRegister.CLNTName, CLNTMaster.CLNTAddress1, CLNTMaster.CLNTAddress2, CLNTMaster.CLNTAddress3,
CLNTMaster.CLNTAddress4, CLNTMaster.CLNTPostcode, CLNTMaster.CLNTTradelongname,
Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end AS Type,
QuoteRegister.[BuildingSI] + QuoteRegister.[Total ContentsSumInsured] AS [Material Damage], Rating.TotalBI, Rating.PLIndemnitylimit,

percentnonliab = Case When Rating.TotalExcludingliabs <>0 Then Rating.KeyednonliabsdiscAmount / Rating.TotalExcludingliabs Else 0 end,

percentliab = Case When Rating.Totalliabilities <>0 Then Rating.KeyedliabilitiesdiscountAmount / Rating.Totalliabilities Else 0 end,

(Rating.[Total MD Premium] + Rating.TotaGITPrem + Rating.TotalmoneyPrem + Rating.DeteriorationofstocklimitPrem ) AS sum1,

MDPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then ( sum1 - ( sum1 * percentnonliab))
Else sum1 - sum1 *( Rating.NonliabsdiscountPercent/100) end ,

TOTBIPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then Rating.TotalBIPrem - Rating.TotalBIPrem * percentnonliab
Else Rating.TotalBIPrem - Rating.TotalBIPrem * (Rating.NonliabsdiscountPercent/100) end ,

PLPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem) * percentliab Else (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem)*(Rating.Totalliabilitiesdiscou ntPercent/100) end ,

ELPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then Rating.TotalELPrem-(Rating.TotalELPrem*(percentliab))
Else Rating.TotalELPrem - Rating.TotalELPrem*(Rating.Totalliabilitiesdiscoun tPercent/100) end ,

QuoteRegister.[Total Premium], (QuoteRegister.[Total Premium]*0.275) AS Commission,
(QuoteRegister.[Total Premium]*0.05) AS IPT, ([Total Premium]+IPT-Commission) AS NIA,
0 AS Finalised, QuoteRegister.Proposalreceived, QuoteRegister.CreationDate, QuoteRegister.CLNTCode,
QuoteRegister.DateonHoldCoveredBDX, Rating.KeyedliabilitiesdiscountAmount, Rating.KeyednonliabsdiscAmount
FROM (QuoteRegister INNER JOIN CLNTMaster ON QuoteRegister.CLNTCode =
CLNTMaster.CLNTCode) INNER JOIN Rating ON (CLNTMaster.CLNTCode = Rating.CLNTCode) AND
(QuoteRegister.QuoteNumber = Rating.QuoteNumber)
WHERE (((Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end ) <> 'MTA') AND ((QuoteRegister.DateonHoldCoveredBDX)=
@.ReportsPrintMenuHoldCoveredText73) AND ((QuoteRegister.ReasonForIssueCode) <> 'MTA') AND
((QuoteRegister.Insurer)='AXACC') AND ((Rating.QuoteNumber)=QuoteRegister.QuoteNumber) AND
((CLNTMaster.CLNTCode)=QuoteRegister.CLNTCode) AND ((Rating.ReasonForIssueCode)<>'MTA'));
/* set nocount on */
returnOriginally posted by colonelquinn
hello

we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.

You consider that a slight problem?

Also, I'm not sure I follow the problem...of course yuo can have derived data...what's the issue?|||derived data??
sorry dont know what that is only started doing this on friday..
to simplify the problem if you do this

select a ,b, c , a+b+c as d
or
select a,b,c, d+e as f , f+g as h
errors fly out saying any pre referenced value are invalid column names.
of course i could type out the calculation in every place but when i come to change the calc i am bound to forget to do it in every place.
i have tried doing two sql queies within the procedure but that gives the same error.
any ideas
#
thanks very much
cq|||Yeah You can't do that...

USE Northwind
GO

SELECT CustomerId + Convert(varchar(10),EmployeeId) As A, A+'Cant be done!'
FROM Orders|||surely there is a work around??!!!?!?!?!?!?|||USE Northwind
GO

SELECT A + ' Can be done!' FROM (SELECT CustomerId + Convert(varchar(10),EmployeeId) As A FROM Orders) As xxx

But it looks like a lot of rework...since you're doing it anyway...|||just define a view with as many of the combinations as you wish -- a+b+c, d+e, etc.

then query the view instead of the table

rudy
http://r937.com|||thankyou gentlemen
there are no errors so far...
now i wait till we have the data|||...or create calculated columns for the combinations you need.

blindman

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?
Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedure
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:

> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?
|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
[vbcol=seagreen]
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored procedure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
[vbcol=seagreen]
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserver.
> Arun
> "Shri.DBA" wrote:

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
--
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedure
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:
> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored procedure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
> > Hi,
> > I am trying to run some queries across the servers.
> > I have admin rights on both the boxes, I have linked the server.
> > But whenever i run a query on the remote server, I get the following error
> > --
> > Server: Msg 7399, Level 16, State 1, Line 1
> > OLE DB provider 'SQLOLEDB' reported an error.
> > [OLE/DB provider returned message: Invalid authorization specification]
> > --
> > The query is a basic one like
> > SELECT distinct [name] FROM remoteserver.sms_xxx.dbo.v_r_system.
> >
> > Any suggestions pls ?|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserver.
> Arun
> "Shri.DBA" wrote:
> > Hi Arun,
> >
> > Possible reasons...
> >
> > * Check whether you have added your login id in the remote sql server. If
> > you haven't added the login id, use sp_addremotelogin system stored procedure
> > to add your local login in the remote server and give appropriate access
> > rights.
> >
> > "Arun" wrote:
> >
> > > Hi,
> > > I am trying to run some queries across the servers.
> > > I have admin rights on both the boxes, I have linked the server.
> > > But whenever i run a query on the remote server, I get the following error
> > > --
> > > Server: Msg 7399, Level 16, State 1, Line 1
> > > OLE DB provider 'SQLOLEDB' reported an error.
> > > [OLE/DB provider returned message: Invalid authorization specification]
> > > --
> > > The query is a basic one like
> > > SELECT distinct [name] FROM remoteserver.sms_xxx.dbo.v_r_system.
> > >
> > > Any suggestions pls ?

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
--
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedur
e
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:

> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification
]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
[vbcol=seagreen]
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored proced
ure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
>|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
[vbcol=seagreen]
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserve
r.
> Arun
> "Shri.DBA" wrote:
>

intra-query parallelism

We have:
SQL7-sp4/w2k
4 Processors
use all available processors
Minimum query plan threshold for considering queries for parallel execution Set to 4
And the query next:
---
select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
from (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo in ('n10429','n10430','n10431')
group by p.id_distribuidora, p.id_cliente
)a inner join (
select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
from Table1 p
inner join Table2 d
on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
group by p.id_distribuidora, p.id_cliente
) b
on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
order by b.BeautiKit desc, a.Monedas desc
---
the Minimum query plan threshold for considering queries for parallel execution we set to 1,2,3,4,5
and have the error: Intra-query parallelism caused your server command (process ID #19) to deadlock
who need set?
p.d. the same query over SQL7/NT and one processor no have problem.
thank, for your helpTry adding "OPTION (MAXDOP 1)" to the query to force the optimizer to NOT
use parallel execution for that particular query. If that works, then wait
for MS to fix it.
Or you can rewrite the query to get around the problem. It looks like the
two derived tables are the same query using different catalog values. You
should be able to create one derived table that generates two sums - you
would also need to determine if data exists for both catalog value sets in
order to get the same result set (data is returned only if matching rows
exists in both derived tables).
"FcoResendiz" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB80CFD-F586-42B3-BCCA-196FC2F2BA9A@.microsoft.com...
> We have:
> SQL7-sp4/w2k
> 4 Processors
> use all available processors
> Minimum query plan threshold for considering queries for parallel
execution Set to 4
> And the query next:
> ---
> select a.id_distribuidora, a.id_cliente, a.Monedas, b.BeautiKit
> from (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) Monedas
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo in
('n10429','n10430','n10431')
> group by p.id_distribuidora, p.id_cliente
> )a inner join (
> select p.id_distribuidora, p.id_cliente, sum(d.cantidad) BeautiKit
> from Table1 p
> inner join Table2 d
> on p.id_distribuidora=d.id_distribuidora and p.id=d.id_pedido
> where año=2003 and p.semana>=43 and d.clave_catalogo ='n10432'
> group by p.id_distribuidora, p.id_cliente
> ) b
> on a.id_distribuidora=b.id_distribuidora and a.id_cliente=b.id_cliente
> order by b.BeautiKit desc, a.Monedas desc
> ---
> the Minimum query plan threshold for considering queries for parallel
execution we set to 1,2,3,4,5
> and have the error: Intra-query parallelism caused your server command
(process ID #19) to deadlock
> who need set?
> p.d. the same query over SQL7/NT and one processor no have problem.
> thank, for your help
>

interrelated report parameters

Is it possible to have parameter available values (coming from different
queries) BUT related to each other?
for example, we have two report parameters, ProductArea and ProductType,
with their available values coming from the queries:
select distinct ProductArea from SalesTable
select distinct ProductType from SalesTable
but not all product types are sold to all areas, so we need to use ONLY the
valid combinations of area and type.
Therefore, when a user selects an area from the drop-down box of available
areas, we need the second parameter to present only the types of products
actually sold in the selected area as available values, so the second query
should change to something like:
select distinct ProductType from SalesTable where ProductArea = <Selected
Value>
Is it possible to reference the selected value of a parameter at runtime in
the query?Yes, this is called Cascading Parameters in Reporting Services. All you need
to do is to set up a query parameter in your main query with the area
identifier. For example:
select Area, ProductType, Product, ListPrice FROM MyTable WHERE Area = @.area
AND ProductType = @.producttype
Your picklist for the area parameter will come from the query you have
provided below:
select distinct ProductArea from SalesTable
Your second parameter for ProductType will have a picklist defined by the
following query:
select distinct ProductType from SalesTable where Area = @.area
Because this query uses a parameter which will not be available until a
selection for the first parameter is made, Reporting Services will have the
listbox greyed out until a selection of Area has been made. And when the
selection for Area has been made, the picklist for the ProductType will be
populated with values which are only relevant to that area.
HTH
Charles Kangai, MCT, MCDBA
"vsiat" wrote:
> Is it possible to have parameter available values (coming from different
> queries) BUT related to each other?
> for example, we have two report parameters, ProductArea and ProductType,
> with their available values coming from the queries:
> select distinct ProductArea from SalesTable
> select distinct ProductType from SalesTable
> but not all product types are sold to all areas, so we need to use ONLY the
> valid combinations of area and type.
> Therefore, when a user selects an area from the drop-down box of available
> areas, we need the second parameter to present only the types of products
> actually sold in the selected area as available values, so the second query
> should change to something like:
> select distinct ProductType from SalesTable where ProductArea = <Selected
> Value>
> Is it possible to reference the selected value of a parameter at runtime in
> the query?
>