How do I link a database in SQL

I am working on a databse on my local box, my source data is on
another. How can I link the database and table from one server to
another? Currently I am using DTS to just transfer the records!(rzito@.si.rr.com) writes:

Quote:

Originally Posted by

I am working on a databse on my local box, my source data is on
another. How can I link the database and table from one server to
another? Currently I am using DTS to just transfer the records!

You can set up a linked server. If the stars align, it's as simple as

sp_addlinkedserver THATSERVER

then you can query the remote tables with four-part notation:

select col1, col2 from THATSERVER.db.dbo.tbl

If you are on SQL 2005, you can use synonyms to make your code cleaner:

CREATE SYNONYM remotebl FOR THATSERVER.db.dbo.tlb

and then go:

select col1, col2 FROM remotetbl

Linked servers are a bit tricky to work with, and one obstacle is to
get authentication to work. I can't say that I have fully understood the
rules, but I have not dug very hard into it. You can use
sp_addlinkedsrvlogin to specify how you are to connect to the remote server.

--
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|||I actually found a much easier way, I connected to my SQL database
using MS Access ADP project, and just clicked on the link and the
wizard walked me through it, thanks for the help!

how do i know which SP am I working on?

How do I know which service pack of SQL 2000, am I working on?
Is there any 'select' for this?
Pls help.
regards
KP
http://support.microsoft.com/default...b;en-us;321185
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> How do I know which service pack of SQL 2000, am I working on?
> Is there any 'select' for this?
> Pls help.
> regards
> KP
|||select serverproperty('ProductLevel')
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
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> How do I know which service pack of SQL 2000, am I working on?
> Is there any 'select' for this?
> Pls help.
> regards
> KP
|||hi jasper,
thanx for quick response. it works.
now tell me, if i know that i'm working on SP4 now, is there any way to
downgrade to SP3? There are a lot of issues experienced in SP4 ...
regards
KP
"Jasper Smith" wrote:

> select serverproperty('ProductLevel')
> --
> 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
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
>
>
|||Read the readme:
To revert to a pre-SP4 version of SQL Server
1.. Detach all user databases. For more information, see "How to attach
and detach a database (Enterprise Manager)" in SQL Server Books Online.
2.. Uninstall SQL Server. In Control Panel, double-click Add/Remove
Programs, select the instance of SQL Server that you want to uninstall, and
click Remove.
3.. Reinstall SQL Server 2000 from the CD-ROM or from the location where
you originally installed SQL Server.
4.. Apply any service packs and hotfixes that were installed before
Database Components SP4.
5.. Restore the databases master, msdb, and model from the last backup
that was created before you installed. If the location of the data files has
not changed, this restoration automatically attaches any user databases that
were attached at the time the backup was created.
6.. Attach any user databases that were created after the last backup of
the master database.
7.. Configure replication if necessary.
Warning When you revert to the pre-SP4 version of SQL Server 2000, all
changes made to the databases master, msdb, and model since applying SP4 are
lost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...[vbcol=seagreen]
> hi jasper,
> thanx for quick response. it works.
> now tell me, if i know that i'm working on SP4 now, is there any way to
> downgrade to SP3? There are a lot of issues experienced in SP4 ...
> regards
> KP
> "Jasper Smith" wrote:
|||Also if you're having issues with SP4 then do contact PSS to open a support
case
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
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...[vbcol=seagreen]
> hi jasper,
> thanx for quick response. it works.
> now tell me, if i know that i'm working on SP4 now, is there any way to
> downgrade to SP3? There are a lot of issues experienced in SP4 ...
> regards
> KP
> "Jasper Smith" wrote:
|||what kind of issues are seen here?is there any known issue in SP4 that it's
not recommended to be installed?
"Jasper Smith" wrote:

> Also if you're having issues with SP4 then do contact PSS to open a support
> case
> --
> 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
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...
>
>

how do i know which SP am I working on?

How do I know which service pack of SQL 2000, am I working on?
Is there any 'select' for this?
Pls help.
regards
KPhttp://support.microsoft.com/default.aspx?scid=kb;en-us;321185
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> How do I know which service pack of SQL 2000, am I working on?
> Is there any 'select' for this?
> Pls help.
> regards
> KP|||select serverproperty('ProductLevel')
--
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
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> How do I know which service pack of SQL 2000, am I working on?
> Is there any 'select' for this?
> Pls help.
> regards
> KP|||hi jasper,
thanx for quick response. it works.
now tell me, if i know that i'm working on SP4 now, is there any way to
downgrade to SP3? There are a lot of issues experienced in SP4 ...
regards
KP
"Jasper Smith" wrote:
> select serverproperty('ProductLevel')
> --
> 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
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> > How do I know which service pack of SQL 2000, am I working on?
> > Is there any 'select' for this?
> >
> > Pls help.
> >
> > regards
> > KP
>
>|||Read the readme:
To revert to a pre-SP4 version of SQL Server
1.. Detach all user databases. For more information, see "How to attach
and detach a database (Enterprise Manager)" in SQL Server Books Online.
2.. Uninstall SQL Server. In Control Panel, double-click Add/Remove
Programs, select the instance of SQL Server that you want to uninstall, and
click Remove.
3.. Reinstall SQL Server 2000 from the CD-ROM or from the location where
you originally installed SQL Server.
4.. Apply any service packs and hotfixes that were installed before
Database Components SP4.
5.. Restore the databases master, msdb, and model from the last backup
that was created before you installed. If the location of the data files has
not changed, this restoration automatically attaches any user databases that
were attached at the time the backup was created.
6.. Attach any user databases that were created after the last backup of
the master database.
7.. Configure replication if necessary.
Warning When you revert to the pre-SP4 version of SQL Server 2000, all
changes made to the databases master, msdb, and model since applying SP4 are
lost.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...
> hi jasper,
> thanx for quick response. it works.
> now tell me, if i know that i'm working on SP4 now, is there any way to
> downgrade to SP3? There are a lot of issues experienced in SP4 ...
> regards
> KP
> "Jasper Smith" wrote:
>> select serverproperty('ProductLevel')
>> --
>> 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
>> "Krishnaprasad Paralikar"
>> <KrishnaprasadParalikar@.discussions.microsoft.com>
>> wrote in message
>> news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
>> > How do I know which service pack of SQL 2000, am I working on?
>> > Is there any 'select' for this?
>> >
>> > Pls help.
>> >
>> > regards
>> > KP
>>|||Also if you're having issues with SP4 then do contact PSS to open a support
case
--
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
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...
> hi jasper,
> thanx for quick response. it works.
> now tell me, if i know that i'm working on SP4 now, is there any way to
> downgrade to SP3? There are a lot of issues experienced in SP4 ...
> regards
> KP
> "Jasper Smith" wrote:
>> select serverproperty('ProductLevel')
>> --
>> 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
>> "Krishnaprasad Paralikar"
>> <KrishnaprasadParalikar@.discussions.microsoft.com>
>> wrote in message
>> news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
>> > How do I know which service pack of SQL 2000, am I working on?
>> > Is there any 'select' for this?
>> >
>> > Pls help.
>> >
>> > regards
>> > KP
>>|||what kind of issues are seen here?is there any known issue in SP4 that it's
not recommended to be installed?
"Jasper Smith" wrote:
> Also if you're having issues with SP4 then do contact PSS to open a support
> case
> --
> 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
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...
> > hi jasper,
> > thanx for quick response. it works.
> > now tell me, if i know that i'm working on SP4 now, is there any way to
> > downgrade to SP3? There are a lot of issues experienced in SP4 ...
> >
> > regards
> > KP
> >
> > "Jasper Smith" wrote:
> >
> >> select serverproperty('ProductLevel')
> >>
> >> --
> >> 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
> >>
> >> "Krishnaprasad Paralikar"
> >> <KrishnaprasadParalikar@.discussions.microsoft.com>
> >> wrote in message
> >> news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> >> > How do I know which service pack of SQL 2000, am I working on?
> >> > Is there any 'select' for this?
> >> >
> >> > Pls help.
> >> >
> >> > regards
> >> > KP
> >>
> >>
> >>
>
>sql

how do i know which SP am I working on?

How do I know which service pack of SQL 2000, am I working on?
Is there any 'select' for this?
Pls help.
regards
KPhttp://support.microsoft.com/defaul...kb;en-us;321185
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> How do I know which service pack of SQL 2000, am I working on?
> Is there any 'select' for this?
> Pls help.
> regards
> KP|||select serverproperty('ProductLevel')
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
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> How do I know which service pack of SQL 2000, am I working on?
> Is there any 'select' for this?
> Pls help.
> regards
> KP|||hi jasper,
thanx for quick response. it works.
now tell me, if i know that i'm working on SP4 now, is there any way to
downgrade to SP3? There are a lot of issues experienced in SP4 ...
regards
KP
"Jasper Smith" wrote:

> select serverproperty('ProductLevel')
> --
> 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
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.co
m>
> wrote in message news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com..
.
>
>|||Read the readme:
To revert to a pre-SP4 version of SQL Server
1.. Detach all user databases. For more information, see "How to attach
and detach a database (Enterprise Manager)" in SQL Server Books Online.
2.. Uninstall SQL Server. In Control Panel, double-click Add/Remove
Programs, select the instance of SQL Server that you want to uninstall, and
click Remove.
3.. Reinstall SQL Server 2000 from the CD-ROM or from the location where
you originally installed SQL Server.
4.. Apply any service packs and hotfixes that were installed before
Database Components SP4.
5.. Restore the databases master, msdb, and model from the last backup
that was created before you installed. If the location of the data files has
not changed, this restoration automatically attaches any user databases that
were attached at the time the backup was created.
6.. Attach any user databases that were created after the last backup of
the master database.
7.. Configure replication if necessary.
Warning When you revert to the pre-SP4 version of SQL Server 2000, all
changes made to the databases master, msdb, and model since applying SP4 are
lost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...[vbcol=seagreen]
> hi jasper,
> thanx for quick response. it works.
> now tell me, if i know that i'm working on SP4 now, is there any way to
> downgrade to SP3? There are a lot of issues experienced in SP4 ...
> regards
> KP
> "Jasper Smith" wrote:
>|||Also if you're having issues with SP4 then do contact PSS to open a support
case
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
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...[vbcol=seagreen]
> hi jasper,
> thanx for quick response. it works.
> now tell me, if i know that i'm working on SP4 now, is there any way to
> downgrade to SP3? There are a lot of issues experienced in SP4 ...
> regards
> KP
> "Jasper Smith" wrote:
>|||what kind of issues are seen here?is there any known issue in SP4 that it's
not recommended to be installed?
"Jasper Smith" wrote:

> Also if you're having issues with SP4 then do contact PSS to open a suppor
t
> case
> --
> 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
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.co
m>
> wrote in message news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com..
.
>
>

How do I know if it is working?

Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciatedAs the service is running try to access it using "osql" utility.

If you are talking about data files, yes you can declare the path while creating your database...

check create database syntax for thissql

How do I include all dates between 2 parameters in a table?

I'm working with a data set where some values have an aggretate total
(for example, a count of something) in a certain date range and others
do not. The problem I am having is that I need each page of the report
to show all the possible dates and the display a 0 when there are no
values for the date. Currently it is dropping that date.
For Example:
@.StartDate = 1/1/06
@.EndDate = 6/31/06
-- I get this on grouped values where there are empty months:
Jan06 5
Feb06 3
Apr06 7
Jun06 10
-- But I want this:
Jan06 5
Feb06 3
Mar06 0
Apr06 7
May06 0
Jun06 10
Due to other report properties and calculations, I can't really modify
my query to fix this problem. It seems like it should be easy to solve
but I'm not getting it!!!
Thanks.I think you must be using table control. so no way you can substitute 0 for
missing one. you need to modify query to use right join to include all the
rows.
if you hard code all the months value still you need to change the query to
get all the rows to column..
Try to change query, rather than breaking head.
Amarnath
"CanoAko" wrote:
> I'm working with a data set where some values have an aggretate total
> (for example, a count of something) in a certain date range and others
> do not. The problem I am having is that I need each page of the report
> to show all the possible dates and the display a 0 when there are no
> values for the date. Currently it is dropping that date.
> For Example:
> @.StartDate = 1/1/06
> @.EndDate = 6/31/06
> -- I get this on grouped values where there are empty months:
> Jan06 5
> Feb06 3
> Apr06 7
> Jun06 10
> -- But I want this:
> Jan06 5
> Feb06 3
> Mar06 0
> Apr06 7
> May06 0
> Jun06 10
> Due to other report properties and calculations, I can't really modify
> my query to fix this problem. It seems like it should be easy to solve
> but I'm not getting it!!!
> Thanks.
>|||Hmm... that doesn't really help. I admit now that I will probably have
to design a query to do it. The problem is that the query joins 2
tables and the date that I'm pulling is on the second table. So the
date doesn't exist to do an aggregate with a total of 0 in the first
place.
I'm going to try it with CASE to force it to happen, but I'm not too
excited about that. I've only been doing this for a month so I still
haven't figured everything out.
Amarnath wrote:
> I think you must be using table control. so no way you can substitute 0 for
> missing one. you need to modify query to use right join to include all the
> rows.
> if you hard code all the months value still you need to change the query to
> get all the rows to column..
> Try to change query, rather than breaking head.
> Amarnath
>
> "CanoAko" wrote:
> > I'm working with a data set where some values have an aggretate total
> > (for example, a count of something) in a certain date range and others
> > do not. The problem I am having is that I need each page of the report
> > to show all the possible dates and the display a 0 when there are no
> > values for the date. Currently it is dropping that date.
> >
> > For Example:
> >
> > @.StartDate = 1/1/06
> > @.EndDate = 6/31/06
> >
> > -- I get this on grouped values where there are empty months:
> > Jan06 5
> > Feb06 3
> > Apr06 7
> > Jun06 10
> >
> > -- But I want this:
> > Jan06 5
> > Feb06 3
> > Mar06 0
> > Apr06 7
> > May06 0
> > Jun06 10
> >
> > Due to other report properties and calculations, I can't really modify
> > my query to fix this problem. It seems like it should be easy to solve
> > but I'm not getting it!!!
> >
> > Thanks.
> >
> >|||Hi,
thats quite easy, use the following link to see how to generate a
calendar table, you can either persits it or create it on the fly.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

How do I go about placing photographs in SQL?

I have SQL 2005 Server and Visual Studio 2005 and I am working in vb. I want to create a database which has photographs.

Not sure if this is the best place for the question, but check into the VARBINARY(MAX) datatype for image storage.

Simone

|||

I found an image data-type.

thanks...

How do I get the SqlDataSource to look at the textbox

I am working with a Detailsview. I am displaying information from a number of different tables, and so when I configure the datasource I have to specify a custom sql statement. I can get it to display the right columns, but I cannot seem to be able to use a where statement.

I am wanting to display the info in the Detailsview based on what is entered into a textbox, but I get an error when I try to add in the where statement. Can someone tell me what I am missing here?

Thanks

Just add WHEREsomefield=@.textboxvalue in your SQL Query. Then (if in VS 2005), hit the refresh parameters button, and choose where to pull @.textboxvalue from (Parameter type would be control, and the ControlID would be Textbox1).|||

Thanks. It was the @. that I was missing.

|||Binding an SQL WHERE clause to a free-text field to return just one record sounds to me rather dodgy, as it indicates that the database design could be poor. Normally a DetailsView is used as part of a master-detail design and should be bound to the primary key of the master table.

How do I get the actual name of a column or table in a sql select statement?

Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

RobertYou might try it this way:

To get table names:

SELECT TABLE_NAME FROM Information_Schema.tables

Of course, that gives you some of those default tables as well like sysconstraints and dtproperties and I'm not sure how to get rid of those without hardcoding it into the query.

To get the Columns:

SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

Hope that helps,
-Ian|||sp_Columns @.TableName afaik|||Well the tips you guys came up with worked really well.

Thanks for the help guys.

Basically I took Ian's idea and played with it in SQL Server's Enterprise Manager.

I found that if you say:
SELECT * FROM Information_Schema.tables

instead of:
SELECT TABLE_NAME FROM Information_Schema.tables

you can see all the information available from information_schema.tables

When I looked at the Table_Type column, I noticed that a value of "BASE TABLE" gives you all the user tables plus dtproperties. So I made my where statement filter out the table name dtproperties and keep only the base tables table type.

here is the final sql statement I used for getting the table names:
SELECT TABLE_NAME
FROM Information_Schema.tables
WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> 'dtproperties')

Also, Ian's columns idea:
SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

worked great without having to tweak it at all.

once again thanks for the help

Robert|||I still think sp_tables is better. It's a procedure with all the execution paths compiled. too all their own though.
----

sp_tables
Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause).

Syntax
sp_tables [ [ @.table_name = ] 'name' ]
[ , [ @.table_owner = ] 'owner' ]
[ , [ @.table_qualifier = ] 'qualifier' ]
[ , [ @.table_type = ] "type" ]

Arguments
[@.table_name =] 'name'

Is the table used to return catalog information. name is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported.

[@.table_owner =] 'owner'

Is the table owner of the table used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If the owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server?, if the current user owns a table with the specified name, the columns of that table are returned. If the owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified name owned by the database owner. If one exists, the columns of that table are returned.

[@.table_qualifier =] 'qualifier'

Is the name of the table qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

[,[@.table_type =] "'type'"]

Is a list of values, separated by commas, that gives information about all tables of the table type(s) specified, including TABLE, SYSTEM TABLE, and VIEW. type is varchar(100), with a default of NULL.

Note Single quotation marks must surround each table type, and double quotation marks must enclose the entire parameter. Table types must be uppercase. If SET QUOTED_IDENTIFIER is ON, each single quotation mark must be doubled and the entire parameter must be surrounded by single quotation marks.

Return Code Values
-----
A. Return a list of objects that can be queried in the current environment
EXEC sp_tables

B. Return information about the syscolumns table in the Company database
EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"
-----|||kragie,

I agree about using Stored Procedures as much as possible, especially if you don't have to code it yourself. There are so many benefits: speed, security, reusability, etc...

The reason why I didn't use the sp_tables procedure was because I wanted to exclude a couple tables (dtproperties and a settings table) that are considered to be user tables from being put in the dropdownlist on the web page. The only way i could figure out a way to do this was to write the select statement manually.

I plan on writing a custom stored procedure that will either use the custom select statement or further filter the results of the sp_tables procedure to get the data. That way i get a speed boost and more flexibility with what data I'm playing with.

This morning I was just trying to figure out if any of this stuff is even possible, now that I know it is I plan on refining the solution so it isn't inefficient.

Once again thanks for the suggestions!

BTW, your signature is hilarious. I couldn't stop laughing for 15 seconds.

how do i get started

I hope some one can help with this most basic of
questions. I am working on a project that requires a
mediium size data base and so MSDE look like a good fit.
Problem is that in all my VB 6.0 work i never did any data
base work and i cant find any good way to get started. I
am lookign for samples and things to help me conect, read
write and update tables. All the MSDN resources talk about
what you can do but none tell me how.
Thanks to any one who can help
"steven davis" <stevend@.parabit.com> wrote in message
news:24ed601c45fdb$8cabc380$a401280a@.phx.gbl...
> I hope some one can help with this most basic of
> questions. I am working on a project that requires a
> mediium size data base and so MSDE look like a good fit.
> Problem is that in all my VB 6.0 work i never did any data
> base work and i cant find any good way to get started. I
> am lookign for samples and things to help me conect, read
> write and update tables. All the MSDN resources talk about
> what you can do but none tell me how.
> Thanks to any one who can help
While this book is geared for MS Access/SQL Server developers, most, if not
all of the concepts (and code) will translate to what you need to do. I
highly recommend it.
Microsoft Access Developer's Guide to SQL Server
by Mary Chipman, Andy Baron
ISBN: 0672319446
Steve
|||Also another starter's book which uses MSDE is 'Beginning Visual Basic .Net databases' by Wrox
But what programming language are you using..
dev
"Steve Thompson" wrote:

> "steven davis" <stevend@.parabit.com> wrote in message
> news:24ed601c45fdb$8cabc380$a401280a@.phx.gbl...
> While this book is geared for MS Access/SQL Server developers, most, if not
> all of the concepts (and code) will translate to what you need to do. I
> highly recommend it.
> Microsoft Access Developer's Guide to SQL Server
> by Mary Chipman, Andy Baron
> ISBN: 0672319446
> Steve
>
>
|||"dev_kh" <devkh@.discussions.microsoft.com> wrote in message
news:11AF35E9-E1EA-45FE-A72A-E95F1AA734B3@.microsoft.com...
> Also another starter's book which uses MSDE is 'Beginning Visual Basic
..Net databases' by Wrox
> But what programming language are you using..
It sounded like VB 6 from his original post...
Steve
|||Hello.
Yes i am using vb 6.0. It looks to me like MSDE is simply
SQL 2000 with out the manager graphical interface. If that
is so then maybe i can just read an SQL & VB book. Am i
correct?
thanks.

>--Original Message--
>"dev_kh" <devkh@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:11AF35E9-E1EA-45FE-A72A-E95F1AA734B3@.microsoft.com...
is 'Beginning Visual Basic
>..Net databases' by Wrox
>It sounded like VB 6 from his original post...
>Steve
>
>.
>
|||hi,
<anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2564501c46169$ef458eb0$a601280a@.phx.gbl...
> Hello.
> Yes i am using vb 6.0. It looks to me like MSDE is simply
> SQL 2000 with out the manager graphical interface. If that
> is so then maybe i can just read an SQL & VB book. Am i
> correct?
you are correct...
I've found
http://www.amazon.com/exec/obidos/AS...948653-0983837
quite intuitive for beginners... it's a little bit dated and offer no cover
about .Net.. but addresses quite all typical scenarios for MSDE solutions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||My book "Hitchhiker's Guide to Visual Basic and SQL Server (6th Edition)"
should help quite a bit. Yes, it's a bit dated but focuses solely on SQL
Server (and MSDE).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
<anonymous@.discussions.microsoft.com> wrote in message
news:2564501c46169$ef458eb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hello.
> Yes i am using vb 6.0. It looks to me like MSDE is simply
> SQL 2000 with out the manager graphical interface. If that
> is so then maybe i can just read an SQL & VB book. Am i
> correct?
> thanks.
>
> message
> is 'Beginning Visual Basic

How do I get my local data in the database on the Internet?

I am used to working in an Intranet where I have access to all the
pieces, but now I am working with the Internet and am stuck. I receive
various datafeeds in the form of a CSV file and need to get that data
into a SQL Server database on my host. On my development machine I put
it in Access for testing my code. But now that I am done testing I
have to get the data to the real database online.
I don't know how to get the access/CSV data into Sql Server on the
Internet. I'm not sure where to start.
Thank you for any help.Hi,
it depends on how your mechanism is to import the data into the
database. If you use the enterprise Manager and DTS to import, just try
to do the same as on your current server, trying to host your file
somewhere and specify it with the http adress where it is hosted on.
(Don=B4t know if htp is supported in DTS)
Another option would be to copy the file via FTP to your server and
specify the local path for importing the data, wheter using DTS or a
linked server to import the data. There are several options to do this,
but its hard to evaluate one without knowing more details.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hi
One method would be to have a scheduled job that runs a DTS package that
will check for files in a give directory and then load them up. You could use
FTP (possibly through a ASP component) to move your files to the location. If
you schedule you job to run periodically then it could pick up the file
without further intervention or you could start the job off manually (say
through a button on your ASP page). As the location that the file is
deposited is probably on the IIS server and not the database server, you will
need a method that will securely allow your database server to see this
directory without compromising the database server.
John
"needin4mation@.gmail.com" wrote:
> I am used to working in an Intranet where I have access to all the
> pieces, but now I am working with the Internet and am stuck. I receive
> various datafeeds in the form of a CSV file and need to get that data
> into a SQL Server database on my host. On my development machine I put
> it in Access for testing my code. But now that I am done testing I
> have to get the data to the real database online.
> I don't know how to get the access/CSV data into Sql Server on the
> Internet. I'm not sure where to start.
> Thank you for any help.
>|||Do you mean ftp the local .mdf and .ldf files from my development
machine to my Internet server?|||Hi
May be look at replication?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replsec_5ad0.asp
John
"needin4mation@.gmail.com" wrote:
> Do you mean ftp the local .mdf and .ldf files from my development
> machine to my Internet server?
>

How do I get my local data in the database on the Internet?

I am used to working in an Intranet where I have access to all the
pieces, but now I am working with the Internet and am stuck. I receive
various datafeeds in the form of a CSV file and need to get that data
into a SQL Server database on my host. On my development machine I put
it in Access for testing my code. But now that I am done testing I
have to get the data to the real database online.
I don't know how to get the access/CSV data into Sql Server on the
Internet. I'm not sure where to start.
Thank you for any help.
Hi,
it depends on how your mechanism is to import the data into the
database. If you use the enterprise Manager and DTS to import, just try
to do the same as on your current server, trying to host your file
somewhere and specify it with the http adress where it is hosted on.
(Don=B4t know if htp is supported in DTS)
Another option would be to copy the file via FTP to your server and
specify the local path for importing the data, wheter using DTS or a
linked server to import the data. There are several options to do this,
but its hard to evaluate one without knowing more details.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Hi
One method would be to have a scheduled job that runs a DTS package that
will check for files in a give directory and then load them up. You could use
FTP (possibly through a ASP component) to move your files to the location. If
you schedule you job to run periodically then it could pick up the file
without further intervention or you could start the job off manually (say
through a button on your ASP page). As the location that the file is
deposited is probably on the IIS server and not the database server, you will
need a method that will securely allow your database server to see this
directory without compromising the database server.
John
"needin4mation@.gmail.com" wrote:

> I am used to working in an Intranet where I have access to all the
> pieces, but now I am working with the Internet and am stuck. I receive
> various datafeeds in the form of a CSV file and need to get that data
> into a SQL Server database on my host. On my development machine I put
> it in Access for testing my code. But now that I am done testing I
> have to get the data to the real database online.
> I don't know how to get the access/CSV data into Sql Server on the
> Internet. I'm not sure where to start.
> Thank you for any help.
>
|||Do you mean ftp the local .mdf and .ldf files from my development
machine to my Internet server?
|||Hi
May be look at replication?
http://msdn.microsoft.com/library/de...plsec_5ad0.asp
John
"needin4mation@.gmail.com" wrote:

> Do you mean ftp the local .mdf and .ldf files from my development
> machine to my Internet server?
>

How do I get my local data in the database on the Internet?

I am used to working in an Intranet where I have access to all the
pieces, but now I am working with the Internet and am stuck. I receive
various datafeeds in the form of a CSV file and need to get that data
into a SQL Server database on my host. On my development machine I put
it in Access for testing my code. But now that I am done testing I
have to get the data to the real database online.
I don't know how to get the access/CSV data into Sql Server on the
Internet. I'm not sure where to start.
Thank you for any help.Hi,
it depends on how your mechanism is to import the data into the
database. If you use the enterprise Manager and DTS to import, just try
to do the same as on your current server, trying to host your file
somewhere and specify it with the http adress where it is hosted on.
(Don=B4t know if htp is supported in DTS)
Another option would be to copy the file via FTP to your server and
specify the local path for importing the data, wheter using DTS or a
linked server to import the data. There are several options to do this,
but its hard to evaluate one without knowing more details.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi
One method would be to have a scheduled job that runs a DTS package that
will check for files in a give directory and then load them up. You could us
e
FTP (possibly through a ASP component) to move your files to the location. I
f
you schedule you job to run periodically then it could pick up the file
without further intervention or you could start the job off manually (say
through a button on your ASP page). As the location that the file is
deposited is probably on the IIS server and not the database server, you wil
l
need a method that will securely allow your database server to see this
directory without compromising the database server.
John
"needin4mation@.gmail.com" wrote:

> I am used to working in an Intranet where I have access to all the
> pieces, but now I am working with the Internet and am stuck. I receive
> various datafeeds in the form of a CSV file and need to get that data
> into a SQL Server database on my host. On my development machine I put
> it in Access for testing my code. But now that I am done testing I
> have to get the data to the real database online.
> I don't know how to get the access/CSV data into Sql Server on the
> Internet. I'm not sure where to start.
> Thank you for any help.
>|||Do you mean ftp the local .mdf and .ldf files from my development
machine to my Internet server?|||Hi
May be look at replication?
http://msdn.microsoft.com/library/d...
ad0.asp
John
"needin4mation@.gmail.com" wrote:

> Do you mean ftp the local .mdf and .ldf files from my development
> machine to my Internet server?
>