[strange] date format issue: 2 servers, one query, 2 different results.

I'm running 2 WIN2K SP4 servers with MSSQL Server (v 7.00.1077). Both NT servers seems to have the same setup. I use the ADODB object to connect to MSSQL via OLE DB and notice that the following query generates an error on one server, not the other:

SELECT DATA FROM TABLE_1 WHERE ((TRANSACTION_DATE >='13/11/2003') AND (TRANSACTIONS_DATE <='20/11/2003')) [dates are DD/MM/YYYY]

Query on server_1:
- using OLE DB: correct
- using ODBC: correct
- using Query Analyzer: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>

On server_2:
- using OLE DB: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>
- using ODBC: correct
- using Query Analyzer: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>

Both servers have MDAC 2.8 RTM installed and a <select @.@.language> returns <us_english> via query analyzer and <franais> via ODBC.

I'd like to setup Server 2 so that queries can be executed the same as on server 1.

Any ideas are welcomed!There's a problem with 20/11/2003 in us_english format. Default is: MDY, so it's reading 20th as month, which is why you're getting that error.

Here's an article I found: http://members.lycos.nl/digispy/C2/P56/C2P56A1806.htm

G'luck!
Meera|||Possible there is something to do with locale settings of windows, check the "Region Options" in Control Panel for both servers.|||I'd say the languages are different...and accordingingly each date format is different

Di SELECT * FROM master..syslanguages

I think English is the only one that mdy|||(D/M/Y is a French date format).

I just had to set <French> to user's default language instead of <US_ENGLISH>. This means ODBC settings override the default SQL Server settings as OLE DB uses the default SQL Server setting.sql

[SQLSTATE 01000] via Agent Job, but not Query Analyzer

I run a sproc through query analyzer, and all is fine, when I run the job via the SQL Agent, [SQLSTATE 01000] appears after every line in the "log" file, any ideas ?
This happens with the sp_help_revlogin procedure.
both are being run with sa.Known issue with the PRINT command in SQL Server.

[SQL2k5] Dynamic SQL Query Select on all user tables

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.

Forgot, for every table it selects from, the table name must be one of the returned columns.|||

Hi, try the following:

sp_msforeachtable 'SELECT *,''?'' AS TableName FROM (SELECT TOP (3) , COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
FROM ?
GROUP BY [Target IP]
ORDER BY EventNum DESC) SubQuery'

But keep in mind that this should be just used for administrative task and adhoc queries not for business logic as the procedure is not documented, therefore not supported and could be deprecated in the next version of SQL Server.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

[SQL Server]Transaction (Process ID 52) was deadlocked

I am using SQL Server 2000 and running number of query simulatneously. While
running update query it is always giving above error. The error discriptions
are -
1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock} reso
urces
with another process and has been chosen as the deadlock victim
3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
I have tried cleaning up the database, increased the fixed memory size of
SQL Server to 800 MB, restart the machine. But nothing works.
Plz help out to resolve this problem.get output from
sp_who
sp_who2
sp_lock
when the trans is running
"Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>I am using SQL Server 2000 and running number of query simulatneously.
>While
> running update query it is always giving above error. The error
> discriptions
> are -
> 1. [SQL Server]Transaction (Process ID 52) was deadlocked on {loc
k}
> resources with another process and has been chosen as the deadlock victim
> 2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock}
> resources
> with another process and has been chosen as the deadlock victim
> 3. [SQL Server]Transaction (Process ID 56) was deadlocked on {loc
k}
> resources with another process and has been chosen as the deadlock victim
>
> I have tried cleaning up the database, increased the fixed memory size of
> SQL Server to 800 MB, restart the machine. But nothing works.
> Plz help out to resolve this problem.|||Thx. I will check that.
"David J. Cartwright" wrote:

> get output from
> sp_who
> sp_who2
> sp_lock
> when the trans is running
> "Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
> news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>
>

[SQL Server]Transaction (Process ID 52) was deadlocked

I am using SQL Server 2000 and running number of query simulatneously. While
running update query it is always giving above error. The error discriptions
are -
1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock} resources
with another process and has been chosen as the deadlock victim
3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
I have tried cleaning up the database, increased the fixed memory size of
SQL Server to 800 MB, restart the machine. But nothing works.
Plz help out to resolve this problem.
get output from
sp_who
sp_who2
sp_lock
when the trans is running
"Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>I am using SQL Server 2000 and running number of query simulatneously.
>While
> running update query it is always giving above error. The error
> discriptions
> are -
> 1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
> resources with another process and has been chosen as the deadlock victim
> 2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock}
> resources
> with another process and has been chosen as the deadlock victim
> 3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
> resources with another process and has been chosen as the deadlock victim
>
> I have tried cleaning up the database, increased the fixed memory size of
> SQL Server to 800 MB, restart the machine. But nothing works.
> Plz help out to resolve this problem.
|||Thx. I will check that.
"David J. Cartwright" wrote:

> get output from
> sp_who
> sp_who2
> sp_lock
> when the trans is running
> "Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
> news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>
>

[SELECT] question

Hi all,
I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [01], the third field the values of parameter code [02] and so forth??

Table structure
CREATE TABLE `test` (
`date` datetime NOT NULL ,
`code` varchar(3) NOT NULL ,
`value` float NOT NULL
) TYPE=MyISAM;

Sample data
+-------+--+---+
| date | code | value |
+-------+--+---+
| 2004-06-01 00:00:00 | 001 | 89 |
| 2004-06-01 00:00:00 | 002 | 1 |
| 2004-06-01 01:00:00 | 001 | 76 |
| 2004-06-01 01:00:00 | 002 | 5 |
| 2004-06-01 02:00:00 | 001 | 67 |
| 2004-06-01 02:00:00 | 002 | 7 |
| 2004-06-01 03:00:00 | 001 | 46 |
| 2004-06-01 03:00:00 | 002 | 4 |
| 2004-06-01 04:00:00 | 001 | 43 |
| 2004-06-01 04:00:00 | 002 | 3 |
+-------+--+---+

Recordset
+-------+----+----+
| date | value_cod1 | value_cod2 |
+-------+----+----+
| 2004-06-01 00:00:00 | 89 | 1 |
| 2004-06-01 01:00:00 | 76 | 5 |
| 2004-06-01 02:00:00 | 67 | 7 |
|..................................... ..............|
+-------+----+----+

Thank you very much!
Paolo SaudinTo select all of the columns in an arbitrary order, you can use:SELECT *
FROM testTo select the columns in a specific order, you can use:SELECT date, code, value
FROM test-PatP|||ah, the good old denormalization question

mysql doesn't support full outer join, but this is an equivalent --

select t1.date
, t1.value as value_cod1
, t2.value as value_cod2
from `test` as t1
left outer
join `test` as t2
on t1.date
= t2.date
and t2.code = '002'
where t1.code = '001'

union all

select t2.date
, null
, t2.value
from `test` as t1
right outer
join `test` as t2
on t1.date
= t2.date
and t1.code = '001'
where t2.code = '002'
and t1.date is null

you'll need mysql 4.0 to support union, and if you don't have any code 002 rows without a code 001 row, then you can get away with just the first of the two queries|||ah, the good old denormalization questionI wondered what on earth you were babbling about as I read your response, but when I went back and re-read the question carefully I discovered that you were correct! I've always preferred the structure:SELECT date
, Sum(CASE WHEN '001' = code THEN value END) AS '001'
, Sum(CASE WHEN '002' = code THEN value END) AS '002'
, Sum(CASE WHEN '003' = code THEN value END) AS '003'
FROM test
GROUP BY dateThis only has to make one pass through the table, so it can be more efficeient if the optimiser does its job.

-PatP|||i babble often

i'm wrong occasionally

:cool:|||Thank you all very much !! :)
Paolo Saudin

Re: Accessing the underlying sql query from the rdl file

Our client uses the report builder to generate reports for collection of employees. We would like to use the employee records in this report to perform some additional processing (such as the list of employees gets assigned to a particular group).

Programmatically, I can retrieve a byte stream from rs.GetReportDefinition( "\myClientEmpReport") and deserialize the stream into Report object (as define by ReportDefinition.xsd).

I can then manually drill down and retrieve the SematicQuery xml from the commandText field.

The problem is how to convert the SemanticQuery format into a T-Sql query that I can run against the view that the report model is based off of.

Is this possible?

Thanks,

Arjay

P.S. Running SQL 2005 Reporting Services, VC# 2005, ASP.Net 2.0.

After attempting to autogenerate serialization classes on the SemanticQuery xml with Xsd and XsdObjectGen, I ended up hand coding some classes that handled recursion. From there, I was able to regenerate the sql query by walking through the filter and grouping sections. While this isn't a generic solution, it works well for my needs because the model I need this for is a single datasource derived from a view.

As a feature request, it sure would be nice to be able get the query string from the reporting engine directly.

[QUERY] Help...

I have 2 table:
1->Deals with ID,IDCompany,Concluded
2->DealDetail with ID,IDDeal,DateStart,DateEnd

I want to extract the list of Deals with the DateStart less than
Today, not concluded.
the list must have this fiels: IDCompany, DateStart,DateEnd

I've did this query:
----
SELECT DISTINCT
D.IDcompany,
DSS.Start,
DSE.[End]
FROM tblDealDetail DD
RIGHT JOIN
(
SELECT
IDCompany,
MIN(DD.DataStart) Start
FROM tblDealDetail DD
LEFT JOIN tblDeals D
ON D.ID = DD.IDDeal
WHERE D.Concluded <> 1
GROUP BY IDCompany
) DSS
ON DSS.Start = DD.DataStart

RIGHT JOIN (
SELECT
IDCompany,
MIN(DD.DataEnd) [End]
FROM tblDealDetail DD
LEFT JOIN tblDeals D
ON D.ID = DD.IDDeal
WHERE D.Concluded <> 1
GROUP BY IDCompany
) DSE
ON DSE.[End] = DD.DataEnd

LEFT JOIN tblDeals D
ON DD.IDDeal = D.ID

WHERE D.IDCompany = DSS.IDCompany
----
But when i have 2 deal with the 2 equal DateStart the resultset don't
show me the deal.

P.S. The datestart and dataend must belong to the same dealdetail...

Thanks
LorenzoI'm not certain but it seems like you need something like this:

SELECT D.id, D.idcompany, MIN(T.datestart), MAX(T.dateend)
FROM tblDeals AS D
JOIN tblDealDetail AS T
ON D.id = T.iddeal
AND concluded = 0
GROUP BY D.id, D.idcompany
HAVING MIN(T.datestart)<'20040423'

If this doesn't answer your question it would help if you could post DDL
(CREATE TABLE statements for the tables), sample data (as INSERT statements)
and show the end result you require.

--
David Portas
SQL Server MVP
--

[Query] Help

Hello,
We someone be so kind to help me out with a (probably simpel) query?
This is some stripped down sample data to illustrate:
(two fields, 1 NAME & 1 NAMEDATE)
bill 01/03/2005 06:25
eric 01/03/2005 07:25
pat 01/03/2005 07:25
eric 01/03/2005 09:25
eric 01/03/2005 10:25
bill 01/03/2005 09:25
pat 01/03/2005 06:25
pat 01/03/2005 08:25
eric 01/03/2005 09:25
bill 01/03/2005 09:25
I am trying to find the count of names per hour period and I am struggling a
bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
work.
The query should in a query result row ...
NAME 6hr 7hr 8hr 9hr 10hr
billl 1 2
eric 1 2 1
etc
So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
Sorry if my question & layout is a bit blurry ;)
Rgds,
T.
Try,
use northwind
go
create table t (
colA varchar(25),
colB datetime
)
go
insert into t values('bill', '1/03/2005 06:25')
insert into t values('eric', '01/03/2005 07:25')
insert into t values('pat', '01/03/2005 07:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('eric', '01/03/2005 10:25')
insert into t values('bill', '01/03/2005 09:25')
insert into t values('pat', '01/03/2005 06:25')
insert into t values('pat', '01/03/2005 08:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('bill', '01/03/2005 09:25')
go
select
colA,
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 6 then
1 end) as [6hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 7 then
1 end) as [7hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 8 then
1 end) as [8hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 9 then
1 end) as [9hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 10 then
1 end) as [10hr]
from
t
group by
colA
order by
colA
drop table t
go
AMB
"TieTo" wrote:

> Hello,
> We someone be so kind to help me out with a (probably simpel) query?
> This is some stripped down sample data to illustrate:
> (two fields, 1 NAME & 1 NAMEDATE)
>
> bill 01/03/2005 06:25
> eric 01/03/2005 07:25
> pat 01/03/2005 07:25
> eric 01/03/2005 09:25
> eric 01/03/2005 10:25
> bill 01/03/2005 09:25
> pat 01/03/2005 06:25
> pat 01/03/2005 08:25
> eric 01/03/2005 09:25
> bill 01/03/2005 09:25
> I am trying to find the count of names per hour period and I am struggling a
> bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
> work.
> The query should in a query result row ...
> NAME 6hr 7hr 8hr 9hr 10hr
> billl 1 2
> eric 1 2 1
> etc
> So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
> Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
> Sorry if my question & layout is a bit blurry ;)
> Rgds,
> T.
>
>
|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:352EE400-E233-4389-90A6-7A02101A1A91@.microsoft.com...
> Try,
> use northwind
> go
>
<cut>
Hey Alejandro,
Will try your proposed query. Thanks for the reply!!!!
Rgds,
T.

[Query] Help

Hello,
We someone be so kind to help me out with a (probably simpel) query?
This is some stripped down sample data to illustrate:
(two fields, 1 NAME & 1 NAMEDATE)
bill 01/03/2005 06:25
eric 01/03/2005 07:25
pat 01/03/2005 07:25
eric 01/03/2005 09:25
eric 01/03/2005 10:25
bill 01/03/2005 09:25
pat 01/03/2005 06:25
pat 01/03/2005 08:25
eric 01/03/2005 09:25
bill 01/03/2005 09:25
I am trying to find the count of names per hour period and I am struggling a
bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
work.
The query should in a query result row ...
NAME 6hr 7hr 8hr 9hr 10hr
---
billl 1 2
eric 1 2 1
etc
So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
Sorry if my question & layout is a bit blurry ;)
Rgds,
T.Try,
use northwind
go
create table t (
colA varchar(25),
colB datetime
)
go
insert into t values('bill', '1/03/2005 06:25')
insert into t values('eric', '01/03/2005 07:25')
insert into t values('pat', '01/03/2005 07:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('eric', '01/03/2005 10:25')
insert into t values('bill', '01/03/2005 09:25')
insert into t values('pat', '01/03/2005 06:25')
insert into t values('pat', '01/03/2005 08:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('bill', '01/03/2005 09:25')
go
select
colA,
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 6 then
1 end) as [6hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 7 then
1 end) as [7hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 8 then
1 end) as [8hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 9 then
1 end) as [9hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 10 then
1 end) as [10hr]
from
t
group by
colA
order by
colA
drop table t
go
AMB
"TieTo" wrote:

> Hello,
> We someone be so kind to help me out with a (probably simpel) query?
> This is some stripped down sample data to illustrate:
> (two fields, 1 NAME & 1 NAMEDATE)
>
> bill 01/03/2005 06:25
> eric 01/03/2005 07:25
> pat 01/03/2005 07:25
> eric 01/03/2005 09:25
> eric 01/03/2005 10:25
> bill 01/03/2005 09:25
> pat 01/03/2005 06:25
> pat 01/03/2005 08:25
> eric 01/03/2005 09:25
> bill 01/03/2005 09:25
> I am trying to find the count of names per hour period and I am struggling
a
> bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
> work.
> The query should in a query result row ...
> NAME 6hr 7hr 8hr 9hr 10hr
> ---
> billl 1 2
> eric 1 2 1
> etc
> So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
> Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
> Sorry if my question & layout is a bit blurry ;)
> Rgds,
> T.
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:352EE400-E233-4389-90A6-7A02101A1A91@.microsoft.com...
> Try,
> use northwind
> go
>
<cut>
Hey Alejandro,
Will try your proposed query. Thanks for the reply!!!!
Rgds,
T.

[Query] Help

Hello,
We someone be so kind to help me out with a (probably simpel) query?
This is some stripped down sample data to illustrate:
(two fields, 1 NAME & 1 NAMEDATE)
bill 01/03/2005 06:25
eric 01/03/2005 07:25
pat 01/03/2005 07:25
eric 01/03/2005 09:25
eric 01/03/2005 10:25
bill 01/03/2005 09:25
pat 01/03/2005 06:25
pat 01/03/2005 08:25
eric 01/03/2005 09:25
bill 01/03/2005 09:25
I am trying to find the count of names per hour period and I am struggling a
bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
work.
The query should in a query result row ...
NAME 6hr 7hr 8hr 9hr 10hr
---
billl 1 2
eric 1 2 1
etc
So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
Sorry if my question & layout is a bit blurry ;)
Rgds,
T.Try,
use northwind
go
create table t (
colA varchar(25),
colB datetime
)
go
insert into t values('bill', '1/03/2005 06:25')
insert into t values('eric', '01/03/2005 07:25')
insert into t values('pat', '01/03/2005 07:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('eric', '01/03/2005 10:25')
insert into t values('bill', '01/03/2005 09:25')
insert into t values('pat', '01/03/2005 06:25')
insert into t values('pat', '01/03/2005 08:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('bill', '01/03/2005 09:25')
go
select
colA,
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 6 then
1 end) as [6hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 7 then
1 end) as [7hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 8 then
1 end) as [8hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 9 then
1 end) as [9hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 10 then
1 end) as [10hr]
from
t
group by
colA
order by
colA
drop table t
go
AMB
"TieTo" wrote:
> Hello,
> We someone be so kind to help me out with a (probably simpel) query?
> This is some stripped down sample data to illustrate:
> (two fields, 1 NAME & 1 NAMEDATE)
>
> bill 01/03/2005 06:25
> eric 01/03/2005 07:25
> pat 01/03/2005 07:25
> eric 01/03/2005 09:25
> eric 01/03/2005 10:25
> bill 01/03/2005 09:25
> pat 01/03/2005 06:25
> pat 01/03/2005 08:25
> eric 01/03/2005 09:25
> bill 01/03/2005 09:25
> I am trying to find the count of names per hour period and I am struggling a
> bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
> work.
> The query should in a query result row ...
> NAME 6hr 7hr 8hr 9hr 10hr
> ---
> billl 1 2
> eric 1 2 1
> etc
> So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
> Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
> Sorry if my question & layout is a bit blurry ;)
> Rgds,
> T.
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:352EE400-E233-4389-90A6-7A02101A1A91@.microsoft.com...
> Try,
> use northwind
> go
>
<cut>
Hey Alejandro,
Will try your proposed query. Thanks for the reply!!!!
Rgds,
T.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegroups.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegroups.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.
SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegrou ps.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.

[ODBC SQL Server Driver]Timeout Error

Hi,
I receive this intermmitent "[ODBC SQL Server Driver]
Timeout Error" eventhough i have set the Query Timeout in
Enterprise Manager to '0' (unlimited). However when
running the query in Query Analyser, it returns a result
in just a few seconds.
Is there someplace else that i need to change the timeout
setting? Or is there some other reason that this error
occasionally comes out?
Any help is much appreciated, thx!!
JaclynWhen it's intermittent, it can often be related to activity
on the server at the time the query is running. You'd want
to check locking, blocking and also check for data, log file
size changes that can contribute to timeouts (e.g. with
autogrow, autoshrink, manually growing or shrinking files)
-Sue
On Wed, 9 Jun 2004 01:45:05 -0700, "Jaclyn"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I receive this intermmitent "[ODBC SQL Server Driver]
>Timeout Error" eventhough i have set the Query Timeout in
>Enterprise Manager to '0' (unlimited). However when
>running the query in Query Analyser, it returns a result
>in just a few seconds.
>Is there someplace else that i need to change the timeout
>setting? Or is there some other reason that this error
>occasionally comes out?
>Any help is much appreciated, thx!!
>Jaclyn

[ODBC SQL Server Driver]Timeout Error

Hi,
I receive this intermmitent "[ODBC SQL Server Driver]
Timeout Error" eventhough i have set the Query Timeout in
Enterprise Manager to '0' (unlimited). However when
running the query in Query Analyser, it returns a result
in just a few seconds.
Is there someplace else that i need to change the timeout
setting? Or is there some other reason that this error
occasionally comes out?
Any help is much appreciated, thx!!
Jaclyn
When it's intermittent, it can often be related to activity
on the server at the time the query is running. You'd want
to check locking, blocking and also check for data, log file
size changes that can contribute to timeouts (e.g. with
autogrow, autoshrink, manually growing or shrinking files)
-Sue
On Wed, 9 Jun 2004 01:45:05 -0700, "Jaclyn"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I receive this intermmitent "[ODBC SQL Server Driver]
>Timeout Error" eventhough i have set the Query Timeout in
>Enterprise Manager to '0' (unlimited). However when
>running the query in Query Analyser, it returns a result
>in just a few seconds.
>Is there someplace else that i need to change the timeout
>setting? Or is there some other reason that this error
>occasionally comes out?
>Any help is much appreciated, thx!!
>Jaclyn

[Microsoft][ODBC SQL Server Driver]Timeout expired

I keep getting the above error when i run a sql from my web page. The query runs fine in enterprise manager. What is worng . Please help.

sql="SELECT top 1 * FROM V_ISSUE WHERE ISSUE_ID=" & issueid & " order by issue_id"

if Session("rs").State = 1 then Session("rs").Close
Session("rs").Open sql,Session("MyConn")Please Try this Code

sql="SELECT top 1 * FROM V_ISSUE WHERE ISSUE_ID=" & issueid & " order by issue_id"

Session("rs").Open sql,Session("MyConn")

Session("rs").close
Set Session("rs")=Nothing

[Microsoft][ODBC Sql Server Driver][Sql Server]Line1: Incorrect Sy

Hi,
I am using SQL server 2000. One of the tables in it has become really large
so i tried to use a sql query to view only a certain rows. I used the
following query:
SELECT * FROM TABLE WHERE Company= X
When i verified the syntax , it verified correctly. Buy when i tried to run
the query i got the error msg box :: [Microsoft][ODBC Sql Server Driver][Sql
Server]Line1: Invalid column name X .
But a column with the company value as X exists!
Also when i tried to use a little different query:: SELECT * FROM TABLE
WHERE Company LIKE s % , then i get the same error but the end part of the
error message says Incorrect Syntax Near %
Why is this happening?
pmud
It's a string, you need string delimiters.
WHERE Company='X'
http://www.aspfaq.com/
(Reverse address to reply.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud
|||you need single quotes around all character fields
SELECT * FROM TABLE WHERE Company= 'X'
SELECT * FROM TABLE WHERE Company LIKE 's %'
-Andre
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
> large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
> run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
> Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
> the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud

[Microsoft][ODBC Sql Server Driver][Sql Server]Line1: Incorrect Sy

Hi,
I am using SQL server 2000. One of the tables in it has become really large
so i tried to use a sql query to view only a certain rows. I used the
following query:
SELECT * FROM TABLE WHERE Company= X
When i verified the syntax , it verified correctly. Buy when i tried to run
the query i got the error msg box :: [Microsoft][ODBC Sql Server Driver][Sql
Server]Line1: Invalid column name X .
But a column with the company value as X exists!
Also when i tried to use a little different query:: SELECT * FROM TABLE
WHERE Company LIKE s % , then i get the same error but the end part of the
error message says Incorrect Syntax Near %
Why is this happening?
--
pmudIt's a string, you need string delimiters.
WHERE Company='X'
--
http://www.aspfaq.com/
(Reverse address to reply.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud|||you need single quotes around all character fields
SELECT * FROM TABLE WHERE Company= 'X'
SELECT * FROM TABLE WHERE Company LIKE 's %'
-Andre
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
> large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
> run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
> Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
> the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud|||Thanks I was trying to use double quotes instead of single...
Thanks :))
"Andre Chan" wrote:
> you need single quotes around all character fields
> SELECT * FROM TABLE WHERE Company= 'X'
> SELECT * FROM TABLE WHERE Company LIKE 's %'
> -Andre
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> > Hi,
> >
> > I am using SQL server 2000. One of the tables in it has become really
> > large
> > so i tried to use a sql query to view only a certain rows. I used the
> > following query:
> >
> > SELECT * FROM TABLE WHERE Company= X
> >
> > When i verified the syntax , it verified correctly. Buy when i tried to
> > run
> > the query i got the error msg box :: [Microsoft][ODBC Sql Server
> > Driver][Sql
> > Server]Line1: Invalid column name X .
> >
> > But a column with the company value as X exists!
> >
> > Also when i tried to use a little different query:: SELECT * FROM TABLE
> > WHERE Company LIKE s % , then i get the same error but the end part of
> > the
> > error message says Incorrect Syntax Near %
> >
> > Why is this happening?
> > --
> > pmud
>
>

[Microsoft][ODBC Sql Server Driver][Sql Server]Line1: Incorrect Sy

Hi,
I am using SQL server 2000. One of the tables in it has become really large
so i tried to use a sql query to view only a certain rows. I used the
following query:
SELECT * FROM TABLE WHERE Company= X
When i verified the syntax , it verified correctly. Buy when i tried to run
the query i got the error msg box :: [Microsoft][ODBC Sql Server Dr
iver][Sql
Server]Line1: Invalid column name X .
But a column with the company value as X exists!
Also when i tried to use a little different query:: SELECT * FROM TABLE
WHERE Company LIKE s % , then i get the same error but the end part of the
error message says Incorrect Syntax Near %
Why is this happening?
--
pmudIt's a string, you need string delimiters.
WHERE Company='X'
http://www.aspfaq.com/
(Reverse address to reply.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud|||you need single quotes around all character fields
SELECT * FROM TABLE WHERE Company= 'X'
SELECT * FROM TABLE WHERE Company LIKE 's %'
-Andre
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
> large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
> run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
> Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
> the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud