Description Metadata

When creating columns in MS-SQL there is a description field. How can I quer
y
this description field? I know how to return the Table Name, Column Name,
Data Type, Is_Nullable, etc. but I don’t know how to return the Descriptio
n.> When creating columns in MS-SQL there is a description field.
This is only ifyou create the columns in Enterprise Manager *and* choose to
use that property. Personally, I don't think you should do either, mostly
because your CREATE TABLE statements should be something that you can
script, modify, store in source control, etc.
In any case:
http://www.aspfaq.com/2244|||Aaron, good point! So where would you store the description of individual
columns? Would you create separate tables to store the descriptions of the
tables and the columns?|||> Aaron, good point! So where would you store the description of individual
> columns? Would you create separate tables to store the descriptions of the
> tables and the columns?
Why do you feel the description of your database schema needs to be stored
in the database?
We create separate human readable documentation. This allows us much more
freedom in how we are describing the schema, e.g. definitions > 64
characters, diagrams, tables, etc.
A|||I was thinking of creating a web app so that the descriptions, rules, etc.
can be documented, searched, and studied by several disparate groups. As of
now the database is completely undocumented. Then to complicate matters even
more, the knowledge of this database is scattered amongst many different
groups and individuals who won’t always work together. I keep running into
columns named TX5_RRT_SDescr and no one knows what the hell that column is
used for. The DBA is too busy to answer most questions and his answers are
too flipped to be of much use.
What form in your documentation in?|||Most of ours are in Word format. But you can make your job easier using an
extraction tool, for example Enterprise Architect.
Hey, if the DBA is too busy to answer questions that need to be answered,
why do you think he'd be willing to populate some table with the answers?
"Tome73" <Tome73@.discussions.microsoft.com> wrote in message
news:200AB1D7-AD01-426A-B580-7CE42716BBDE@.microsoft.com...
>I was thinking of creating a web app so that the descriptions, rules, etc.
> can be documented, searched, and studied by several disparate groups. As
> of
> now the database is completely undocumented. Then to complicate matters
> even
> more, the knowledge of this database is scattered amongst many different
> groups and individuals who won’t always work together. I keep running
> into
> columns named TX5_RRT_SDescr and no one knows what the hell that column is
> used for. The DBA is too busy to answer most questions and his answers are
> too flipped to be of much use.
> What form in your documentation in?|||Lol, he won’t! The rest of us peons will have to collaboratively piece thi
ngs
together. There are about 5 of us who have volunteered for this project and
I
just need to figure out how to best do it. I have a zero budget to work with
.
Ok then your suggestion is not to store the description in database
properties. Thanks.
The answer to my original post is:
Where Table name is ‘Admin_Users’ and the column name is ‘User_ID’
SELECT name, CONVERT(varchar(2000), [value]) AS Description
FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
'admin_Users', 'Column', 'User_ID')|||> The answer to my original post is:
> Where Table name is ‘Admin_Users’ and the column name is ‘User_ID’
> SELECT name, CONVERT(varchar(2000), [value]) AS Description
> FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
> 'admin_Users', 'Column', 'User_ID')
Yep, I posted a link to code samples earlier:
http://www.aspfaq.com/2244|||You could store your meta data in SQL Server tables and then develop your
own custom GUI and reports around it, but there are many 3rd party
applications would do a better job.
Tools like ERWin, Viseo (or even Enterprise Managers Diagramer) can be used
to create annotated diagrams of the database model
http://www.databaseanswers.com/modelling_tools.htm
Reporting Services 2005 has a Model Designer that provides end users with a
high level view of the database model for use with Report Builder.
http://www.devx.com/dbzone/Article/28047/1954?pf=true.
"Tome73" <Tome73@.discussions.microsoft.com> wrote in message
news:200AB1D7-AD01-426A-B580-7CE42716BBDE@.microsoft.com...
>I was thinking of creating a web app so that the descriptions, rules, etc.
> can be documented, searched, and studied by several disparate groups. As
> of
> now the database is completely undocumented. Then to complicate matters
> even
> more, the knowledge of this database is scattered amongst many different
> groups and individuals who won't always work together. I keep running into
> columns named TX5_RRT_SDescr and no one knows what the hell that column is
> used for. The DBA is too busy to answer most questions and his answers are
> too flipped to be of much use.
> What form in your documentation in?|||Aaron, yes you did and I missed it at first. I found
http://developer.com/db/article.php/3361751 which is basically the same.
Hey thanks Aaron for all your help and insight.sql

Deployment 'utility' script using sqlcmd

Hi all,

I'm looking at creating a sample utility script that will invoking

scripts to deploy some SQL code. For example, a utlity script that will

run a SQL script, and on successful completion, execute the next script.

Having not used SQLCMD at all before, and being very new to SQL2005

(< 1 month) please guide me if there is a better way of invoking

this... For example, a way of avoiding the xp_cmdshell invocation!

The following code invokes a script, but I'm trying to find a way of

getting a return code back from sqlcmd, so I can progress and do the

next, or fail if the return code <> 0 (success).

[code]

--Process to create DB, Tables, and Stored Procedures

set nocount on

DECLARE

@.Error int,

@.ExecCommand varchar(512),

@.FullFilePath varchar(255)

--create the database

BEGIN TRY

SET @.FullFilePath =

'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF

Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB

TescoDNF_SSISPackageManager.sql'

SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '

SELECT @.FullFilePath

UNION

SELECT @.ExecCommand

EXEC (@.ExecCommand)

SELECT @.@.ERROR

SELECT @.Error

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

GOTO ErrorAbort

END CATCH
ErrorAbort:

[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set

of SQL scripts to, for example, a server. For example, some pseudo-code

Create Database

If Error abort

Create Table1

If Error abort

Create Table2

If Error Abort

Create Stored Procedure1

If Error Abort

ELSE Complete and report success

I do agree that this is something that could be (better) done in a

"proper" coding language like .Net, c# etc. but (a) it's just a simple

utility script (b) it teaches me moore about usage of SQLCMD and (c) I

do not have any skill in any normal programming language, hence I was

planning to write a quick deployment utility with a script.

The idea might be something as ugly as a table structure that has a

list of scripts registered in it, with some sequence logic - like

creating parent tables before children tables - and then a cursor (or a

better method if I can find it) that fetches a sqlcmd filename

execution command, executes it, and on success fetches the next one

based on the sequence logic.

I can probably do all of that in about 4 hours in T-SQL, if I can find

a way to confirm the successful execution of the previous command....|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files

and execute them, and not merge them all into a single large script.

So, I want utility script to do this:

Run external sql script

On failure abort, on Success

Run external sql script

On Failure abort, On Success

...

You being to see why I referred to a cursor?

The point is that the utility script wouldn't contain any of the client

SQL commands - it would fetch them by referring to the table, and

fetching the path to the SQL file, and building a SQLCMD to execute

that script

I guess, as you say, I could add a generic update ##SQLScriptTracker

table, then check it on the new execution, or abort. I had hoped for a

neater solution - i.e. SQLCMD being able to return a returncode that it

gets from a SQL file it ran....|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as

well, since with the batch file I have to add a file each time.

Thanks|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

Deployment 'utility' script using sqlcmd

Hi all,
I'm looking at creating a sample utility script that will invoking scripts to deploy some SQL code. For example, a utlity script that will run a SQL script, and on successful completion, execute the next script.
Having not used SQLCMD at all before, and being very new to SQL2005 (< 1 month) please guide me if there is a better way of invoking this... For example, a way of avoiding the xp_cmdshell invocation!
The following code invokes a script, but I'm trying to find a way of getting a return code back from sqlcmd, so I can progress and do the next, or fail if the return code <> 0 (success).
[code]
--Process to create DB, Tables, and Stored Procedures
set nocount on
DECLARE
@.Error int,
@.ExecCommand varchar(512),
@.FullFilePath varchar(255)
--create the database
BEGIN TRY
SET @.FullFilePath = 'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB TescoDNF_SSISPackageManager.sql'
SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '
SELECT @.FullFilePath
UNION
SELECT @.ExecCommand
EXEC (@.ExecCommand)
SELECT @.@.ERROR
SELECT @.Error
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GOTO ErrorAbort
END CATCH

ErrorAbort:
[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set of SQL scripts to, for example, a server. For example, some pseudo-code
Create Database
If Error abort
Create Table1
If Error abort
Create Table2
If Error Abort
Create Stored Procedure1
If Error Abort
ELSE Complete and report success
I do agree that this is something that could be (better) done in a "proper" coding language like .Net, c# etc. but (a) it's just a simple utility script (b) it teaches me moore about usage of SQLCMD and (c) I do not have any skill in any normal programming language, hence I was planning to write a quick deployment utility with a script.
The idea might be something as ugly as a table structure that has a list of scripts registered in it, with some sequence logic - like creating parent tables before children tables - and then a cursor (or a better method if I can find it) that fetches a sqlcmd filename execution command, executes it, and on success fetches the next one based on the sequence logic.
I can probably do all of that in about 4 hours in T-SQL, if I can find a way to confirm the successful execution of the previous command....
|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files and execute them, and not merge them all into a single large script.
So, I want utility script to do this:
Run external sql script
On failure abort, on Success
Run external sql script
On Failure abort, On Success
...
You being to see why I referred to a cursor?
The point is that the utility script wouldn't contain any of the client SQL commands - it would fetch them by referring to the table, and fetching the path to the SQL file, and building a SQLCMD to execute that script
I guess, as you say, I could add a generic update ##SQLScriptTracker table, then check it on the new execution, or abort. I had hoped for a neater solution - i.e. SQLCMD being able to return a returncode that it gets from a SQL file it ran....
|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as well, since with the batch file I have to add a file each time.
Thanks
|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

sql

Deployment for a small office

Hi,
I am creating a shrinkwrap winapp using vb.net which uses MSDE for backend. I have used the MSDE deployment toolkit to deploy MSDE along with the app. Everything works fine when the db and the app are on the same machine. I just install MSDE with named
instance for my company, create database and start the service. Even backup/restore work fine.
Now, my confusion is to how to make all this work for a small office where the setup will be like this (For e.g.):
1> 4 pcs (win xp) where my app will be installed to be used by 4 different users.
2> 1 central pc A (can be win xp) where the database is.
3> All 4 users want to share my database which is on the central pc A.
So, my concerns are:
1> How will the setup and deployment change.
2> Will the MSDE deployment toolkit still work.
3> Will I need to install myapp + MSDE on client's machine or just myapp.
4> What will I have to install on the central pc A.
5> Right now my connection string is like this:
data source=(local)\MyInstanceName;initial catalog=MyDB; User id =MyUser; Password=MyPassword
How will this change when the Db is on the central machine A.
6> If I use DMO, then what extra will I need to install on the 4 pcs and the central machine A.
Thanks
dev
hi dev_kh,
"dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
news:9D43D477-50C4-47DE-8174-CE196F5BD683@.microsoft.com...
> ...
> So, my concerns are:
> 1> How will the setup and deployment change.
your setup shoul'd ask for MSDE installation, app installation or both...
you can even install MSDE on all client PCs, but they will not be used, or
shall not..

> 2> Will the MSDE deployment toolkit still work.
don't know.. never used :-(

> 3> Will I need to install myapp + MSDE on client's machine or just myapp.
see >1>

> 4> What will I have to install on the central pc A.
it depends... MSDE for sure, but il the server will actually works as a user
client too [as usal for small offices :-( ] you have to install your app
too..

> 5> Right now my connection string is like this:
> data source=(local)\MyInstanceName;initial catalog=MyDB; User id =MyUser;
>Password=MyPassword
> How will this change when the Db is on the central machine A.
data source=ServerComputerName\MyInstanceName;initial catalog=MyDB; User id
=MyUser; Password=MyPassword

> 6> If I use DMO, then what extra will I need to install on the 4 pcs and
the central machine A.
con the server PC you have to install nothing, as SQL-DMO is included in
MSDE package...
on all 4 client PCs, you have to install SQL-DMO component and
dependencies...
the complete dependency list is as above:
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\odbcbcp.dll; DestDir: WinSys ; sharedfile
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\sqlwoa.dll ; DestDir: WinSys
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\sqlwid.dll ; DestDir: WinSys
...\Programmi\Microsoft SQL Server\80\Tools\Binn\w95scm.dll; DestDir:
DestinationFolder\Binn
...\WINDOWS\SYSTEM\sqlunirl.dll ; DestDir: WinSys
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlresld.dll; DestDir:
DestinationFolder\Binn
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll; DestDir:
DestinationFolder\Binn
; not licensed by redist.txt but available after installation of MDAC2.6
...\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\sqlsvc.RLL;
DestDir: DestinationFolder\Binn\Resources\1033
; not licensed by redist.txt but available after installation of MDAC2.6
...\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\Sqldmo.rll;
DestDir: DestinationFolder\Binn\Resources\1033
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll; DestDir:
DestinationFolder\Binn ; file to be registered via regserver
DestinationFolder can either be the installation directory of one instance
of Microsoft SqlServer 2000, like ..\Program Files\Microsoft SQL
Server\80\Tools, even if no istance of SQL Server has been installed, or the
installation directory of your app. Please do respect the hierarchy
\Binn\Resources\1033 (where 1033 specifies the language), where needed, in
order to grant correct functionality of Ole-Automation objects.
In order to install SQL-DMO components for MSDE 2000, Microsoft Internet
Explorer 5.5 or higher is required.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||See comments inline.
"dev_kh" <devkh@.discussions.microsoft.com> wrote in message
news:9D43D477-50C4-47DE-8174-CE196F5BD683@.microsoft.com...
> Hi,
> I am creating a shrinkwrap winapp using vb.net which uses MSDE for
backend. I have used the MSDE deployment toolkit to deploy MSDE along with
the app. Everything works fine when the db and the app are on the same
machine. I just install MSDE with named instance for my company, create
database and start the service. Even backup/restore work fine.
> Now, my confusion is to how to make all this work for a small office where
the setup will be like this (For e.g.):
> 1> 4 pcs (win xp) where my app will be installed to be used by 4 different
users.
> 2> 1 central pc A (can be win xp) where the database is.
> 3> All 4 users want to share my database which is on the central pc A.
> So, my concerns are:
> 1> How will the setup and deployment change.
I'd prefer seperate installations of MSDE and app to put them in a single
setup procedure, unless the use who does the setup knows nothing but
double-clicking "setup.exe", since most likely, MSDE will be installed on
only one computer on the network while app will be installed on all
computers (of course it may also places on a network share and all computers
only get a shortcut to run it).

> 2> Will the MSDE deployment toolkit still work.
See above.

> 3> Will I need to install myapp + MSDE on client's machine or just myapp.
Install MSED on computer A only. as for app, you can choose install on all
client computers or place it on a sinle network share location. Now that it
is .NET app, of course yu have to make sure all computer has .NET framework
installed; amd also do some security configuration, since app loaded from
other computer is now allowed to run by default, you need to give it
permission to run: go to control panel->MS .NET framework confuguration to
allow the app loaed from other computer to run.
When I put my app in use, very often I know there are quite some bugs
undetected and some fixes are required soon or later, place the app in a
network share allows me just replace file(s) in a single location.

> 4> What will I have to install on the central pc A.
Nothing but MSDE.

> 5> Right now my connection string is like this:
> data source=(local)\MyInstanceName;initial catalog=MyDB; User id =MyUser;
Password=MyPassword
Just change "data source" to computer A's name: "data
source=computerAName\MSDEInstanceName;Initial..."

> How will this change when the Db is on the central machine A.
> 6> If I use DMO, then what extra will I need to install on the 4 pcs and
the central machine A.
You do not need install anything, unless your app uses DMO. DMO is installed
automatically with MSDE. Since MSDE is only installed on one computer, if
your app do require DMO to run, the get DMO on client computer without
install MSDE is quite tricky.
You probably use DMO for a few MSDE management task, like backup/restore.
I'd suggest you seperate this kind of task from your app into a single MSDE
app, which is only installed on the computer A. You do not want every user
to perform MSDE management task, do you?

> Thanks
> dev
|||Great,
So basically, in a small networked office setup I described,I will:
1> Install myapp on all 4 machines
2> Install MSDE on the centralserver A
3> Connection string will point to the machine A for datasource
But what about:
1> If I need to start the sqlserver service on clients machine, for e.g. to backup/restore database. How will I do it. Currently I use the following code to create the service controller and then start it if needed:
svc = New ServiceController("MSSQL$MyInstanceName)
What will I do when the service is on the central machine. How to start the service using the above code.
(See my clients will not have a dba and are assumed to not know that there is anything related to sql server on their machines, so we are providing the UI for backup/restore etc and the db functionality is kept as hidden as possible from them, this UI wil
l be in the tools on my vb.net app)
2> Will I need to run any network config utilities on the client machine. I know I will have to set disablenetworkprotocols to false on the server, but is there anything related to network access to be done on the client.
3> Andrea sometime back you mentioned something like, the location to backup should be from the MSDE machine's point of view.. so if PC1 wants to backup the db on it's D:\Backup drive, then what change will I have to do to this code below(which works fine
for a machine which has msde installed)
'--
cn = New SqlConnection(mMasterConnString)
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE " & mDBName & " TO DISK = '" & bkpFileWithPath & "' WITH NAME = My Backup', INIT"
.ExecuteNonQuery()
End With
'Note: bkpFileWithPath argument is: "D:\Backup\MyDB.bak"
'---
4> I guess I will take DMO slowly..
Thanks a lot guys. Hey Andrea do you have a blog?
dev_kh
"Norman Yuan" wrote:

> See comments inline.
> "dev_kh" <devkh@.discussions.microsoft.com> wrote in message
> news:9D43D477-50C4-47DE-8174-CE196F5BD683@.microsoft.com...
> backend. I have used the MSDE deployment toolkit to deploy MSDE along with
> the app. Everything works fine when the db and the app are on the same
> machine. I just install MSDE with named instance for my company, create
> database and start the service. Even backup/restore work fine.
> the setup will be like this (For e.g.):
> users.
> I'd prefer seperate installations of MSDE and app to put them in a single
> setup procedure, unless the use who does the setup knows nothing but
> double-clicking "setup.exe", since most likely, MSDE will be installed on
> only one computer on the network while app will be installed on all
> computers (of course it may also places on a network share and all computers
> only get a shortcut to run it).
>
> See above.
>
> Install MSED on computer A only. as for app, you can choose install on all
> client computers or place it on a sinle network share location. Now that it
> is .NET app, of course yu have to make sure all computer has .NET framework
> installed; amd also do some security configuration, since app loaded from
> other computer is now allowed to run by default, you need to give it
> permission to run: go to control panel->MS .NET framework confuguration to
> allow the app loaed from other computer to run.
> When I put my app in use, very often I know there are quite some bugs
> undetected and some fixes are required soon or later, place the app in a
> network share allows me just replace file(s) in a single location.
>
> Nothing but MSDE.
> Password=MyPassword
> Just change "data source" to computer A's name: "data
> source=computerAName\MSDEInstanceName;Initial..."
> the central machine A.
> You do not need install anything, unless your app uses DMO. DMO is installed
> automatically with MSDE. Since MSDE is only installed on one computer, if
> your app do require DMO to run, the get DMO on client computer without
> install MSDE is quite tricky.
> You probably use DMO for a few MSDE management task, like backup/restore.
> I'd suggest you seperate this kind of task from your app into a single MSDE
> app, which is only installed on the computer A. You do not want every user
> to perform MSDE management task, do you?
>
>
>

Deployment Error

After creating a my project which has several dts packages.

I want to deploy and place them in different sql servers to run automatically.

I do the following:

After building my project.I select project ->properties ->deployment utility->i set create deployment to true.

Then within deployment folder i double click .projectdeploymentmanifest file

where package installation i select sql server instead of file system.

and i get the following error.

TITLE: Package Installation Wizard

Could not save the package "C:\Program Files\Microsoft SQL Server\SSIS\bin\Deployment\Procedure.dtsx" to SQL Server "SQL-DEV".

ADDITIONAL INFORMATION:

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection). The SQL statement that was issued has failed.

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection). The SQL statement that was issued has failed.

BUTTONS:

OK

It would be great if some one guides me with this.Is this anything to deal with security.please let me know

Open up Management Studio and try to connect to "Integration Services" on SQL-DEV.|||

It says Access Denied.

Do i need to be under sysadmin role to do this?

Please let me know.

Thanks

|||

See this link from my blog:

http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/

|||

I have worked with the steps from this site.I still have the same problem.

Access denied when i connect to integration services on a particular server from i local machine.

Do i have any other options?

|||The answers are all right there, but need to be done on the server, not the local machine.|||

Thanks,I was working on my local machine instead of server.

Deploying a Database

Hi,
I am creating an application that will use msde, and I would like to know
how to create a sql server in the server application in a straight forward
way to the final user?
any tutorials are appreciated
Thanks in advance
hi Diogo,
Diogo Alves - Software Developer wrote:
> Hi,
> I am creating an application that will use msde, and I would like to
> know how to create a sql server in the server application in a
> straight forward way to the final user?
>
as regard the engine distribution, you can perhaps have a look at
http://www.microsoft.com/downloads/d...displaylang=en ,
a toolkit by Microsoft I'm not confortable with, as I usually use a
companion home built application that provides a visual interface to the
standard MSDE setup.exe boostrap installer, where all desired parameters can
be visually edited by the end user, then "shelling" to the actual installer
providing all parameters...
as regard database deployment, you can have a look at
http://msdn.microsoft.com/msdnmag/is...aseinstaller/, the
best article I know about that stuff, supporting versioning, updates and the
like..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

deploy SQL Server 2000

I have read a very good article, "Creating and Deploying Access
Solutions with the Microsoft Data Engine" by Scott Smith, published in
January 1999. Now i wish to do the same thing, I mean install sql
server 2000, at the end of an installation.
I think I have to change the line:
start /wait msdex86.exe -s -a -f1 "sql70ins.iss"
, which "extract and install MSDE on the user's machine".
But I still coudn't find what to use instead of it.
Thanks !
RaduHave a look here :- http://www.microsoft.com/sql/msde/
--
HTH
Ryan Waight, MCDBA, MCSE
"Radu Cernea" <r_cernea@.yahoo.com> wrote in message
news:6ff3b57a.0311100635.c0d963@.posting.google.com...
> I have read a very good article, "Creating and Deploying Access
> Solutions with the Microsoft Data Engine" by Scott Smith, published in
> January 1999. Now i wish to do the same thing, I mean install sql
> server 2000, at the end of an installation.
> I think I have to change the line:
> start /wait msdex86.exe -s -a -f1 "sql70ins.iss"
> , which "extract and install MSDE on the user's machine".
> But I still coudn't find what to use instead of it.
> Thanks !
> Radu

deny permission to create temporary tables

Dear All,

This is my first post to this forum.

I would like to know if there is any way to restrict users from creating temp tables.

Problem: I am facing problems with lots of temporary objects getting created in my database. The users have read-only access to the database for adhoc-querying purpose through QA. Yet they are able to create temporary tables in tempdb database taking lot of resources on tempdb disk causing abnormally high growth of tempdb.

Thanks in advance.

Best Regards,

Chetan Jain

Just by executing a query, users may be using space in TempDb -that is what it is designed for. Query execution may, totally on its own volition, create temporary tables in TempDb. TempDb can growth large if the users are executing queries that require a lot of temporary storage to gather data to work with -JOINs with large resultsets, large resultsets to be sorted, etc.

Are the users creating tables 'temp' tables (starting with [#], or [##]? Or, are they creating tables?

Normally, the users' temp objects are removed from TempDb when the user connection is closed.

Perhaps the real issue is trying to determine how much space TempDb requires in order to support your users query needs, and then giving TempDb adaquate disk space.

|||

You can not stop any user from creating temprory objects in tempdb. ofcourse you can stop creating permanent table in tempdb by DDL trigger. but DDL trigger can not sense temp table.

create TRIGGER db_trg_RestrictTableChanges

ON DATABASE

FOR CREATE_Table, ALTER_Table, DROP_Table

AS

SET NOCOUNT ON

rollback

the above mentioned trigger will stop creating permanent tables in tempdb but even this can not stop temporary table

Madhu

|||Madhu's suggestion is certainly a valid one, but, unfortunately, only works in SQL 2005 (and higher).|||

Thanks for the information!. The real issue is denying explicit statements like "create table #temp" or "create table ##temp"

Best Regards,

Chetan

|||

As Madhu indicated, you can't even deny creating temp tables in SQL 2005 using the new DDL Triggers -and you also can't do so in SQL 2000.

Just make sure that the users are logging out, and then their connection will be cleared, and the space used for any temp tables will be released.