describe OK- select not

Hello,
I am connecting to SQLserver via an application running on linux.
Under the hood it does a describe which brings back the field types
for any table OK.
a select always fails (no data returned) however. I am troubleshooting
this remotely and have asked the MSSQL dba to run a select with the
same user id/password that I use on the client - this succeeds.
There is a firewall between client and db, but I guess this is OK cos
the describe succeeds.
Anything I can ask the dba to do or check to help diagnose the
problem? Does it follow that if userid is good for selects on MS box
it will be good for remote queries?
TIA
TonTry connecting to the DB remotely via Query Analyzer. I know you are on
Linux , so can you see if you can connect from a Windows, with the
username/password and run the SELECT . Does it return results?
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"ton de w" <ton_de_winter@.yahoo.co.uk> wrote in message
news:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
> Hello,
> I am connecting to SQLserver via an application running on linux.
> Under the hood it does a describe which brings back the field types
> for any table OK.
> a select always fails (no data returned) however. I am troubleshooting
> this remotely and have asked the MSSQL dba to run a select with the
> same user id/password that I use on the client - this succeeds.
> There is a firewall between client and db, but I guess this is OK cos
> the describe succeeds.
> Anything I can ask the dba to do or check to help diagnose the
> problem? Does it follow that if userid is good for selects on MS box
> it will be good for remote queries?
> TIA
> Ton
>|||On 21 Sep, 11:23, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Try connecting to the DB remotely via Query Analyzer. I know you are on
> Linux , so can you see if you can connect from a Windows, with the
> username/password and run the SELECT . Does it return results?
> --
> Jack Vamvas
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com/SQL
> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in messagenews:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
>
> > Hello,
> > I am connecting to SQLserver via an application running on linux.
> > Under the hood it does a describe which brings back the field types
> > for any table OK.
> > a select always fails (no data returned) however. I am troubleshooting
> > this remotely and have asked the MSSQL dba to run a select with the
> > same user id/password that I use on the client - this succeeds.
> > There is a firewall between client and db, but I guess this is OK cos
> > the describe succeeds.
> > Anything I can ask the dba to do or check to help diagnose the
> > problem? Does it follow that if userid is good for selects on MS box
> > it will be good for remote queries?
> > TIA
> > Ton- Hide quoted text -
> - Show quoted text -
Thanks for that Query Analyzer works well with userid/password
So does linux commandline tool tsql which allows me to pull back the
data with a "select * from tablename" - it just needs the !P port
username and password to logon.
This works fine.
However the application I am dealing with does not bring the data back
<sigh>
I am not sure how tsql gets the data without a databasename ie i would
have expected "select * from tablename" to fail and a select * from
databasename.tablename to succeed - perhaps cos of my Oracle
experience.
The application that is failing needs the database name before it will
log on OK. but "select * from tablename" generates a syntax error near
FROM - and so does a "select * from databasename.tablename"|||Ton,
sql server's specification of table in a select statement can have up to
four parts. In addition to the name itself, you can have / may need three
qualifiers:
select column_list from server_name.database_name.schema_name.table_name.
It is recommended if you are in the database where the table is located, you
use the schema_name (also know as owner) to qualify the table. If you are
not in the database where the table is, you need to add the database name
too, and then (better) specify the schema as well, which can be omitted if
it is dbo (such as mydb..mytalbe). If you are accessing a liked server, you
then also need to specify the server name.
In your case, if your default database is where the table is located, when
you establish the connection, you can directly run a select * from
tablename. But select * from databasename.tablename will never work unless
the databasename is the same as the schema name by accident, in which case
sql server considers you were doing a select * from schemaname.tablename.
hth
Quentin
"ton de w" <ton_de_winter@.yahoo.co.uk> wrote in message
news:1190390145.227481.285540@.g4g2000hsf.googlegroups.com...
> On 21 Sep, 11:23, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
>> Try connecting to the DB remotely via Query Analyzer. I know you are on
>> Linux , so can you see if you can connect from a Windows, with the
>> username/password and run the SELECT . Does it return results?
>> --
>> Jack Vamvas
>> ___________________________________
>> Need an IT job? http://www.ITjobfeed.com/SQL
>> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in
>> messagenews:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
>>
>> > Hello,
>> > I am connecting to SQLserver via an application running on linux.
>> > Under the hood it does a describe which brings back the field types
>> > for any table OK.
>> > a select always fails (no data returned) however. I am troubleshooting
>> > this remotely and have asked the MSSQL dba to run a select with the
>> > same user id/password that I use on the client - this succeeds.
>> > There is a firewall between client and db, but I guess this is OK cos
>> > the describe succeeds.
>> > Anything I can ask the dba to do or check to help diagnose the
>> > problem? Does it follow that if userid is good for selects on MS box
>> > it will be good for remote queries?
>> > TIA
>> > Ton- Hide quoted text -
>> - Show quoted text -
> Thanks for that Query Analyzer works well with userid/password
> So does linux commandline tool tsql which allows me to pull back the
> data with a "select * from tablename" - it just needs the !P port
> username and password to logon.
> This works fine.
> However the application I am dealing with does not bring the data back
> <sigh>
> I am not sure how tsql gets the data without a databasename ie i would
> have expected "select * from tablename" to fail and a select * from
> databasename.tablename to succeed - perhaps cos of my Oracle
> experience.
> The application that is failing needs the database name before it will
> log on OK. but "select * from tablename" generates a syntax error near
> FROM - and so does a "select * from databasename.tablename"
>
>|||On 21 Sep, 17:20, "Quentin Ran" <remove_qr...@.yahoo.com> wrote:
> Ton,
> sql server's specification of table in a select statement can have up to
> four parts. In addition to the name itself, you can have / may need three
> qualifiers:
> select column_list from server_name.database_name.schema_name.table_name.
> It is recommended if you are in the database where the table is located, you
> use the schema_name (also know as owner) to qualify the table. If you are
> not in the database where the table is, you need to add the database name
> too, and then (better) specify the schema as well, which can be omitted if
> it is dbo (such as mydb..mytalbe). If you are accessing a liked server, you
> then also need to specify the server name.
> In your case, if your default database is where the table is located, when
> you establish the connection, you can directly run a select * from
> tablename. But select * from databasename.tablename will never work unless
> the databasename is the same as the schema name by accident, in which case
> sql server considers you were doing a select * from schemaname.tablename.
> hth
> Quentin
> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in messagenews:1190390145.227481.285540@.g4g2000hsf.googlegroups.com...
>
> > On 21 Sep, 11:23, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> >> Try connecting to the DB remotely via Query Analyzer. I know you are on
> >> Linux , so can you see if you can connect from a Windows, with the
> >> username/password and run the SELECT . Does it return results?
> >> --
> >> Jack Vamvas
> >> ___________________________________
> >> Need an IT job? http://www.ITjobfeed.com/SQL
> >> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in
> >> messagenews:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
> >> > Hello,
> >> > I am connecting to SQLserver via an application running on linux.
> >> > Under the hood it does a describe which brings back the field types
> >> > for any table OK.
> >> > a select always fails (no data returned) however. I am troubleshooting
> >> > this remotely and have asked the MSSQL dba to run a select with the
> >> > same user id/password that I use on the client - this succeeds.
> >> > There is a firewall between client and db, but I guess this is OK cos
> >> > the describe succeeds.
> >> > Anything I can ask the dba to do or check to help diagnose the
> >> > problem? Does it follow that if userid is good for selects on MS box
> >> > it will be good for remote queries?
> >> > TIA
> >> > Ton- Hide quoted text -
> >> - Show quoted text -
> > Thanks for that Query Analyzer works well with userid/password
> > So does linux commandline tool tsql which allows me to pull back the
> > data with a "select * from tablename" - it just needs the !P port
> > username and password to logon.
> > This works fine.
> > However the application I am dealing with does not bring the data back
> > <sigh>
> > I am not sure how tsql gets the data without a databasename ie i would
> > have expected "select * from tablename" to fail and a select * from
> > databasename.tablename to succeed - perhaps cos of my Oracle
> > experience.
> > The application that is failing needs the database name before it will
> > log on OK. but "select * from tablename" generates a syntax error near
> > FROM - and so does a "select * from databasename.tablename"- Hide quoted text -
> - Show quoted text -
Gosh just what I need to know - thanks very much!
I have an idea I am going to meet a few more problems along the way
tho.
Can I ask the MSSQL dba to capture incoming queries to help me with
troubleshooting BTW?|||yes, get them to run a Profiler trace - asking to then to filter just on
your DatabaseId and Database name.
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"ton de w" <ton_de_winter@.yahoo.co.uk> wrote in message
news:1190581978.259876.259490@.d55g2000hsg.googlegroups.com...
> On 21 Sep, 17:20, "Quentin Ran" <remove_qr...@.yahoo.com> wrote:
>> Ton,
>> sql server's specification of table in a select statement can have up to
>> four parts. In addition to the name itself, you can have / may need
>> three
>> qualifiers:
>> select column_list from server_name.database_name.schema_name.table_name.
>> It is recommended if you are in the database where the table is located,
>> you
>> use the schema_name (also know as owner) to qualify the table. If you
>> are
>> not in the database where the table is, you need to add the database name
>> too, and then (better) specify the schema as well, which can be omitted
>> if
>> it is dbo (such as mydb..mytalbe). If you are accessing a liked server,
>> you
>> then also need to specify the server name.
>> In your case, if your default database is where the table is located,
>> when
>> you establish the connection, you can directly run a select * from
>> tablename. But select * from databasename.tablename will never work
>> unless
>> the databasename is the same as the schema name by accident, in which
>> case
>> sql server considers you were doing a select * from schemaname.tablename.
>> hth
>> Quentin
>> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in
>> messagenews:1190390145.227481.285540@.g4g2000hsf.googlegroups.com...
>>
>> > On 21 Sep, 11:23, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
>> >> Try connecting to the DB remotely via Query Analyzer. I know you are
>> >> on
>> >> Linux , so can you see if you can connect from a Windows, with the
>> >> username/password and run the SELECT . Does it return results?
>> >> --
>> >> Jack Vamvas
>> >> ___________________________________
>> >> Need an IT job? http://www.ITjobfeed.com/SQL
>> >> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in
>> >> messagenews:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
>> >> > Hello,
>> >> > I am connecting to SQLserver via an application running on linux.
>> >> > Under the hood it does a describe which brings back the field types
>> >> > for any table OK.
>> >> > a select always fails (no data returned) however. I am
>> >> > troubleshooting
>> >> > this remotely and have asked the MSSQL dba to run a select with the
>> >> > same user id/password that I use on the client - this succeeds.
>> >> > There is a firewall between client and db, but I guess this is OK
>> >> > cos
>> >> > the describe succeeds.
>> >> > Anything I can ask the dba to do or check to help diagnose the
>> >> > problem? Does it follow that if userid is good for selects on MS box
>> >> > it will be good for remote queries?
>> >> > TIA
>> >> > Ton- Hide quoted text -
>> >> - Show quoted text -
>> > Thanks for that Query Analyzer works well with userid/password
>> > So does linux commandline tool tsql which allows me to pull back the
>> > data with a "select * from tablename" - it just needs the !P port
>> > username and password to logon.
>> > This works fine.
>> > However the application I am dealing with does not bring the data back
>> > <sigh>
>> > I am not sure how tsql gets the data without a databasename ie i would
>> > have expected "select * from tablename" to fail and a select * from
>> > databasename.tablename to succeed - perhaps cos of my Oracle
>> > experience.
>> > The application that is failing needs the database name before it will
>> > log on OK. but "select * from tablename" generates a syntax error near
>> > FROM - and so does a "select * from databasename.tablename"- Hide
>> > quoted text -
>> - Show quoted text -
> Gosh just what I need to know - thanks very much!
> I have an idea I am going to meet a few more problems along the way
> tho.
> Can I ask the MSSQL dba to capture incoming queries to help me with
> troubleshooting BTW?
>
>

Deployment Issue

We have a requirement to display some 80,000 records in the report. The Stored procedure in the back end is displaying results in less than 2 mins..but the system hangs when we run the report.

Any work around for this strange behaviour?

When you run a report, not only do you have to collect the data, you also have to render the report, 80K records is 800 pages if you have 100 records per page.

What happens if you run the report as a scheduled report, and set the run time to the middle of the night when database and report server activity is low?

Deploy database develop from MSDE

Hi,
I have a small access database develop using msde as back end. I want to put
this backend database on shared drive so that other user can access to it.
My question is
1 - Does I need to setup MSDE on every workstation?
2 - Can other user connect to this database using ODBC?
SF
Thank you very much for this useful tip.
SF
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:4s0fftFsu16iU1@.mid.individual.net...
> hi,
> SF wrote:
> SQL Server/MSDE is not a file based database like Access is... so you only
> have to put the database on the machine hosting the MSDE instance (and not
> on a shared folder).. obviously every machine in the lan can then access
> the MSDE service...
> ODBC api? yes, if you like the hard way, but you probably shoud consider
> other data acces methods, like ADO, Ado.Net and the like...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>