SQL Alert: select

Showing posts with label select. Show all posts

Tuesday, March 27, 2012

Accessing local temporary tables

I've been able to get the local temporary table name from sysobjects, but I still can't select from it.

for example, the following select statement won't work
select * from #temptable_____00015
It says invalid object name.

I've even stored the temp table name in a variable and tryed to execute dynamic sql to get to it--no luck.

I need this technique to handle two support situations: A user freezes during data entry to a temp table, I want to capture the data before they reboot, so they don't have to reenter.

Temp tables are used among several stored procedures and then crunched into other tables. I'm getting incorrect results and want to see the raw data in the temp tables to assist me in figuring out what's going on.Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.|||Originally posted by rnealejr
Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.

They exist on the hard drive. Maybe if I changed the status value in sysobjects, I could select from them?|||Please define in detail the issue you are trying to solve.

Accessing Linked Excel Server

I created a linked Excel server that is stored in a SQL2000 database.

I can run the following from the SQL server with no problem.

Select * From CSCNEDI...EDI$

When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)

[OLE/DB provider returned message: Unspecified error]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

Thanks

David Davis

Schuette Inc.

Hi, David,

The error above simply indicates a failure of the Provider to open a "connection", in this case - the MDB file. Unfortunately, this is quite generic. How are you connecting to the SQL Server? Are you using SQL or NT Authentication? Is your Excel file local on the SQL box or is it on a file share? What we might be facing here seems to be an authentication problem. Here're a couple of ideas:

== If the Excel file is on a share, try to put it locally on the SQL box (reconfigure the linked server) and try the query from the workstation again

== If you are using NT authentication, try using SQL authentication to see if this changes the effect

== To confirm if this is an authentication/permission issue, use FileMon tool (http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx) and capture the file activity when you get the failure (a good idea is to recycle SQL Server and capture the first attempt). Check the log for your excel file name and for error like "Access Denied".

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, March 25, 2012

Accessing Different Databases on Same Server with Dot Notation.

BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
BlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the insight, I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.
|||Now you post a different error message than you posted earlier. "Invalid object name" simply states
that there is no table or view named "Test1" inside the DB1 database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>
|||That was me, not the OP...;-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt
|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your questions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>

Accessing Different Databases on Same Server with Dot Notation.

BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
MattBlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the insight,
I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankNone. I pasted your code into my QA and it parses correctly (as I assu
med). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.T
K2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.|||Now you post a different error message than you posted earlier. "Invalid obj
ect name" simply states
that there is no table or view named "Test1" inside the DB1 database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assu
med). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.T
K2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>|||That was me, not the OP...;-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your quest
ions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>

Thursday, March 22, 2012

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
John
"ipramod@.gmail.com" wrote:
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegroups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.
Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/default...b;en-us;270599
John
"ipramod@.gmail.com" wrote:

> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>
|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegro ups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/defaul...kb;en-us;270599
John
"ipramod@.gmail.com" wrote:

> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegroups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing columns without the coulmn names

Hi,
I am looking for some help in MS SQL server. I want to access the columns of a table without using the names of the colulmns.
Example - SELECT table1.field[1], table1.field[2] FROM table1;
Any information to this effect is much appreciated.
cheers/- PradeepCan you explain how you want to use this information? There are probably ways to get you what you'd like, but there are at least as many really bad answers as really good ones.

-PatP|||Pat, I need to get the value of columns 1 and 2 from the table EMP, while not being bothered as to the names of the columns.|||I'd suggest something like:SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE 'table1' = c.TABLE_NAME
AND 1 = c.ORDINAL_POSITIONSee the BOL regarding INFORMATION_SCHEMA.COLUMNS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_87w3.asp) for more details.

-PatP|||Thanks a million Pat, this is what I was looking for.
Are you a SQL Server DBA?

cheers/- Pradeep|||Heck, I've been called even worse than than SQL DBA!

I answer to just about anything from "Hey you" to "Data Modeler", and everything in between. I tend to refer to myself as a Resident Curmudgeon, which seems about as good a description as any to me!

On a (very slightly) more serious note, yes, SQL DBA is one of the hats that I wear nearly every day.

-PatP|||SQL DataBase Analist|||SQL DataBase AnalistI'm a what-ist ?!?!

-PatP|||i think i can translate ...

ahem.

NEH-HERD.

Scott Davis
"If you are not part of the solution, you are probably 1/5 of the suspension" RDJabarov.|||Now Scott, what the heck does that mean? And when are you planning to have your Margarita? At my funeral?

Tuesday, March 20, 2012

Accessing #temp table in a proc as a user with minimal rights.

I need to create a #temp table in a proc as a user with minimal rights and t
hen
insert into it and select from it.
However, I am getting an error telling me that either the table does not exi
st or I
do not have sufficient rights to access it.
How do I get around this rights problem?
Thank you.
MikeMike,
Can we see the code?
AMB
"Mike Malter" wrote:

> I need to create a #temp table in a proc as a user with minimal rights and
then
> insert into it and select from it.
> However, I am getting an error telling me that either the table does not e
xist or I
> do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>
>|||YOu have to post soe DDL for us to see the error.
HTH (us), Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
>then insert into it and select from it.
> However, I am getting an error telling me that either the table does not
> exist or I do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Anyone can create temp tables. Post use the code, or code to reproduce the p
roblem and we can have a
look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
then insert into it and
>select from it.
> However, I am getting an error telling me that either the table does not e
xist or I do not have
> sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Guys,
This was my goof.
When I was in the process of reviewing the code I was going to put up here,
I
realized that instead of creating the table and then inserting from a select
. I was
creating the table and then selected into. Which you can't do if the table
already
exists.
Sorry for bothering you guys, and thank everyone for their willingness to ju
mp in and
help.
Best regards.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
> Mike,
> Can we see the code?
>
> AMB
> "Mike Malter" wrote:
>|||That is what SQL Community is for :-))
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:e8BTHwRRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Guys,
> This was my goof.
> When I was in the process of reviewing the code I was going to put up
> here, I realized that instead of creating the table and then inserting
> from a select. I was creating the table and then selected into. Which
> you can't do if the table already exists.
> Sorry for bothering you guys, and thank everyone for their willingness to
> jump in and help.
> Best regards.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
>

Monday, March 19, 2012

Access Violation When Selecting Data

Hi,

I have suddenly developed a problem when trying to select from a table. Even a standard select * from generates a SQL Dump exception handler. The table is below:

CREATE TABLE [Stagetb_GIDeptGroup] (
[f_GIImportID] [int] IDENTITY (1, 1) NOT NULL ,
[f_GIAction] [int] NOT NULL CONSTRAINT [DF_stagetb_GIDeptGroup_f_GIAction] DEFAULT (0),
[f_ImportBatchID] [int] NULL ,
[f_VisualDeptGroupID] [int] NULL ,
[f_DeptGroupName] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[f_DeptGroupTarget] [d_Percentage] NULL ,
[f_ExternalDeptGroupID] [int] NULL ,
[f_StatusID] [int] NOT NULL CONSTRAINT [DF_stagetb_GIDeptGroup_f_StatusID] DEFAULT (0),
[f_StatusDetailID] [int] NOT NULL CONSTRAINT [DF_stagetb_GIDeptGroup_f_StatusDetailID] DEFAULT (0),
CONSTRAINT [PK_stagetb_GIDeptGroup] PRIMARY KEY CLUSTERED
(
[f_GIImportID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

When i run a dbcc checkdb it finds nothing, if i try and connect from a remote connection i get a general network error and when connecting from the local machine i get this Dump exception error:

2005-12-06 08:40:55.28 spid53 Error: 0, Severity: 19, State: 0
2005-12-06 08:40:55.28 spid53 language_exec: Process 53 generated an access violation. SQL Server is terminating this process..
2005-12-06 08:40:57.76 spid53 Using 'sqlimage.dll' version '4.0.5'
Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQL00010.dmp
2005-12-06 08:40:57.76 spid53 Error: 0, Severity: 19, State: 0
2005-12-06 08:40:57.76 spid53 SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 12/06/05 08:40:57 spid 53
*
* Exception Address = 00425E61
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 70 bytes -
* select * from stagetb_GIDeptGroup
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF 0071a000
* ntdll 77F50000 77FF6FFF 000a7000
* kernel32 77E60000 77F45FFF 000e6000
* ADVAPI32 77DD0000 77E5CFFF 0008d000
* RPCRT4 78000000 78085FFF 00086000
* USER32 77D40000 77DC5FFF 00086000
* GDI32 7E090000 7E0D0FFF 00041000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77C10000 77C62FFF 00053000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 002A0000 002AFFFF 00010000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 71C20000 71C6DFFF 0004e000
* wmi 76D30000 76D33FFF 00004000
* SSNETLIB 42CF0000 42D05FFF 00016000
* WSOCK32 71AD0000 71AD7FFF 00008000
* WS2_32 71AB0000 71AC4FFF 00015000
* WS2HELP 71AA0000 71AA7FFF 00008000
* SSNMPN70 410D0000 410D5FFF 00006000
* security 71F80000 71F83FFF 00004000
* SECUR32 76F90000 76F9FFFF 00010000
* crypt32 762C0000 76347FFF 00088000
* MSASN1 762A0000 762AFFFF 00010000
* VERSION 77C00000 77C06FFF 00007000
* userenv 75A70000 75B14FFF 000a5000
* mswsock 71A50000 71A8AFFF 0003b000
* DNSAPI 76F20000 76F44FFF 00025000
* winrnr 76FB0000 76FB6FFF 00007000
* WLDAP32 76F60000 76F8BFFF 0002c000
* rasadhlp 76FC0000 76FC4FFF 00005000
* wshtcpip 71A90000 71A97FFF 00008000
* SSmsLPCn 42CD0000 42CD6FFF 00007000
* ntdsapi 767A0000 767B2FFF 00013000
* msv1_0 76D10000 76D2CFFF 0001d000
* OLE32 771B0000 772D0FFF 00121000
* CLBCATQ 76FD0000 77047FFF 00078000
* OLEAUT32 77120000 771AAFFF 0008b000
* COMRes 77050000 77114FFF 000c5000
* sqloledb 01D80000 01DF2FFF 00073000
* MSDART 74C50000 74C6EFFF 0001f000
* COMCTL32 77340000 773CAFFF 0008b000
* comdlg32 763B0000 763F4FFF 00045000
* SHLWAPI 70A70000 70AD4FFF 00065000
* SHELL32 773D0000 77BC1FFF 007f2000
* MSDATL3 60E30000 60E44FFF 00015000
* comctl32 71950000 71A33FFF 000e4000
* oledb32 02020000 02084FFF 00065000
* OLEDB32R 01E00000 01E0FFFF 00010000
* xpsqlbot 42F00000 42F05FFF 00006000
* xpstar 410F0000 41133FFF 00044000
* SQLUNIRL 5C1B0000 5C1DCFFF 0002d000
* WINSPOOL 73000000 73022FFF 00023000
* SQLRESLD 42AC0000 42AC6FFF 00007000
* SQLSVC 42C40000 42C56FFF 00017000
* ODBC32 02150000 02181FFF 00032000
* odbcbcp 02190000 02195FFF 00006000
* W95SCM 41140000 4114BFFF 0000c000
* NDDEAPI 75940000 75946FFF 00007000
* odbcint 022B0000 022C5FFF 00016000
* clusapi 55560000 55570FFF 00011000
* resutils 750B0000 750C0FFF 00011000
* SQLSVC 43970000 43975FFF 00006000
* xpstar 439E0000 439EBFFF 0000c000
* DBGHELP 02420000 02432FFF 00013000
* msdbi 6BE90000 6BEABFFF 0001c000
* sqlimage 4A400000 4A40CFFF 0000d000
*
* Edi: 1ADA13F4: 00000000 1ADA1758 00000000 1ADA1400 00000001 00000000
* Esi: 00000000:
* Eax: 1ADA13E0: 009802E4 00000032 00000000 00000000 00000000 00000000
* Ebx: 1ADA13E0: 009802E4 00000032 00000000 00000000 00000000 00000000
* Ecx: 00000000:
* Edx: 1ADA1410: 00000000 6E006100 00000000 00000000 00000000 1ADA0030
* Eip: 00425E61: 7589018B 8B10FFFC F44D8BF0 A5A5A5A5 5EC38B5F 0D89645B
* Ebp: 0213DFE8: 0213E01C 004C4EB7 1ADA0020 00000000 1ADA1168 1ADC84D0
* SegCs: 0000001B:
* EFlags: 00010282: 0050004F 00300048 00300033 00560030 004F0042 004E0000
* Esp: 0213DFCC: 1ADA07F0 1ADA0020 00A0A4A0 1ADA13E0 0213E010 0092D7B0
* SegSs: 00000023:
************************************************** *****************************
------------------------
Short Stack Dump
00425E61 Module(sqlservr+00025E61)
004C4EB7 Module(sqlservr+000C4EB7)
0049DCD9 Module(sqlservr+0009DCD9)
0049DAF9 Module(sqlservr+0009DAF9)
00447078 Module(sqlservr+00047078)
004510FE Module(sqlservr+000510FE)
00415080 Module(sqlservr+00015080)
004592CE Module(sqlservr+000592CE)
005A7208 Module(sqlservr+001A7208) (SQLExit(unsigned long)+000523F6)
005A7099 Module(sqlservr+001A7099) (SQLExit(unsigned long)+00052287)
00446A83 Module(sqlservr+00046A83)
004510FE Module(sqlservr+000510FE)
00415080 Module(sqlservr+00015080)
004592CE Module(sqlservr+000592CE)
0041534F Module(sqlservr+0001534F)
00459A54 Module(sqlservr+00059A54)
004175D8 Module(sqlservr+000175D8)
410735D0 Module(UMS+000035D0) (UmsSystemUserContext::UmsSystemUserContext(class UmsScheduler *,struct UMS_SYSPARAMS *)+00000434)
4107382C Module(UMS+0000382C) (UmsScheduler::FiberEnabled(void)+000001D2)
77C37E53 Module(MSVCRT+00027E53) (_endthread+000000AA)
77E7D33B Module(kernel32+0001D33B) (RegisterWaitForInputIdle+00000043)

I am running Windows XP Professional & MSDE Service Pack 3. Any ideas anyone?Has anyone ever experienced this before with any tables? Unable to select, update, insert or delete from the table as it throws a dump exception error. But DBCC CHECKDB returns no error?

Thursday, March 8, 2012

Access to SQL conversion

I'm trying to convert an Access Query to SQL. I am using a "Mid Statement" to select a portion of a value:

Mid([ITEM_NUMBER],2,7)

When I try to parse this statement in SQL I always get an error "Does not recognize Mid as a valid procedure."

Is there an SQL statement I could use to accomplish this?the sql server equivalent is the SUBSTRING function

instead of
Mid([ITEM_NUMBER],2,7)

use
substring([ITEM_NUMBER],2,7)

Saturday, February 25, 2012

access result of "dynamic sql query" via transact sql

He,
want i want to do ist creating a dynamic query, execute it and access
the result via transact-sql.
e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE ..
The first thing i tried was to use dynamic sql in udf's, but i realised
very fast, that this wont work.
After that I tried to build the query in a stored procedure but i can't
return the result set to a function or use it in an sql statement (like
SELECT * FROM (exec sp...)). I also tried it with temporary tables but
i also can't access them via userdefined functions. And i can't use
static names for the temp-Tables or even let the user exec the stored
procedure itself, because the user should not see how the whole thing
is working. He should just type "SELECT * FROM [function name]" and not
more.
So if somebody knows how to solve this problem .. please tell me
Thanks,
stephansteph
If I understood you correctly
CREATE TABLE #T
(
col INT
)
INSERT INTO #T EXEC myStoredProcedure
"steph" <stephan@.aiche.info> wrote in message
news:1125924583.250239.32680@.o13g2000cwo.googlegroups.com...
> He,
> want i want to do ist creating a dynamic query, execute it and access
> the result via transact-sql.
> e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE ..
> The first thing i tried was to use dynamic sql in udf's, but i realised
> very fast, that this wont work.
> After that I tried to build the query in a stored procedure but i can't
> return the result set to a function or use it in an sql statement (like
> SELECT * FROM (exec sp...)). I also tried it with temporary tables but
> i also can't access them via userdefined functions. And i can't use
> static names for the temp-Tables or even let the user exec the stored
> procedure itself, because the user should not see how the whole thing
> is working. He should just type "SELECT * FROM [function name]" and not
> more.
> So if somebody knows how to solve this problem .. please tell me
> Thanks,
> stephan
>|||this would work, but i think it won't work in a udf. but i need to do
it with a udf becaus my users just want to type
SELECT * FROM ... and not
CREATE TABLE #T
(
col INT
)
INSERT INTO #T EXEC myStoredProcedure
SELECT * FROM #T
So is there any possibilty to do it with a udf ?|||steph
INSERT INTO #T SELECT <columnsd> FROM dbo.UDF does not work?
"steph" <stephan@.aiche.info> wrote in message
news:1125925509.599913.104850@.g43g2000cwa.googlegroups.com...
> this would work, but i think it won't work in a udf. but i need to do
> it with a udf becaus my users just want to type
> SELECT * FROM ... and not
> CREATE TABLE #T
> (
> col INT
> )
>
> INSERT INTO #T EXEC myStoredProcedure
> SELECT * FROM #T
> So is there any possibilty to do it with a udf ?
>|||INSERT INTO #T SELECT <columnsd> FROM dbo.UDF does not work?
not this way,
it won't work this way
create function dbo.udf ..
returns table
exec sp_creating_temp_table
return (select * from #created_temp_table)
so the user just have to type "SELECT * FROM dbo.udf WHERE .. "|||Please explain your requirement more fully and I'm sure someone can
suggest a better way. It isn't clear to me exactly why you want to do
this. Why can't you just write a query or create a view?
David Portas
SQL Server MVP
--|||I want to do a preselection like "SELECT * FROM ( dbo.udf(@.table_name,
@.other_param) ) WHERE ..." to accelerate the query. So i want to pass
the table name and the preselection params to the udf and the udf
returns the result set. The problem is i got to do some caltculations
for the preselection and then build the preselect query with the
calculated values and i think in this case a view or a selfwritten
query wont work ...
thanks
stephan|||Why not use a parameterized stored procedure? And by the way,
parameterizing table names is a really, really bad idea - and totally
unnecessary in a well-designed system.
David Portas
SQL Server MVP
--|||You didn't explain why you can't use a view or subquery. You can't use
dynamic code in a function.
Have you seen:
http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/dyn-search.html
Without more information all I can suggest is that you should review
your overall design - it sounds like a pretty odd setup to me. Have you
looked at middleware and BI tools?
David Portas
SQL Server MVP
--|||Stephan,
Can you explain what this "preselection" is (preferably with specific
examples - see http://www.aspfaq.com/etiquette.asp?id=5006).
In a well-designed database, it should not be necessary to jump
through hoops in order "to accelerate the query", whatever that
means.
Then again, if when you say "tables are dynamic," you mean
that you never know what tables exist at a given time, I think you
are in bigger trouble than if you were missing some indexes. I have
never seen a design that created and dropped tables willy-nilly that
was not little more than a huge mess.
Asking clear questions about a system like this is like asking
"What color is a chameleon?" Trying to manage one is like
trying to make clothes for amoebae. Nothing fits for more
than a few moments.
Steve Kass
Drew University
steph wrote:

>I already tried to use "parameterized stored procedure" but i can't
>access the result of a sp via t-sql so it won't work for a
>preselection.
>
>
>I know that it is not the best idea, but the tables in the db are
>dynamic, and also i want to use the functionality for more than one
>table and more then one db.
>thanks
>stephan
>
>

Friday, February 24, 2012

Access queries using up space on server's C: drive

I have users who are running large SELECT type queries in Access against
a SQL Server via ODBC. What happens is that the c:\documents and
settings\<username>\Local Settings\Temp on the SQL server itself gets filled
until the drive starts running low on space. Other than to free up space on
the server's C: drive, are there any good suggestions to redirecting where
Access stores the temp files?

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
yeah
use access data projects instead and keep all your temp tables on the
db server.. where they belong (and have a good io system on the server)
|||Who's profile "<username>" ? And \temp dir gets filled up with what?
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| I have users who are running large SELECT type queries in Access
against
| a SQL Server via ODBC. What happens is that the c:\documents and
| settings\<username>\Local Settings\Temp on the SQL server itself gets
filled
| until the drive starts running low on space. Other than to free up space
on
| the server's C: drive, are there any good suggestions to redirecting where
| Access stores the temp files?
|
|
| --
|
| *************************************************
| Andy S.
| andymcdba1@.noreply.yahoo.com
|
| Please remove "noreply" before replying.
|
| *************************************************
|
|
|||Unfortunately, the end users are creating the queries. I wish I could have
coded everything myself, but that isn't an option. I'm thinking of even
trying to redirect the ODBC log directory setting, but I was hoping there
might be other ideas.
I'd actually rather keep all the temp files locally on the clients where
there is plenty of room.
<dbahooker@.hotmail.com> wrote in message
news:1128027434.706417.58830@.g49g2000cwa.googlegro ups.com...
> yeah
> use access data projects instead and keep all your temp tables on the
> db server.. where they belong (and have a good io system on the server)
>
|||end users CAN create queries in Access Data Projects on the SQL Server
side.. just as easily as on the Access side almost
stored procs are a lot more fun that mdb
|||username would be the end user's profile. I'm not sure of the file
extension, but it is a single large temp file that can grow several hundred
MB in size. I think it is from the ODBC setting for saving long running
queries to a particular folder. I'm not sure why it wouldn't be on the
workstation vs. the server.
"Dave Patrick" <DSPatrick@.nOsPAM.gmail.com> wrote in message
news:ermq9mTxFHA.3740@.tk2msftngp13.phx.gbl...
> Who's profile "<username>" ? And \temp dir gets filled up with what?
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Andy S." wrote:
> | I have users who are running large SELECT type queries in Access
> against
> | a SQL Server via ODBC. What happens is that the c:\documents and
> | settings\<username>\Local Settings\Temp on the SQL server itself gets
> filled
> | until the drive starts running low on space. Other than to free up
> space
> on
> | the server's C: drive, are there any good suggestions to redirecting
> where
> | Access stores the temp files?
> |
> |
> | --
> |
> | *************************************************
> | Andy S.
> | andymcdba1@.noreply.yahoo.com
> |
> | Please remove "noreply" before replying.
> |
> | *************************************************
> |
> |
>
|||How did the user profile folders on the SQL server come about? Do they
terminal service into the box? Some details of the files and or file types
may help. I've been running this scenario for a long time and never seen
this before.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| username would be the end user's profile. I'm not sure of the file
| extension, but it is a single large temp file that can grow several
hundred
| MB in size. I think it is from the ODBC setting for saving long running
| queries to a particular folder. I'm not sure why it wouldn't be on the
| workstation vs. the server.
|||why would you ever make a temp file that is hundreds of mb in size?
use some views man

Access queries using up space on server's C: drive

I have users who are running large SELECT type queries in Access against
a SQL Server via ODBC. What happens is that the c:\documents and
settings\<username>\Local Settings\Temp on the SQL server itself gets filled
until the drive starts running low on space. Other than to free up space on
the server's C: drive, are there any good suggestions to redirecting where
Access stores the temp files?
****************************************
*********
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
****************************************
*********yeah
use access data projects instead and keep all your temp tables on the
db server.. where they belong (and have a good io system on the server)|||Who's profile "<username>" ? And \temp dir gets filled up with what?
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| I have users who are running large SELECT type queries in Access
against
| a SQL Server via ODBC. What happens is that the c:\documents and
| settings\<username>\Local Settings\Temp on the SQL server itself gets
filled
| until the drive starts running low on space. Other than to free up space
on
| the server's C: drive, are there any good suggestions to redirecting where
| Access stores the temp files?
|
|
| --
|
| ****************************************
*********
| Andy S.
| andymcdba1@.noreply.yahoo.com
|
| Please remove "noreply" before replying.
|
| ****************************************
*********
|
||||Unfortunately, the end users are creating the queries. I wish I could have
coded everything myself, but that isn't an option. I'm thinking of even
trying to redirect the ODBC log directory setting, but I was hoping there
might be other ideas.
I'd actually rather keep all the temp files locally on the clients where
there is plenty of room.
<dbahooker@.hotmail.com> wrote in message
news:1128027434.706417.58830@.g49g2000cwa.googlegroups.com...
> yeah
> use access data projects instead and keep all your temp tables on the
> db server.. where they belong (and have a good io system on the server)
>|||end users CAN create queries in Access Data Projects on the SQL Server
side.. just as easily as on the Access side almost
stored procs are a lot more fun that mdb|||username would be the end user's profile. I'm not sure of the file
extension, but it is a single large temp file that can grow several hundred
MB in size. I think it is from the ODBC setting for saving long running
queries to a particular folder. I'm not sure why it wouldn't be on the
workstation vs. the server.
"Dave Patrick" <DSPatrick@.nOsPAM.gmail.com> wrote in message
news:ermq9mTxFHA.3740@.tk2msftngp13.phx.gbl...
> Who's profile "<username>" ? And \temp dir gets filled up with what?
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Andy S." wrote:
> | I have users who are running large SELECT type queries in Access
> against
> | a SQL Server via ODBC. What happens is that the c:\documents and
> | settings\<username>\Local Settings\Temp on the SQL server itself gets
> filled
> | until the drive starts running low on space. Other than to free up
> space
> on
> | the server's C: drive, are there any good suggestions to redirecting
> where
> | Access stores the temp files?
> |
> |
> | --
> |
> | ****************************************
*********
> | Andy S.
> | andymcdba1@.noreply.yahoo.com
> |
> | Please remove "noreply" before replying.
> |
> | ****************************************
*********
> |
> |
>|||How did the user profile folders on the SQL server come about? Do they
terminal service into the box? Some details of the files and or file types
may help. I've been running this scenario for a long time and never seen
this before.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| username would be the end user's profile. I'm not sure of the file
| extension, but it is a single large temp file that can grow several
hundred
| MB in size. I think it is from the ODBC setting for saving long running
| queries to a particular folder. I'm not sure why it wouldn't be on the
| workstation vs. the server.|||why would you ever make a temp file that is hundreds of mb in size?
use some views man

Access queries using up space on server's C: drive

I have users who are running large SELECT type queries in Access against
a SQL Server via ODBC. What happens is that the c:\documents and
settings\<username>\Local Settings\Temp on the SQL server itself gets filled
until the drive starts running low on space. Other than to free up space on
the server's C: drive, are there any good suggestions to redirecting where
Access stores the temp files?
--
*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************yeah
use access data projects instead and keep all your temp tables on the
db server.. where they belong (and have a good io system on the server)|||Who's profile "<username>" ? And \temp dir gets filled up with what?
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| I have users who are running large SELECT type queries in Access
against
| a SQL Server via ODBC. What happens is that the c:\documents and
| settings\<username>\Local Settings\Temp on the SQL server itself gets
filled
| until the drive starts running low on space. Other than to free up space
on
| the server's C: drive, are there any good suggestions to redirecting where
| Access stores the temp files?
|
|
| --
|
| *************************************************
| Andy S.
| andymcdba1@.noreply.yahoo.com
|
| Please remove "noreply" before replying.
|
| *************************************************
|
||||Unfortunately, the end users are creating the queries. I wish I could have
coded everything myself, but that isn't an option. I'm thinking of even
trying to redirect the ODBC log directory setting, but I was hoping there
might be other ideas.
I'd actually rather keep all the temp files locally on the clients where
there is plenty of room.
<dbahooker@.hotmail.com> wrote in message
news:1128027434.706417.58830@.g49g2000cwa.googlegroups.com...
> yeah
> use access data projects instead and keep all your temp tables on the
> db server.. where they belong (and have a good io system on the server)
>|||end users CAN create queries in Access Data Projects on the SQL Server
side.. just as easily as on the Access side almost
stored procs are a lot more fun that mdb|||username would be the end user's profile. I'm not sure of the file
extension, but it is a single large temp file that can grow several hundred
MB in size. I think it is from the ODBC setting for saving long running
queries to a particular folder. I'm not sure why it wouldn't be on the
workstation vs. the server.
"Dave Patrick" <DSPatrick@.nOsPAM.gmail.com> wrote in message
news:ermq9mTxFHA.3740@.tk2msftngp13.phx.gbl...
> Who's profile "<username>" ? And \temp dir gets filled up with what?
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Andy S." wrote:
> | I have users who are running large SELECT type queries in Access
> against
> | a SQL Server via ODBC. What happens is that the c:\documents and
> | settings\<username>\Local Settings\Temp on the SQL server itself gets
> filled
> | until the drive starts running low on space. Other than to free up
> space
> on
> | the server's C: drive, are there any good suggestions to redirecting
> where
> | Access stores the temp files?
> |
> |
> | --
> |
> | *************************************************
> | Andy S.
> | andymcdba1@.noreply.yahoo.com
> |
> | Please remove "noreply" before replying.
> |
> | *************************************************
> |
> |
>|||How did the user profile folders on the SQL server come about? Do they
terminal service into the box? Some details of the files and or file types
may help. I've been running this scenario for a long time and never seen
this before.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| username would be the end user's profile. I'm not sure of the file
| extension, but it is a single large temp file that can grow several
hundred
| MB in size. I think it is from the ODBC setting for saving long running
| queries to a particular folder. I'm not sure why it wouldn't be on the
| workstation vs. the server.|||why would you ever make a temp file that is hundreds of mb in size?
use some views man

Thursday, February 16, 2012

Access Linked Server Breaks

Hi,
1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
couple of weeks. If I do a SELECT statement to the Linked server in Query
Analyzer,, even on a small table of 3 records, QA seems to be working, and
no error messages come up. However, the SELECT command never completes.
2. This problem can always be cured by a reboot of server, but then it
happens again a few days or a few weeks later.
3. This exact same setup worked on another server for 1 3/4 years, but the
problem is on a newly set up SQLServer box.
4. Has anyone experienced such symptoms and found a resolution for sporadic
Linked Server to mdb failures?
5. When a Linked Server connects to an Access mdb, is this being done via an
IP connnection?
I believe a couple of years ago I had viewed the linked server process in
motion, showing among other things some IP connections, but I don't remember
how I did that.
Thanks for any help.
AlanI can't put my finger on it, but this feels like a SQL Server "mem-to-leave"
issue (the portion of SQL Server process memory that is not the buffer
pool.). Linked server info is stored there. Don't know how to help other
than suggesting running DBCC FREEPROCCACHE instead of restarting sql server
(or do you have to reboot the whole box?)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
> couple of weeks. If I do a SELECT statement to the Linked server in Query
> Analyzer,, even on a small table of 3 records, QA seems to be working, and
> no error messages come up. However, the SELECT command never completes.
> 2. This problem can always be cured by a reboot of server, but then it
> happens again a few days or a few weeks later.
> 3. This exact same setup worked on another server for 1 3/4 years, but the
> problem is on a newly set up SQLServer box.
> 4. Has anyone experienced such symptoms and found a resolution for
> sporadic Linked Server to mdb failures?
> 5. When a Linked Server connects to an Access mdb, is this being done via
> an IP connnection?
> I believe a couple of years ago I had viewed the linked server process in
> motion, showing among other things some IP connections, but I don't
> remember how I did that.
> Thanks for any help.
> Alan
>|||Kevin,
Thanks very much for your reply.
I will try that next time Linked server breaks.
Whole box has had to be rebooted to cure this.
Regards,
Alan
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>I can't put my finger on it, but this feels like a SQL Server
>"mem-to-leave" issue (the portion of SQL Server process memory that is not
>the buffer pool.). Linked server info is stored there. Don't know how to
>help other than suggesting running DBCC FREEPROCCACHE instead of restarting
>sql server (or do you have to reboot the whole box?)
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
>|||You may also want to test SP4...lots of memory leaks were fixed between 3
and 4
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:e1YnGx8PHHA.4924@.TK2MSFTNGP05.phx.gbl...
> Kevin,
> Thanks very much for your reply.
> I will try that next time Linked server breaks.
> Whole box has had to be rebooted to cure this.
> Regards,
> Alan
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>

Access Linked Server Breaks

Hi,
1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
couple of weeks. If I do a SELECT statement to the Linked server in Query
Analyzer,, even on a small table of 3 records, QA seems to be working, and
no error messages come up. However, the SELECT command never completes.
2. This problem can always be cured by a reboot of server, but then it
happens again a few days or a few weeks later.
3. This exact same setup worked on another server for 1 3/4 years, but the
problem is on a newly set up SQLServer box.
4. Has anyone experienced such symptoms and found a resolution for sporadic
Linked Server to mdb failures?
5. When a Linked Server connects to an Access mdb, is this being done via an
IP connnection?
I believe a couple of years ago I had viewed the linked server process in
motion, showing among other things some IP connections, but I don't remember
how I did that.
Thanks for any help.
Alan
I can't put my finger on it, but this feels like a SQL Server "mem-to-leave"
issue (the portion of SQL Server process memory that is not the buffer
pool.). Linked server info is stored there. Don't know how to help other
than suggesting running DBCC FREEPROCCACHE instead of restarting sql server
(or do you have to reboot the whole box?)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
> couple of weeks. If I do a SELECT statement to the Linked server in Query
> Analyzer,, even on a small table of 3 records, QA seems to be working, and
> no error messages come up. However, the SELECT command never completes.
> 2. This problem can always be cured by a reboot of server, but then it
> happens again a few days or a few weeks later.
> 3. This exact same setup worked on another server for 1 3/4 years, but the
> problem is on a newly set up SQLServer box.
> 4. Has anyone experienced such symptoms and found a resolution for
> sporadic Linked Server to mdb failures?
> 5. When a Linked Server connects to an Access mdb, is this being done via
> an IP connnection?
> I believe a couple of years ago I had viewed the linked server process in
> motion, showing among other things some IP connections, but I don't
> remember how I did that.
> Thanks for any help.
> Alan
>
|||Kevin,
Thanks very much for your reply.
I will try that next time Linked server breaks.
Whole box has had to be rebooted to cure this.
Regards,
Alan
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>I can't put my finger on it, but this feels like a SQL Server
>"mem-to-leave" issue (the portion of SQL Server process memory that is not
>the buffer pool.). Linked server info is stored there. Don't know how to
>help other than suggesting running DBCC FREEPROCCACHE instead of restarting
>sql server (or do you have to reboot the whole box?)
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
>
|||You may also want to test SP4...lots of memory leaks were fixed between 3
and 4
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:e1YnGx8PHHA.4924@.TK2MSFTNGP05.phx.gbl...
> Kevin,
> Thanks very much for your reply.
> I will try that next time Linked server breaks.
> Whole box has had to be rebooted to cure this.
> Regards,
> Alan
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>

Access' Last equivalent in SQL2005

Microsoft Access has the functions - First & Last that you can apply to a select list to limit the returned rows to those. I need to convert several queries, developed by a data researcher in Access, that use these functions into genuine Transact SQL.

I can use T-SQL's Top for the First function, but I'm stumped on what to use for Last.

Also, the inherited queries don't have any 'order' clauses, wouldn't they be necessary when using such a function?

Thanks.

For a 'q&d' (quick and dirty') way to accomplish your task, for LAST, just like using the TOP 1 function for the FIRST, you can reverse the sort [ORDER BY DESC] and retrieve the TOP 1 for the LAST.

And a custom function may be more efficient -it depends upon several factors. If you posted your entire query, someone here may be able to provide more directed assistance.

|||

This is the one I'm working on now(first), it's designed to show which students didn't finish school but were pretty close to a degree. It's nasty.

SELECT Last(fice_table.inst_type) AS LastOfinst_type, student_table.academic_year, Max(student_table.term) AS term, Last(student_table.geo_state) AS LastOfgeo_state, student_table.ssn_id, Last(student_table.degree_intent) AS LastOfdegree_intent, Last(student_table.student_level) AS LastOfstudent_level, graduated_student_table.ssn_id, Last(stdnt_lvl_desc_table.description) AS StudentLevel, Last(degr_intnt_desc_table.description) AS DegreeIntent

FROM (((student_table INNER JOIN fice_table

ON student_table.fice_code = fice_table.fice_code)

LEFT JOIN graduated_student_table

ON student_table.ssn_id = graduated_student_table.ssn_id)

INNER JOIN degr_intnt_desc_table

ON (student_table.academic_year = degr_intnt_desc_table.academic_year) AND (student_table.degree_intent = degr_intnt_desc_table.degree_intent))

INNER JOIN stdnt_lvl_desc_table

ON (student_table.academic_year = stdnt_lvl_desc_table.academic_year) AND (student_table.student_level = stdnt_lvl_desc_table.code)

GROUP BY student_table.academic_year, student_table.ssn_id, graduated_student_table.ssn_id

HAVING (((Last(fice_table.inst_type))="1") AND

((student_table.academic_year)="2006" And Not (student_table.academic_year)="2007") AND

((Last(student_table.geo_state))="04") AND

(Not (Last(student_table.degree_intent))="3") AND

((Last(student_table.student_level))="01" Or (Last(student_table.student_level))="02" Or (Last(student_table.student_level))="03" Or (Last(student_table.student_level))="04") AND

((graduated_student_table.ssn_id) Is Null));

And since she is using 'Last' without any ordering, I'm now wondering if the thing has ever worked correctly.

Thanks for your time.

|||

That is an interesting query. There is a bit of difference between Access and SQL Server. Access is a record manager and tables do have some order, so it might not be a problem. In SQL Server, it is much more particular about things like this. SQL Server optimizes query plans in a very strict manner.

|||

Interesting that's a kind word for it

I tried using top with descending indices, it was a disaster. I wound up with 1 row. Access seems able to stack up the Last functions, while SQL only allows a single TOP.

This is looking like it will have to be done with a program or stay in Access. It's beyond my meager T-SQL abilities.

Thanks.

|||

You can specify the number of rows TOP returns

SELECT TOP (5) * ...

DECLARE @.cnt int

SET @.cnt = 5

SELECT TOP (@.cnt) * ...

SELECT TOP (10) PERCENT * ...

|||

Paul,

What determines the 'LAST' entry in each of the tables?

(Is there a datetime column in each table?)

It would be helpful to have the table DDL...

|||

I'm not sure what determines 'Last' of each column. That lack of ordering was the basis of my questioning the whole history of the use of this query.

But generally, the columns that are 'Last'ed are char(1 or 2) with strictly numeric data. e.g. the domain of fice_table.inst_type is '1', '2', '3', '4' but there is no index on that column. Likewise with student_table.geo_state, the type is char(2) with domain of '00' -> '50' inclusive & '98' & '99' again no index on that column.

And I know that I can specify TOP x <col name>, but I need the Last one of each group by and I can't figure out how to do that. I've been stumped forever by a simple query to get the top 5 scores from each county from a table with a county code column domain of '00' to '75' and an integer score column. I know it can be done and it has to be the grouping that's the trouble.

|||

If you want to post some sample DDL (create table, etc.) and sample data (preferrablyin the form of INSERT statements)...we can all take a stab at coming up with something that works for you.

|||

You should be able to use the row_number() function to some advantage here. Elsewhere you asked about finding the top 5 rows for each student, which you can also do this way.

WITH Ranked AS (
select
<the columns you need>,
row_number() over (
partition by
student_table.academic_year,
student_table.ssn_id,
graduated_student_table.ssn_id
order by
whatever column list will put the rows
you want for each student/year at the top
) as rk
from your join
), TopFiveEachStudentEachYear AS (
select * from Ranked
where rk <= 5
)
select * from TopFiveEachStudentEachYear
-- or a different query against these results

The ordering in the OVER clause may benefit from expressions like:

ORDER BY

student_table.student_level DESC,

case when student table_academic_year = '2006' then 0 else 1 end,

...

Steve Kass

Drew University

www.stevekass.com

|||

OK, here's the DDL, I'm cutting out a LOT of columns and deleting a bunch of indices (it's a huge database):

This first table is kind of the parent of all tables underneath, one row per college:

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[fice_table] Script Date: 07/03/2007 14:00:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fice_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[fice_table](
[fice_code] [char](6) NOT NULL,
[school_name] [varchar](62) NOT NULL,
[school_abbr] [varchar](8) NOT NULL,
[school_type] [char](1) NULL,
[inst_type] [char](1) NULL,
[act_code] [char](4) NULL,
[fap_code] [char](3) NULL,
[ipeds_code] [char](6) NULL,
[type_code] [char](1) NULL,
[sreb_cat] [char](2) NULL,
[ets_code] [char](4) NULL,
[system_code] [char](1) NULL,
[title_iv_code] [char](6) NULL,
CONSTRAINT [PK_fice_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO

This is the student_table, one row per student per term per year per college, so a student can have up to 4 rows per year 0 = Summer II, 1 = Fall, 2 = Spring, 3 = Summer I

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[student_table](
[fice_code] [char](6) NOT NULL,
[academic_year] [char](4) NOT NULL,
[term] [char](1) NOT NULL,
[ssn_id] [char](9) NOT NULL,
...
[geo_county] [char](3) NULL,
[geo_state] [char](2) NULL,
...
[degree_intent] [char](1) NULL,
[attend_status] [char](1) NULL,
[init_admit_date] [char](6) NULL,
[student_level] [char](2) NULL,
...
[diploma_ged] [char](1) NULL,
...
[on_cr_hours] [tinyint] NULL,
[off_cr_hours] [tinyint] NULL,
...
) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [PK_student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'PK_student_table')
ALTER TABLE [dbo].[student_table] ADD CONSTRAINT [PK_student_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC,
[academic_year] ASC,
[term] ASC,
[ssn_id] ASC
)...
END
GO
SET ANSI_PADDING OFF
GO

/****** Object: Index [_dtw_index_student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'_dtw_index_student_table')
CREATE NONCLUSTERED INDEX [_dtw_index_student_table] ON [dbo].[student_table]
(
[academic_year] ASC,
[term] ASC,
[fice_code] ASC
)
INCLUDE ( [student_level],
[on_cr_hours]) ...
GO

/****** Object: Index [fice_ssn_admit_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_ssn_admit_idx')
CREATE NONCLUSTERED INDEX [fice_ssn_admit_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[ssn_id] ASC,
[admit_status] ASC,
[hs_grad_year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
GO

/****** Object: Index [fice_ssn_enroll_status_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_ssn_enroll_status_idx')
CREATE NONCLUSTERED INDEX [fice_ssn_enroll_status_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[ssn_id] ASC,
[enroll_status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
GO

/****** Object: Index [fice_year_term_ssn_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_year_term_ssn_idx')
CREATE NONCLUSTERED INDEX [fice_year_term_ssn_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[academic_year] ASC,
[term] ASC,
[ssn_id] ASC
)...
GO

Now here is the graduated_student_table, again lot's cut out:

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[graduated_student_table] Script Date: 07/03/2007 13:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[graduated_student_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[graduated_student_table](
[fice_code] [char](6) NOT NULL,
[academic_year] [char](4) NOT NULL,
[ssn_id] [char](9) NOT NULL,
...
[graduation_date] [char](6) NULL,
[degree_level] [char](2) NULL,
...

[init_enroll_status] [char](1) NULL,
[init_attend_status] [char](1) NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
...

/****** Object: Index [PK_graduated_student_table] Script Date: 07/03/2007 13:54:12 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[graduated_student_table]') AND name = N'PK_graduated_student_table')
ALTER TABLE [dbo].[graduated_student_table] ADD CONSTRAINT [PK_graduated_student_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC,
[academic_year] ASC,
[ssn_id] ASC,
[degree_1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
GO

Now here is the stdnt_lvl_desc_table, there are codes for each student level, 01 freshman, 02 sophomore, etc, etc.

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[stdnt_lvl_desc_table] Script Date: 07/03/2007 13:58:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stdnt_lvl_desc_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[stdnt_lvl_desc_table](
[academic_year] [char](4) NOT NULL,
[code] [char](2) NOT NULL,
[description] [varchar](40) NOT NULL,
CONSTRAINT [PK_stdnt_lvl_desc_table] PRIMARY KEY NONCLUSTERED
(
[academic_year] ASC,
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

And the degree_intnt_desc_table:

There are codes for the reason the student began college, 1 thru 9 for Bacc Degree Seeking, Assoc Degree Seeking, etc, etc.

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[degr_intnt_desc_table] Script Date: 07/03/2007 13:59:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[degr_intnt_desc_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[degr_intnt_desc_table](
[academic_year] [char](4) NOT NULL,
[degree_intent] [char](1) NOT NULL,
[description] [varchar](50) NULL,
[include_in_fall_cohort] [char](1) NULL,
CONSTRAINT [PK_degr_intnt_desc_table] PRIMARY KEY CLUSTERED
(
[academic_year] ASC,
[degree_intent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

As for sample data, I'm afraid I can't let that out and my VSDBPro project isn't finished yet. But, you can imagine that each row in the student_table has a Social Security Number or College Assigned ID and the graduated_student_table has a corresponding ssn_id for graduates.

Tuesday, March 27, 2012

Accessing local temporary tables

Accessing Linked Excel Server

Sunday, March 25, 2012

Accessing Different Databases on Same Server with Dot Notation.

Accessing Different Databases on Same Server with Dot Notation.

Thursday, March 22, 2012

Accessing current active trace file

Accessing current active trace file

Accessing current active trace file

Accessing columns without the coulmn names

Tuesday, March 20, 2012

Accessing #temp table in a proc as a user with minimal rights.

Monday, March 19, 2012

Access Violation When Selecting Data

Thursday, March 8, 2012

Access to SQL conversion

Saturday, February 25, 2012

access result of "dynamic sql query" via transact sql

Friday, February 24, 2012

Access queries using up space on server's C: drive

Access queries using up space on server's C: drive

Access queries using up space on server's C: drive

Access queries using up space on server's C: drive

Thursday, February 16, 2012

Access Linked Server Breaks

Access Linked Server Breaks

Access' Last equivalent in SQL2005

SQL Alert

Blog Archive

About Me