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 files needed by sqlserver 2005 driver

Hello all

I built an application that connect to MS SqlServer2005 using Native driver (sqlncli.msi) I install that file from MS site, I need to deply my application to the end-user, and I would like to know what files do I need to deploy to make sure the application is gona run okay on the client PC's.

I search in the registry for the driver, and I found this "sqlncli.dll", is it enough or I need to include more files !!

Thanks and best regrdas

Waleed

SQLNCLI does not have specific dependencies, however your customers should have MDAC installed, which should be not a problem with the recent releases of Windows.

See also http://technet.microsoft.com/en-us/library/ms131334.aspx

Deploying a website with user authentication - getting errors

I have built a site that uses form-based authentication. Am I correct in assuming that the users and passwords will be stored in the SQLServer database in the aspnetdb.mdf file? The website works great and the authentication works how I want it to. However, I've tried to copy over the file onto a remote machine. The first page is a login screen and this is displayed without any problems but as soon as I attempt to log in I get an error message of:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I'm wondering if this is something to do with the fact I installed SQLServer Express edition on the remote machine. The website works no problem on the local machine in debug mode of Visual Studio 2005 (Professional Edition). I followed instructions to enable remote connections on SQLServer Express from a Microsoft support doc. If anybody can point me in the right direction I'll be very grateful as I've hit a wall here.

Did you check the connection string in web.config if it is pointing to the right server. Also, if you are using aspnetdb.mdf, you should have at least SQL Server Express edition installed on the machine hosting the website.

|||

Hi. Thanks for replying. The only connection string I have in my webconfig file is for an Oracle database that I access elsewhere in the website. Since the original website works in the debug mode of Vis Studio 2005 without any other connection string then I just copied the webconfig file over to the remote server as it was. Does that mean I have to add a connection string to the aspnetdb.mdf file? (The server has SQLServer Express installed on it.)

|||

Have a look at this and try adding a connectionstring to the server.
https://help.maximumasp.com/SmarterTicket/Customer/KBArticle.aspx?articleid=878

You might have to attach the local mdf file using this in the connection string.

AttachDBFilename=|DataDirectory|aspnet.mdf 
connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/></connectionStrings>
 

Deploy .MDF to a web host

Hi all,

I'm new to SQLServer (Express edition) so I was wondering: if the web
host supports SQLServer 2005 do I just need to move the .MDF file to my
directory on the web host to be able to use it?

Thanks,
LorenzoIf you have rights attaching a user database to a SQL Server and the
instance has access to your upload directory you can do this.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Jens wrote:
> If you have rights attaching a user database to a SQL Server and the
> instance has access to your upload directory you can do this.

Thanks for the answer, Jens

Lorenzo|||lbolognini@.gmail.com (lbolognini@.gmail.com) writes:
> I'm new to SQLServer (Express edition) so I was wondering: if the web
> host supports SQLServer 2005 do I just need to move the .MDF file to my
> directory on the web host to be able to use it?

You should probably talk to your web host about this. Jens suggested that
you may rights to attach databases. I say that the likelyhood for this is
nil, as this requires hefty rights on the server.

A little more likely is that they create an empty database for you,
and then you may be permitted to overwrite that database with a backup
of your database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dependency Info

I am under the impression that either there are deficiencies in the way SQL
Server 2000 is keeping track of depedencies or perhaps the dependencies
simply need to be re-evaulated / recompiled.

What is the best and/or easiest way to ensure that the dependency info is
accurate?

Is there a way to tell MSSQL to rebuild/reexamine them all?

Thanks in advance to the MVPs and other generous and knowledgable posters
who have helped me recently.

ChadChad (chad.dokmanovich@.unisys.com) writes:
> I am under the impression that either there are deficiencies in the way
> SQL Server 2000 is keeping track of depedencies or perhaps the
> dependencies simply need to be re-evaulated / recompiled.
> What is the best and/or easiest way to ensure that the dependency info is
> accurate?

To forget about it!

Seriously, the nature of SQL Server makes it very difficult for the
server to really keep track of dependencies. If you drop a procedure and
recreate it - of course now SQL Server cannot know which procedure
that refers to it. (The same happens if you alter a procedure. Now, that
is stupid.) Also, SQL Server permits you to create a procedure that
refers to a table that does yet not exist. And you can drop and recreate
a table.

In my daily work, I often use the dependency information to get a
quick information about references to tables and individual columns.
I often have access to an empty database that I have built from scripts,
so this information should be correct. (Since my build tools loads tables
before procedures, and anyway tells me if I refer to something non-
existing.

But if I am looking for where a procedure is being referenced, I run
a tool that searchces SourceSafe. In that case I am also interested in
references from client code, so SQL Server would not be sufficient, even
if the information was correct and complete.

> Is there a way to tell MSSQL to rebuild/reexamine them all?

Not really. What you can do is to generate scripts for your database
and then build a database from that script. But since that script
would sort the objects according to the known dependency information,
you might still not get everything loaded in the right order.

(And anyway, you should have all your source code under version control.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I check dependencies in several ways.

The least effective is to check sysdepends. As Erland pointed out,
it's not usually up-to-date or complete in the face of schema changes.
There are some ways to keep it more up-to-date though. A major problem
with sysdepends is that it doesn't keep track of cross-database
references.

You can also do a text search of stored procedure code via syscomments.

The best approach for checking dependencies I have found is to simply
script out the entire database to text files, then use a text search
utility to search for table or procedure names. This is fast and
complete. I use textpad to "find in files", it works really well. I
automatically script the databases out every day to have a current
snapshot.

My dream is to have a T-SQL parser which reads through all the code and
compiles exactly which procs, tables and fields are being used and
which operations are being done on them. And compile the results into
a set of user tables. Some day.

Deny access to tables

Hi,
I was hoping someone could help me with a problem I have
in SQL 2000, I'm an old SQL7 DBA and I haven't used SQL
Server in a few years, so I'm quite rusty.
How do you allow a user access to a database but restrict
what tables they can see/edit. The problem I'm
experiencing is the Public role, I can't seem to delete
it and I don't want to change it's permissions as all the
other users need access to the tables it allows..
Any help - I'm probably missing something so basic and
will kick myself.
Many thanks in advance for any help received.
MichelleHi;
Have a look into DENY statement in Books online
Sample statement to deny all revilages on emp_master table from user : test
DENY all on emp_master to test
Thanks
Hari
MCDBA
"Michelle T" <anonymous@.discussions.microsoft.com> wrote in message
news:192e801c44c78$5252a170$a001280a@.phx
.gbl...
> Hi,
> I was hoping someone could help me with a problem I have
> in SQL 2000, I'm an old SQL7 DBA and I haven't used SQL
> Server in a few years, so I'm quite rusty.
> How do you allow a user access to a database but restrict
> what tables they can see/edit. The problem I'm
> experiencing is the Public role, I can't seem to delete
> it and I don't want to change it's permissions as all the
> other users need access to the tables it allows..
> Any help - I'm probably missing something so basic and
> will kick myself.
> Many thanks in advance for any help received.
> Michelle
>|||Hari,
Thanks for that, I thought that a script might be what
was required.
Thanks for all your help.
Michelle
>--Original Message--
>Hi;
>Have a look into DENY statement in Books online
>Sample statement to deny all revilages on emp_master
table from user : test
>DENY all on emp_master to test
>Thanks
>Hari
>MCDBA
>"Michelle T" <anonymous@.discussions.microsoft.com> wrote
in message
> news:192e801c44c78$5252a170$a001280a@.phx
.gbl...
have[vbcol=seagreen]
restrict[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>