RSS Text Data Mining: February 2012

Saturday, February 25, 2012

Run a DTS Package "in the background" or with low priority?

Configuration:
Windows 2000
Sql Server 2000
Quad CPU
Problem:
I have a DTS update operation that takes many hours to run. The
process is monthly, and for that process speed is not an issue; however,
the database server hosts multiple databases, some with applications
that do interactive queries. The DTS Update step brings down the speed
of interactive queries on the database being updated and all other
databases dramatically.
Is there any way to lower the priority of a DTS package operation so
that it does not impact interactive queries on that databases and other
databases running on the server?
Basically, can I run a DTS package "in the background" or with a very
low priority?
Hi
You may want to read The Guru's Guide to SQL Server Architecture and
Internals by Ken Henderson ISDN 0-201-70047-6 which goes into depth on
thread scheduling. You may get some benefit by setting the MAXDOP query hint
on some of your statements. Highlighting what part of your process is slow
and why it is slow may help you to provide a more efficient solution.
You may also want to review the architecture of this process and either
split it into smaller independent chunks that can be run separately, or if
the data created by this process is read-only to the other processes, then
you may want to look at doing it offline and then swapping it in when the
process is complete e.g. having it all in a separate read-only database.
John
"John Bailo" <jabailo@.texeme.com> wrote in message
news:gsudneQq3ZlEYAzeRVn-uw@.speakeasy.net...
> Configuration:
> Windows 2000
> Sql Server 2000
> Quad CPU
>
> Problem:
> I have a DTS update operation that takes many hours to run. The process
> is monthly, and for that process speed is not an issue; however,
> the database server hosts multiple databases, some with applications that
> do interactive queries. The DTS Update step brings down the speed of
> interactive queries on the database being updated and all other databases
> dramatically.
> Is there any way to lower the priority of a DTS package operation so that
> it does not impact interactive queries on that databases and other
> databases running on the server?
> Basically, can I run a DTS package "in the background" or with a very low
> priority?
>
>

Run a DTS Package "in the background" or with low priority?

Configuration:
Windows 2000
Sql Server 2000
Quad CPU
Problem:
I have a DTS update operation that takes many hours to run. The
process is monthly, and for that process speed is not an issue; however,
the database server hosts multiple databases, some with applications
that do interactive queries. The DTS Update step brings down the speed
of interactive queries on the database being updated and all other
databases dramatically.
Is there any way to lower the priority of a DTS package operation so
that it does not impact interactive queries on that databases and other
databases running on the server?
Basically, can I run a DTS package "in the background" or with a very
low priority?Hi
You may want to read The Guru's Guide to SQL Server Architecture and
Internals by Ken Henderson ISDN 0-201-70047-6 which goes into depth on
thread scheduling. You may get some benefit by setting the MAXDOP query hint
on some of your statements. Highlighting what part of your process is slow
and why it is slow may help you to provide a more efficient solution.
You may also want to review the architecture of this process and either
split it into smaller independent chunks that can be run separately, or if
the data created by this process is read-only to the other processes, then
you may want to look at doing it offline and then swapping it in when the
process is complete e.g. having it all in a separate read-only database.
John
"John Bailo" <jabailo@.texeme.com> wrote in message
news:gsudneQq3ZlEYAzeRVn-uw@.speakeasy.net...
> Configuration:
> Windows 2000
> Sql Server 2000
> Quad CPU
>
> Problem:
> I have a DTS update operation that takes many hours to run. The process
> is monthly, and for that process speed is not an issue; however,
> the database server hosts multiple databases, some with applications that
> do interactive queries. The DTS Update step brings down the speed of
> interactive queries on the database being updated and all other databases
> dramatically.
> Is there any way to lower the priority of a DTS package operation so that
> it does not impact interactive queries on that databases and other
> databases running on the server?
> Basically, can I run a DTS package "in the background" or with a very low
> priority?
>
>

Run a DTS Package "in the background" or with low priority?

Configuration:
Windows 2000
Sql Server 2000
Quad CPU
Problem:
I have a DTS update operation that takes many hours to run. The
process is monthly, and for that process speed is not an issue; however,
the database server hosts multiple databases, some with applications
that do interactive queries. The DTS Update step brings down the speed
of interactive queries on the database being updated and all other
databases dramatically.
Is there any way to lower the priority of a DTS package operation so
that it does not impact interactive queries on that databases and other
databases running on the server?
Basically, can I run a DTS package "in the background" or with a very
low priority?Hi
You may want to read The Guru's Guide to SQL Server Architecture and
Internals by Ken Henderson ISDN 0-201-70047-6 which goes into depth on
thread scheduling. You may get some benefit by setting the MAXDOP query hint
on some of your statements. Highlighting what part of your process is slow
and why it is slow may help you to provide a more efficient solution.
You may also want to review the architecture of this process and either
split it into smaller independent chunks that can be run separately, or if
the data created by this process is read-only to the other processes, then
you may want to look at doing it offline and then swapping it in when the
process is complete e.g. having it all in a separate read-only database.
John
"John Bailo" <jabailo@.texeme.com> wrote in message
news:gsudneQq3ZlEYAzeRVn-uw@.speakeasy.net...
> Configuration:
> Windows 2000
> Sql Server 2000
> Quad CPU
>
> Problem:
> I have a DTS update operation that takes many hours to run. The process
> is monthly, and for that process speed is not an issue; however,
> the database server hosts multiple databases, some with applications that
> do interactive queries. The DTS Update step brings down the speed of
> interactive queries on the database being updated and all other databases
> dramatically.
> Is there any way to lower the priority of a DTS package operation so that
> it does not impact interactive queries on that databases and other
> databases running on the server?
> Basically, can I run a DTS package "in the background" or with a very low
> priority?
>
>

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Now, I login the SQL Server with a network user MyUser and run the package manually and I see the excel file is created in the remote server. I want to do the same thing with a SQL Agent Job task. I created a stored procedure and put my DTSRun all in it as follows;

dtsrun /S MyServer /U MyUSer /P MyPass /N MyDTS . I also create the job and make the owner and “RunAs” MyUser and run the task I get the following error. Apparently since MyUser is a network user, it could not login to SQL Server as I defined in my DTSRun. Is there any way I can get this DTS run under that user without changing SQL Agent user which is running under a system user.

Error:

DTSRun:Loading...

Error:-2147217843 (80040E4D); Provider Error:18456 (4818)

Error string:Login failed for user ‘MyUSer’.

Error source:Microsoft OLE DB Provider for SQL Server

Help file:

Help context:0

(null)

First make a login for your MyUser windows account and assign it to the sysadmin role - make sure that MyUser is a local admin on the server that SQL is running on. Then create a Proxy account for the SQL Server (right click on the agent and select properties, then go the the Job System) use MyUser as the proxy account. Look at SQL Server Agent Properties (Job System Tab) on BOL for more information.

Brad

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Error:

DTSRun: Loading...

Error: -2147217843 (80040E4D); Provider Error: 18456 (4818)

Error string: Login failed for user ‘MyUSer’.

Error source: Microsoft OLE DB Provider for SQL Server

Help file:

Help context: 0

(null)

No but you can clone your account to run the Agent with the proxy account, try the thread below for details. If it did not run start over it works. Hope this helps.

http://forums.asp.net/thread/1358665.aspx

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Error:

DTSRun:Loading...

Error:-2147217843 (80040E4D); Provider Error:18456 (4818)

Error string:Login failed for user ‘MyUSer’.

Error source:Microsoft OLE DB Provider for SQL Server

Help file:

Help context:0

(null)

Brad

run a dos command?

Hi all,
I am just wondering is there a way to run a dos command in the stored procedure? Please fill me in on how to go about doing if is there is a way. If this is not the correct forum to post this question, please let me know. Thanks in advance.
Daren

Hi faren,

you can open up a shell with XP_cmdshell and execute commands via passing the command as a paramter:

EXEC MASTER.XP_CMDSHELL 'dir C:\'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

run a .sql in different databases

I want to know if it is posible to run one query file in different databases

right now I have 2 different .sql and 6 databases and I want to run it in the following order

queries1.sql on db1
quieries2.sql on db2
quieries1.sql on db3
queries2.sql on db4
quieries1.sql on db5
queries2.sql on db6

all this just in one execution and they cannot run at the same time

Check the utility SQLCMD.EXE in BOL, or OSQL.EXE if you are using SS 2000.

AMB

|||im using MSSQL server 2005
|||

Relatively simple using a SQLCmd.exe script.

Refer to Books Online for the complete syntax, description, and samples.

|||i found this

Code Snippet

Create a folder named MyFolder. Use the File/Save As menu to save the above script as a file named MyScript.sql in the folder C:\MyFolder. Run the following command from the command prompt to run the script and place the output in a file named MyOutput.txt in the same folder:

sqlcmd -i C:\MyFolder\MyScript.sql -o C:\MyFolder\MyOutput.txt

When you view the contents of MyOutput.txt in Notepad, you will see the following:

Changed database context to 'AdventureWorks'.
ContactID FirstName LastName
-- -- --
1 Syed Abbas
2 Catherine Abel
3 Kim Abercrombie
4 Humberto Acevedo
5 Pilar Ackerman

(5 rows affected)

but how can I say to it, run myscript.sql in the database db1 for example
|||

Check BOL for more info about SQLCMD.EXE parameters.

-d use database name

AMB

|||you mean something like this?

sqlcmd -i C:\MyFolder\MyScript.sql -d db1 -o C:\MyFolder\MyOutput.txt
|||

Something I've done to execute a script against multiple databases is a DOS call.

Command Line Reference

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ntcmds_o.mspx?mfr=true

for /F ["usebackqParsingKeywords"] {%% | %}variable in (`command`) do command [CommandLineOptions]

Here we go

for /F %a in ('select [name] from sysdatabases') do isql /E /i sql_script /d %a

The sqlcmd call is not much different than isql. I've also used this method to execute a script against multiple database servers. It can get a bit ugly, but it does the trick.

Good luck,

Bruce.

run a .sql in different databases

I want to know if it is posible to run one query file in different databases

right now I have 2 different .sql and 6 databases and I want to run it in the following order

queries1.sql on db1
quieries2.sql on db2
quieries1.sql on db3
queries2.sql on db4
quieries1.sql on db5
queries2.sql on db6

all this just in one execution and they cannot run at the same time

Check the utility SQLCMD.EXE in BOL, or OSQL.EXE if you are using SS 2000.

AMB

|||im using MSSQL server 2005
|||

Relatively simple using a SQLCmd.exe script.

Refer to Books Online for the complete syntax, description, and samples.

|||i found this

Code Snippet

sqlcmd -i C:\MyFolder\MyScript.sql -o C:\MyFolder\MyOutput.txt

When you view the contents of MyOutput.txt in Notepad, you will see the following:

Changed database context to 'AdventureWorks'.
ContactID FirstName LastName
-- -- --
1 Syed Abbas
2 Catherine Abel
3 Kim Abercrombie
4 Humberto Acevedo
5 Pilar Ackerman

(5 rows affected)

but how can I say to it, run myscript.sql in the database db1 for example
|||

Check BOL for more info about SQLCMD.EXE parameters.

-d use database name

AMB

|||you mean something like this?

sqlcmd -i C:\MyFolder\MyScript.sql -d db1 -o C:\MyFolder\MyOutput.txt
|||

Something I've done to execute a script against multiple databases is a DOS call.

Command Line Reference

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ntcmds_o.mspx?mfr=true

for /F ["usebackqParsingKeywords"] {%% | %}variable in (`command`) do command [CommandLineOptions]

Here we go

for /F %a in ('select [name] from sysdatabases') do isql /E /i sql_script /d %a

The sqlcmd call is not much different than isql. I've also used this method to execute a script against multiple database servers. It can get a bit ugly, but it does the trick.

Good luck,

Bruce.

Run a DTS Win32 Process Task hidden

Anyone?
I have a picky client who does not want to see the command window open when a batch file is called from DTS.
Can anyone help me in running it hidden?If you are using a batch file, then the dos window will always open up, at least for a short instant, and there is no way that you can avoid that.

To avoid lookup of commands used then use @.ECHO OFF at the beginning of the batch file.|||That's what I was afrain of.

Originally posted by Satya
If you are using a batch file, then the dos window will always open up, at least for a short instant, and there is no way that you can avoid that.

To avoid lookup of commands used then use @.ECHO OFF at the beginning of the batch file.

run 3 dynamic selects from stored proc

I am trying to run 3 dynamic selects from stored proc, really only
the table name is dynamic.. Anway I'm kinda lost on how I can
accomplish this.. this is what I have but it only returns the first
result.. that being basic

CREATE PROCEDURE email_complexity

@.TableName VarChar(100)

AS
Declare @.SQL VarChar(1000)
Declare @.SQL1 VarChar(1000)

Set nocount on

SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'

Exec ( @.SQL)

SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
SELECT @.SQL1 = @.SQL1 + @.TableName
SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'

Exec ( @.SQL1)

Return

Is there a better way of doing this??

tia

DaveOn 30 Sep 2004 10:12:28 -0700, dave wrote:

> I am trying to run 3 dynamic selects from stored proc, really only
> the table name is dynamic.. Anway I'm kinda lost on how I can
> accomplish this.. this is what I have but it only returns the first
> result.. that being basic
> CREATE PROCEDURE email_complexity
> @.TableName VarChar(100)
> AS
> Declare @.SQL VarChar(1000)
> Declare @.SQL1 VarChar(1000)
> Set nocount on
> SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> SELECT @.SQL = @.SQL + @.TableName
> SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> Exec ( @.SQL)
> SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> SELECT @.SQL1 = @.SQL1 + @.TableName
> SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> Exec ( @.SQL1)
>
> Return
> Is there a better way of doing this??
> tia
> Dave

If your client isn't prepared to accept multiple resultsets, then you'll
only see the first one. You could join them together with a union:

CREATE PROCEDURE email_complexity

@.TableName VarChar(100)

AS
Declare @.SQL VarChar(1000)
Declare @.SQL1 VarChar(1000)

Set nocount on

SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'

SELECT @.SQL = @.SQL + ' UNION ALL '

SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
SELECT @.SQL1 = @.SQL1 + @.TableName
SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'

Exec ( @.SQL1)

Return|||Ross Presser <rpresser@.imtek.com> wrote in message news:<nt2t9zlcjwzx.dlg@.rpresser.invalid>...
> On 30 Sep 2004 10:12:28 -0700, dave wrote:
> > I am trying to run 3 dynamic selects from stored proc, really only
> > the table name is dynamic.. Anway I'm kinda lost on how I can
> > accomplish this.. this is what I have but it only returns the first
> > result.. that being basic
> > CREATE PROCEDURE email_complexity
> > @.TableName VarChar(100)
> > AS
> > Declare @.SQL VarChar(1000)
> > Declare @.SQL1 VarChar(1000)
> > Set nocount on
> > SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> > SELECT @.SQL = @.SQL + @.TableName
> > SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> > Exec ( @.SQL)
> > SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> > SELECT @.SQL1 = @.SQL1 + @.TableName
> > SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> > Exec ( @.SQL1)
> > Return
> > Is there a better way of doing this??
> > tia
> > Dave
> If your client isn't prepared to accept multiple resultsets, then you'll
> only see the first one. You could join them together with a union:
> CREATE PROCEDURE email_complexity
> @.TableName VarChar(100)
> AS
> Declare @.SQL VarChar(1000)
> Declare @.SQL1 VarChar(1000)
> Set nocount on
> SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> SELECT @.SQL = @.SQL + @.TableName
> SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> SELECT @.SQL = @.SQL + ' UNION ALL '
> SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> SELECT @.SQL1 = @.SQL1 + @.TableName
> SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> Exec ( @.SQL1)
>
> Return

But if you do that, you should be aware that what you'll receive in
return is two rows, under the single column "basic". There are ways to
improve this (to either add a second column with the texts "basic" and
"moderate", or by converting it into a single row, with columns
"basic" and "moderate").

If the OP is interested in either of these approaches, reply back
here, and I'll post more.

Run 2nd SQL in stored proc

I have a stored procedure (see below) that inserts records into a history
table from a source table (PayTotals). After that is successful, I would
like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
@.PaidYr" but only if the previous INSERT is successful. What would I need
to add to the proc below to make that happen? Thanks.
David
CREATE PROCEDURE [mc_inshstPayTotals]
(@.PaidYr [smallint])
AS INSERT INTO dbo.hstPayTotals
([EmployerCaseNumber],
[EmployerMax],
[PayFirst],
[PaySecond],
[PaidYr],
[PaidMo])
SELECT
EmployerCaseNumber,
EmployerMax,
PayFirst,
PaySecond,
PaidYr,
PaidMo
FROM dbo.PayTotals
WHERE PaidYr = @.PaidYr
GOWhat does "successful" mean? If it means exactly one row is inserted:
IF @.@.ROWCOUNT = 1
BEGIN
DELETE ...
END
Otherwise, you'll have to be more specific...
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OdaU4pF8FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
> David
> CREATE PROCEDURE [mc_inshstPayTotals]
> (@.PaidYr [smallint])
> AS INSERT INTO dbo.hstPayTotals
> ([EmployerCaseNumber],
> [EmployerMax],
> [PayFirst],
> [PaySecond],
> [PaidYr],
> [PaidMo])
> SELECT
> EmployerCaseNumber,
> EmployerMax,
> PayFirst,
> PaySecond,
> PaidYr,
> PaidMo
> FROM dbo.PayTotals
> WHERE PaidYr = @.PaidYr
> GO
>|||The system variable @.@.rowcount will return the number of rows affected (in
this case inserted). Also, there is the variable @.@.error that contains <> 0
in the event of an error.
For example:
insert into ...
if @.@.rowcount > 0
begin
..
end
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OdaU4pF8FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
> David
> CREATE PROCEDURE [mc_inshstPayTotals]
> (@.PaidYr [smallint])
> AS INSERT INTO dbo.hstPayTotals
> ([EmployerCaseNumber],
> [EmployerMax],
> [PayFirst],
> [PaySecond],
> [PaidYr],
> [PaidMo])
> SELECT
> EmployerCaseNumber,
> EmployerMax,
> PayFirst,
> PaySecond,
> PaidYr,
> PaidMo
> FROM dbo.PayTotals
> WHERE PaidYr = @.PaidYr
> GO
>|||The INSERT will always handle thousands of records, so I would think
that if the @.@.rowcount was > 0 then it worked, correct?
David
*** Sent via Developersdex http://www.examnotes.net ***|||JT,
If there IS an error, won't the @.@.rowcount = 0?
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||Yes, I would expect that to be the case.
Just because @.@.error = 0, it doesn't necessarily mean that 0 rows were
inserted, becuase the select query may return no rows or a trigger on
hstPayTotals may rollback the insert. Therefore, you will want to at least
check the status of @.@.rowcount.
"David" <daman@.lifetime.com> wrote in message
news:unZmE0F8FHA.1864@.TK2MSFTNGP12.phx.gbl...
> JT,
> If there IS an error, won't the @.@.rowcount = 0?
> Thanks.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||> The INSERT will always handle thousands of records, so I would think
> that if the @.@.rowcount was > 0 then it worked, correct?
Well, again, just because you inserted a bunch of rows doesn't necessarily
mean they were the right ones. :-)
But yes, if all you care about is that at least one row was inserted, then
checking for a positive @.@.ROWCOUNT should suffice.|||>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
I must be missing something in the midst of all this discussion of
@.@.ROWCOUNT and @.@.ERROR. Perhaps I'm being retarded today, but why not just
do this:
BEGIN TRANSACTION
INSERT stuff
DELETE stuff
COMMIT TRANSACTION
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23kJ4e7G8FHA.2616@.TK2MSFTNGP15.phx.gbl...
> I must be missing something in the midst of all this discussion of
> @.@.ROWCOUNT and @.@.ERROR. Perhaps I'm being retarded today, but why not
> just do this:
> BEGIN TRANSACTION
> INSERT stuff
> DELETE stuff
> COMMIT TRANSACTION
It depends what you want to do if the INSERT fails.
Do you still want to DELETE?
And if the INSERT succedes and the DELETE fails.
Do you still want to Commit the INSERT.
Normally you don't.
So after each one, check @.@.rowcount and @.@.errors and Commit only if both
succede.
Else, Rollback.

Run .SQL file

I have a whole bunch of update statments stored in a .sql
file. Is there any way to run it in the program (just pass
a filename)?
I know I can put the statments in a store procedure and
run the store procedure in the code such as
sqlcommand.commandtext="execute storeprocedure"
However, the database is in the client site and it's not
convient for them to change the store procedure. So I'd
like develop a functionality which can read and execute
the script file.
Any suggestion?
ThanksSee OSQL utility in BOL.
AMB
"Ying" wrote:

> I have a whole bunch of update statments stored in a .sql
> file. Is there any way to run it in the program (just pass
> a filename)?
> I know I can put the statments in a store procedure and
> run the store procedure in the code such as
> sqlcommand.commandtext="execute storeprocedure"
> However, the database is in the client site and it's not
> convient for them to change the store procedure. So I'd
> like develop a functionality which can read and execute
> the script file.
> Any suggestion?
> Thanks
>

Run .exe

Hi, how are you doing?
Can I run a .exe inside a SP? Because before sending emails it needs to
authenticate, so I did a Delphi .exe !
MS SQL 2000
Thanks!Yes, by using xp_cmdshell. However this is a really ugly thing, and whatever is performed will
probably be lost after the scope of xp_cmdshell.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paulo" <prbspfc@.uol.com.br> wrote in message news:ub89Zq7gIHA.3352@.TK2MSFTNGP04.phx.gbl...
> Hi, how are you doing?
> Can I run a .exe inside a SP? Because before sending emails it needs to authenticate, so I did a
> Delphi .exe !
> MS SQL 2000
> Thanks!
>|||"Paulo" <prbspfc@.uol.com.br> wrote in message
news:ub89Zq7gIHA.3352@.TK2MSFTNGP04.phx.gbl...
> Hi, how are you doing?
> Can I run a .exe inside a SP? Because before sending emails it needs to
> authenticate, so I did a Delphi .exe !
>
Yes you can do this, but don't.
There's a lot of things that can go wrong here, including xp_cmdshell
hanging and tying up resources.
And then of course there's just the time involved.
> MS SQL 2000
> Thanks!
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Run .exe

Hi, how are you doing?
Can I run a .exe inside a SP? Because before sending emails it needs to
authenticate, so I did a Delphi .exe !
MS SQL 2000
Thanks!
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:ub89Zq7gIHA.3352@.TK2MSFTNGP04.phx.gbl...
> Hi, how are you doing?
> Can I run a .exe inside a SP? Because before sending emails it needs to
> authenticate, so I did a Delphi .exe !
>
Yes you can do this, but don't.
There's a lot of things that can go wrong here, including xp_cmdshell
hanging and tying up resources.
And then of course there's just the time involved.

> MS SQL 2000
> Thanks!
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

RUN "Intergration Services Deployment Manifest" through Command Prompt

Hi, I want to run "Intergration Services Deployment Manifest" through the command prompt, passing the values .. which should basically go and install that Package either on File System or SQL Server. Does any one have any idea on how to do that ...

Thanks,

You can use the dtutil command prompt utility to copy a package to the Microsoft SQL Server database, the SSIS Package Store, or the file system.

However, the package must already exist in one of these three locations.

For more information, see "dtutil Utility" at http://msdn2.microsoft.com/en-us/library/ms162820.aspx.

|||Thanks alot for your reply ..

RUN "Intergration Services Deployment Manifest" through Command Prompt

Thanks,

You can use the dtutil command prompt utility to copy a package to the Microsoft SQL Server database, the SSIS Package Store, or the file system.

However, the package must already exist in one of these three locations.

For more information, see "dtutil Utility" at http://msdn2.microsoft.com/en-us/library/ms162820.aspx.

|||Thanks alot for your reply ..

Ruling out OS - SQL Server Performance.

Hi:
I have a question hopefully the SQL Server Pros and MVPs would have an
answer for. Most of the time like any normal DBA to find performance issues
with SQL Server I make use of the performance counters through performance
monitor or system monitor and SQL Server Profiler and then rule out OS as
the bottleneck and then concentrate on the application part. My question
here is if there a simple way that without using any of these counters or
tools a DBA can rule out OS as the potential bottleneck. For example I would
assume that if my backups start taking a long time all of a sudden when they
usually take less time every day then I can probably point out OS as the
issue. I am not sure if thats the right way to tell.
I am looking for a comprehensive list of possible symptoms (like paging,
Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
the bottle neck without me making use of any of the above mentioned tools.
Any KB Article or a White paper or blogs with more information would be
really helpful. Any other information is greatly appreciated.
Thanks for your attention.
MMeher
Well, my first question is what is your SQL Server's verion?
If you are using SQL Server 2005 you have lots of system catalogs,views that
contain such kind of info.Personally , I have not played with it
unfortunatly but as I heard it provides a comprehensive info about internal
behaviour of SQL Server as well as OS.
If you use SQL Server 2000 I have my doubt that without these tolls you can
roll out OS as the potential bottleneck at 'first glance'.
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message new, views
s:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi:
> I have a question hopefully the SQL Server Pros and MVPs would have an
> answer for. Most of the time like any normal DBA to find performance
> issues with SQL Server I make use of the performance counters through
> performance monitor or system monitor and SQL Server Profiler and then
> rule out OS as the bottleneck and then concentrate on the application
> part. My question here is if there a simple way that without using any of
> these counters or tools a DBA can rule out OS as the potential bottleneck.
> For example I would assume that if my backups start taking a long time all
> of a sudden when they usually take less time every day then I can probably
> point out OS as the issue. I am not sure if thats the right way to tell.
> I am looking for a comprehensive list of possible symptoms (like paging,
> Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
> the bottle neck without me making use of any of the above mentioned tools.
> Any KB Article or a White paper or blogs with more information would be
> really helpful. Any other information is greatly appreciated.
> Thanks for your attention.
> M
>|||Hi Uri:
My Version is SQL Server 2000. You are right that without using counters how
can a DBA Can say that OS Can be ruled out and that the Application is
possibly the bottleneck. That I assumed is my limited knowledge of
performance and if there are some kind of telltale signs that a DBA Can
point out without even using tools. If thats not possible then i guess the
tools are the only one i can rely on.
Thanks Uri for your response.
Meher
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uXhj3U$JGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Meher
> Well, my first question is what is your SQL Server's verion?
> If you are using SQL Server 2005 you have lots of system catalogs,views
> that contain such kind of info.Personally , I have not played with it
> unfortunatly but as I heard it provides a comprehensive info about
> internal behaviour of SQL Server as well as OS.
>
> If you use SQL Server 2000 I have my doubt that without these tolls you
> can roll out OS as the potential bottleneck at 'first glance'.
>
>
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message new, views
> s:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
>> Hi:
>> I have a question hopefully the SQL Server Pros and MVPs would have an
>> answer for. Most of the time like any normal DBA to find performance
>> issues with SQL Server I make use of the performance counters through
>> performance monitor or system monitor and SQL Server Profiler and then
>> rule out OS as the bottleneck and then concentrate on the application
>> part. My question here is if there a simple way that without using any of
>> these counters or tools a DBA can rule out OS as the potential
>> bottleneck. For example I would assume that if my backups start taking a
>> long time all of a sudden when they usually take less time every day then
>> I can probably point out OS as the issue. I am not sure if thats the
>> right way to tell.
>> I am looking for a comprehensive list of possible symptoms (like paging,
>> Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
>> the bottle neck without me making use of any of the above mentioned
>> tools. Any KB Article or a White paper or blogs with more information
>> would be really helpful. Any other information is greatly appreciated.
>> Thanks for your attention.
>> M
>|||Meher
Yes , based on my experience 80% of the problems ( lack of indexes, bad
written queries) caused by an application and only 10% -20% caused by a
hardware. I used to capture some events by using SQL Server Profiler and it
is absolutely reliable tool that provides very valuable info as well as
Perf. Monitor
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:ewjsHY$JGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi Uri:
> My Version is SQL Server 2000. You are right that without using counters
> how can a DBA Can say that OS Can be ruled out and that the Application is
> possibly the bottleneck. That I assumed is my limited knowledge of
> performance and if there are some kind of telltale signs that a DBA Can
> point out without even using tools. If thats not possible then i guess the
> tools are the only one i can rely on.
> Thanks Uri for your response.
> Meher
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uXhj3U$JGHA.3912@.TK2MSFTNGP10.phx.gbl...
>> Meher
>> Well, my first question is what is your SQL Server's verion?
>> If you are using SQL Server 2005 you have lots of system catalogs,views
>> that contain such kind of info.Personally , I have not played with it
>> unfortunatly but as I heard it provides a comprehensive info about
>> internal behaviour of SQL Server as well as OS.
>>
>> If you use SQL Server 2000 I have my doubt that without these tolls you
>> can roll out OS as the potential bottleneck at 'first glance'.
>>
>>
>>
>> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message new, views
>> s:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
>> Hi:
>> I have a question hopefully the SQL Server Pros and MVPs would have an
>> answer for. Most of the time like any normal DBA to find performance
>> issues with SQL Server I make use of the performance counters through
>> performance monitor or system monitor and SQL Server Profiler and then
>> rule out OS as the bottleneck and then concentrate on the application
>> part. My question here is if there a simple way that without using any
>> of these counters or tools a DBA can rule out OS as the potential
>> bottleneck. For example I would assume that if my backups start taking a
>> long time all of a sudden when they usually take less time every day
>> then I can probably point out OS as the issue. I am not sure if thats
>> the right way to tell.
>> I am looking for a comprehensive list of possible symptoms (like paging,
>> Virtual memory, Disk I/O) that can conclusively deduce that OS is/is
>> not the bottle neck without me making use of any of the above mentioned
>> tools. Any KB Article or a White paper or blogs with more information
>> would be really helpful. Any other information is greatly appreciated.
>> Thanks for your attention.
>> M
>>
>|||I use performance counters to supplement my SQL performance monitoring, not
to eliminate it. Most of the time, performance problems are due excessive
resource consumption. The counters tell me which resource is being
consumed. I then look at SQL to see which processes are consuming those
resources, often by using SQL traces. Be aware that SQL will trade one
resource for another for speed. Primarily it trades higher memory and CPU
usage for less disk usage. Sometimes a problem in one area can appear to be
something else. For example, I had a system with very high disk queue
lengths on the data disk. That would seem to indicate an I/O issue.
However, it also had an extremely short page life expectency, indicating
lack of cache memory. I upped the RAM and the disk problem went away. The
I/O problem was caused by SQL having to constantly reload data that should
have stayed in cache. I still need to tune some processes that use large
intermediate result sets, but that isn't an emergency priority.
This example illustrates that it is not just when X counter exceeds Y value
that tells you there is a problem. You have to know what is normal for your
system and work from there. Counters only give you a partial picture.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi:
> I have a question hopefully the SQL Server Pros and MVPs would have an
> answer for. Most of the time like any normal DBA to find performance
> issues with SQL Server I make use of the performance counters through
> performance monitor or system monitor and SQL Server Profiler and then
> rule out OS as the bottleneck and then concentrate on the application
> part. My question here is if there a simple way that without using any of
> these counters or tools a DBA can rule out OS as the potential bottleneck.
> For example I would assume that if my backups start taking a long time all
> of a sudden when they usually take less time every day then I can probably
> point out OS as the issue. I am not sure if thats the right way to tell.
> I am looking for a comprehensive list of possible symptoms (like paging,
> Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
> the bottle neck without me making use of any of the above mentioned tools.
> Any KB Article or a White paper or blogs with more information would be
> really helpful. Any other information is greatly appreciated.
> Thanks for your attention.
> M
>|||Thanks Geoff. Your post is really informative. The reason why I am asking
this question is i was asked iimagining i dont have any tools to troubleshoot
SQL Server Performance issues, before driving to the conclusion that
application related issues are the cause of performance, how do I eliminate
OS as the primary cause. I am trying to get a grasp on the approach of senior
SQL DBAs in such a scenario.
Thanks again.
"Geoff N. Hiten" wrote:
> I use performance counters to supplement my SQL performance monitoring, not
> to eliminate it. Most of the time, performance problems are due excessive
> resource consumption. The counters tell me which resource is being
> consumed. I then look at SQL to see which processes are consuming those
> resources, often by using SQL traces. Be aware that SQL will trade one
> resource for another for speed. Primarily it trades higher memory and CPU
> usage for less disk usage. Sometimes a problem in one area can appear to be
> something else. For example, I had a system with very high disk queue
> lengths on the data disk. That would seem to indicate an I/O issue.
> However, it also had an extremely short page life expectency, indicating
> lack of cache memory. I upped the RAM and the disk problem went away. The
> I/O problem was caused by SQL having to constantly reload data that should
> have stayed in cache. I still need to tune some processes that use large
> intermediate result sets, but that isn't an emergency priority.
> This example illustrates that it is not just when X counter exceeds Y value
> that tells you there is a problem. You have to know what is normal for your
> system and work from there. Counters only give you a partial picture.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
> > Hi:
> >
> > I have a question hopefully the SQL Server Pros and MVPs would have an
> > answer for. Most of the time like any normal DBA to find performance
> > issues with SQL Server I make use of the performance counters through
> > performance monitor or system monitor and SQL Server Profiler and then
> > rule out OS as the bottleneck and then concentrate on the application
> > part. My question here is if there a simple way that without using any of
> > these counters or tools a DBA can rule out OS as the potential bottleneck.
> > For example I would assume that if my backups start taking a long time all
> > of a sudden when they usually take less time every day then I can probably
> > point out OS as the issue. I am not sure if thats the right way to tell.
> >
> > I am looking for a comprehensive list of possible symptoms (like paging,
> > Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
> > the bottle neck without me making use of any of the above mentioned tools.
> > Any KB Article or a White paper or blogs with more information would be
> > really helpful. Any other information is greatly appreciated.
> >
> > Thanks for your attention.
> >
> > M
> >
>
>|||Meher
A bottle neck can be due to either of LAN/WAN, SAN/disk, CPU or memory.
You will need to use an approach to see if a similar task can be
completed at the OS level without using SQL specific connetivity.
An example would be if you have network performance issues you will
test a command prompt file copy (say 100MB) and capture packets with
Ethereal to see how that performs compared to data transfer using SQL
commands.
regards
Pradeep Arora
Meher wrote:
> Thanks Geoff. Your post is really informative. The reason why I am asking
> this question is i was asked iimagining i dont have any tools to troubleshoot
> SQL Server Performance issues, before driving to the conclusion that
> application related issues are the cause of performance, how do I eliminate
> OS as the primary cause. I am trying to get a grasp on the approach of senior
> SQL DBAs in such a scenario.
> Thanks again.
> "Geoff N. Hiten" wrote:
> > I use performance counters to supplement my SQL performance monitoring, not
> > to eliminate it. Most of the time, performance problems are due excessive
> > resource consumption. The counters tell me which resource is being
> > consumed. I then look at SQL to see which processes are consuming those
> > resources, often by using SQL traces. Be aware that SQL will trade one
> > resource for another for speed. Primarily it trades higher memory and CPU
> > usage for less disk usage. Sometimes a problem in one area can appear to be
> > something else. For example, I had a system with very high disk queue
> > lengths on the data disk. That would seem to indicate an I/O issue.
> > However, it also had an extremely short page life expectency, indicating
> > lack of cache memory. I upped the RAM and the disk problem went away. The
> > I/O problem was caused by SQL having to constantly reload data that should
> > have stayed in cache. I still need to tune some processes that use large
> > intermediate result sets, but that isn't an emergency priority.
> >
> > This example illustrates that it is not just when X counter exceeds Y value
> > that tells you there is a problem. You have to know what is normal for your
> > system and work from there. Counters only give you a partial picture.
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> > "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> > news:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
> > > Hi:
> > >
> > > I have a question hopefully the SQL Server Pros and MVPs would have an
> > > answer for. Most of the time like any normal DBA to find performance
> > > issues with SQL Server I make use of the performance counters through
> > > performance monitor or system monitor and SQL Server Profiler and then
> > > rule out OS as the bottleneck and then concentrate on the application
> > > part. My question here is if there a simple way that without using any of
> > > these counters or tools a DBA can rule out OS as the potential bottleneck.
> > > For example I would assume that if my backups start taking a long time all
> > > of a sudden when they usually take less time every day then I can probably
> > > point out OS as the issue. I am not sure if thats the right way to tell.
> > >
> > > I am looking for a comprehensive list of possible symptoms (like paging,
> > > Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
> > > the bottle neck without me making use of any of the above mentioned tools.
> > > Any KB Article or a White paper or blogs with more information would be
> > > really helpful. Any other information is greatly appreciated.
> > >
> > > Thanks for your attention.
> > >
> > > M
> > >
> >
> >
> >

Ruling out OS - SQL Server Performance.

Rules Vrs Check Constraints

Hello,
Could someone please explain the difference between them?,
I'm edging towards the CHECK CONSTRAINT as I believe it
offers better perfomance, but I just want to make sure I
have my facts right.
J
Rules is not in ANSI SQL. So you will only find it implemented as in SQL Server in MS and possibly
Sybase SQL Server.
Constraints are ANSI SQL.
Most focus today is on constraints. Many consider rules and "old technology". They have the
advantage of "define once, use many", though.
I prefer constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:10f801c518fa$6b320ce0$a401280a@.phx.gbl...
> Hello,
> Could someone please explain the difference between them?,
> I'm edging towards the CHECK CONSTRAINT as I believe it
> offers better perfomance, but I just want to make sure I
> have my facts right.
> J
|||Thanks Tibor

>--Original Message--
>Rules is not in ANSI SQL. So you will only find it
implemented as in SQL Server in MS and possibly
>Sybase SQL Server.
>Constraints are ANSI SQL.
>Most focus today is on constraints. Many consider rules
and "old technology". They have the
>advantage of "define once, use many", though.
>I prefer constraints.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:10f801c518fa$6b320ce0$a401280a@.phx.gbl...
them?,
>
>.
>

Rules Vrs Check Constraints

Hello,
Could someone please explain the difference between them?,
I'm edging towards the CHECK CONSTRAINT as I believe it
offers better perfomance, but I just want to make sure I
have my facts right.
JRules is not in ANSI SQL. So you will only find it implemented as in SQL Ser
ver in MS and possibly
Sybase SQL Server.
Constraints are ANSI SQL.
Most focus today is on constraints. Many consider rules and "old technology"
. They have the
advantage of "define once, use many", though.
I prefer constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:10f801c518fa$6b320ce0$a401280a@.phx.gbl...
> Hello,
> Could someone please explain the difference between them?,
> I'm edging towards the CHECK CONSTRAINT as I believe it
> offers better perfomance, but I just want to make sure I
> have my facts right.
> J|||Thanks Tibor

>--Original Message--
>Rules is not in ANSI SQL. So you will only find it
implemented as in SQL Server in MS and possibly
>Sybase SQL Server.
>Constraints are ANSI SQL.
>Most focus today is on constraints. Many consider rules
and "old technology". They have the
>advantage of "define once, use many", though.
>I prefer constraints.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:10f801c518fa$6b320ce0$a401280a@.phx.gbl...
them?,[vbcol=seagreen]
>
>.
>

Rules Vrs Check Constraints

Hello,
Could someone please explain the difference between them?,
I'm edging towards the CHECK CONSTRAINT as I believe it
offers better perfomance, but I just want to make sure I
have my facts right.
JRules is not in ANSI SQL. So you will only find it implemented as in SQL Server in MS and possibly
Sybase SQL Server.
Constraints are ANSI SQL.
Most focus today is on constraints. Many consider rules and "old technology". They have the
advantage of "define once, use many", though.
I prefer constraints.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:10f801c518fa$6b320ce0$a401280a@.phx.gbl...
> Hello,
> Could someone please explain the difference between them?,
> I'm edging towards the CHECK CONSTRAINT as I believe it
> offers better perfomance, but I just want to make sure I
> have my facts right.
> J|||Thanks Tibor
>--Original Message--
>Rules is not in ANSI SQL. So you will only find it
implemented as in SQL Server in MS and possibly
>Sybase SQL Server.
>Constraints are ANSI SQL.
>Most focus today is on constraints. Many consider rules
and "old technology". They have the
>advantage of "define once, use many", though.
>I prefer constraints.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:10f801c518fa$6b320ce0$a401280a@.phx.gbl...
>> Hello,
>> Could someone please explain the difference between
them?,
>> I'm edging towards the CHECK CONSTRAINT as I believe it
>> offers better perfomance, but I just want to make sure I
>> have my facts right.
>> J
>
>.
>

rules on a table

In My table i'm collecting information about our customers.In that i have fields zip and phone.So i wanted to implemement a rule that zip should be atlaest of of 5 characters and phone should be 13 characters Including '-'s(333-333-3333).How can i implement these two rules on my table.First of all, decide if you really want to add those constraints. They will automagically limit your database to dealing with data like what you'd find in the United States.

If that is an Ok thing, then I would suggest that you add constraints to your table. You could use something like:ALTER TABLE myTable
ADD CONSTRAINT XCK01myTable CHECK (5 <= Len(zip))
, ADD CONSTRAINT XCK02myTable CHECK (phone
LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')Constraints like these make me nervous, since I see them as rather arbitrary, but they are much better done as constraints than as code because you can easily change them in the database if needed, instead of having to hunt down hundreds or thousands of snippets of code!

-patP|||I'm with Pat. I think you'll end up regretting this as the users start to complain about limiting their ability to enter data. The zip-code you might get away with, though what about zip-4 extensions? I don't think enforcing a 13 character phone number is going to make you a lot of friends with the users.|||True, but you can fix this in one place (the database) with one command (DROP CONSTRAINT) if it turns out to be a problem. This is far better than coding it into an executable or a web page, at least in my opinion.

-PatP|||Oh, I agree. Table constraints are the best places to store design flaws, hands down. :p|||I don't know that it's a design flaw to insure data consistency in the data layer, even if that means adding "another" layer to the data. It's isn't so much to cover design flaws as it is to allow the data group to insure data is consistent and secure for the enterprise.|||It's not a design flaw to enforce data integrity. I'm just concerned about enforcing this particular constraint, based on past experience. I guess I'd say it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users. Unless there is some sort of application process that depends upon the phone number being absolutely 13 characters, then why place an artificial limit on how the database can be used? You are locking out the possibility of international numbers, extensions, etc...|||i agree with blindman

"it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users"

what about the guy who paid a few thousand bucks to secure the number 1-800-BEST-DBA

this guy's going to be p1ssed if you force him to put the dash where you want him to put the dash

note for those who still don't get it: there is no dash on the phone keypad|||That's not an unnecessary constraint. 1800BESTDBA isn't a phone number. It's a way to represent a phone number. You can't dial it though. You can't feed it to a dialer or use it for customer service. Anyone looking at the number is going to wonder what the idiot was thinking putting it into the database like that anyway. If you want to have a descriptor for the phone number, then have one. He can put whatever he wants in there. 1-800-BEST-DBA, 1-800-STUPID-Q...whatever.

The number is 18003334444. The display handled on the front-end is 1-800-333-4444. The descriptiong, which can be displayed or not is 1-800-STUPID-Q.|||well, that just proves my point

the number is not 18003334444

it's 18002378322 -- you could look it up!

and yes, i sure can dial 1-800-BEST-DBA

even on my rotary phone!!

and of course your very descriptive "what the idiot was thinking..." reveals an attitude that might best be set aside when dealing with people in the real world

Rules based Configuration ER design

I'm looking at a head start in developing a "Rules based configuration
system." One like Dell Computer has where you build your PC. Are there any
database designs available for this type of thing?
Regards,
Gary Blakely
Hello Gary,
Since rules based system is too general and you may want to be more
specific.
A very simple sample, let's say you have 3 configuration parameters and 2
rules. 1 rule has some relation to all 3 parameters. The tables shall be
tbl-rule
ruleid rulename ..
1rule1
tbl-parameters
paramid paramname ..
1p1
tbl-rule-parm-relation
ruleid paramid value relation
1110>
125<
Some more complex situation might be based on knowledge management, expert
system etc which is beyond a simple E-R desgin.
Some issues may require a bit more in depth attention and may fall under
the umbrella of Advisory Services. Microsoft now offers short-term and
proactive assistance for specific planning, design, development or
assistance with installing, deploying, and general "how to" advice via
telephone. For more information:
http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Rules based Configuration ER design

I'm looking at a head start in developing a "Rules based configuration
system." One like Dell Computer has where you build your PC. Are there any
database designs available for this type of thing?
Regards,
Gary BlakelyHello Gary,
Since rules based system is too general and you may want to be more
specific.
A very simple sample, let's say you have 3 configuration parameters and 2
rules. 1 rule has some relation to all 3 parameters. The tables shall be
tbl-rule
ruleid rulename ..
1 rule1
tbl-parameters
paramid paramname ..
1 p1
tbl-rule-parm-relation
ruleid paramid value relation
1 1 10 >
1 2 5 <
Some more complex situation might be based on knowledge management, expert
system etc which is beyond a simple E-R desgin.
Some issues may require a bit more in depth attention and may fall under
the umbrella of Advisory Services. Microsoft now offers short-term and
proactive assistance for specific planning, design, development or
assistance with installing, deploying, and general "how to" advice via
telephone. For more information:
http://support.microsoft.com/defaul...advisoryservice
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Rules based Configuration ER design

I'm looking at a head start in developing a "Rules based configuration
system." One like Dell Computer has where you build your PC. Are there any
database designs available for this type of thing?
--
Regards,
Gary BlakelyHello Gary,
Since rules based system is too general and you may want to be more
specific.
A very simple sample, let's say you have 3 configuration parameters and 2
rules. 1 rule has some relation to all 3 parameters. The tables shall be
tbl-rule
ruleid rulename ..
1 rule1
tbl-parameters
paramid paramname ..
1 p1
tbl-rule-parm-relation
ruleid paramid value relation
1 1 10 >
1 2 5 <
Some more complex situation might be based on knowledge management, expert
system etc which is beyond a simple E-R desgin.
Some issues may require a bit more in depth attention and may fall under
the umbrella of Advisory Services. Microsoft now offers short-term and
proactive assistance for specific planning, design, development or
assistance with installing, deploying, and general "how to" advice via
telephone. For more information:
http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Rules and defaults

Hi,

Can any one provide me information on creating rules and defaults in SQL Server2005, when i tried right clicking rules or defaults in management studio instead of new option i'am getting only refresh option.

This feature was working smoothly in sql2000.....I'am trying this on Microsoft SQL Server2005 Standard Edition.

thanks in advance

Mat

Rules and defaults are deprecated in 2005. You should now use constraints, which you define in the table designer. Rules and defaults are still supported, but you have to create them manually by writing the code yourself. You cannot create them in Management Studio because you should not be using them any more.

This comes from the 2000 documentation
"Rules are a backward-compatibility feature that perform some of the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict the values in a column."
"Defaults, a backward compatibility feature, perform some of the same functions as default definitions created using the DEFAULT keyword of ALTER or CREATE TABLE statements. Default definitions are the preferred, standard way to restrict column data because the definition is stored with the table and automatically dropped when the table is dropped."

And this from the 2005 documentation
"CREATE RULE will be removed in a future version of Microsoft SQL Server. Avoid using CREATE RULE in new development work, and plan to modify applications that currently use it. We recommend that you use check constraints instead."
"CREATE DEFAULT will be removed in a future version of Microsoft SQL Server. Avoid using CREATE DEFAULT in new development work, and plan to modify applications that currently use it. Instead, use default definitions created using the DEFAULT keyword of ALTER TABLE or CREATE TABLE."

Saturday, February 25, 2012

Run a DTS Package "in the background" or with low priority?

Run a DTS Package "in the background" or with low priority?

Run a DTS Package "in the background" or with low priority?

run a DTS

run a DTS

run a DTS

run a dos command?

run a .sql in different databases

run a .sql in different databases

Run a DTS Win32 Process Task hidden

run 3 dynamic selects from stored proc

Run 2nd SQL in stored proc

Run .SQL file

Run .exe

Run .exe

RUN "Intergration Services Deployment Manifest" through Command Prompt

RUN "Intergration Services Deployment Manifest" through Command Prompt

Ruling out OS - SQL Server Performance.

Ruling out OS - SQL Server Performance.

Ruling out OS - SQL Server Performance.

Rules Vrs Check Constraints

Rules Vrs Check Constraints

Rules Vrs Check Constraints

rules on a table

Rules based Configuration ER design

Rules based Configuration ER design

Rules based Configuration ER design

Rules and defaults

RSS Text Data Mining

Blog Archive

About Me