Hi,
I was wondering if anyone has a neat (preferably automated) method of creating small testing databases from large production instances.
My requirement would be to copy the schema and a subset of configuration data from a production database into a test database. The subset of data would be a full copy of a subset of tables, rather than a subset of data within one or more tables. There is a mixture of SQL2000 and SQL2005 servers involved in this requirement. I'm familar with the scripting mechanisms of Enterprise Manager and Management studio and DTS packages, sufficent to perform a process like this manually, but want to productionise and schedule this process to be performed automatically.
I'm sure this must be a commonly performed task, so I'm interested to know if anyone has a "best practice" for this requirement.
Thanks,
BillI've always used backup/restore for this. of course that doesn't give you a subset of anything.
for me the issue with taking a subset would be: if in test you don't have all the data that prod is working with, how do you really know your stuff will work in prod?|||I've always used backup/restore for this. of course that doesn't give you a subset of anything.
for me the issue with taking a subset would be: if in test you don't have all the data that prod is working with, how do you really know your stuff will work in prod?
Thanks Jezemine, The problem with backup restore, is that my production database is 3Tb+. However nearly all of this is historic transactional data. A small subset (1 or 2 Gb) is the configuration data that dictates how any transactions I post into the database will process. So it is this data (which is as important as source code wrt to the behaviour of the system) that I want to replicate to any test environment.|||This won't help much at this point, but I frequently set up file groups in Microsoft SQL databases for just this purpose. The filegroups can be backed up individually, so it is easy to "pick and choose" at the table level what data you want.
If you are doing this "after the fact" where you already have a 3Tb database, my first thought would be to script the schema of the whole tamale (all tables) and play that script into an empty database container. Then I'd pick one of (linked server, DTS, BCP, copy wizard) to move the data from the production database to the container with only the schema in it.
-PatPsql
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Creating stored procs with MSforeachdb
I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.
Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan
|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.
|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.
|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve
|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.
|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve
|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.
|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan
|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.
Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan
|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.
|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.
|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve
|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.
|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve
|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.
|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan
|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve
Creating stored procs with MSforeachdb
I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - (
1;Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - (
1;Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve
Creating stored procs with MSforeachdb
I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 = 'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 = 'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve
Monday, March 19, 2012
Creating SQL Server Databases With No Security Restrictions
First, let me apologize for crossposting and the lengthy post, but this
question involved both MSDE and (I believe) security settings.
We use Visual Studio .NET 2003 with MSDE in classrooms and labs and have
had no security issues once things were configured properly for student
access. The sample databases install and work correctly for any authorized
users who might be logged on to a given PC.
We are using a text that supplies .mdf files as part of the support
materials for the book. We are able to copy the .mdf files to the
appropriate directory (..\Microsoft SQL Server\MSSQL$InstanceName\Data),
then use Server Explorer in VS .NET to create a database link to the file
(Connect To Database button on the Server Explorer toolbar). Once VS .NET is
restarted, the database appears in the list of databases under the SQL
Server instance and is fully accessible.
I now want to create additional SQL Server databases for students to use
with development projects, etc. I have access to the SQL Server GUI
management tools (from our MSDNAA subscription) and they are installed and
working with the MSDE instance on my office PC. When I create a
database/tables/etc. on my instance, then attempt to copy the .mdf to
another PC to use the setup technique described above, a window pops up
wanting me to select a security option and then refuses me access to the new
database regardless of the option (NT Security or database
username/password) I try to choose.
After all that, what I'm seeking is the way to create a database to export
to any PC, with any student or faculty member logged on so that they can
import the .mdf file and connect to it using Server Explorer *without*
encountering security issues. So, when I'm creating a database, what
settings do I apply so that it doesn't care who is interacting with it?
Thanks in advance for reading and any insights!
hi Peter,
Peter van der Goes wrote:
> First, let me apologize for crossposting and the lengthy post, but
> this question involved both MSDE and (I believe) security settings.
> We use Visual Studio .NET 2003 with MSDE in classrooms and labs and
> have had no security issues once things were configured properly for
> student access. The sample databases install and work correctly for
> any authorized users who might be logged on to a given PC.
> We are using a text that supplies .mdf files as part of the support
> materials for the book. We are able to copy the .mdf files to the
> appropriate directory (..\Microsoft SQL
> Server\MSSQL$InstanceName\Data), then use Server Explorer in VS .NET
> to create a database link to the file (Connect To Database button on
> the Server Explorer toolbar). Once VS .NET is restarted, the database
> appears in the list of databases under the SQL Server instance and is
> fully accessible.
> I now want to create additional SQL Server databases for students to
> use with development projects, etc. I have access to the SQL Server
> GUI management tools (from our MSDNAA subscription) and they are
> installed and working with the MSDE instance on my office PC. When I
> create a database/tables/etc. on my instance, then attempt to copy
> the .mdf to another PC to use the setup technique described above, a
> window pops up wanting me to select a security option and then
> refuses me access to the new database regardless of the option (NT
> Security or database username/password) I try to choose.
> After all that, what I'm seeking is the way to create a database to
> export to any PC, with any student or faculty member logged on so
> that they can import the .mdf file and connect to it using Server
> Explorer *without* encountering security issues. So, when I'm
> creating a database, what settings do I apply so that it doesn't care
> who is interacting with it?
> Thanks in advance for reading and any insights!
in order to attach an existing db file, as long as create a brand new
database, the logged user must be member of the sysadmin and/or dbcreator
server role...
in order to connect to an MSDE instance you have to provide a login
information, both for Windows NT (trusted) authenticated or SQL Server
authenticated connections, as you provide this kind of information in the
Data Link dialog to (Connect To Database button on the Server Explorer
toolbar)... so there's no difference in the behaviour you are
experimenting..
what kind of error do you get when accessing the attached database?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:36sc8vF556sgkU1@.individual.net...
> hi Peter,
> in order to attach an existing db file, as long as create a brand new
> database, the logged user must be member of the sysadmin and/or dbcreator
> server role...
> in order to connect to an MSDE instance you have to provide a login
> information, both for Windows NT (trusted) authenticated or SQL Server
> authenticated connections, as you provide this kind of information in the
> Data Link dialog to (Connect To Database button on the Server Explorer
> toolbar)... so there's no difference in the behaviour you are
> experimenting..
> what kind of error do you get when accessing the attached database?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Thanks for responding, Andrea.
The issue is not permissions assigned to a given user (all users must log on
to any campus PC, thus providing username/password information), but the
difference between a .mdf file I create vs. those supplied by the publisher
of the textbook.
When we work with the .mdf files supplied by the publisher, there is no
issue with requested authentication information. Any user who can log onto a
campus PC can copy the .mdf file to the correct location on that PC, follow
the procedures I outlined in my original post using Visual Studio .NET 2003
Server Explorer and "create" an accessible database using the file in
question, then access it and its objects in Server Explorer at will. Some
setting in that .mdf file is allowing this behavior for any user. This
database can be "opened" by anybody who logs onto that PC and uses Server
Explorer.
When I create a database (.mdf) file using SQL Server Enterprise Manager and
try to "create" a database from it on a different PC, the process appears to
work. When I subsequently attempt to open that database in Server Explorer
(by clicking on the + next to the newly-created database name), I get a
dialog box insisting that I supply either NT Security information or, select
SQL Server access and provide a username/password for the database. Any
combination of username/password information I supply at that stage is
rejected by the dialog. When I open a database supplied by the publisher, no
such dialog appears and I have full access. My goal is to create databases
which behave like those supplied by the publisher.
As the security issue does not occur when using the .mdf files provided by
the publisher, I can only assume that these files were from databases set up
as accessible to all. As I'm not smart on SQL Server administration
(everything I "know" comes from SQL Server 2000 Programming, Step by Step
from MS Press), I'm hoping that somebody will guide me to the correct
settings for a SQL Server database so it's openly accessible to any user who
logs onto a PC where the database resides without triggering any
authentication dialogs. For what it's worth, I can replicate all the
behaviors above on my home PC which just uses a simple default logon.
I can send you (or, anybody else) one of the publisher's .mdf files if it
would help identify the settings in question.
|||On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> When I create a database (.mdf) file using SQL Server Enterprise Manager and
> try to "create" a database from it on a different PC, the process appears to
> work. When I subsequently attempt to open that database in Server Explorer
> (by clicking on the + next to the newly-created database name), I get a
> dialog box insisting that I supply either NT Security information or, select
> SQL Server access and provide a username/password for the database. Any
> combination of username/password information I supply at that stage is
> rejected by the dialog. When I open a database supplied by the publisher, no
> such dialog appears and I have full access. My goal is to create databases
> which behave like those supplied by the publisher.
Perhaps this would do the trick:
EXECUTE sp_grantdbaccess guest
EXECUTE sp_addrolemember 'db_owner',guest
Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
execute it from Query Analyzer.
|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:uauu8880rpzp$.dlg@.rpresser.invalid...
> On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> Perhaps this would do the trick:
> EXECUTE sp_grantdbaccess guest
> EXECUTE sp_addrolemember 'db_owner',guest
> Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
> execute it from Query Analyzer.
Unfortunately, the commands didn't alter the situation. However, through
additional experimentation, I've discovered the following:
The database itself is OK, as I can access it in Enterprise Manager. In
Server Explorer, the database appears, but its State property says
"Disconnected" and I get the SQL Server authentication dialog when Server
Explorer attempts to connect. I've checked the database file's network
security properties, and they appear to be set the same as those for the
database files (from the book) that I am able to access in Server Explorer.
Public has full access. I'm beginning to think I'm dealing with a NTFS file
or folder access issue, as the same database is fully accessible through
Server Explorer on a PC with a FAT32 partition?!?
question involved both MSDE and (I believe) security settings.
We use Visual Studio .NET 2003 with MSDE in classrooms and labs and have
had no security issues once things were configured properly for student
access. The sample databases install and work correctly for any authorized
users who might be logged on to a given PC.
We are using a text that supplies .mdf files as part of the support
materials for the book. We are able to copy the .mdf files to the
appropriate directory (..\Microsoft SQL Server\MSSQL$InstanceName\Data),
then use Server Explorer in VS .NET to create a database link to the file
(Connect To Database button on the Server Explorer toolbar). Once VS .NET is
restarted, the database appears in the list of databases under the SQL
Server instance and is fully accessible.
I now want to create additional SQL Server databases for students to use
with development projects, etc. I have access to the SQL Server GUI
management tools (from our MSDNAA subscription) and they are installed and
working with the MSDE instance on my office PC. When I create a
database/tables/etc. on my instance, then attempt to copy the .mdf to
another PC to use the setup technique described above, a window pops up
wanting me to select a security option and then refuses me access to the new
database regardless of the option (NT Security or database
username/password) I try to choose.
After all that, what I'm seeking is the way to create a database to export
to any PC, with any student or faculty member logged on so that they can
import the .mdf file and connect to it using Server Explorer *without*
encountering security issues. So, when I'm creating a database, what
settings do I apply so that it doesn't care who is interacting with it?
Thanks in advance for reading and any insights!
hi Peter,
Peter van der Goes wrote:
> First, let me apologize for crossposting and the lengthy post, but
> this question involved both MSDE and (I believe) security settings.
> We use Visual Studio .NET 2003 with MSDE in classrooms and labs and
> have had no security issues once things were configured properly for
> student access. The sample databases install and work correctly for
> any authorized users who might be logged on to a given PC.
> We are using a text that supplies .mdf files as part of the support
> materials for the book. We are able to copy the .mdf files to the
> appropriate directory (..\Microsoft SQL
> Server\MSSQL$InstanceName\Data), then use Server Explorer in VS .NET
> to create a database link to the file (Connect To Database button on
> the Server Explorer toolbar). Once VS .NET is restarted, the database
> appears in the list of databases under the SQL Server instance and is
> fully accessible.
> I now want to create additional SQL Server databases for students to
> use with development projects, etc. I have access to the SQL Server
> GUI management tools (from our MSDNAA subscription) and they are
> installed and working with the MSDE instance on my office PC. When I
> create a database/tables/etc. on my instance, then attempt to copy
> the .mdf to another PC to use the setup technique described above, a
> window pops up wanting me to select a security option and then
> refuses me access to the new database regardless of the option (NT
> Security or database username/password) I try to choose.
> After all that, what I'm seeking is the way to create a database to
> export to any PC, with any student or faculty member logged on so
> that they can import the .mdf file and connect to it using Server
> Explorer *without* encountering security issues. So, when I'm
> creating a database, what settings do I apply so that it doesn't care
> who is interacting with it?
> Thanks in advance for reading and any insights!
in order to attach an existing db file, as long as create a brand new
database, the logged user must be member of the sysadmin and/or dbcreator
server role...
in order to connect to an MSDE instance you have to provide a login
information, both for Windows NT (trusted) authenticated or SQL Server
authenticated connections, as you provide this kind of information in the
Data Link dialog to (Connect To Database button on the Server Explorer
toolbar)... so there's no difference in the behaviour you are
experimenting..
what kind of error do you get when accessing the attached database?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:36sc8vF556sgkU1@.individual.net...
> hi Peter,
> in order to attach an existing db file, as long as create a brand new
> database, the logged user must be member of the sysadmin and/or dbcreator
> server role...
> in order to connect to an MSDE instance you have to provide a login
> information, both for Windows NT (trusted) authenticated or SQL Server
> authenticated connections, as you provide this kind of information in the
> Data Link dialog to (Connect To Database button on the Server Explorer
> toolbar)... so there's no difference in the behaviour you are
> experimenting..
> what kind of error do you get when accessing the attached database?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Thanks for responding, Andrea.
The issue is not permissions assigned to a given user (all users must log on
to any campus PC, thus providing username/password information), but the
difference between a .mdf file I create vs. those supplied by the publisher
of the textbook.
When we work with the .mdf files supplied by the publisher, there is no
issue with requested authentication information. Any user who can log onto a
campus PC can copy the .mdf file to the correct location on that PC, follow
the procedures I outlined in my original post using Visual Studio .NET 2003
Server Explorer and "create" an accessible database using the file in
question, then access it and its objects in Server Explorer at will. Some
setting in that .mdf file is allowing this behavior for any user. This
database can be "opened" by anybody who logs onto that PC and uses Server
Explorer.
When I create a database (.mdf) file using SQL Server Enterprise Manager and
try to "create" a database from it on a different PC, the process appears to
work. When I subsequently attempt to open that database in Server Explorer
(by clicking on the + next to the newly-created database name), I get a
dialog box insisting that I supply either NT Security information or, select
SQL Server access and provide a username/password for the database. Any
combination of username/password information I supply at that stage is
rejected by the dialog. When I open a database supplied by the publisher, no
such dialog appears and I have full access. My goal is to create databases
which behave like those supplied by the publisher.
As the security issue does not occur when using the .mdf files provided by
the publisher, I can only assume that these files were from databases set up
as accessible to all. As I'm not smart on SQL Server administration
(everything I "know" comes from SQL Server 2000 Programming, Step by Step
from MS Press), I'm hoping that somebody will guide me to the correct
settings for a SQL Server database so it's openly accessible to any user who
logs onto a PC where the database resides without triggering any
authentication dialogs. For what it's worth, I can replicate all the
behaviors above on my home PC which just uses a simple default logon.
I can send you (or, anybody else) one of the publisher's .mdf files if it
would help identify the settings in question.
|||On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> When I create a database (.mdf) file using SQL Server Enterprise Manager and
> try to "create" a database from it on a different PC, the process appears to
> work. When I subsequently attempt to open that database in Server Explorer
> (by clicking on the + next to the newly-created database name), I get a
> dialog box insisting that I supply either NT Security information or, select
> SQL Server access and provide a username/password for the database. Any
> combination of username/password information I supply at that stage is
> rejected by the dialog. When I open a database supplied by the publisher, no
> such dialog appears and I have full access. My goal is to create databases
> which behave like those supplied by the publisher.
Perhaps this would do the trick:
EXECUTE sp_grantdbaccess guest
EXECUTE sp_addrolemember 'db_owner',guest
Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
execute it from Query Analyzer.
|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:uauu8880rpzp$.dlg@.rpresser.invalid...
> On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> Perhaps this would do the trick:
> EXECUTE sp_grantdbaccess guest
> EXECUTE sp_addrolemember 'db_owner',guest
> Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
> execute it from Query Analyzer.
Unfortunately, the commands didn't alter the situation. However, through
additional experimentation, I've discovered the following:
The database itself is OK, as I can access it in Enterprise Manager. In
Server Explorer, the database appears, but its State property says
"Disconnected" and I get the SQL Server authentication dialog when Server
Explorer attempts to connect. I've checked the database file's network
security properties, and they appear to be set the same as those for the
database files (from the book) that I am able to access in Server Explorer.
Public has full access. I'm beginning to think I'm dealing with a NTFS file
or folder access issue, as the same database is fully accessible through
Server Explorer on a PC with a FAT32 partition?!?
Creating SQL Server Databases With No Security Restrictions
First, let me apologize for crossposting and the lengthy post, but this
question involved both MSDE and (I believe) security settings.
We use Visual Studio .NET 2003 with MSDE in classrooms and labs and have
had no security issues once things were configured properly for student
access. The sample databases install and work correctly for any authorized
users who might be logged on to a given PC.
We are using a text that supplies .mdf files as part of the support
materials for the book. We are able to copy the .mdf files to the
appropriate directory (..\Microsoft SQL Server\MSSQL$InstanceName\Data),
then use Server Explorer in VS .NET to create a database link to the file
(Connect To Database button on the Server Explorer toolbar). Once VS .NET is
restarted, the database appears in the list of databases under the SQL
Server instance and is fully accessible.
I now want to create additional SQL Server databases for students to use
with development projects, etc. I have access to the SQL Server GUI
management tools (from our MSDNAA subscription) and they are installed and
working with the MSDE instance on my office PC. When I create a
database/tables/etc. on my instance, then attempt to copy the .mdf to
another PC to use the setup technique described above, a window pops up
wanting me to select a security option and then refuses me access to the new
database regardless of the option (NT Security or database
username/password) I try to choose.
After all that, what I'm seeking is the way to create a database to export
to any PC, with any student or faculty member logged on so that they can
import the .mdf file and connect to it using Server Explorer *without*
encountering security issues. So, when I'm creating a database, what
settings do I apply so that it doesn't care who is interacting with it?
Thanks in advance for reading and any insights!hi Peter,
Peter van der Goes wrote:
> First, let me apologize for crossposting and the lengthy post, but
> this question involved both MSDE and (I believe) security settings.
> We use Visual Studio .NET 2003 with MSDE in classrooms and labs and
> have had no security issues once things were configured properly for
> student access. The sample databases install and work correctly for
> any authorized users who might be logged on to a given PC.
> We are using a text that supplies .mdf files as part of the support
> materials for the book. We are able to copy the .mdf files to the
> appropriate directory (..\Microsoft SQL
> Server\MSSQL$InstanceName\Data), then use Server Explorer in VS .NET
> to create a database link to the file (Connect To Database button on
> the Server Explorer toolbar). Once VS .NET is restarted, the database
> appears in the list of databases under the SQL Server instance and is
> fully accessible.
> I now want to create additional SQL Server databases for students to
> use with development projects, etc. I have access to the SQL Server
> GUI management tools (from our MSDNAA subscription) and they are
> installed and working with the MSDE instance on my office PC. When I
> create a database/tables/etc. on my instance, then attempt to copy
> the .mdf to another PC to use the setup technique described above, a
> window pops up wanting me to select a security option and then
> refuses me access to the new database regardless of the option (NT
> Security or database username/password) I try to choose.
> After all that, what I'm seeking is the way to create a database to
> export to any PC, with any student or faculty member logged on so
> that they can import the .mdf file and connect to it using Server
> Explorer *without* encountering security issues. So, when I'm
> creating a database, what settings do I apply so that it doesn't care
> who is interacting with it?
> Thanks in advance for reading and any insights!
in order to attach an existing db file, as long as create a brand new
database, the logged user must be member of the sysadmin and/or dbcreator
server role...
in order to connect to an MSDE instance you have to provide a login
information, both for Windows NT (trusted) authenticated or SQL Server
authenticated connections, as you provide this kind of information in the
Data Link dialog to (Connect To Database button on the Server Explorer
toolbar)... so there's no difference in the behaviour you are
experimenting..
what kind of error do you get when accessing the attached database?
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:36sc8vF556sgkU1@.individual.net...
> hi Peter,
> in order to attach an existing db file, as long as create a brand new
> database, the logged user must be member of the sysadmin and/or dbcreator
> server role...
> in order to connect to an MSDE instance you have to provide a login
> information, both for Windows NT (trusted) authenticated or SQL Server
> authenticated connections, as you provide this kind of information in the
> Data Link dialog to (Connect To Database button on the Server Explorer
> toolbar)... so there's no difference in the behaviour you are
> experimenting..
> what kind of error do you get when accessing the attached database?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Thanks for responding, Andrea.
The issue is not permissions assigned to a given user (all users must log on
to any campus PC, thus providing username/password information), but the
difference between a .mdf file I create vs. those supplied by the publisher
of the textbook.
When we work with the .mdf files supplied by the publisher, there is no
issue with requested authentication information. Any user who can log onto a
campus PC can copy the .mdf file to the correct location on that PC, follow
the procedures I outlined in my original post using Visual Studio .NET 2003
Server Explorer and "create" an accessible database using the file in
question, then access it and its objects in Server Explorer at will. Some
setting in that .mdf file is allowing this behavior for any user. This
database can be "opened" by anybody who logs onto that PC and uses Server
Explorer.
When I create a database (.mdf) file using SQL Server Enterprise Manager and
try to "create" a database from it on a different PC, the process appears to
work. When I subsequently attempt to open that database in Server Explorer
(by clicking on the + next to the newly-created database name), I get a
dialog box insisting that I supply either NT Security information or, select
SQL Server access and provide a username/password for the database. Any
combination of username/password information I supply at that stage is
rejected by the dialog. When I open a database supplied by the publisher, no
such dialog appears and I have full access. My goal is to create databases
which behave like those supplied by the publisher.
As the security issue does not occur when using the .mdf files provided by
the publisher, I can only assume that these files were from databases set up
as accessible to all. As I'm not smart on SQL Server administration
(everything I "know" comes from SQL Server 2000 Programming, Step by Step
from MS Press), I'm hoping that somebody will guide me to the correct
settings for a SQL Server database so it's openly accessible to any user who
logs onto a PC where the database resides without triggering any
authentication dialogs. For what it's worth, I can replicate all the
behaviors above on my home PC which just uses a simple default logon.
I can send you (or, anybody else) one of the publisher's .mdf files if it
would help identify the settings in question.|||On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> When I create a database (.mdf) file using SQL Server Enterprise Manager a
nd
> try to "create" a database from it on a different PC, the process appears
to
> work. When I subsequently attempt to open that database in Server Explorer
> (by clicking on the + next to the newly-created database name), I get a
> dialog box insisting that I supply either NT Security information or, sele
ct
> SQL Server access and provide a username/password for the database. Any
> combination of username/password information I supply at that stage is
> rejected by the dialog. When I open a database supplied by the publisher,
no
> such dialog appears and I have full access. My goal is to create databases
> which behave like those supplied by the publisher.
Perhaps this would do the trick:
EXECUTE sp_grantdbaccess guest
EXECUTE sp_addrolemember 'db_owner',guest
Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
execute it from Query Analyzer.|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:uauu8880rpzp$.dlg@.rpresser.invalid...
> On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> Perhaps this would do the trick:
> EXECUTE sp_grantdbaccess guest
> EXECUTE sp_addrolemember 'db_owner',guest
> Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
> execute it from Query Analyzer.
Unfortunately, the commands didn't alter the situation. However, through
additional experimentation, I've discovered the following:
The database itself is OK, as I can access it in Enterprise Manager. In
Server Explorer, the database appears, but its State property says
"Disconnected" and I get the SQL Server authentication dialog when Server
Explorer attempts to connect. I've checked the database file's network
security properties, and they appear to be set the same as those for the
database files (from the book) that I am able to access in Server Explorer.
Public has full access. I'm beginning to think I'm dealing with a NTFS file
or folder access issue, as the same database is fully accessible through
Server Explorer on a PC with a FAT32 partition?!?
question involved both MSDE and (I believe) security settings.
We use Visual Studio .NET 2003 with MSDE in classrooms and labs and have
had no security issues once things were configured properly for student
access. The sample databases install and work correctly for any authorized
users who might be logged on to a given PC.
We are using a text that supplies .mdf files as part of the support
materials for the book. We are able to copy the .mdf files to the
appropriate directory (..\Microsoft SQL Server\MSSQL$InstanceName\Data),
then use Server Explorer in VS .NET to create a database link to the file
(Connect To Database button on the Server Explorer toolbar). Once VS .NET is
restarted, the database appears in the list of databases under the SQL
Server instance and is fully accessible.
I now want to create additional SQL Server databases for students to use
with development projects, etc. I have access to the SQL Server GUI
management tools (from our MSDNAA subscription) and they are installed and
working with the MSDE instance on my office PC. When I create a
database/tables/etc. on my instance, then attempt to copy the .mdf to
another PC to use the setup technique described above, a window pops up
wanting me to select a security option and then refuses me access to the new
database regardless of the option (NT Security or database
username/password) I try to choose.
After all that, what I'm seeking is the way to create a database to export
to any PC, with any student or faculty member logged on so that they can
import the .mdf file and connect to it using Server Explorer *without*
encountering security issues. So, when I'm creating a database, what
settings do I apply so that it doesn't care who is interacting with it?
Thanks in advance for reading and any insights!hi Peter,
Peter van der Goes wrote:
> First, let me apologize for crossposting and the lengthy post, but
> this question involved both MSDE and (I believe) security settings.
> We use Visual Studio .NET 2003 with MSDE in classrooms and labs and
> have had no security issues once things were configured properly for
> student access. The sample databases install and work correctly for
> any authorized users who might be logged on to a given PC.
> We are using a text that supplies .mdf files as part of the support
> materials for the book. We are able to copy the .mdf files to the
> appropriate directory (..\Microsoft SQL
> Server\MSSQL$InstanceName\Data), then use Server Explorer in VS .NET
> to create a database link to the file (Connect To Database button on
> the Server Explorer toolbar). Once VS .NET is restarted, the database
> appears in the list of databases under the SQL Server instance and is
> fully accessible.
> I now want to create additional SQL Server databases for students to
> use with development projects, etc. I have access to the SQL Server
> GUI management tools (from our MSDNAA subscription) and they are
> installed and working with the MSDE instance on my office PC. When I
> create a database/tables/etc. on my instance, then attempt to copy
> the .mdf to another PC to use the setup technique described above, a
> window pops up wanting me to select a security option and then
> refuses me access to the new database regardless of the option (NT
> Security or database username/password) I try to choose.
> After all that, what I'm seeking is the way to create a database to
> export to any PC, with any student or faculty member logged on so
> that they can import the .mdf file and connect to it using Server
> Explorer *without* encountering security issues. So, when I'm
> creating a database, what settings do I apply so that it doesn't care
> who is interacting with it?
> Thanks in advance for reading and any insights!
in order to attach an existing db file, as long as create a brand new
database, the logged user must be member of the sysadmin and/or dbcreator
server role...
in order to connect to an MSDE instance you have to provide a login
information, both for Windows NT (trusted) authenticated or SQL Server
authenticated connections, as you provide this kind of information in the
Data Link dialog to (Connect To Database button on the Server Explorer
toolbar)... so there's no difference in the behaviour you are
experimenting..
what kind of error do you get when accessing the attached database?
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:36sc8vF556sgkU1@.individual.net...
> hi Peter,
> in order to attach an existing db file, as long as create a brand new
> database, the logged user must be member of the sysadmin and/or dbcreator
> server role...
> in order to connect to an MSDE instance you have to provide a login
> information, both for Windows NT (trusted) authenticated or SQL Server
> authenticated connections, as you provide this kind of information in the
> Data Link dialog to (Connect To Database button on the Server Explorer
> toolbar)... so there's no difference in the behaviour you are
> experimenting..
> what kind of error do you get when accessing the attached database?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Thanks for responding, Andrea.
The issue is not permissions assigned to a given user (all users must log on
to any campus PC, thus providing username/password information), but the
difference between a .mdf file I create vs. those supplied by the publisher
of the textbook.
When we work with the .mdf files supplied by the publisher, there is no
issue with requested authentication information. Any user who can log onto a
campus PC can copy the .mdf file to the correct location on that PC, follow
the procedures I outlined in my original post using Visual Studio .NET 2003
Server Explorer and "create" an accessible database using the file in
question, then access it and its objects in Server Explorer at will. Some
setting in that .mdf file is allowing this behavior for any user. This
database can be "opened" by anybody who logs onto that PC and uses Server
Explorer.
When I create a database (.mdf) file using SQL Server Enterprise Manager and
try to "create" a database from it on a different PC, the process appears to
work. When I subsequently attempt to open that database in Server Explorer
(by clicking on the + next to the newly-created database name), I get a
dialog box insisting that I supply either NT Security information or, select
SQL Server access and provide a username/password for the database. Any
combination of username/password information I supply at that stage is
rejected by the dialog. When I open a database supplied by the publisher, no
such dialog appears and I have full access. My goal is to create databases
which behave like those supplied by the publisher.
As the security issue does not occur when using the .mdf files provided by
the publisher, I can only assume that these files were from databases set up
as accessible to all. As I'm not smart on SQL Server administration
(everything I "know" comes from SQL Server 2000 Programming, Step by Step
from MS Press), I'm hoping that somebody will guide me to the correct
settings for a SQL Server database so it's openly accessible to any user who
logs onto a PC where the database resides without triggering any
authentication dialogs. For what it's worth, I can replicate all the
behaviors above on my home PC which just uses a simple default logon.
I can send you (or, anybody else) one of the publisher's .mdf files if it
would help identify the settings in question.|||On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> When I create a database (.mdf) file using SQL Server Enterprise Manager a
nd
> try to "create" a database from it on a different PC, the process appears
to
> work. When I subsequently attempt to open that database in Server Explorer
> (by clicking on the + next to the newly-created database name), I get a
> dialog box insisting that I supply either NT Security information or, sele
ct
> SQL Server access and provide a username/password for the database. Any
> combination of username/password information I supply at that stage is
> rejected by the dialog. When I open a database supplied by the publisher,
no
> such dialog appears and I have full access. My goal is to create databases
> which behave like those supplied by the publisher.
Perhaps this would do the trick:
EXECUTE sp_grantdbaccess guest
EXECUTE sp_addrolemember 'db_owner',guest
Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
execute it from Query Analyzer.|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:uauu8880rpzp$.dlg@.rpresser.invalid...
> On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> Perhaps this would do the trick:
> EXECUTE sp_grantdbaccess guest
> EXECUTE sp_addrolemember 'db_owner',guest
> Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
> execute it from Query Analyzer.
Unfortunately, the commands didn't alter the situation. However, through
additional experimentation, I've discovered the following:
The database itself is OK, as I can access it in Enterprise Manager. In
Server Explorer, the database appears, but its State property says
"Disconnected" and I get the SQL Server authentication dialog when Server
Explorer attempts to connect. I've checked the database file's network
security properties, and they appear to be set the same as those for the
database files (from the book) that I am able to access in Server Explorer.
Public has full access. I'm beginning to think I'm dealing with a NTFS file
or folder access issue, as the same database is fully accessible through
Server Explorer on a PC with a FAT32 partition?!?
Creating Sql Server 2005 Express Databases via VS2005 Std.
Hello all,
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it.
Any help is appreciated and thanks in advance,
I'd be interested in knowing how VS doesn't allow you to create a database. What error are you getting?
There are two ways to create databases, depending on your goal:
To just create a database on your server, do the following:Creating Sql Server 2005 Express Databases via VS2005 Std.
Hello all,
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it.
Any help is appreciated and thanks in advance,
I'd be interested in knowing how VS doesn't allow you to create a database. What error are you getting?
There are two ways to create databases, depending on your goal:
To just create a database on your server, do the following:
Sunday, March 11, 2012
Creating seperate databases for acquired companies - best practice
Hi All,
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Creating seperate databases for acquired companies - best practice
Hi All,
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Creating seperate databases for acquired companies - best practice
Hi All,
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.
nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.
nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Creating reports: Where to start ?
I'm not familiar with reporting and sql databases, but would like to create some simple (trend) reports for our work item tracking system.
I browsed to the MSDN help, saw some video's, took a Virtual Lab on Report Builder, spent almost 2 days to get Report Builder working (had to reinstall .Net 2.0 to get it working), opened some sample reports from the TFS Sample report zip packages, tried to create some reports in Report Builder (forum advises no to use this; thread;http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766354&SiteID=1) and tried to create some reports in Report Designer.
It seems to me that you have to be an SQL,MDX,RDL,VB and god may know what else guru to be able to produce some simple status reports.
For example, we have defined several Team Queries to quickly find the workitems assigned to different teams (QA-ToDo, RND-ToDo, RnD-Ready, etc). Now we would like to see some trend reports that show the these counts over time.
As far as I can see I have to redefine this queries all over again in my report definition because the language used is different.
when you say you have to redefine your queries, what platform are these queries written in?
I think anyone who will be using reporting services (or most report writers) will at least have to eventually have an understanding of the SQL language. The nice thing about learning SQL is it you can generally apply what you know in SQL Server (T-SQL) to other database platforms like oracle, MySQL, etc...
Learning the vb syntax for inline expressions justs extends the capabilities of RS but isn't required for basic reports, and there are really only a hand full that you will use with any frequenecy (such as the IIF statement).
It isn't really required to know about RDL's (it's just xml). If you understand the basics of xml the RDL (go into code view of a report) makes perfect sense. Learning the basics about xml is very easy, and understanding this will make the RDL code make more sense.
All-in-all these are all technologies you should have a fundemental understanding of anyway if you are going to be writing reports. For starters, you'll have to do some research and reading:
XML (to help understand what an RDL is):
http://www.w3schools.com/xml/default.asp
here is one on sql:
http://www.w3schools.com/sql/
I think if you are going to delve into report writing, you'll will need to become familiar with these technologies, particulaly SQL. RS does have a query designer to make it easier, but in time you should learn how to also write it out.
Thursday, March 8, 2012
Creating Replication
Hello there
I've started creating my own replication between two databases with the same
structure:
Local server and internet server.
The Internet server is not updated.
When i configure the local server for replication it gave me an error that i
can't use the local server agent.
After that i tried to create publisher. Then it didn't let me to build
transactional publication as a result if licence
what can cause the problem?
any help would be useful
what is the exact error message you are getting?
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:%23DM8hcWpEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I've started creating my own replication between two databases with the
> same
> structure:
> Local server and internet server.
> The Internet server is not updated.
> When i configure the local server for replication it gave me an error that
> i
> can't use the local server agent.
> After that i tried to create publisher. Then it didn't let me to build
> transactional publication as a result if licence
> what can cause the problem?
> any help would be useful
>
|||Whell Hilery
The problem is that i ran it from MDSE (as i think)
when i ran it afterword from Enterprize the error didn't came back
But then i stuck with diffrent issue:
After i create the publisher as transactional replication I pressed Push
Subscriber
I filled all the data that was nessesery on the wizart and it didn't gave me
in any level which table i would like to subscribe to or which database
I realy don't understand what's going on
can you help me on it?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uq9PYmWpEHA.1272@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> what is the exact error message you are getting?
> "Oded Kovach" <roygoldh@.hotmail.com> wrote in message
> news:%23DM8hcWpEHA.2636@.TK2MSFTNGP09.phx.gbl...
that
>
|||I'm still unclear as to what your error/problem is. Could you enable logging
and paste the logs here?
Please follow the steps outlined here to enable logging.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:Og61w7WpEHA.3464@.TK2MSFTNGP14.phx.gbl...
> Whell Hilery
> The problem is that i ran it from MDSE (as i think)
> when i ran it afterword from Enterprize the error didn't came back
> But then i stuck with diffrent issue:
> After i create the publisher as transactional replication I pressed Push
> Subscriber
> I filled all the data that was nessesery on the wizart and it didn't gave
me[vbcol=seagreen]
> in any level which table i would like to subscribe to or which database
> I realy don't understand what's going on
> can you help me on it?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uq9PYmWpEHA.1272@.TK2MSFTNGP09.phx.gbl...
the
> that
>
I've started creating my own replication between two databases with the same
structure:
Local server and internet server.
The Internet server is not updated.
When i configure the local server for replication it gave me an error that i
can't use the local server agent.
After that i tried to create publisher. Then it didn't let me to build
transactional publication as a result if licence
what can cause the problem?
any help would be useful
what is the exact error message you are getting?
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:%23DM8hcWpEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I've started creating my own replication between two databases with the
> same
> structure:
> Local server and internet server.
> The Internet server is not updated.
> When i configure the local server for replication it gave me an error that
> i
> can't use the local server agent.
> After that i tried to create publisher. Then it didn't let me to build
> transactional publication as a result if licence
> what can cause the problem?
> any help would be useful
>
|||Whell Hilery
The problem is that i ran it from MDSE (as i think)
when i ran it afterword from Enterprize the error didn't came back
But then i stuck with diffrent issue:
After i create the publisher as transactional replication I pressed Push
Subscriber
I filled all the data that was nessesery on the wizart and it didn't gave me
in any level which table i would like to subscribe to or which database
I realy don't understand what's going on
can you help me on it?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uq9PYmWpEHA.1272@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> what is the exact error message you are getting?
> "Oded Kovach" <roygoldh@.hotmail.com> wrote in message
> news:%23DM8hcWpEHA.2636@.TK2MSFTNGP09.phx.gbl...
that
>
|||I'm still unclear as to what your error/problem is. Could you enable logging
and paste the logs here?
Please follow the steps outlined here to enable logging.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:Og61w7WpEHA.3464@.TK2MSFTNGP14.phx.gbl...
> Whell Hilery
> The problem is that i ran it from MDSE (as i think)
> when i ran it afterword from Enterprize the error didn't came back
> But then i stuck with diffrent issue:
> After i create the publisher as transactional replication I pressed Push
> Subscriber
> I filled all the data that was nessesery on the wizart and it didn't gave
me[vbcol=seagreen]
> in any level which table i would like to subscribe to or which database
> I realy don't understand what's going on
> can you help me on it?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uq9PYmWpEHA.1272@.TK2MSFTNGP09.phx.gbl...
the
> that
>
Creating relationships between tables in different databases
Hello guys,
Does anybody know how can i create a relationship between tables in differen
t databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different d
atabases and i'd like to have some kind of referencial integrity in the data
that is stored on the separated databases by using the same shared tables.
I could use triggers for do
ing that but this will decrease the performance of my databases dramatically
.
Thanks for the help,
Paulo R.Cross database constraints are not supported. You would need
to go the route along the lines of triggers to achieve
something like this.
-Sue
On Mon, 29 Mar 2004 13:41:08 -0800, "paulo"
<anonymous@.discussions.microsoft.com> wrote:
>Hello guys,
>Does anybody know how can i create a relationship between tables in differe
nt databases? Does SQL Server supports this kind of operation?
>The main problem is that i have some data that is shared between different database
s and i'd like to have some kind of referencial integrity in the data that is stored
on the separated databases by using the same shared tables. I could use triggers fo
r d
oing that but this will decrease the performance of my databases dramatically.d">
>Thanks for the help,
>Paulo R.
Does anybody know how can i create a relationship between tables in differen
t databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different d
atabases and i'd like to have some kind of referencial integrity in the data
that is stored on the separated databases by using the same shared tables.
I could use triggers for do
ing that but this will decrease the performance of my databases dramatically
.
Thanks for the help,
Paulo R.Cross database constraints are not supported. You would need
to go the route along the lines of triggers to achieve
something like this.
-Sue
On Mon, 29 Mar 2004 13:41:08 -0800, "paulo"
<anonymous@.discussions.microsoft.com> wrote:
>Hello guys,
>Does anybody know how can i create a relationship between tables in differe
nt databases? Does SQL Server supports this kind of operation?
>The main problem is that i have some data that is shared between different database
s and i'd like to have some kind of referencial integrity in the data that is stored
on the separated databases by using the same shared tables. I could use triggers fo
r d
oing that but this will decrease the performance of my databases dramatically.d">
>Thanks for the help,
>Paulo R.
Creating relationships between different databases
Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Even if SQL Server did support it (which I don't believe it does) it would
be a very poor design. What if the databases were installed on separate
servers? That would be a real mess. If the tables are that important for a
database then some sort of replication from one database to the other would
be a better solution.
Jim
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:4B7733D7-AFCE-4992-9734-59C7FA38194A@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||>>Does SQL Server supports this kind of operation?<<
Cross database ref. integrity is not supported in SQL Server.
BOL: "FOREIGN KEY constraints can reference only tables within the same database on the same
server. Cross-database referential integrity must be implemented through triggers. For more
information, see CREATE TRIGGER. "
So this can be accomplished through triggers.
See following topic in bol.
"FOREIGN KEY Constraints"
Vishal Parkar
vgparkar@.yahoo.co.in
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Even if SQL Server did support it (which I don't believe it does) it would
be a very poor design. What if the databases were installed on separate
servers? That would be a real mess. If the tables are that important for a
database then some sort of replication from one database to the other would
be a better solution.
Jim
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:4B7733D7-AFCE-4992-9734-59C7FA38194A@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||>>Does SQL Server supports this kind of operation?<<
Cross database ref. integrity is not supported in SQL Server.
BOL: "FOREIGN KEY constraints can reference only tables within the same database on the same
server. Cross-database referential integrity must be implemented through triggers. For more
information, see CREATE TRIGGER. "
So this can be accomplished through triggers.
See following topic in bol.
"FOREIGN KEY Constraints"
Vishal Parkar
vgparkar@.yahoo.co.in
Creating relationships between different databases
Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Triggers work, but as you point out there are performance problems.
You can't do cross database constraints or you will get a message like:
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'master.dbo.jobs'.
I'm not sure what to suggest other than triggers.
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:A4BC16DB-C2BB-4EC9-88C4-284F0BA4E957@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||Paulo,
if you really want this functionality, then you could enforce the constraints in your stored procedures, assuming database access is via sps, or use triggers, or use filegroups. This last option is unconventional and in my opinion not ideal, but it is pos
sible to put all your tables into one database and declare constraints there. What were databases now become filegroups. You can back up individual filegroups like you back up databases. I know one financial services institution that uses this methodology
. However, this is limiting - there is no posibility of a point-in-time restore and you need to be able to get hold of the last transaction log if you want to do a full restore. However, if you only use database backups, then this is an option to consider
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Triggers work, but as you point out there are performance problems.
You can't do cross database constraints or you will get a message like:
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'master.dbo.jobs'.
I'm not sure what to suggest other than triggers.
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:A4BC16DB-C2BB-4EC9-88C4-284F0BA4E957@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||Paulo,
if you really want this functionality, then you could enforce the constraints in your stored procedures, assuming database access is via sps, or use triggers, or use filegroups. This last option is unconventional and in my opinion not ideal, but it is pos
sible to put all your tables into one database and declare constraints there. What were databases now become filegroups. You can back up individual filegroups like you back up databases. I know one financial services institution that uses this methodology
. However, this is limiting - there is no posibility of a point-in-time restore and you need to be able to get hold of the last transaction log if you want to do a full restore. However, if you only use database backups, then this is an option to consider
Creating relationships between 2 databases
Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?
This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully.
I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?
So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL.
Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.
? No, it is not possible. If your only goal is to get around the 4gb limit, then my suggestion is that you upgrade to Workgroup Edition, which has no such limitation. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:d2141cdb-833b-49ba-8bc4-98feb1cd8a83@.discussions.microsoft.com... Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully. I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL. Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.|||No NNTP User,
my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that.
Can you think of any workaround ?
|||? You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:7915ff66-3526-4b90-aea2-8762ed87d8dc@.discussions.microsoft.com... No NNTP User, my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that. Can you think of any workaround ?|||To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution.
I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?
|||? Actually, you only need distributed transactions if you're going across servers or instances. Cross-database work in the same instance will not need to make use of a distributed transaction. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <bass_player@.discussions.microsoft.com> wrote in message news:793732c8-c600-43b9-8e1a-294100137ac6@.discussions.microsoft.com...To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||? Unfortunately, you can't do most of this stuff via the UI. DML is Data Manipulation Language; DML triggers fire on DML events such as inserting, updating, or deleting data. I recommend that you pick up a good basic SQL book to start with. I'm not sure if there are any out for SQL Server 2005 yet, but you might just start with a SQL Server 2000 one. I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:b6185a4a-9770-456b-86e3-feacd72df877@.discussions.microsoft.com... you can also wrap them in distributed transactions. You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?|||
Rishi, it is not possible to enforce foreign key relationship across database. NNTP is right, you can use triggers. Triggers are programs which runs when there is a new rows created in a table. Or when there is a row deleted from a table. Or when a row is updated. That's DML trigger, there is something else called DDL trigger, which is a new feature in 2005, but let's not talk about this right now.
Compared to foreign key (FK), this DML trigger has a performance issue, it will make the insert (or delete or update) slower than usual. But it has advantages: it is more flexible than FK. A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.
Vincent
A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.
But for that, I would have to write code, wouldn't I ? I mean, I cannot do all this stuff using the GUI ?
Anyway, I wouldn't want to implement a design which slows down the performance of the database much. Thanks for all the help guys.
Rishi.
|||Say, is there anyway I can create the relationship in the front end application using VB Express ?
Because I really want to put all the info into one application. Its like having everything under one roof. A complete ERP solution ( for small proprietory concerns with no employees, not counting peons ). I have seen many organisations use different software for different purposes and I did not like the idea. For instance, they use different software for keeping track of their inventory, employee details (which covers employee attendance, performance, incentives etc.), financial records etc. Now for example, I have the inventory software, which has a separate table which stores employee names, and this is used so that the sales reports show the employee wise sales, but all the main employee information is stored in the database of another application altogether. Now, suppose I am the manager, and I am going through the sales by emp report, and I suddenly feel I want to know more details about the emp, so in this scenario, I would have to quit the inventory program, fire up the employee program, and then look up the employee's name, and then pull up the emp's info. Now wouldn't it be nice if I had both the databases linked in the front end application, so I could directly pull up the employee's info ? Why I don't want to put both these separate pieces of info in the same database is again performance, and I need to clarify this point - If I have both the inventory and the employee databases from the above example consolidated into a single database, would it slow down the performance of the database as it grows larger in size ? Or would it offer performance benefits if they are kept in separate dbs and are linked through the front end.
I know the idea given to me above about using DML triggers would considerably slow down the database, as the program would have to scan every record every time a record is created or updated or deleted. So I have scrapped the plan of using DML triggers ( after learning DML of course )
Any ideas about using the VB Express created front end application though ?
|||>>I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson.
Anyone know if there's going to be a SQL Server 2005 version of this book? I just "discovered" it last night at B&N; it looks like a great book. However, there's nothing on Amazon.com nor the publisher's site about an upcoming revised edition. 
Creating Relational Databases(Primary/Foreign Key?)
Hey everyone,
I have just started getting into to SQL and am completely brand new to the whole concepts of relational databases. Someone on this forum pointed to the MSDN videos on LEARNVISUALSTUDIO.NET which have been very helpful. Unfortunately while learning about relational databases and looking at the program that I want to design and make using them, I have run into a pretty big wall, concerning the primary key and foreign key.
For my program, I am trying to save an object, and lets say the base class is SLIDE. Now SLIDE will store basically most of the information that I will ever want to store, such as timeCreated and mainText and slideID(primarykey). But there are other classes that derive from slide that will store just a bit more information than that because of what they are. Lets say there is a class derived from SLIDE called PERSON that stores its parentNode, which is to say something that this slide specifically belongs to and has a reference to. Now the tricky part is that in this program, every single slide can have a reference to another slide, that you would see displayed and that you could just click on and view if you wanted to.
Now relating what I just told about the classes in my program to a relation database standpoint is what confuses me. If I make a table SLIDE, it would hold incomplete data about the PERSON object, because that object has more data than SLIDE has. The answer to this was to make another table called PERSON, which would have more columns. But now we arrive at the big problem: The primary key called maybe SLIDEID would be different in this new PERSON table than in the other table called SLIDE (or any other derived class). Therefore the link between these tables is messed up. In my object orientated mind I am thinking of static class variables that would still stay constant for derived classes, so that when I make a PERSON slide it will increment off of the primary key of the SLIDE table. In other words, is there some sort of super TABLE that you can derive from, like an abstract class, where the primary keys of other tables can build off of because they will be used as the same type of reference to eachother.
If none of this made sense to the reader, I am greatly sorry. I do not really know what else I can say to convey to you the problem I have. Maybe its because I am so used to object orientated languages that this is making it so difficult to explain. If however you do understand what I am talking about, please think about it and help me find a solution to this problem. I am not an experienced programmer, but I do very much enjoy it and I am very excited about starting to make this program, and I have learned that before I start coding it is very important to have a very firm design in mind first.
Thank your for reading,
Jeremy
Thank you for the reply, I'll see if I can get the book so I can get a much better idea on what relational database design should all be about.
I have been thinking about my problem a little more lately and I think that I have come up with an idea. I can use my program in C# to keep track of the identities keys(maybe an int but leaning forward to a string, where like the first three letter distinguish what type of object it is, ie. PER-4539) through the use of a class static variable and then when I create a new entry into the table I input that key directly, instead of letting the database handle it. So basically I use C# to handle this problem instead of SQL. Is that a good idea, or should I still think of something else?
Jeremy
|||
Jeremy
I have just started getting into to SQL and am completely brand new to the whole concepts of relational databases. Someone on this forum pointed to the MSDN videos on LEARNVISUALSTUDIO.NET which have been very helpful. Unfortunately while learning about relational databases and looking at the program that I want to design and make using them, I have run into a pretty big wall, concerning the primary key and foreign key.
For my program, I am trying to save an object, and lets say the base class is SLIDE. Now SLIDE will store basically most of the information that I will ever want to store, such as timeCreated and mainText and slideID(primarykey). But there are other classes that derive from slide that will store just a bit more information than that because of what they are. Lets say there is a class derived from SLIDE called PERSON that stores its parentNode, which is to say something that this slide specifically belongs to and has a reference to. Now the tricky part is that in this program, every single slide can have a reference to another slide, that you would see displayed and that you could just click on and view if you wanted to.
Now relating what I just told about the classes in my program to a relation database standpoint is what confuses me. If I make a table SLIDE, it would hold incomplete data about the PERSON object, because that object has more data than SLIDE has. The answer to this was to make another table called PERSON, which would have more columns. But now we arrive at the big problem: The primary key called maybe SLIDEID would be different in this new PERSON table than in the other table called SLIDE (or any other derived class). Therefore the link between these tables is messed up. In my object orientated mind I am thinking of static class variables that would still stay constant for derived classes, so that when I make a PERSON slide it will increment off of the primary key of the SLIDE table. In other words, is there some sort of super TABLE that you can derive from, like an abstract class, where the primary keys of other tables can build off of because they will be used as the same type of reference to eachother.
If none of this made sense to the reader, I am greatly sorry. I do not really know what else I can say to convey to you the problem I have. Maybe its because I am so used to object orientated languages that this is making it so difficult to explain. If however you do understand what I am talking about, please think about it and help me find a solution to this problem. I am not an experienced programmer, but I do very much enjoy it and I am very excited about starting to make this program, and I have learned that before I start coding it is very important to have a very firm design in mind first.
Thank your for reading,
Jeremy
fwiw, I'll try to give you a nudge in the right direction.. =;o)
First off, you're completely right in that having a firm design (along with an understanding of the same) in mind before starting, is a very good thing.
Having said that, it's not certain that it'll be an easy or fast journey to get there.
You're currently thinking objectoriented, and trying to fit that into a relational design. Here's where the difficulties start.
Object orientation and Relational theory are quite different, to say the least...
Probably the best place to start, is to begin reading in Handbook of Relational Database Design
( http://www.amazon.com/Handbook-Relational-Database-Candace-Fleming/dp/0201114348 )
This is pretty much (still) the 'bible' on the subject.
It's not productspecific, so there's no 'specials' in there, but you'll find everything you want to know about how-to and why
and the reasonings and rules of what makes up a 'good' database design, and imo it's all pretty 'easy-reading' too, along with
examples and explanations about why different things may or may not be a good idea, and what sort of problems or benefits
different design choices may give you.
Once you get a grip about how the relational world works, you can then see how to apply that to how you want your program to work.
Additionally, if you just google on 'relational database design', you'll get a bunch of links to different online tutorials
(at least it looks that way, haven't looked at all)
Good luck, and welcome to 'our' world =;o)
/Kenneth
|||Dear Kenneth,Thank you for the reply, I'll see if I can get the book so I can get a much better idea on what relational database design should all be about.
I have been thinking about my problem a little more lately and I think that I have come up with an idea. I can use my program in C# to keep track of the identities keys(maybe an int but leaning forward to a string, where like the first three letter distinguish what type of object it is, ie. PER-4539) through the use of a class static variable and then when I create a new entry into the table I input that key directly, instead of letting the database handle it. So basically I use C# to handle this problem instead of SQL. Is that a good idea, or should I still think of something else?
Jeremy
|||
Well, as a db guy, I shiver at the thought.
There are some 'stuff' out there that does keymanagement along those lines, though.. But only because you can doesn't always mean you should.. =;o)
Something about keys...
A key is the 'identificator' within a domain. A domain in this case, is typically a table. A table is also many times referred to as an 'entity'.
There are a few different names for different flavors of keys, such as Primary key, Alternate key, Foreign key, Super key.
The purpose of the Primary key is to single out the one unique row.
The PK may consist of a single column or more (composite key).
It's called 'Primary' just because it's the one chosen 'primarily' among the available alternate keys within the table.
In general (pertaining your question) it's not a good idea to have an 'intelligent' PK.
That is, the key shouldn't be any construction with some innate meaning or code. It will bring you more trouble than joy in the long run.
The Alternate Key could serve as a PK, but isn't declared as a PK, thus being 'alternate' by name.
The Super Key is all the columns in the table. It's always there automagically, and could be used as a PK right away,
but that wouldn't be very practical in most cases. (and there's also limitations on how indexes can be created)
A Foreign Key is just what the name implies - it's a key from 'somewhere else'.
'Else' meaning another table, or entity.
(sry for the lack of a creative example) =;o)
For example, you have an entity (table) 'Parent' that has some attributes. (columns)
Among the columns you decide which one is to be the PK.
The Parent table has a relation to another table 'Child'.
The relation is zero to many. That is, a parent may have zero, one or more children.
Child is then another entity (table) with some attributes (columns) of it's own.
Child has it's own PK, but how to know which child belongs to which parent?
In the Child table, you also place the PK from Parent, which then becomes a FK in Child.
As you may notice, we're heading into unknown waters pretty quick, and most of this stuff are related to other stuff that is related to other stuff.. etc etc
As soon as we start thinking about tables, columns, keys and relations, the question also arises about which columns should go into which table?
What is the difference if the PK is a single or composite key?
Here's where 'normalization' starts to show, which is another thing to consider.
I like this site http://www.datamodel.org/reference.php for it's simplicity.
It shows a great overview of the rules for normalization and also about cardinality - both are a 'must know' for you.
If you go there and browse around a bit, I think that you'll get some more pieces into place.
You'll also find, that in this world there are very few (if any) absolutes or 'right answers'.
Practically everything is depending on scope and/or context - what is the good choice here, might be not good over there etc.
All in all - 'it depends'
/Kenneth
|||Once again Kenneth, thank you so very much. I will definintly be trying to get that book and will look through that website.Jeremy
Saturday, February 25, 2012
Creating multiple temporary databases.
Hello,
I noticed that when we installed SQL 2000 Reporting services that two
databases are created one being ReportServerTempDB. This got me to thinkin
g
about Oracle databases where I can create multiple temporary 'tablespaces'
and then I can assign specific users to specific tablespaces.
In a SQL environment, can I create separate tempdbs that will be used by
separate users and/or applications? How do Reporting Services use the
ReportServerTempDB?
I'm trying to create a shared hosting environment in my organization and
don't want competing applications filling up the tempdb. I'd much prefer
that each application uses it's own temp space.
Thanks!
Art"Art Decker" <ArtDecker@.discussions.microsoft.com> wrote in message
news:E8D4949F-A0C5-4408-8318-F58EF0847085@.microsoft.com...
> Hello,
> I noticed that when we installed SQL 2000 Reporting services that two
> databases are created one being ReportServerTempDB. This got me to
> thinking
> about Oracle databases where I can create multiple temporary 'tablespaces'
> and then I can assign specific users to specific tablespaces.
> In a SQL environment, can I create separate tempdbs that will be used by
> separate users and/or applications?
>How do Reporting Services use the ReportServerTempDB?
It uses it for caching and snapshots. Lots of reads and writes with a low
requirement for backup.
But it's an ordinary user database.
> I'm trying to create a shared hosting environment in my organization and
> don't want competing applications filling up the tempdb. I'd much prefer
> that each application uses it's own temp space.
>
All databases on a server share memory, CPU and TempDB. You can install
multiple instances to segregate these resources.
David
I noticed that when we installed SQL 2000 Reporting services that two
databases are created one being ReportServerTempDB. This got me to thinkin
g
about Oracle databases where I can create multiple temporary 'tablespaces'
and then I can assign specific users to specific tablespaces.
In a SQL environment, can I create separate tempdbs that will be used by
separate users and/or applications? How do Reporting Services use the
ReportServerTempDB?
I'm trying to create a shared hosting environment in my organization and
don't want competing applications filling up the tempdb. I'd much prefer
that each application uses it's own temp space.
Thanks!
Art"Art Decker" <ArtDecker@.discussions.microsoft.com> wrote in message
news:E8D4949F-A0C5-4408-8318-F58EF0847085@.microsoft.com...
> Hello,
> I noticed that when we installed SQL 2000 Reporting services that two
> databases are created one being ReportServerTempDB. This got me to
> thinking
> about Oracle databases where I can create multiple temporary 'tablespaces'
> and then I can assign specific users to specific tablespaces.
> In a SQL environment, can I create separate tempdbs that will be used by
> separate users and/or applications?
>How do Reporting Services use the ReportServerTempDB?
It uses it for caching and snapshots. Lots of reads and writes with a low
requirement for backup.
But it's an ordinary user database.
> I'm trying to create a shared hosting environment in my organization and
> don't want competing applications filling up the tempdb. I'd much prefer
> that each application uses it's own temp space.
>
All databases on a server share memory, CPU and TempDB. You can install
multiple instances to segregate these resources.
David
Creating multiple temporary databases.
Hello,
I noticed that when we installed SQL 2000 Reporting services that two
databases are created one being ReportServerTempDB. This got me to thinking
about Oracle databases where I can create multiple temporary 'tablespaces'
and then I can assign specific users to specific tablespaces.
In a SQL environment, can I create separate tempdbs that will be used by
separate users and/or applications? How do Reporting Services use the
ReportServerTempDB?
I'm trying to create a shared hosting environment in my organization and
don't want competing applications filling up the tempdb. I'd much prefer
that each application uses it's own temp space.
Thanks!
Art"Art Decker" <ArtDecker@.discussions.microsoft.com> wrote in message
news:E8D4949F-A0C5-4408-8318-F58EF0847085@.microsoft.com...
> Hello,
> I noticed that when we installed SQL 2000 Reporting services that two
> databases are created one being ReportServerTempDB. This got me to
> thinking
> about Oracle databases where I can create multiple temporary 'tablespaces'
> and then I can assign specific users to specific tablespaces.
> In a SQL environment, can I create separate tempdbs that will be used by
> separate users and/or applications?
>How do Reporting Services use the ReportServerTempDB?
It uses it for caching and snapshots. Lots of reads and writes with a low
requirement for backup.
But it's an ordinary user database.
> I'm trying to create a shared hosting environment in my organization and
> don't want competing applications filling up the tempdb. I'd much prefer
> that each application uses it's own temp space.
>
All databases on a server share memory, CPU and TempDB. You can install
multiple instances to segregate these resources.
David
I noticed that when we installed SQL 2000 Reporting services that two
databases are created one being ReportServerTempDB. This got me to thinking
about Oracle databases where I can create multiple temporary 'tablespaces'
and then I can assign specific users to specific tablespaces.
In a SQL environment, can I create separate tempdbs that will be used by
separate users and/or applications? How do Reporting Services use the
ReportServerTempDB?
I'm trying to create a shared hosting environment in my organization and
don't want competing applications filling up the tempdb. I'd much prefer
that each application uses it's own temp space.
Thanks!
Art"Art Decker" <ArtDecker@.discussions.microsoft.com> wrote in message
news:E8D4949F-A0C5-4408-8318-F58EF0847085@.microsoft.com...
> Hello,
> I noticed that when we installed SQL 2000 Reporting services that two
> databases are created one being ReportServerTempDB. This got me to
> thinking
> about Oracle databases where I can create multiple temporary 'tablespaces'
> and then I can assign specific users to specific tablespaces.
> In a SQL environment, can I create separate tempdbs that will be used by
> separate users and/or applications?
>How do Reporting Services use the ReportServerTempDB?
It uses it for caching and snapshots. Lots of reads and writes with a low
requirement for backup.
But it's an ordinary user database.
> I'm trying to create a shared hosting environment in my organization and
> don't want competing applications filling up the tempdb. I'd much prefer
> that each application uses it's own temp space.
>
All databases on a server share memory, CPU and TempDB. You can install
multiple instances to segregate these resources.
David
Subscribe to:
Posts (Atom)