Microsoft SQL Server 2005 beta - Database Dump

Hi, I would like to generate a database dump of my Microsoft SQL Server 2005 beta databases.

Does anyone know, how to do it?

Since I have many tables in one database, I would need the dump to include the schema as well as the data,

Thanks a lot for your help!

Use Backup/Restore method to get the Schema with data.

a. Backup Database YourDatabaseName To Disk='D:\Yourdb.bak'

b. RESTORE FILELISTONLY FROM DISK='D:\Yourdb.bak'

GO

c.

RESTORE DATABASE NewDBName

FROM DISK='D:\Database Backups\TTCProvidedData.BAK'

WITH MOVE'OldDatabaseDatafileLogicalName' TO 'D:\NewDBName.mdf',

MOVE OldDatabaseLogfileLogicalName' TO'D:\NewDBName_log.ldf'

change the path and db name accordingly.

Madhu

Microsoft SQL Server 2000 Desktop Engine

I have recently installed Microsoft SQL Server 2000 Desktop Engine on a
computer. How do i create tables and stuff like I do in the standard versio
n
of SQL server? I tried connecting to it with enterprise manager but this
fails. Are you allowed to use enterprise manager with this product?
I created a dts package that deletes several database tables and reimports
the tables once a day. Ultimately I would like to run this package on this
new install of SQL desktop engine. Im not sure how to do this w/o enterprise
manager?
Any help would be greatly appreciated!-- Are you allowed to use enterprise manager with this product?
Yes the only limitation is that it is not shipped with MSDE, but it can be
used if you gotta licence from SQL Server.
Another program could be QALITE which can be downloaded for free.
--I tried connecting to it with enterprise manager but this
> fails.
This should work, what kind of error are you getting ?
Ultimately I would like to run this package on this
> new install of SQL desktop engine. Im not sure how to do this w/o
> enterprise
> manager?
You can use dtsrun.exe from the commandline to run the package if you
stored it properly(for example in a structured storage file)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"PCL" <PCL@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85CB0C41-377E-4A4D-8335-103A4AAE8488@.microsoft.com...
>I have recently installed Microsoft SQL Server 2000 Desktop Engine on a
> computer. How do i create tables and stuff like I do in the standard
> version
> of SQL server? I tried connecting to it with enterprise manager but this
> fails. Are you allowed to use enterprise manager with this product?
> I created a dts package that deletes several database tables and reimports
> the tables once a day. Ultimately I would like to run this package on this
> new install of SQL desktop engine. Im not sure how to do this w/o
> enterprise
> manager?
> Any help would be greatly appreciated!|||Thank you for your quick response and info - The QALITE program looks pretty
interesting - i have never heard of it before.
When I try to use enterprise manager. I am running it from my workstation
and am trying to register the machine that is using the desktop Engine - Whe
n
i try to do the registration it says the server either doesnt exist or i do
not have the right credentials. I am using the sa user name and the password
I gave it when i installed the desktop engine. Does enterprise manage have
to be run on the mackine that is using the desktop engine or is it the same
as a standard version of SQL server where u can manage it on a remote
workstation?
Is there anyway I can schedule my saved DTS package to run on the Desktop
Engine? I would like it to run every morning at 3AM?
"Jens Sü?meyer" wrote:

> -- Are you allowed to use enterprise manager with this product?
> Yes the only limitation is that it is not shipped with MSDE, but it can be
> used if you gotta licence from SQL Server.
> Another program could be QALITE which can be downloaded for free.
> --I tried connecting to it with enterprise manager but this
> This should work, what kind of error are you getting ?
> Ultimately I would like to run this package on this
> You can use dtsrun.exe from the commandline to run the package if you
> stored it properly(for example in a structured storage file)
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "PCL" <PCL@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85CB0C41-377E-4A4D-8335-103A4AAE8488@.microsoft.com...
>
>

Microsoft SQL Server 2000 Desktop Engine

I have recently installed Microsoft SQL Server 2000 Desktop Engine on a
computer. How do i create tables and stuff like I do in the standard version
of SQL server? I tried connecting to it with enterprise manager but this
fails. Are you allowed to use enterprise manager with this product?
I created a dts package that deletes several database tables and reimports
the tables once a day. Ultimately I would like to run this package on this
new install of SQL desktop engine. Im not sure how to do this w/o enterprise
manager?
Any help would be greatly appreciated!-- Are you allowed to use enterprise manager with this product?
Yes the only limitation is that it is not shipped with MSDE, but it can be
used if you gotta licence from SQL Server.
Another program could be QALITE which can be downloaded for free.
--I tried connecting to it with enterprise manager but this
> fails.
This should work, what kind of error are you getting ?
Ultimately I would like to run this package on this
> new install of SQL desktop engine. Im not sure how to do this w/o
> enterprise
> manager?
You can use dtsrun.exe from the commandline to run the package if you
stored it properly(for example in a structured storage file)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"PCL" <PCL@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85CB0C41-377E-4A4D-8335-103A4AAE8488@.microsoft.com...
>I have recently installed Microsoft SQL Server 2000 Desktop Engine on a
> computer. How do i create tables and stuff like I do in the standard
> version
> of SQL server? I tried connecting to it with enterprise manager but this
> fails. Are you allowed to use enterprise manager with this product?
> I created a dts package that deletes several database tables and reimports
> the tables once a day. Ultimately I would like to run this package on this
> new install of SQL desktop engine. Im not sure how to do this w/o
> enterprise
> manager?
> Any help would be greatly appreciated!|||Thank you for your quick response and info - The QALITE program looks pretty
interesting - i have never heard of it before.
When I try to use enterprise manager. I am running it from my workstation
and am trying to register the machine that is using the desktop Engine - When
i try to do the registration it says the server either doesnt exist or i do
not have the right credentials. I am using the sa user name and the password
I gave it when i installed the desktop engine. Does enterprise manage have
to be run on the mackine that is using the desktop engine or is it the same
as a standard version of SQL server where u can manage it on a remote
workstation?
Is there anyway I can schedule my saved DTS package to run on the Desktop
Engine? I would like it to run every morning at 3AM?
"Jens Sü�meyer" wrote:
> -- Are you allowed to use enterprise manager with this product?
> Yes the only limitation is that it is not shipped with MSDE, but it can be
> used if you gotta licence from SQL Server.
> Another program could be QALITE which can be downloaded for free.
> --I tried connecting to it with enterprise manager but this
> > fails.
> This should work, what kind of error are you getting ?
> Ultimately I would like to run this package on this
> > new install of SQL desktop engine. Im not sure how to do this w/o
> > enterprise
> > manager?
> You can use dtsrun.exe from the commandline to run the package if you
> stored it properly(for example in a structured storage file)
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "PCL" <PCL@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85CB0C41-377E-4A4D-8335-103A4AAE8488@.microsoft.com...
> >I have recently installed Microsoft SQL Server 2000 Desktop Engine on a
> > computer. How do i create tables and stuff like I do in the standard
> > version
> > of SQL server? I tried connecting to it with enterprise manager but this
> > fails. Are you allowed to use enterprise manager with this product?
> >
> > I created a dts package that deletes several database tables and reimports
> > the tables once a day. Ultimately I would like to run this package on this
> > new install of SQL desktop engine. Im not sure how to do this w/o
> > enterprise
> > manager?
> >
> > Any help would be greatly appreciated!
>
>

Microsoft SQL Server 2000 Desktop Engine

I have recently installed Microsoft SQL Server 2000 Desktop Engine on a
computer. How do i create tables and stuff like I do in the standard version
of SQL server? I tried connecting to it with enterprise manager but this
fails. Are you allowed to use enterprise manager with this product?
I created a dts package that deletes several database tables and reimports
the tables once a day. Ultimately I would like to run this package on this
new install of SQL desktop engine. Im not sure how to do this w/o enterprise
manager?
Any help would be greatly appreciated!
-- Are you allowed to use enterprise manager with this product?
Yes the only limitation is that it is not shipped with MSDE, but it can be
used if you gotta licence from SQL Server.
Another program could be QALITE which can be downloaded for free.
--I tried connecting to it with enterprise manager but this
> fails.
This should work, what kind of error are you getting ?
Ultimately I would like to run this package on this
> new install of SQL desktop engine. Im not sure how to do this w/o
> enterprise
> manager?
You can use dtsrun.exe from the commandline to run the package if you
stored it properly(for example in a structured storage file)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"PCL" <PCL@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85CB0C41-377E-4A4D-8335-103A4AAE8488@.microsoft.com...
>I have recently installed Microsoft SQL Server 2000 Desktop Engine on a
> computer. How do i create tables and stuff like I do in the standard
> version
> of SQL server? I tried connecting to it with enterprise manager but this
> fails. Are you allowed to use enterprise manager with this product?
> I created a dts package that deletes several database tables and reimports
> the tables once a day. Ultimately I would like to run this package on this
> new install of SQL desktop engine. Im not sure how to do this w/o
> enterprise
> manager?
> Any help would be greatly appreciated!
|||Thank you for your quick response and info - The QALITE program looks pretty
interesting - i have never heard of it before.
When I try to use enterprise manager. I am running it from my workstation
and am trying to register the machine that is using the desktop Engine - When
i try to do the registration it says the server either doesnt exist or i do
not have the right credentials. I am using the sa user name and the password
I gave it when i installed the desktop engine. Does enterprise manage have
to be run on the mackine that is using the desktop engine or is it the same
as a standard version of SQL server where u can manage it on a remote
workstation?
Is there anyway I can schedule my saved DTS package to run on the Desktop
Engine? I would like it to run every morning at 3AM?
"Jens Sü?meyer" wrote:

> -- Are you allowed to use enterprise manager with this product?
> Yes the only limitation is that it is not shipped with MSDE, but it can be
> used if you gotta licence from SQL Server.
> Another program could be QALITE which can be downloaded for free.
> --I tried connecting to it with enterprise manager but this
> This should work, what kind of error are you getting ?
> Ultimately I would like to run this package on this
> You can use dtsrun.exe from the commandline to run the package if you
> stored it properly(for example in a structured storage file)
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "PCL" <PCL@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85CB0C41-377E-4A4D-8335-103A4AAE8488@.microsoft.com...
>
>
sql

Microsoft Great Plain integration application and new tables on sql

In dexterity iam using table_compare() function to check if tables in datasource and dictionary are same but its not picking up the primary key change but it does pick up the column change

any suggestion

thanks

This forum is for SQL Server Integration Services. Are you trying to integrate Great Plains with another system via SQL Server Integration Services? I read your question as being about Great Plains only, so this is probably not a good forum, you may have better luck on a Great Plains specific forum.

Microsoft Access to SQL Server 2005

Hi,

I have a database in MSACCESS 2003 that is 680MB (after compacting) and contains 41 tables.

I tried to import these tables to SQL Server 2005 (express edition) using the :"upsizing wizard" provided in MSACCESS. The wizard works fine but all the tables are not being imported to SQL Server (only 19 tables are getting imported) and the size of the database in SQL Server (the ".mdf file") is 1.03 GB (I have tried using the "shrink" option also) . Further, when I try to open one of the tables (which has 625295 rows and 90 columns) , it takes lot of time to display all records.

So, my problem is do I need to set any options for any of these above issues, I tried to look for any information regarding this but was unsuccessful.

Any help on this will be very much helpful for me!

thanks

Further, when I try to open one of the tables (which has 625295 rows and 90 columns) , it takes lot of time to display all records.

So filter the records by queries and never select so much data to client application ( I hasn't ever seen a user able to deal with such amount of data ), and all will be ok.

only 19 tables are getting imported

And there were no any error message during import?

".mdf file" is 1.03 GB

It is possible, that MSSQL requieres more space to store the data.

|||

Hi,

Thanks for the information

I see then, it is normal for SQL Server 2005 to take some amount of time to display all the records of a table, if the table is huge.

Regarding the import problem, I dont get any error while importing.I wonder why only 19 tables are getting imported (may be due to huge table I have in the database)

Microsoft Access 2000 missing ODBC Links

I have a Microsoft Access 2000 with several ODBC linked tables.
The back end DB is SQL Server 2000.
At the beginning everything was running OK.
Apparently randomly, Access returns the following error:
"ODBC -- connection to SQL Server failed" when I try to open a linked table that previously work.
I must delete the linked table and create the link again to let the system work again.
Please Help !
Thanks
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
That happens whenever you make a schema change to the table in SQL
Server. One way to avoid these errors is to write VBA code that runs
on startup. It deletes any old links and re-creates them when the
Access app opens, guaranteeing that the links are always valid.
--Mary
On Fri, 22 Oct 2004 02:40:27 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>I have a Microsoft Access 2000 with several ODBC linked tables.
>The back end DB is SQL Server 2000.
>At the beginning everything was running OK.
>Apparently randomly, Access returns the following error:
>"ODBC -- connection to SQL Server failed" when I try to open a linked table that previously work.
>I must delete the linked table and create the link again to let the system work again.
>Please Help !
>Thanks
>
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Thanks for the help, but the base SQL Server tables are always the same !
No schema update have been made to them.
PS: Sorry for my POOR english...
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||It might be a network error if you're not making any schema changes.
I'd still recommend creating a procedure to deletelink/relink tables.
This will save you from having to do it manually every time there's an
error and the connection gets dropped.
--Mary
On Fri, 22 Oct 2004 08:33:12 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for the help, but the base SQL Server tables are always the same !
>No schema update have been made to them.
>PS: Sorry for my POOR english...
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Thanks for answering me !
I'll keep on trying.
Table relinking solution is OK but it's too slow...
Thanks anyway.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||It shouldn't be slow at all if you have a valid SQL Server connection
over a LAN. It should only take a couple of seconds for the code to
execute.
--Mary
On Mon, 25 Oct 2004 01:38:45 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for answering me !
>I'll keep on trying.
>Table relinking solution is OK but it's too slow...
>Thanks anyway.
>
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Relinking one or two tables is OK, but I have 110 tables linked :-O !
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||I'm not talking about doing it manually, I'm talking about running VBA
code that uses DAO to delete and recreate tabledef objects. This
should run very quickly since all it's doing is creating tabledef
objects in the local .mdb and setting their connection properties. You
don't access the actual tables on the server until you open them. The
only way this code takes longer than a few seconds is if you're
linking thousands of tables, which no one in their right mind is going
to do anyway.
--Mary
On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Relinking one or two tables is OK, but I have 110 tables linked :-O !
>
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||You shouldn't need to delete and recreate the TableDef objects Umberto. Just
call the RefreshLink method. Mary is correct it should go very fast -
seconds not minutes. Use this code:
For Each tdf In CurrentDb.TableDefs
If InStr(1, tdf.Connect, "ODBC") > 0 Then
tdf.RefreshLink
End If
Next
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ivlsn0p3bhju561t3i46vu8g01j2a73d37@.4ax.com... [vbcol=seagreen]
> I'm not talking about doing it manually, I'm talking about running VBA
> code that uses DAO to delete and recreate tabledef objects. This
> should run very quickly since all it's doing is creating tabledef
> objects in the local .mdb and setting their connection properties. You
> don't access the actual tables on the server until you open them. The
> only way this code takes longer than a few seconds is if you're
> linking thousands of tables, which no one in their right mind is going
> to do anyway.
> --Mary
> On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
> wrote:
ASP.NET resources...
>
|||I have been having the same trouble lately, but I think I just fixed it.
When linking the table there is a check box to save password (I swear I
have never seen it before). I reestablished one table from each
database I am linked to and it seems to work. Previously every time I
closed and reopened Access I had to relink the tables.
Ed21
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message443464.html

Microsoft Access 2000 missing ODBC Links

I have a Microsoft Access 2000 with several ODBC linked tables.
The back end DB is SQL Server 2000.
At the beginning everything was running OK.
Apparently randomly, Access returns the following error:
"ODBC -- connection to SQL Server failed" when I try to open a linked table
that previously work.
I must delete the linked table and create the link again to let the system w
ork again.
Please Help !
Thanks
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...That happens whenever you make a schema change to the table in SQL
Server. One way to avoid these errors is to write VBA code that runs
on startup. It deletes any old links and re-creates them when the
Access app opens, guaranteeing that the links are always valid.
--Mary
On Fri, 22 Oct 2004 02:40:27 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>I have a Microsoft Access 2000 with several ODBC linked tables.
>The back end DB is SQL Server 2000.
>At the beginning everything was running OK.
>Apparently randomly, Access returns the following error:
>"ODBC -- connection to SQL Server failed" when I try to open a linked table
that previously work.
>I must delete the linked table and create the link again to let the system
work again.
>Please Help !
>Thanks
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Thanks for the help, but the base SQL Server tables are always the same !
No schema update have been made to them.
PS: Sorry for my POOR english...
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||It might be a network error if you're not making any schema changes.
I'd still recommend creating a procedure to deletelink/relink tables.
This will save you from having to do it manually every time there's an
error and the connection gets dropped.
--Mary
On Fri, 22 Oct 2004 08:33:12 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for the help, but the base SQL Server tables are always the same !
>No schema update have been made to them.
>PS: Sorry for my POOR english...
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Thanks for answering me !
I'll keep on trying.
Table relinking solution is OK but it's too slow...
Thanks anyway.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||It shouldn't be slow at all if you have a valid SQL Server connection
over a LAN. It should only take a couple of seconds for the code to
execute.
--Mary
On Mon, 25 Oct 2004 01:38:45 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for answering me !
>I'll keep on trying.
>Table relinking solution is OK but it's too slow...
>Thanks anyway.
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Relinking one or two tables is OK, but I have 110 tables linked :-O !
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||I'm not talking about doing it manually, I'm talking about running VBA
code that uses DAO to delete and recreate tabledef objects. This
should run very quickly since all it's doing is creating tabledef
objects in the local .mdb and setting their connection properties. You
don't access the actual tables on the server until you open them. The
only way this code takes longer than a few seconds is if you're
linking thousands of tables, which no one in their right mind is going
to do anyway.
--Mary
On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Relinking one or two tables is OK, but I have 110 tables linked :-O !
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||You shouldn't need to delete and recreate the TableDef objects Umberto. Just
call the RefreshLink method. Mary is correct it should go very fast -
seconds not minutes. Use this code:
For Each tdf In CurrentDb.TableDefs
If InStr(1, tdf.Connect, "ODBC") > 0 Then
tdf.RefreshLink
End If
Next
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ivlsn0p3bhju561t3i46vu8g01j2a73d37@.
4ax.com...
> I'm not talking about doing it manually, I'm talking about running VBA
> code that uses DAO to delete and recreate tabledef objects. This
> should run very quickly since all it's doing is creating tabledef
> objects in the local .mdb and setting their connection properties. You
> don't access the actual tables on the server until you open them. The
> only way this code takes longer than a few seconds is if you're
> linking thousands of tables, which no one in their right mind is going
> to do anyway.
> --Mary
> On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
> wrote:
>
ASP.NET resources...[vbcol=seagreen]
>|||I have been having the same trouble lately, but I think I just fixed it. Whe
n linking the table there is a check box to save password (I swear I have ne
ver seen it before). I reestablished one table from each database I am linke
d to and it seems to work. Previously every time I closed and reopened Acces
s I had to relink the tables.

Metadata about tables

Is there a way to add metadata to tables in a database in either SQL
2000 or 2005, describing what they are for and what their data
contains (just plain text string description) , from a seperate table
that contains teh database table names and a field that might be
called 'Description'?
tia MattRead in Books Online about "Extended properties".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ma77g@.clara.co.uk> wrote in message news:1190916619.563956.256400@.19g2000hsx.googlegroups.com...
> Is there a way to add metadata to tables in a database in either SQL
> 2000 or 2005, describing what they are for and what their data
> contains (just plain text string description) , from a seperate table
> that contains teh database table names and a field that might be
> called 'Description'?
> tia Matt
>

Meta Database Design Select Question.

Hi,

I have a Datbase with teh following two tables.

Table 1: default_en_listingsdb
it contains the following fields
ID user_ID Title

Table2:
default_en_listingsdbelements
ID field_name field_value listing_id

listing_ID in table 2 is equal to the ID in table 1.

I am building a search for these two tables where i want to return the ID, user_ID and Title. The table data looks like this.

Table 1:
ID user_ID Title
1 1 Test
2 2 Test2

Table2:
ID field_name field_value listing_id
1 beds 2 2
2 beds 1 1
3 city kingsley 1
4 city kingsley 2

I want to return only the ID's where they contain both beds = 2 and city = kingsley.. I want to do this with on e select statement if possible.

Thanks,I hate this, what Tom Kyte calls the "funky data model". Flexible as you like, but so hard to query (and this is a very simple query!)

Also, data integrity is non-existent, because there can be no database constraint that stops you putting 2 for city or kingsley for beds...

Anyway... </RANT>

select t1.*
from table1 t1, table2 beds, table2 city
where t1.id = beds.listing_id
and beds.field_name = 'beds'
and beds.field_value = '2'
and city.field_name = 'city'
and city.field_value = 'kingsley';

Yuk!|||Thanks, I knew it was going to be ugly, i agree that this data model in some ways really sucks... ohh well off to make this work.. Thanks again|||Originally posted by greengaint
Thanks, I knew it was going to be ugly, i agree that this data model in some ways really sucks... ohh well off to make this work.. Thanks again
Everyone "invents" this data model at some point early in their database careers; well, I'm sure I did once anyway. "Hey, look! With this model we don't have to alter tables or application code ever again! The users can just define their own 'fields' whenever they like!" Then later you find that:
(a) performance sucks very badly, and
(b) you need to write SQL 17 pages long to produce the simplest report, and
(c) the data is full of nonsense like beds='4.2' and city = ' kignsley' and startdate = '31 Feb 2004'... and
(d) nobody likes this system any more

Meta data??

Hi,
We have about 50 databases all of them are being merge replicated. On a
average , each database has about 150 tables which are published.
We get meta data retention error atleast one per week on a average. We have
two category of users, Users at the publisher server when they log (dialup
or dedicated Network) .Users logging on to the subscriber server -no dial up
always via dedicated network.
We notice meta-deta retention error message only when users connect to the
publisher server, that too when people dial up. Does this indicate any clues
?
Alternatively, to solve this meta-deta retention error ,we plan to drop
publication/delete replication and redo the replication setup process for
the database , where this error happens atleast once in a month. Does this
solve this problem. Is there any other solution ?
Please advise
Thanks,
Soura.
What is the exact error message or error number.
I don't understand how users come into the picture. Are you saying these
users are pulling the subscriptions? Are you using Windows Synchronization
Manager.
Dropping a publication and recreating it will definitely clean up merge
replication meta data, but there should be better ways of doing this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:68164873-E280-4B01-B8C8-54502CC2E4A4@.microsoft.com...
> Hi,
> We have about 50 databases all of them are being merge replicated. On a
> average , each database has about 150 tables which are published.
> We get meta data retention error atleast one per week on a average. We
> have
> two category of users, Users at the publisher server when they log (dialup
> or dedicated Network) .Users logging on to the subscriber server -no dial
> up
> always via dedicated network.
> We notice meta-deta retention error message only when users connect to the
> publisher server, that too when people dial up. Does this indicate any
> clues
> ?
> Alternatively, to solve this meta-deta retention error ,we plan to drop
> publication/delete replication and redo the replication setup process for
> the database , where this error happens atleast once in a month. Does this
> solve this problem. Is there any other solution ?
> Please advise
> Thanks,
> Soura.
>
|||Thanks for you response Mr Cotter. We have provided answers for your further
questions.
1. Error message : The merge process could not perform retention-based meta
data cleanup in database 'database name'".
2. No. We are NOT using Windows Synchronization Manager.
3. The users dialing up are not pulling the subscriptions, since they
connect only to the publiser. We notice the above error message once these
users connect after this replication error, we also notice timeout error.
When users log-out and log-in replication works normally as usual and
timeout message also gets solved.
4. You are right, there should be some better ways cleanup of meta deta,
besides droping and recreating. Eager to know what are they, so that we can
solve this problem.
thanks
Soura.
"Hilary Cotter" wrote:

> What is the exact error message or error number.
> I don't understand how users come into the picture. Are you saying these
> users are pulling the subscriptions? Are you using Windows Synchronization
> Manager.
> Dropping a publication and recreating it will definitely clean up merge
> replication meta data, but there should be better ways of doing this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:68164873-E280-4B01-B8C8-54502CC2E4A4@.microsoft.com...
>
>

Meta Data Help Needed[:(]

Hi Guys,

I have a DataBase in which I have several Tables.

What I want is an SP or Query which takes as its parameter the "tablename".

The Output Should be a having three fields only.

Field name, DataType Of the Field, Length of the DataType.

For Example

Suppose the StoredProcedure Name is "SP_GetTables"

if i have a table named "tbl_Users" with fields

UserName varchar(50)

UserPass varchar(20)

UserAge int

UserStatus bit

In my program side if I pass the parameter as "tbl_Users" to the StoredProcedure SP_Users,

I should get the O/P as

Field Name DataType Length

UserName varchar 50

UserPass varchar 20

UserAge int

UserStatus bit

Regards,

Naveen.

Here it is,

Createproc SP_GetTables(@.TableNameSysname)
as
Begin
Select
column_name [field name]
, data_type [datatype]
, character_maximum_length [length]
From
INFORMATION_SCHEMA.COLUMNS
Where
table_name= @.tablename
Orderby
ordinal_position
End

|||

HiManivannna.D.Sekaran,

Thanks a lot!!!

Is there a way by which I can pass the DataBase name too.

My requirement is like this.

I want an SP where i can pass DBName and Table Name as Parameters and that SP finds the DB and the table inside it.

The SP must reside in My DB too.

Regards,

Naveen

|||

Createproc SP_GetTables(@.TableNameSysname, @.databaseSysname)
as
Begin
Exec('Select
column_name [field name]
, data_type [datatype]
, character_maximum_length [length]
From
['+ @.database+'].INFORMATION_SCHEMA.COLUMNS
Where
table_name = '''+ @.tablename+'''
Order by
ordinal_position')
End

message that the tables are read-only

I have an Access 2000 ADP application and MS SQL 7 and when the user opens it on a second computer (and a seperate application) they get the message that the tables are read-only.

When I try it in my office on 2 computers I don't get it.

What should I be looking for?

Michaelthat essentially says that you open the file in exclusive mode. If you have this setting (its the default I believe) then if you can't get it in exclusive mode, it becomes read only.

I might have this wrong in that if a machine grabs the file in exclusive mode it might be locking out the others.

Check the settings on your machine(s), I believe its under Tools-Options-Advanced.

HTH|||I did not think you could open the tables in an MS SQL 7 database exclusively. I know you can when using an .MDB back end.

Are you sure this is the case? I will look at it on my server side.

Message says another user accessing data

My Access database as link SQL Server tables. I am trying to remove records
from a subtable and getting this message:
The Microsoft Jet database engine stopped the process becuase you or another
user are attempting to change the same data at the same time.
This isn't the case, however. No one else is on the Database ( I have a
tool which displays the users) and if they were, I am working with test
records which noone would have an interest in viewing.
This happened on a table before, but cleared up when I shut down the
database and opened it again. Now I can't delete the records.
Any help is appreciated.
God Bless,
Mark A. Sam
Mark A. Sam wrote:
> My Access database as link SQL Server tables. I am trying to remove
> records from a subtable and getting this message:
> The Microsoft Jet database engine stopped the process becuase you or
> another user are attempting to change the same data at the same time.
> This isn't the case, however. No one else is on the Database ( I
> have a tool which displays the users) and if they were, I am working
> with test records which noone would have an interest in viewing.
> This happened on a table before, but cleared up when I shut down the
> database and opened it again. Now I can't delete the records.
> Any help is appreciated.
Add a Timestamp column to the table on the server and make sure any bit fields
do not allow Nulls. Links below explain reasons for this to occur.
http://support.microsoft.com/default...kb;en-us;96897
http://support.microsoft.com/default...b;en-us;280730
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
|||Rick,
The table had a time stamp, but a bit filed I just added was allowing nulls.
When I unchecked and tried to save I got this error:
'Customer ST Products Tasks' table
- Unable to modify table.
ADO error: Cannot insert the value NULL into column 'TestRecord', table
'STSIData.dbo.Tmp_Customer ST Products Tasks'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
I don't know what this means since it was checked to allow nulls.
I am working remotely through terminal services and don't have the use of
Project Manager, so I set up a project in Access 2000 to modify the tables.
That could be the problem, I don't know. I tried removing the field and
reentering it, unchecking the 'AllowNulls' checkbox with the same problem.
God Bless,
Mark
"Rick Brandt" <rickbrandt2@.hotmail.com> wrote in message
news:IdJIe.6467$6D5.722@.newssvr29.news.prodigy.net ...
> Mark A. Sam wrote:
> Add a Timestamp column to the table on the server and make sure any bit
fields
> do not allow Nulls. Links below explain reasons for this to occur.
> http://support.microsoft.com/default...kb;en-us;96897
> http://support.microsoft.com/default...b;en-us;280730
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
|||You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
Value and NO NULL value.
The reason is, several of the records in your table have null values... when
you try to modify the design so that it does not allow nulls, it cant modify
it because there already are nulls in the table in some records.
Try running an update query on that table that converts all NULLS on the
field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
then try to modify your table Again
"Mark A. Sam" wrote:

> Rick,
> The table had a time stamp, but a bit filed I just added was allowing nulls.
> When I unchecked and tried to save I got this error:
> 'Customer ST Products Tasks' table
> - Unable to modify table.
> ADO error: Cannot insert the value NULL into column 'TestRecord', table
> 'STSIData.dbo.Tmp_Customer ST Products Tasks'; column does not allow nulls.
> INSERT fails.
> The statement has been terminated.
>
> I don't know what this means since it was checked to allow nulls.
> I am working remotely through terminal services and don't have the use of
> Project Manager, so I set up a project in Access 2000 to modify the tables.
> That could be the problem, I don't know. I tried removing the field and
> reentering it, unchecking the 'AllowNulls' checkbox with the same problem.
> God Bless,
> Mark
>
> "Rick Brandt" <rickbrandt2@.hotmail.com> wrote in message
> news:IdJIe.6467$6D5.722@.newssvr29.news.prodigy.net ...
> fields
>
>
|||There aren't any Null values. In fact I am able to delete new records, but
there is a batch that I am unable to remove. When I added the Field, the
system entered 0 to the existing records.
"TNB" <TNB@.discussions.microsoft.com> wrote in message
news:509E4A52-DE3B-4B03-9996-2DA7698B7DCB@.microsoft.com...
> You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
> Value and NO NULL value.
> The reason is, several of the records in your table have null values...
when
> you try to modify the design so that it does not allow nulls, it cant
modify[vbcol=seagreen]
> it because there already are nulls in the table in some records.
> Try running an update query on that table that converts all NULLS on the
> field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
> then try to modify your table Again
> "Mark A. Sam" wrote:
nulls.[vbcol=seagreen]
nulls.[vbcol=seagreen]
of[vbcol=seagreen]
tables.[vbcol=seagreen]
problem.[vbcol=seagreen]
time.[vbcol=seagreen]
bit[vbcol=seagreen]
|||Well this clear up for no apparent reason other than prayer. ;)
"TNB" <TNB@.discussions.microsoft.com> wrote in message
news:509E4A52-DE3B-4B03-9996-2DA7698B7DCB@.microsoft.com...
> You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
> Value and NO NULL value.
> The reason is, several of the records in your table have null values...
when
> you try to modify the design so that it does not allow nulls, it cant
modify[vbcol=seagreen]
> it because there already are nulls in the table in some records.
> Try running an update query on that table that converts all NULLS on the
> field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
> then try to modify your table Again
> "Mark A. Sam" wrote:
nulls.[vbcol=seagreen]
nulls.[vbcol=seagreen]
of[vbcol=seagreen]
tables.[vbcol=seagreen]
problem.[vbcol=seagreen]
time.[vbcol=seagreen]
bit[vbcol=seagreen]

Message says another user accessing data

My Access database as link SQL Server tables. I am trying to remove records
from a subtable and getting this message:
The Microsoft Jet database engine stopped the process becuase you or another
user are attempting to change the same data at the same time.
This isn't the case, however. No one else is on the Database ( I have a
tool which displays the users) and if they were, I am working with test
records which noone would have an interest in viewing.
This happened on a table before, but cleared up when I shut down the
database and opened it again. Now I can't delete the records.
Any help is appreciated.
God Bless,
Mark A. SamMark A. Sam wrote:
> My Access database as link SQL Server tables. I am trying to remove
> records from a subtable and getting this message:
> The Microsoft Jet database engine stopped the process becuase you or
> another user are attempting to change the same data at the same time.
> This isn't the case, however. No one else is on the Database ( I
> have a tool which displays the users) and if they were, I am working
> with test records which noone would have an interest in viewing.
> This happened on a table before, but cleared up when I shut down the
> database and opened it again. Now I can't delete the records.
> Any help is appreciated.
Add a Timestamp column to the table on the server and make sure any bit fiel
ds
do not allow Nulls. Links below explain reasons for this to occur.
http://support.microsoft.com/defaul...=kb;en-us;96897
http://support.microsoft.com/defaul...kb;en-us;280730
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com|||Rick,
The table had a time stamp, but a bit filed I just added was allowing nulls.
When I unchecked and tried to save I got this error:
'Customer ST Products Tasks' table
- Unable to modify table.
ADO error: Cannot insert the value NULL into column 'TestRecord', table
'STSIData.dbo.Tmp_Customer ST Products Tasks'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
I don't know what this means since it was checked to allow nulls.
I am working remotely through terminal services and don't have the use of
Project Manager, so I set up a project in Access 2000 to modify the tables.
That could be the problem, I don't know. I tried removing the field and
reentering it, unchecking the 'AllowNulls' checkbox with the same problem.
God Bless,
Mark
"Rick Brandt" <rickbrandt2@.hotmail.com> wrote in message
news:IdJIe.6467$6D5.722@.newssvr29.news.prodigy.net...
> Mark A. Sam wrote:
> Add a Timestamp column to the table on the server and make sure any bit
fields
> do not allow Nulls. Links below explain reasons for this to occur.
> http://support.microsoft.com/defaul...=kb;en-us;96897
> http://support.microsoft.com/defaul...kb;en-us;280730
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>|||You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
Value and NO NULL value.
The reason is, several of the records in your table have null values... when
you try to modify the design so that it does not allow nulls, it cant modify
it because there already are nulls in the table in some records.
Try running an update query on that table that converts all NULLS on the
field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
then try to modify your table Again
"Mark A. Sam" wrote:

> Rick,
> The table had a time stamp, but a bit filed I just added was allowing null
s.
> When I unchecked and tried to save I got this error:
> 'Customer ST Products Tasks' table
> - Unable to modify table.
> ADO error: Cannot insert the value NULL into column 'TestRecord', table
> 'STSIData.dbo.Tmp_Customer ST Products Tasks'; column does not allow nulls
.
> INSERT fails.
> The statement has been terminated.
>
> I don't know what this means since it was checked to allow nulls.
> I am working remotely through terminal services and don't have the use of
> Project Manager, so I set up a project in Access 2000 to modify the tables
.
> That could be the problem, I don't know. I tried removing the field and
> reentering it, unchecking the 'AllowNulls' checkbox with the same problem.
> God Bless,
> Mark
>
> "Rick Brandt" <rickbrandt2@.hotmail.com> wrote in message
> news:IdJIe.6467$6D5.722@.newssvr29.news.prodigy.net...
> fields
>
>|||There aren't any Null values. In fact I am able to delete new records, but
there is a batch that I am unable to remove. When I added the Field, the
system entered 0 to the existing records.
"TNB" <TNB@.discussions.microsoft.com> wrote in message
news:509E4A52-DE3B-4B03-9996-2DA7698B7DCB@.microsoft.com...
> You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
> Value and NO NULL value.
> The reason is, several of the records in your table have null values...
when
> you try to modify the design so that it does not allow nulls, it cant
modify[vbcol=seagreen]
> it because there already are nulls in the table in some records.
> Try running an update query on that table that converts all NULLS on the
> field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
> then try to modify your table Again
> "Mark A. Sam" wrote:
>
nulls.[vbcol=seagreen]
nulls.[vbcol=seagreen]
of[vbcol=seagreen]
tables.[vbcol=seagreen]
problem.[vbcol=seagreen]
time.[vbcol=seagreen]
bit[vbcol=seagreen]|||Well this clear up for no apparent reason other than prayer. ;)
"TNB" <TNB@.discussions.microsoft.com> wrote in message
news:509E4A52-DE3B-4B03-9996-2DA7698B7DCB@.microsoft.com...
> You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
> Value and NO NULL value.
> The reason is, several of the records in your table have null values...
when
> you try to modify the design so that it does not allow nulls, it cant
modify[vbcol=seagreen]
> it because there already are nulls in the table in some records.
> Try running an update query on that table that converts all NULLS on the
> field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
> then try to modify your table Again
> "Mark A. Sam" wrote:
>
nulls.[vbcol=seagreen]
nulls.[vbcol=seagreen]
of[vbcol=seagreen]
tables.[vbcol=seagreen]
problem.[vbcol=seagreen]
time.[vbcol=seagreen]
bit[vbcol=seagreen]

Merging two tables with selection

I would like to have two tables. One I call SystemPropertyTypeTable which
contains the defaults and the other UserPropertyTypeTable. Each has 3
fields. PropertyType, Description, Status.

The idea here is to allow a user to change his/her defaults or to add a new
Property Type without messing with the system default list.

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the status.
The UserPropertyTypeTable all records.

Group by Name and remove any duplicates.
if the UserPropertyTypeTable has INACTIVE then Throw away the Active Record
from the SystemPropertyTypeTable and keep the INACTIVE record.

Here is my code so far.

SELECT T.PropertyType, T.Status
FROM [SELECT PropertyType,Status
FROM SystemPropertyTypeTable Where Status='ACTIVE'
UNION ALL
SELECT PropertyType,Status
FROM UserPropertyTypeTable]. AS T
GROUP BY T.PropertyType, T.Status
HAVING (((Count(*))=1));

here is the resultset
ShowAllRecordsMerged PropertyType Status
APARTMENT ACTIVE
APARTMENT INACTIVE
BUILDING ACTIVE
GARAGE ACTIVE
KOISK ACTIVE
MAINTENANCE SHOP ACTIVE
MAINTENANCE STORAGE AREA ACTIVE
OFFICE ACTIVE
PARKING SPACE ACTIVE
PARKING SPACE INACTIVE
SHOP ACTIVE
STORAGE AREA ACTIVE

So looking at this I would still like to remove any duplicates leaving the
INACTIVE ones which would be the first APARTMENT record and the first
PARKING SPACE record. Also it would be nice to add the description back into
this as well.

Any help anyone can be here would be wonderful.

Thanks in advance.

BruceBruce Stradling (bstradling@.cox.net) writes:

Quote:

Originally Posted by

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the status.
The UserPropertyTypeTable all records.
>
Group by Name and remove any duplicates. if the UserPropertyTypeTable
has INACTIVE then Throw away the Active Record from the
SystemPropertyTypeTable and keep the INACTIVE record.

If I understand this correctly, you want:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTable U
UNION ALL
SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyType S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTable U
WHERE S.Property = U.Property)

--
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|||Thanks ... that was exactly what I needed. Here is the final code:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTypeTable U
UNION ALL SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyTypeTable S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTypeTable U
WHERE S.PropertyType = U.PropertyType)
ORDER BY PropertyType;

And then another that removed all inactive records:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTypeTable U
WHERE U.Status = 'ACTIVE'
UNION ALL SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyTypeTable S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTypeTable U
WHERE S.PropertyType = U.PropertyType)
ORDER BY PropertyType;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns982BE98115752Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Bruce Stradling (bstradling@.cox.net) writes:

Quote:

Originally Posted by

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the

status.

Quote:

Originally Posted by

Quote:

Originally Posted by

The UserPropertyTypeTable all records.

Group by Name and remove any duplicates. if the UserPropertyTypeTable
has INACTIVE then Throw away the Active Record from the
SystemPropertyTypeTable and keep the INACTIVE record.

>
If I understand this correctly, you want:
>
SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTable U
UNION ALL
SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyType S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTable U
WHERE S.Property = U.Property)
>
>
--
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

Merging two tables then grouping them

I have two tables, one is named Employee and the other Job_title. I'm trying to combine the two tables so I can group certain columns.

This is what I thought of so far and please correct me if I'm wrong.

SELECT Last_name FROM Employee
UNION
SELECT Job_title_code FROM Job_title
GROUP BY Exempt_non_exempt
FROM Job_title

Now this is just a theory (obviously it doesn't work) but basiclly I'm trying to have columns from two tables and have the grouped.Huh?

Wha?

There is no relationship between these tables?
No foreign keys?
You are selecting a single column and performing GROUP BY without any aggregate functions?

This makes no sense.|||Well, in both tables, they each have a job_title_code column.

Employee table
lastname, firstname, job title code

Job_title Table
job title code, job title, salary, exempt/non exempt|||Is this homework?

It looks like homework.
It sounds like homework.

Are we studying relational databases?|||So why aren't you using a join?

SELECT Last_name,
Job_title_code,
Exemp_non_exempt
FROM Employee
INNER JOIN Job_title on Employee.Job_title_code = Job_title.Job_title_code

I don't think this is homework. If it was homework his question would be more clearly phrased!|||sorry for the mix up, yes it is homework. that's for suggesting the INNER JOIN command. i found a variation of what you did and it worked out for my db. by the way, how did you put your code in a window like that?

SELECT Employee.Last_Name, Job_title.Exempt_non_exempt_status
FROM Employee
INNER JOIN Job_title
ON Employee.Job_title_code=Job_title.Job_title_code|||Enclose your code in CODE tagsL

[XCode]
Your code here
[X/Code]

Remove the X characters...

Your code here

Merging two tables

SQL 7
How do I merge two table's records? In other words, merge
T1 into T2. There are records in both tables that are the
same, but where T1 has a record that T2 doesn't have,
T1's record needs to be inserted into T2.
I've been reading Join syntax til my head is spinning.
Thanks,
DonInsert into T2
select * from T1 where T1.PrimaryKey
where T1.PrimaryKey NOT IN (Select PrimaryKey from T2)
Replace PrimaryKey with the unique priamry key from each table.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:7ff101c402df$8f68d030$a101280a@.phx.gbl...
> SQL 7
> How do I merge two table's records? In other words, merge
> T1 into T2. There are records in both tables that are the
> same, but where T1 has a record that T2 doesn't have,
> T1's record needs to be inserted into T2.
> I've been reading Join syntax til my head is spinning.
> Thanks,
> Don
>|||Don,

> How do I merge two table's records? In other words, merge
> T1 into T2. There are records in both tables that are the
> same, but where T1 has a record that T2 doesn't have,
> T1's record needs to be inserted into T2.
insert T2
select * from T1
where not exists (select * from T2 where T1.keycol = T2.keycol)
Linda

Merging two sql server DBs

Hi All,
i want to merge two sql server DBs (SourceDB,DestinationDB).
there could be some common tables between SourceDB and DestinationDB.
if that happens than i want sourceDb to overrite Destination DB table.
i know i can do this using exportWizard/DTS.
but problem with this is my source DB is huge and export wizard takes lot of
time.
i do not think i can do this using deattach\attach DB or using backup restor
e because both of this will overrite the detination database where as i want
to merge the two.
this is a one time job.
any ideas how i can do this.
thanks
siddharthHi
Are both databases's tables identical?
If they are you can create a view that contains data set of these tables
CREATE VIEW v_myview
AS
SELECT dbname1.dbo.table
UNION --or UNION ALL
SELECT dbname2.dbo.table
GO
SELECT * FROM v_myview
> if that happens than i want sourceDb to overrite Destination DB table
SELECT * INTO dbname2.dbo.NewTable FROM dbname1.dbo.table
GO
DROP TABLE dbname2.dbo.table
GO
--Run on dbname2
EXEC sp_rename 'NewTable','TABLE'
"siddharth" <anonymous@.discussions.microsoft.com> wrote in message
news:D9C89088-714B-4A6B-8AF8-10E0351EBF8F@.microsoft.com...
> Hi All,
> i want to merge two sql server DBs (SourceDB,DestinationDB).
> there could be some common tables between SourceDB and DestinationDB.
> if that happens than i want sourceDb to overrite Destination DB table.
> i know i can do this using exportWizard/DTS.
> but problem with this is my source DB is huge and export wizard takes lot
of time.
> i do not think i can do this using deattach\attach DB or using backup
restore because both of this will overrite the detination database where as
i want to merge the two.
> this is a one time job.
> any ideas how i can do this.
> thanks
> siddharth
>|||Hi
It sounds like you have a source and destination database the wrong way arou
nd! if you restored what you currently call the source database onto the des
tination server, you would then only need to more from the destination datab
ase the objects that are no
t in the source database! It would be possible to do this from the system ta
bles. DTS may still be an option to transfer the data or alternatively a DMO
program. If you don't have indexes, primary keys etc a straight forward SEL
ECT INTO statement would be
possible.
Alternatively you may want to look at something like the red gate tools to s
ee if they fulfil your requiremets:
http://www.red-gate.com/sql/summary.htm
John
-- siddharth wrote: --
Hi All,
i want to merge two sql server DBs (SourceDB,DestinationDB).
there could be some common tables between SourceDB and DestinationDB.
if that happens than i want sourceDb to overrite Destination DB table.
i know i can do this using exportWizard/DTS.
but problem with this is my source DB is huge and export wizard takes lot of
time.
i do not think i can do this using deattach\attach DB or using backup restor
e because both of this will overrite the detination database where as i want
to merge the two.
this is a one time job.
any ideas how i can do this.
thanks
siddharth

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
RegardsYou can script out all the objects and try running on the 2000 Server|||The easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>