how to create sql database in visual basic

First i am newbie in vb and Sql server...

Is possible to create and attaching, via vb classic or VBA, a sql
database in this instance:\\DVD377-14D9E48C\SQLEXPRESS...
I have Sql Express.

Database name: mydatabase
Database Table: mytable

fileds:
fiedl1 text format
fiedl2 text format
fiedl3 text format
fiedl4 date format
fiedl5 number format
fiedl6 text formatFirst i am newbie in vb and Sql server...

Is possible to create and attaching, via vb classic or VBA, a sql
database in this instance:\\DVD377-14D9E48C\SQLEXPRESS...
I have Sql Express.

Database name: mydatabase
Database Table: mytable

fileds:
fiedl1 text format
fiedl2 text format
fiedl3 text format
fiedl4 date format
fiedl5 number format
fiedl6 text format

You can execute sql statement from your code...its better to read a book on vb - database programming.|||Also it's best not to be creating tables from app code.

If your app logs in as a user that has rights to create tables, then it's logging in as a user with far too many rights - usually only an admin would be allowed to create tables. So this would ONLY be acceptable to me if your app was an administrative app, such as SSMS. :)

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
miso
Miso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>
|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How to create indented, outline formatted report

Anyone thought of a clever way to create a proper outline format - for
example using the results from a recursive hierarchy via the Level function?
The recommended approach seems to be to use the left pad textbox attribute,
setting it to a formula based on the indentation level desired (e.g., =5 +
(Level * 10) & "Pt"). This sort of works, but all it does is make the
starting char move over to the right in a fixed position text box. If the
text is almost as long as the text box, it will wrap - unless you make the
text box (table control column) very wide (or merged with other cols), which
is not feasible if you have other table colums to its right.
The right way to do it would be to shift the position of the text box itself
to the right, but text box posn can not controlled by a formula. This seems
to be true even if the text box is in a list control.
Has anyone figured out a good way to do this?On Oct 12, 7:05 pm, isaksp00 <isaks...@.discussions.microsoft.com>
wrote:
> Anyone thought of a clever way to create a proper outline format - for
> example using the results from a recursive hierarchy via the Level function?
> The recommended approach seems to be to use the left pad textbox attribute,
> setting it to a formula based on the indentation level desired (e.g., =5 +
> (Level * 10) & "Pt"). This sort of works, but all it does is make the
> starting char move over to the right in a fixed position text box. If the
> text is almost as long as the text box, it will wrap - unless you make the
> text box (table control column) very wide (or merged with other cols), which
> is not feasible if you have other table colums to its right.
> The right way to do it would be to shift the position of the text box itself
> to the right, but text box posn can not controlled by a formula. This seems
> to be true even if the text box is in a list control.
> Has anyone figured out a good way to do this?
One way to do it (albeit a lot of work) could be to programmatically
create the RDL file. That way you can control the text box width on
the fly. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the reply. I have all but concluded that what I want to do (which
seems like something that is not at all uncommon) just isn't supported by RS.|||On Oct 13, 8:28 am, isaksp00 <isaks...@.discussions.microsoft.com>
wrote:
> Thanks for the reply. I have all but concluded that what I want to do (which
> seems like something that is not at all uncommon) just isn't supported by RS.
You're welcome. Another small thing that might control the table size
and expansion w/the option you mentioned is to put the table(s) in a
rectangle control. Here's an MSDN link to the option I mentioned
previously.
http://msdn2.microsoft.com/en-us/library/ms170667.aspx
Regards,
Enrique Martinez
Sr. Software Consultant

How to create a Recordset that joins 3 temp tables

I have an stp where I want to return a Recordset via a SELECT that joins 3 temp tables...

Here's what the temp tables look like (I am being brief)...

CREATE TABLE #Employees (EmpID INTEGER, EmpName NVARCHAR(40))

CREATE TABLE #PayPeriods (PayPeriodIndex INTEGER, StartDate DATETIME, EndDate DATETIME)

CREATE TABLE #Statistics (EmpID INTEGER, PayPeriodIndex INTEGER, HoursWorked REAL)

The #Employees table is populated for all employees.

The #PayPeriods table is populated for each calandar week of the year (PayPeriodIndex=0 to 51).

The #Statistics table is populated from data within another permanent table.

Some employees do not work all 52 weeks.

Some employees do not work any of the 52 weeks.

So, the #Statistics table doesn't have all possible combinations of Employees and PayPeriods.

I want to return a Recordset for all possible combinations of Employees and PayPeriods...

Here's a SELECT that I have used...

SELECT e.EmpId, e.Name, pp.PayPeriodIndex, ISNULL(s.HoursWorked,0)

FROM #Statistics s

LEFT OUTER JOIN #Employees e....

LEFT OUTER JOIN #PayPeriods pp ....

WHERE s.EmpId = e.EmpId

AND s.PayPeriodIndex = pp.PayPeriodIndex

I have had no luck with this SELECT.

Can anybody help?

TIA

I think this is what you want

SELECT e.EmpId, e.EmpName, pp.PayPeriodIndex, ISNULL(s.HoursWorked,0)
FROM #Employees e
CROSS JOIN #PayPeriods pp
LEFT OUTER JOIN #Statistics s
ON e.EmpID = s.EmpID
AND s.PayPeriodIndex = pp.PayPeriodIndex

If you want all employess and all pay periods - you should base your query on the employee table and cross join it to the payperiods table - that should give you all employees and all possible pay period combos.

then you should left outer join to the Stats table to get the hours worked if they exist.

Thanks

AWAL

|||

AWAL,

Thanks alot for your response

I tried the Cross Join and Left Outer Join and I am now generasting the correct recordset...

Thanks again!!!

Dan

|||

There is a lot going on with this report. I can definitely see the solution using the CROSS and LEFT joins; however, I ask the following questions:

When I run this with pay periods that are for future dates I get the future pay periods on the report -- is that wanted?|||

Maybe my questions are off-base. Please forgive me for muddying the water here.

Dave

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a Datasource which is not shared

Is it possible to create a datasource, which is not at al shared via vs 2005

If you go to the 'Data' tab in Visual Studio, you can select '<New Dataset...>' from the drop down. Setup your data source, then enter your query.

Setting up your connection on the report (rather than a shared connection in your project), will only affect the current report and no shared data source will be created.

Hope this helps.

Jarret

How to creat a database via SQL Querry Analyzer to a Web Server

I am new to ASP.NET and met a problem about access a database on a Web Server (www.myserver.com). My meaning here that I created a .sql from my localhost to test, but when to upload to my web server on the internet, an error occured: "System.Data.SqlClient.SqlException: SQL Server does not exist or access denied".
Anyone here can help me to solve my problem. Waiting for your reply soon. Thanksyour SQL Connection on your local host and your Server aren't the same. your should check your connectionstring, eypecially the servername.|||First of all thank to MISIU...
Your idea i had to try first! Just here, I change my line of code from <Add key> to a connection string to myserver for instance hereby. But nothing looked good and still got the above problem. Could you pls show me the detail of creat a database and publish it to my Web Server. And i am waiting for anyone give me the BEST. God bless all of you!

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***
"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>
|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***
|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.developersdex.com ***"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > We got a production database that gets
> >
> > tables added to it daily.
> > So, every single day we transfer those new
> >
> > tables via DTS to five (5) other servers!
> >
> > Can we accomplish the same thing using a
> >
> > 'Select' statement?
> > I found no way to use the 'select' stmt
> >
> > across servers.
> >
> > I'm not conversant with TSQL (yet!) and
> >
> > would very much appreciate any help you can
> >
> > give me.
> >
> > Thank you very much
> >
> > mike
> >
> >
> > Mike
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:
> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
> > "mike" <-nospam@.yahoo.com> wrote in message
> > news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > We got a production database that gets
> > >
> > > tables added to it daily.
> > > So, every single day we transfer those new
> > >
> > > tables via DTS to five (5) other servers!
> > >
> > > Can we accomplish the same thing using a
> > >
> > > 'Select' statement?
> > > I found no way to use the 'select' stmt
> > >
> > > across servers.
> > >
> > > I'm not conversant with TSQL (yet!) and
> > >
> > > would very much appreciate any help you can
> > >
> > > give me.
> > >
> > > Thank you very much
> > >
> > > mike
> > >
> > >
> > > Mike
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> >
> >
> > Did I understand you correctly? Your database schema changes DAILY? I know
> > nothing of your application obviously but new tables daily sounds like you
> > have some major issues that ought to be better fixed by a different
> > architecture or a better change control process.
> >
> > The best way to replicate schema changes is to script them and then apply
> > the scripts. Or invest in a tool like RedGate SQL Compare
> > (http://www.red-gate.com/). Don't you do that anyway in order to test the
> > changes?
> >
> > --
> > David Portas, SQL Server MVP
> >
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
> >
> > SQL Server Books Online:
> > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> > --
>|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
--
Adam J Warne, MCDBA
"mike" wrote:
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
>