Database Setup Package/Installation

Hi Folks,
I've created a SQL Server database and converted it to Oracle (an app we
made supports MSSQL and Oracle) and I'd like to be able to provide a
"server-side" installation that clients can run on their database server
that would automatically create the MSSQL or Oracle database, the tables,
index, relationships, etc. and insert the needed data for a "new" database.
I have very little experience in the realm of database setup & installation.
Has anyone ever done this before and if so, what product/technology did you
use? I've been searching the newsgroups and haven't found much useful info
on the topic. Am I looking in the wrong place?
Thanks,
Jason-- Jason wrote: --
>> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database.
--
Hi Jason,
This is a fairly trivial exercise. Have a look at the sample *.sql scripts that automatically creates the sample Northwind and Pubs database when you install SQL Server. They will give you some idea on how to automatically create a database and objects within that database.
The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs, respectively. The files can be found on your <drive>:\Program Files\Microsoft SQL Server\MSSQL\Install folder
Hope this helps,
-Eric Cárdenas
SQL Server support|||You could, from your install program, either spawn OSQL with your script file (see Eric's post) as
input parameter. Or you can write your own app which reads the file and execute the TSQL statements
in the script file. Or you could, of course, embed the various CREATE statements inside your setup
programs source code.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database.
> I have very little experience in the realm of database setup & installation.
> Has anyone ever done this before and if so, what product/technology did you
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>|||I would probably
1. Script out Your DB. This will enable you to create a blank version of
your DB somewhere else.
2. Have your standing data in text files and use BULK INSERT or bcp
statements to load it into the tables.
3. Use osql to load the scripts you have created.
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\co
prompt.chm::/cp_osql_1wxl.htm)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app
we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new"
database.
> I have very little experience in the realm of database setup &
installation.
> Has anyone ever done this before and if so, what product/technology did
you
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>|||Thanks for the responses from everyone. I'm aware I can provide someone with
.sql scripts...what I'm more interested in though is an installation I can
provide that goes something like this:
User runs Setup.exe to luanch the setup.
During Setup, one of the screens prompts them to enter the db information
(name of the server, un/pw)
Then setup takes that information and runs the DDL scripts against the
server they provided.
Are there any products (like Wise or Installshield maybe) that help automate
the creation of setups to do this or am I looking at needing to write my own
custom setup program?
Jason
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:BE41F8CB-4DBA-439D-A034-32CF737D0ED0@.microsoft.com...
> -- Jason wrote: --
> >> Hi Folks,
> > I've created a SQL Server database and converted it to Oracle (an app
we
> > made supports MSSQL and Oracle) and I'd like to be able to provide a
> > "server-side" installation that clients can run on their database
server
> > that would automatically create the MSSQL or Oracle database, the
tables,
> > index, relationships, etc. and insert the needed data for a "new"
database.
> --
> Hi Jason,
> This is a fairly trivial exercise. Have a look at the sample *.sql scripts
that automatically creates the sample Northwind and Pubs database when you
install SQL Server. They will give you some idea on how to automatically
create a database and objects within that database.
> The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs,
respectively. The files can be found on your <drive>:\Program
Files\Microsoft SQL Server\MSSQL\Install folder
> Hope this helps,
> -Eric Cárdenas
> SQL Server support
>

Database Setup Package/Installation

Hi Folks,
I've created a SQL Server database and converted it to Oracle (an app we
made supports MSSQL and Oracle) and I'd like to be able to provide a
"server-side" installation that clients can run on their database server
that would automatically create the MSSQL or Oracle database, the tables,
index, relationships, etc. and insert the needed data for a "new" database.
I have very little experience in the realm of database setup & installation.
Has anyone ever done this before and if so, what product/technology did you
use? I've been searching the newsgroups and haven't found much useful info
on the topic. Am I looking in the wrong place?
Thanks,
JasonYou can create scripts to do this. You can find similar
scripts from the installation of databases on SQL Server.
For an example, check the script that creates the Northwinds
database. The file is instnwnd.sql and it's located in the
install subdirectory under MSSQL, e.g.
C:\Program Files\Microsoft SQL Server\MSSQL\Install
-Sue
On Tue, 2 Dec 2003 14:35:53 -0800, "Jason"
<jasonmauss_nospam@.vsdotnetguru.com> wrote:

quote:

---

>Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app w
e
>made supports MSSQL and Oracle) and I'd like to be able to provide a
>"server-side" installation that clients can run on their database server
>that would automatically create the MSSQL or Oracle database, the tables,
>index, relationships, etc. and insert the needed data for a "new" database.
>I have very little experience in the realm of database setup & installation
.
>Has anyone ever done this before and if so, what product/technology did you
>use? I've been searching the newsgroups and haven't found much useful info
>on the topic. Am I looking in the wrong place?
>Thanks,
>Jason
>

---

|||You could, from your install program, either spawn OSQL with your script fil
e (see Eric's post) as
input parameter. Or you can write your own app which reads the file and exec
ute the TSQL statements
in the script file. Or you could, of course, embed the various CREATE statem
ents inside your setup
programs source code.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app
we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database
.
> I have very little experience in the realm of database setup & installatio
n.
> Has anyone ever done this before and if so, what product/technology did yo
u
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>

---

|||I would probably
1. Script out Your DB. This will enable you to create a blank version of
your DB somewhere else.
2. Have your standing data in text files and use BULK INSERT or bcp
statements to load it into the tables.
3. Use osql to load the scripts you have created.
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\co
prompt.chm::/cp_osql_1wxl.htm)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app

---

we

quote:

---

> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new"

---

database.

quote:

---

> I have very little experience in the realm of database setup &

---

installation.

quote:

---

> Has anyone ever done this before and if so, what product/technology did

---

you

quote:

---

> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>

---

|||Thanks for the responses from everyone. I'm aware I can provide someone with
.sql scripts...what I'm more interested in though is an installation I can
provide that goes something like this:
User runs Setup.exe to luanch the setup.
During Setup, one of the screens prompts them to enter the db information
(name of the server, un/pw)
Then setup takes that information and runs the DDL scripts against the
server they provided.
Are there any products (like Wise or Installshield maybe) that help automate
the creation of setups to do this or am I looking at needing to write my own
custom setup program?
Jason
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:BE41F8CB-4DBA-439D-A034-32CF737D0ED0@.microsoft.com...

quote:

---

> -- Jason wrote: --
we[QUOTE]
server[QUOTE]
tables,[QUOTE]
database.[QUOTE]
> --
> Hi Jason,
> This is a fairly trivial exercise. Have a look at the sample *.sql scripts

---

that automatically creates the sample Northwind and Pubs database when you
install SQL Server. They will give you some idea on how to automatically
create a database and objects within that database.

quote:

---

> The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs,

---

respectively. The files can be found on your <drive>:\Program
Files\Microsoft SQL Server\MSSQL\Install folder

quote:

---

> Hope this helps,
> -Eric Crdenas
> SQL Server support
>

---

Database reporting time differently than system

Hiya,

I have a database on a SQL Server 2000 (sp3a) installation. For some reason it's reporting time that is 7 hours ahead of the system time.

The application is on one server the DB is on a shared production server. The app server and the DB server are reporting the same system time and are using a network time server. All the other db's on the shared production db server are reporting time correctly.

My questions:

Is there a T-SQL query to use to see what the time/timezone is for that database?
Is there a T-SQL query I can use to set the db time (not the system time)?
Anyone have any other suggestions as to what could be wrong?

Thanks in advance for any help!
'cheleIs this from a select getdate() query in QA or the output of some app or stored proc? If app or stored proc, check for date manipulation. SQL Server picks up its time from the serer clock, there is no separate SQL Server clock.


Unless it is using the getutcdate(), which is also derived from the system date / time / time zone setting.

<QUOTE>
GETUTCDATE

Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.

Syntax

GETUTCDATE()

</QUOTE>|||Thanks for the help.

It's a canned app. Epolicy by McAfee, to be precise. I guess I will go to them for tech support now that I know it's probably not something I did.

Database Recovery Problem

Greetings,
I am attempting to restore some databases to a SQL Server
2000 Installation that fell apart after loading SP4 for
Windows 2000. The backups were made using Veritas Backup
Exec 8.6. on a remote backup server. When I do a restore
on my databases Veritas restores all of them to the the
SQL Server and they appear in Enterprise Manager. However
the databases are in a state of loading and I cannot seem
to find a way to take them out of this loading state to
complete the restore to normal operation.
I am new to SQL Server so I am at a loss as to what to do
to rectify this situation.
Thanks in advance for the help.
Joe.If you have restored the databases with norecovery you can
restore database mydb with recovery
to bring it on-line.
If in fact veritas hasn't completed the restore then you
will have to go back to that product.
>"Joe Toth" <jtoth@.cmefcu.org> wrote in message
>news:029d01c34727$da60cee0$a401280a@.phx.gbl...
>> Greetings,
>> I am attempting to restore some databases to a SQL
Server
>> 2000 Installation that fell apart after loading SP4 for
>> Windows 2000. The backups were made using Veritas Backup
>> Exec 8.6. on a remote backup server. When I do a restore
>> on my databases Veritas restores all of them to the the
>> SQL Server and they appear in Enterprise Manager.
However
>> the databases are in a state of loading and I cannot
seem
>> to find a way to take them out of this loading state to
>> complete the restore to normal operation.
>> I am new to SQL Server so I am at a loss as to what to
do
>> to rectify this situation.
>> Thanks in advance for the help.
>> Joe.
>
>.
>|||It's been a few months since I restored from BE, but I
think it's an option when configuring the restore on the
SQL tab. The db is left in this state so you can apply
logs if you need to.
--Scott
>--Original Message--
>Greetings,
>I am attempting to restore some databases to a SQL Server
>2000 Installation that fell apart after loading SP4 for
>Windows 2000. The backups were made using Veritas Backup
>Exec 8.6. on a remote backup server. When I do a restore
>on my databases Veritas restores all of them to the the
>SQL Server and they appear in Enterprise Manager. However
>the databases are in a state of loading and I cannot seem
>to find a way to take them out of this loading state to
>complete the restore to normal operation.
>I am new to SQL Server so I am at a loss as to what to do
>to rectify this situation.
>Thanks in advance for the help.
>Joe.
>.
>

DataBase Problem

Hello
I have a problem with a Report Server installation.
The report server database is on another computer than the report server
frontend.
On the frontend Server SqlServer Service Pack2 was installed last night.
Now I can´t connect to the ReportServer Database.
How can I fix this problem, without creating a new Database?
I have tried to refresh the database but I get an error
thanx for any help
regards
BjörnCan you post the error you are getting? Also, check the Reporting Services
error logs located in C:\Program Files\Microsoft SQL
Server\MSSQL.#\Reporting Services\LogFiles and see if there is any more
detailed information in there.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Berger" <prodevteam@.gmx.net>
> Subject: DataBase Problem
> Date: Thu, 4 Oct 2007 13:31:32 +0200
> Hello
> I have a problem with a Report Server installation.
> The report server database is on another computer than the report server
> frontend.
> On the frontend Server SqlServer Service Pack2 was installed last night.
> Now I can´t connect to the ReportServer Database.
> How can I fix this problem, without creating a new Database?
> I have tried to refresh the database but I get an error
>
> thanx for any help
> regards
> Björn
>
>|||Hello
I´ve got the following error in windows eventlog:
http://www.eventid.net/display.asp?eventid=107&eventno=7858&source=Report%20Server&phase=1
Can´t paste from Windows because it´s german ;)
In the log directory I´ve found an error that the sqlaccount can´t login at
the database.
I also have read an article that descripes that the wmi provider changed in
SP2.
The database was sp2 but the reporting service frontend wasn´t sp2.
So I believe it´s because SP2 was installed on the reporting service
frontend
regards
Björn
"Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
news:zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl...
> Can you post the error you are getting? Also, check the Reporting Services
> error logs located in C:\Program Files\Microsoft SQL
> Server\MSSQL.#\Reporting Services\LogFiles and see if there is any more
> detailed information in there.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> From: "Berger" <prodevteam@.gmx.net>
>> Subject: DataBase Problem
>> Date: Thu, 4 Oct 2007 13:31:32 +0200
>> Hello
>> I have a problem with a Report Server installation.
>> The report server database is on another computer than the report server
>> frontend.
>> On the frontend Server SqlServer Service Pack2 was installed last night.
>> Now I can´t connect to the ReportServer Database.
>> How can I fix this problem, without creating a new Database?
>> I have tried to refresh the database but I get an error
>>
>> thanx for any help
>> regards
>> Björn
>>
>|||If the front end IIS Server does not have SP2 applied then it will not
connect to a SP2 backend ReportServer database. They both need to be at the
same SP level.
To check this do the following:
1. open up the "Reporting Services Configuration Tool" and go to the
"Database Setup" section.
2. Click "Connect" and connect to the backend database server you are using.
3. Make sure the credentials are specified correctly for the database
connection.
4. Click the "Apply" Button.
If you get an error saying "Expected version C.0.8.43 but found C.0.8.54'
or something similar that means that your versions are out of sync and you
need to upgrade one or the other.
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Berger" <prodevteam@.gmx.net>
> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
<zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
> Subject: Re: DataBase Problem
> Date: Thu, 4 Oct 2007 16:58:34 +0200
> Hello
> I´ve got the following error in windows eventlog:
>
http://www.eventid.net/display.asp?eventid=107&eventno=7858&source=Report%20
Server&phase=1
> Can´t paste from Windows because it´s german ;)
> In the log directory I´ve found an error that the sqlaccount can´t login
at
> the database.
> I also have read an article that descripes that the wmi provider changed
in
> SP2.
> The database was sp2 but the reporting service frontend wasn´t sp2.
> So I believe it´s because SP2 was installed on the reporting service
> frontend
> regards
> Björn
> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
> news:zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl...
> > Can you post the error you are getting? Also, check the Reporting
Services
> > error logs located in C:\Program Files\Microsoft SQL
> > Server\MSSQL.#\Reporting Services\LogFiles and see if there is any more
> > detailed information in there.
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > --
> >> From: "Berger" <prodevteam@.gmx.net>
> >> Subject: DataBase Problem
> >> Date: Thu, 4 Oct 2007 13:31:32 +0200
> >>
> >> Hello
> >>
> >> I have a problem with a Report Server installation.
> >> The report server database is on another computer than the report
server
> >> frontend.
> >> On the frontend Server SqlServer Service Pack2 was installed last
night.
> >> Now I can´t connect to the ReportServer Database.
> >> How can I fix this problem, without creating a new Database?
> >> I have tried to refresh the database but I get an error
> >>
> >>
> >> thanx for any help
> >>
> >> regards
> >>
> >> Björn
> >>
> >>
> >>
> >
>
>|||Hey Chris
Thank´s for your answer.
If I do this I´ve got the question if I wish to update the databse.
I say Yes and got an error in the SqlScript.
The error is from type System.Data.SqlClient.SqlException.
The error text: in sysdatabases no entry was found for "my Reporting
Database".
The Version of my reporting database is C.0.8.40
thanks
Björn
"Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
news:oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> If the front end IIS Server does not have SP2 applied then it will not
> connect to a SP2 backend ReportServer database. They both need to be at
> the
> same SP level.
> To check this do the following:
> 1. open up the "Reporting Services Configuration Tool" and go to the
> "Database Setup" section.
> 2. Click "Connect" and connect to the backend database server you are
> using.
> 3. Make sure the credentials are specified correctly for the database
> connection.
> 4. Click the "Apply" Button.
> If you get an error saying "Expected version C.0.8.43 but found C.0.8.54'
> or something similar that means that your versions are out of sync and you
> need to upgrade one or the other.
>
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> From: "Berger" <prodevteam@.gmx.net>
>> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
> <zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
>> Subject: Re: DataBase Problem
>> Date: Thu, 4 Oct 2007 16:58:34 +0200
>> Hello
>> I´ve got the following error in windows eventlog:
> http://www.eventid.net/display.asp?eventid=107&eventno=7858&source=Report%20
> Server&phase=1
>> Can´t paste from Windows because it´s german ;)
>> In the log directory I´ve found an error that the sqlaccount can´t login
> at
>> the database.
>> I also have read an article that descripes that the wmi provider changed
> in
>> SP2.
>> The database was sp2 but the reporting service frontend wasn´t sp2.
>> So I believe it´s because SP2 was installed on the reporting service
>> frontend
>> regards
>> Björn
>> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
>> news:zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl...
>> > Can you post the error you are getting? Also, check the Reporting
> Services
>> > error logs located in C:\Program Files\Microsoft SQL
>> > Server\MSSQL.#\Reporting Services\LogFiles and see if there is any more
>> > detailed information in there.
>> >
>> > --
>> > Chris Alton, Microsoft Corp.
>> > SQL Server Developer Support Engineer
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> > --
>> >> From: "Berger" <prodevteam@.gmx.net>
>> >> Subject: DataBase Problem
>> >> Date: Thu, 4 Oct 2007 13:31:32 +0200
>> >>
>> >> Hello
>> >>
>> >> I have a problem with a Report Server installation.
>> >> The report server database is on another computer than the report
> server
>> >> frontend.
>> >> On the frontend Server SqlServer Service Pack2 was installed last
> night.
>> >> Now I can´t connect to the ReportServer Database.
>> >> How can I fix this problem, without creating a new Database?
>> >> I have tried to refresh the database but I get an error
>> >>
>> >>
>> >> thanx for any help
>> >>
>> >> regards
>> >>
>> >> Björn
>> >>
>> >>
>> >>
>> >
>>
>|||Is "My Reporting Database" really your database name? If so you've run into
a bug with the create/upgrade script.
If your database name has spaces in it the script doesn't put [] around the
database name for some reason so it blows off.
Follow these steps to generate a script:
1. Open the Reporting Services Configuration Tool
2. Click the "Database Setup" link on the left side.
3. Click the "Script" button.
4. In the window that opens select either "New Database Script" or "Upgrade
Database Script" on the left hand side.
5. If you are creating a new database follow these steps:
a. Enter the name of the database you would like to create in the box.
(The default is ReportServer)
b. Select the language in the second drop down box.
c. Specify the location where you want to save the created script. (this
defaults to the current users "My Documents" folder)
d. Click "OK".
6. If you are creating an "Upgrade" database script follow these steps:
a. Type in the name of the Reporting Services database that you want to
upgrade in the first box.
b. Select the version of the database you are upgrading from in the
second drop down box.
c. Select the save file location in the 3rd drop down box. (this
defaults to the current users "My Documents" folder)
d. Click "OK".
7. Go to the "My Documents" folder.
8. Open the created script file in the file editor of your choice.
9. You should see a line in the script that looks similar to this: USE
ReportServer[ReportServer] (This can vary depending on the name of your
ReportServer database)
10. Do a find and replace on the following:
Find: USE ReportServer[
Replace USE [
11. Replace all instances of this in the script.
12. Save the file.
13. Execute this script in a Query Window on the database where you want to
perform the operation.
If that is not your issue then make sure you hit "Connect" first and then
select the existing database in the drop down. If it is in the drop down
then the databases exists on the server.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Berger" <prodevteam@.gmx.net>
> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
<zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
<uPLCLcpBIHA.5980@.TK2MSFTNGP04.phx.gbl>
<oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl>
> Subject: Re: DataBase Problem
> Date: Fri, 5 Oct 2007 11:52:27 +0200
> Hey Chris
> Thank´s for your answer.
> If I do this I´ve got the question if I wish to update the databse.
> I say Yes and got an error in the SqlScript.
> The error is from type System.Data.SqlClient.SqlException.
> The error text: in sysdatabases no entry was found for "my Reporting
> Database".
> The Version of my reporting database is C.0.8.40
> thanks
> Björn
>
> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
> news:oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> > If the front end IIS Server does not have SP2 applied then it will not
> > connect to a SP2 backend ReportServer database. They both need to be at
> > the
> > same SP level.
> >
> > To check this do the following:
> > 1. open up the "Reporting Services Configuration Tool" and go to the
> > "Database Setup" section.
> > 2. Click "Connect" and connect to the backend database server you are
> > using.
> > 3. Make sure the credentials are specified correctly for the database
> > connection.
> > 4. Click the "Apply" Button.
> > If you get an error saying "Expected version C.0.8.43 but found
C.0.8.54'
> > or something similar that means that your versions are out of sync and
you
> > need to upgrade one or the other.
> >
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.|||Hey Chris
The script doesn´t work :(
Now I have tried to create a new report database.
I think I have a problem with the wmi provider.
In R.S. Configuration Tool I´ve got an error when I want to change to the
menu "Initialization"
the error text is something like: Error while changing scope. The reason can
be an error while calling WMI properties. Details: Reportserver can´t
connect to database
In windows eventviewer I´ve got the error 107 again.
Any ideas?
regards
Björn
"Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
news:NLAHUa1BIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> Is "My Reporting Database" really your database name? If so you've run
> into
> a bug with the create/upgrade script.
> If your database name has spaces in it the script doesn't put [] around
> the
> database name for some reason so it blows off.
> Follow these steps to generate a script:
> 1. Open the Reporting Services Configuration Tool
> 2. Click the "Database Setup" link on the left side.
> 3. Click the "Script" button.
> 4. In the window that opens select either "New Database Script" or
> "Upgrade
> Database Script" on the left hand side.
> 5. If you are creating a new database follow these steps:
> a. Enter the name of the database you would like to create in the box.
> (The default is ReportServer)
> b. Select the language in the second drop down box.
> c. Specify the location where you want to save the created script. (this
> defaults to the current users "My Documents" folder)
> d. Click "OK".
> 6. If you are creating an "Upgrade" database script follow these steps:
> a. Type in the name of the Reporting Services database that you want to
> upgrade in the first box.
> b. Select the version of the database you are upgrading from in the
> second drop down box.
> c. Select the save file location in the 3rd drop down box. (this
> defaults to the current users "My Documents" folder)
> d. Click "OK".
> 7. Go to the "My Documents" folder.
> 8. Open the created script file in the file editor of your choice.
> 9. You should see a line in the script that looks similar to this: USE
> ReportServer[ReportServer] (This can vary depending on the name of your
> ReportServer database)
> 10. Do a find and replace on the following:
> Find: USE ReportServer[
> Replace USE [
> 11. Replace all instances of this in the script.
> 12. Save the file.
> 13. Execute this script in a Query Window on the database where you want
> to
> perform the operation.
> If that is not your issue then make sure you hit "Connect" first and then
> select the existing database in the drop down. If it is in the drop down
> then the databases exists on the server.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> From: "Berger" <prodevteam@.gmx.net>
>> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
> <zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
> <uPLCLcpBIHA.5980@.TK2MSFTNGP04.phx.gbl>
> <oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl>
>> Subject: Re: DataBase Problem
>> Date: Fri, 5 Oct 2007 11:52:27 +0200
>> Hey Chris
>> Thank´s for your answer.
>> If I do this I´ve got the question if I wish to update the databse.
>> I say Yes and got an error in the SqlScript.
>> The error is from type System.Data.SqlClient.SqlException.
>> The error text: in sysdatabases no entry was found for "my Reporting
>> Database".
>> The Version of my reporting database is C.0.8.40
>> thanks
>> Björn
>>
>> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
>> news:oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl...
>> > If the front end IIS Server does not have SP2 applied then it will not
>> > connect to a SP2 backend ReportServer database. They both need to be at
>> > the
>> > same SP level.
>> >
>> > To check this do the following:
>> > 1. open up the "Reporting Services Configuration Tool" and go to the
>> > "Database Setup" section.
>> > 2. Click "Connect" and connect to the backend database server you are
>> > using.
>> > 3. Make sure the credentials are specified correctly for the database
>> > connection.
>> > 4. Click the "Apply" Button.
>> > If you get an error saying "Expected version C.0.8.43 but found
> C.0.8.54'
>> > or something similar that means that your versions are out of sync and
> you
>> > need to upgrade one or the other.
>> >
>> >
>> > --
>> > Chris Alton, Microsoft Corp.
>> > SQL Server Developer Support Engineer
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>|||You'll need to repair your database connection before you can finish the
initialization piece.
Get the script to work and then you should be able to get your database
connection functional. To finish the installation for SRS you shouldn't
need to go to the Initialization portion of the config tool.
Also, if the script failed what was the error when it failed?
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Berger" <prodevteam@.gmx.net>
> Hey Chris
> The script doesn´t work :(
> Now I have tried to create a new report database.
> I think I have a problem with the wmi provider.
> In R.S. Configuration Tool I´ve got an error when I want to change to the
> menu "Initialization"
> the error text is something like: Error while changing scope. The reason
can
> be an error while calling WMI properties. Details: Reportserver can´t
> connect to database
> In windows eventviewer I´ve got the error 107 again.
> Any ideas?
> regards
> Björn
> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
> news:NLAHUa1BIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> > Is "My Reporting Database" really your database name? If so you've run
> > into
> > a bug with the create/upgrade script.
> >
> > If your database name has spaces in it the script doesn't put [] around
> > the
> > database name for some reason so it blows off.
> >
> > Follow these steps to generate a script:
> > 1. Open the Reporting Services Configuration Tool
> > 2. Click the "Database Setup" link on the left side.
> > 3. Click the "Script" button.
> > 4. In the window that opens select either "New Database Script" or
> > "Upgrade
> > Database Script" on the left hand side.
> > 5. If you are creating a new database follow these steps:
> > a. Enter the name of the database you would like to create in the box.
> > (The default is ReportServer)
> > b. Select the language in the second drop down box.
> > c. Specify the location where you want to save the created script.
(this
> > defaults to the current users "My Documents" folder)
> > d. Click "OK".
> > 6. If you are creating an "Upgrade" database script follow these steps:
> > a. Type in the name of the Reporting Services database that you want
to
> > upgrade in the first box.
> > b. Select the version of the database you are upgrading from in the
> > second drop down box.
> > c. Select the save file location in the 3rd drop down box. (this
> > defaults to the current users "My Documents" folder)
> > d. Click "OK".
> > 7. Go to the "My Documents" folder.
> > 8. Open the created script file in the file editor of your choice.
> > 9. You should see a line in the script that looks similar to this: USE
> > ReportServer[ReportServer] (This can vary depending on the name of your
> > ReportServer database)
> > 10. Do a find and replace on the following:
> > Find: USE ReportServer[
> > Replace USE [
> > 11. Replace all instances of this in the script.
> > 12. Save the file.
> > 13. Execute this script in a Query Window on the database where you
want
> > to
> > perform the operation.
> >
> > If that is not your issue then make sure you hit "Connect" first and
then
> > select the existing database in the drop down. If it is in the drop down
> > then the databases exists on the server.
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > --
> >> From: "Berger" <prodevteam@.gmx.net>
> >> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
> > <zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
> > <uPLCLcpBIHA.5980@.TK2MSFTNGP04.phx.gbl>
> > <oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl>
> >> Subject: Re: DataBase Problem
> >> Date: Fri, 5 Oct 2007 11:52:27 +0200
> >>
> >> Hey Chris
> >>
> >> Thank´s for your answer.
> >>
> >> If I do this I´ve got the question if I wish to update the databse.
> >> I say Yes and got an error in the SqlScript.
> >> The error is from type System.Data.SqlClient.SqlException.
> >> The error text: in sysdatabases no entry was found for "my Reporting
> >> Database".
> >> The Version of my reporting database is C.0.8.40
> >>
> >> thanks
> >>
> >> Björn
> >>
> >>
> >> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im
Newsbeitrag
> >> news:oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> >> > If the front end IIS Server does not have SP2 applied then it will
not
> >> > connect to a SP2 backend ReportServer database. They both need to be
at
> >> > the
> >> > same SP level.
> >> >
> >> > To check this do the following:
> >> > 1. open up the "Reporting Services Configuration Tool" and go to the
> >> > "Database Setup" section.
> >> > 2. Click "Connect" and connect to the backend database server you are
> >> > using.
> >> > 3. Make sure the credentials are specified correctly for the database
> >> > connection.
> >> > 4. Click the "Apply" Button.
> >> > If you get an error saying "Expected version C.0.8.43 but found
> > C.0.8.54'
> >> > or something similar that means that your versions are out of sync
and
> > you
> >> > need to upgrade one or the other.
> >> >
> >> >
> >> > --
> >> > Chris Alton, Microsoft Corp.
> >> > SQL Server Developer Support Engineer
> >> > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >
>
>

Database ownership best practice

Does it matter much who owns a database? I'm reviewing security on our
SQL installation and find a number of different owners: sa, Domain
administrators and some others. I want to tidy this up. Is it best to
make an NT administrator the owner of all databases, assign a special
user which does nothing else but own databases?
Having looked at Books Online and elsewhere, I don't see any advice as
broad based as what I'm seeking.
Any suggestions would be welcome.I think it's mostly an audit trail, to see who created the database.|||Hi
Our corporate standard is that all objects must be owned by SA. If it owned
by a domain user, or a SQL Server login, if that user leaves you have to
change the ownership of the object before you can remove the user from the
DB/system.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||Like Mike Epprecht said, it's a good practice to use a standard SQL login as
the database owner since this will ensure the database owner account is
never orphaned. An orphaned database owner account can have side affects,
such as problems using sp_helpdb or using Enterprise Manager. If only
sysadmin role members can create database objects on your SQL Server, 'sa'
database ownership is probably ok. However, I'd like to add some additional
notes on the importance of the database owner.
The login associated with the database owner determines the mapping of the
database's 'dbo' user. 'dbo' has full permissions within the database so
the database owner login account needs to be appropriately secured.
Furthermore, when cross-database chaining is enabled (configurable option in
SQL 2000 SP3+ and always on in older SPs/versions), dbo-owned views, procs,
functions and triggers can access dbo-owned objects in other databases owned
by the same login even without user permissions on the referenced objects.
This is a major security concern with sa-owned databases because master is
also owned by 'sa' and is why one should enable cross-database chaining in
an sa-owned database when only sysadmin role members can create dbo-owned
objects. It is best to avoid turning on cross-database chaining unless
absolutely necessary.
If non-sysadmin role members can create dbo-owned database objects and you
must enable cross-database chaining, you should instead consider creating
and using a specialized SQL login for database ownership. See the Books
Online for more information on cross-database chaining.
Hope this helps.
Dan Guzman
SQL Server MVP
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||The owner info is of limited use from an audit perspective because the
database owner can be subsequently changed. See my response on security
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1123249058.756243.222770@.z14g2000cwz.googlegroups.com...
>I think it's mostly an audit trail, to see who created the database.
>|||Thanks for the advice. I liked the idea of a dedicated db owner, to
keep things modular, but the orphaned user concern is a good point.
I'll review those areas mentioned and go with sa as long as the
associated points are not a problem.