Query longer in SP: msg

Showing posts with label msg. Show all posts

Friday, March 30, 2012

Query question

Hi,
I am trying to use two databases in one query... and here is the simple
query I have created but it it throwing an error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.db1'.
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE @.db1
USE @.db2
SELECT * from authors
SELECT * from customers
What is that I am doing wrong... I went through SQL manual but no
help..
HJThe line number is wrong...
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.db1'.|||Oh by the way I am a complete novice to SQL and SQL server 2000 so
please exuse my stupid question...|||You can't have a variable for the USE command, quite simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147809433.886525.234400@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to use two databases in one query... and here is the simple
> query I have created but it it throwing an error:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '@.db1'.
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE @.db1
> USE @.db2
> SELECT * from authors
> SELECT * from customers
> What is that I am doing wrong... I went through SQL manual but no
> help..
> HJ
>|||got ya.. so how about something like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE PUBS
USE NORTHWIND
SELECT * from @.db1.dbo.authors
SELECT * from @.db2.dbo.customers|||Same problem. You cannot have a variable as the database qualifier in a SELECT statement.
Perhaps you can explain what you want to achieve in the end, and you might get some suggestions...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147810701.999538.273200@.38g2000cwa.googlegroups.com...
> got ya.. so how about something like this...
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE PUBS
> USE NORTHWIND
> SELECT * from @.db1.dbo.authors
> SELECT * from @.db2.dbo.customers
>|||Here is what I want to do... I want to use two different tables from
two different databases on the same server. I could always write full
names of the tables and databases in my script but as I am a novice, I
thought there must be someway to put table_names and db_names in a
variable...
After doing some research and copy & paste... I came with something
like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
Declare @.tbl_name1 varchar (40)
Declare @.tbl_name2 varchar (40)
select @.db1 = 'PUBS'
select @.db2 = 'NORTHWIND'
select @.tbl_name1 = 'authors'
select @.tbl_name2 = 'customers'
/*USE PUBS
USE NORTHWIND */
EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
That worked but still do not understand that EXEC () thingy...
anyother way to accomplish this?
Thank you
HJ|||On 16 May 2006 13:37:04 -0700, Hitesh Joshi wrote:
(snip)
>EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
>EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
>That worked but still do not understand that EXEC () thingy...
>anyother way to accomplish this?
Hi HJ,
http://www.sommarskog.se/dynamic_sql.html
--
Hugo Kornelis, SQL Server MVPsql

Query question

Hi,
I am trying to use two databases in one query... and here is the simple
query I have created but it it throwing an error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.db1'.
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE @.db1
USE @.db2
SELECT * from authors
SELECT * from customers
What is that I am doing wrong... I went through SQL manual but no
help..
HJThe line number is wrong...
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.db1'.|||Oh by the way I am a complete novice to SQL and SQL server 2000 so
please exuse my stupid question...|||You can't have a variable for the USE command, quite simply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147809433.886525.234400@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to use two databases in one query... and here is the simple
> query I have created but it it throwing an error:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '@.db1'.
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE @.db1
> USE @.db2
> SELECT * from authors
> SELECT * from customers
> What is that I am doing wrong... I went through SQL manual but no
> help..
> HJ
>|||got ya.. so how about something like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE PUBS
USE NORTHWIND
SELECT * from @.db1.dbo.authors
SELECT * from @.db2.dbo.customers|||Same problem. You cannot have a variable as the database qualifier in a SELE
CT statement.
Perhaps you can explain what you want to achieve in the end, and you might g
et some suggestions...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147810701.999538.273200@.38g2000cwa.googlegroups.com...
> got ya.. so how about something like this...
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE PUBS
> USE NORTHWIND
> SELECT * from @.db1.dbo.authors
> SELECT * from @.db2.dbo.customers
>|||Here is what I want to do... I want to use two different tables from
two different databases on the same server. I could always write full
names of the tables and databases in my script but as I am a novice, I
thought there must be someway to put table_names and db_names in a
variable...
After doing some research and copy & paste... I came with something
like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
Declare @.tbl_name1 varchar (40)
Declare @.tbl_name2 varchar (40)
select @.db1 = 'PUBS'
select @.db2 = 'NORTHWIND'
select @.tbl_name1 = 'authors'
select @.tbl_name2 = 'customers'
/*USE PUBS
USE NORTHWIND */
EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
That worked but still do not understand that EXEC () thingy...
anyother way to accomplish this?
Thank you
HJ|||On 16 May 2006 13:37:04 -0700, Hitesh Joshi wrote:
(snip)
>EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
>EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
>That worked but still do not understand that EXEC () thingy...
>anyother way to accomplish this?
Hi HJ,
http://www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVPsql

Wednesday, March 28, 2012

Query Problem

Monday, March 26, 2012

Query Problem

I have the following query that is giving me an error that I can't quite
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESCI found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>|||Hi Jeff,
Do you still know what the solution for your problem was? I get this error
also:
Server: Msg 8624, Level 16, State 3, Line 1
According to MSFT i should install the latest SP but that did not help.
Thanks,
STanley
"Jeff Cichocki" wrote:
> I found the problem.
> Thanks
> Jeff
> "Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
> news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
> >I have the following query that is giving me an error that I can't quite
> >figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
> >Internal SQL Server error. I think it is related to the sub-query or the
> >group by. The sub-query executes successfully if I run it by itself.
> >
> > Any Ideas as to what I can try?
> >
> > Thanks
> >
> > Jeff
> >
> >
> >
> > Declare @.StartDate VarChar(12)
> > SET @.StartDate = '04/07/03'
> >
> > SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> > @.StartDate as StartDate
> > FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> > CASE
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> > DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> > /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> > And DateAdd(Day, 13, @.StartDate) Then 4 */
> > End AS Period
> > FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> > WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> > AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> > AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> > AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> > AND A.EMPLOYEEID = B.EMPLOYEEID
> > AND B.PayRuleName <> 'All Exempt'
> > AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate)
> > GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> > GROUP BY Plant, Period
> > ORDER BY Plant, Period DESC
> >
>
>

Query Problem

Monday, February 20, 2012

Query Memory?

I had posted a msg in another thread asking about forcing MSSQL to use
query memory vs disk I/O due to long fields.

Is there any way for me to benchmark what SQL is using in Memory vs.
allocated memory.

We are running SQL 2000 Standard, not Enterprise, so it can only
address the 2GB.Here is a link to the other posting...

http://groups.google.com/group/comp...659ba61722dc3ea

Friday, March 30, 2012

Query question

Query question

Wednesday, March 28, 2012

Query Problem

Monday, March 26, 2012

Query Problem

Query Problem

Monday, February 20, 2012

Query Memory?

Query longer in SP

Blog Archive

About Me