Credentials and extended stored procedures

I have a database script that uses the extended stored procedures sp_OACreate and sp_OCMethod to execute an .exe file. The .exe file is located on the same machine as the SQL Server. At this time it does nothing but log the name of the user calling it.

When I execute the script from Management Studio (logged in as myself) the user being logged as the caller of the .exe is still NT AUTHORITY\SYSTEM. I don't know why NT AUTHORITY\SYSTEM is the caller, cause the SQL Server service runs under another domain account.

I have tried playing around with EXECUTE AS USER but no matter what, the caller of the .exe is always logged as NT AUTHORITY\SYSTEM.

Are there any way I can pass my credentials to the executable that I am calling from the T-SQL script?

Have you given the NT AUTHORITY/SYSTEM privileges on SQL Server?|||

No, not intentionally. It is a default SQL server installation.

I switched the user that runs the SQL service to a domain user (it was local system account before), and that is basically all that has been changed, apart from giving some domain users access to some databases on the server.

Creating/Running DTS Packages with MSDE

Hi,
Is it possible to create DTS packages with MSDE? If so, how?
Would you execute them via the "dtsrun" command-prompt utility?
Thanks very much,
Mark Holahan
MSDE does not come with the tools that are necessary to create DTS packages.
For that you will need to user Enterprise Manager, which come with the full
versions of SQL Server, including the Developers version. MSDE instances can
store DTS packages in their repository and MSDE 2000 comes with the
dtsrun.exe utility, allowing you to execute packages.
Jim
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message
news:e93IFVWBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to create DTS packages with MSDE? If so, how?
> Would you execute them via the "dtsrun" command-prompt utility?
> Thanks very much,
> Mark Holahan
>

Creating table using dynamic SQL

I have the following dataset using dynamic SQL which works when i execute it, when i try to create a table using this dataset i cant see any fields. Does anyone know why dynamic SQL doesnt work ?

Declare @.TopRange int
Declare @.BottomRange int
Declare @.SQL Varchar(1000)

IF @.Param_leadage = '91+'
SET @.TopRange = 91
ELSE
Set @.TopRange = RTRIM(LEFT(REPLACE(@.Param_leadage,'-',''),2))

IF @.Param_leadage = '91+'
SET @.BottomRange = 4000
ELSE
Set @.BottomRange = LTRIM(RIGHT(REPLACE(@.Param_leadage,'-',''),2))

SET @.SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= ' + convert(varchar,@.TopRange) + ' AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= ' + convert(varchar,@.BottomRange)

IF @.Param_status = 'Online Churn'
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = 4 or dbo.tblCustomer.idStatus = 5 or dbo.tblCustomer.idStatus = 11'
ELSE
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = ' + @.Param_idstatus

EXEC(@.SQL)

If the Refresh Fields toolbar button enabled on the Data tab is enabled, try clicking it to get the fields. Or, plug in only the SELECT statement and hit Refresh Fields to get the fields.Then, change it to the original SQL. Finally, you can create the dataset fields manually.

|||

Hi Teo,

This isnt working, when i try to select a data field from the table textbox properties value dropdown box, it says 'Dataset1 has no fields'

When I enter it in manually it gives an error which is obviously to do with the fields not being in the scope.

In the Data screen within VSS 2005 the sql executes fine, when i refresh it prompts for parameters off which i enter and the data is returned, it is not recognising any fields when i try to access any fields from any controls..

it has something to do with the exec(sql) command and dynamic data i would suspect

I also tried doing the SQL first off which the table fields are recognised but as soon as i add the other portions such as the single quotes etc it gives the same error

Can someone please help

thanks

|||

Try putting this code in a stored proc and changing the command type to be stored proc.

The other option is to not use dynamic SQL, instead use an expression based query and construct the SQL using expression syntax e.g.

= Iif(Parameters!x.value = "91+", "Select...", "select ...")

Again, in this case I follow the previous advice of just putting in the SQL query first, hitting refresh so RS auto-generates the Filed definitions and then swapping in the expression

|||

@. is interpreted as a query parameter in the Query Designer. You can make the whole darn thing expression-based as Adam suggested or move this query to a stored procedure.

|||

thanks guys, i put it in a stored proc and it is working now

Creating indexes

I need to improve the performance of a query that is scheduled to run
every 5 minutes but is now taking longer than 5 minutes to execute.
I want to add an index or 2 to the table but I'm not sure of the
impact that will have during production hours. Another question, does
an index get used based on the "select" list or the "where" condition.
Lastly I used the database index tuning wizard and would like to know
what people think of the recommedations that the wizard provide.1) Be wary of ITW.
2) Indexes can be used for both select (if they cover all columns in the
output) and where clauses.
3) index tuning is much more than just "I think i need an index or 2". It
is a very complex subject with lots of variables that come into play. Best
is to get a pro to give you a quick review to help you develop an
appropriate indexing strategy. Failing that, indexes do take up space and
have overhead associated with maintaining them for inserts, updates and
deletes. Sometimes they don't help out at all like you think they should
because the data is very unspecific.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:000e3e3a-936d-41df-95f0-5336b20289f6@.b32g2000hsa.googlegroups.com...
>I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||In addition to Kevin's reply: when you create a new index, the entire
table will be scanned, and you should expect that during this time the
table will be locked.
Obviously, it would be best to test both the index creation and the
index' effectiveness on a test machine.
And finally, to repeat the previous reply: each addition index adds to
the cost of each insert and delete, and to all relevant updates. This is
also something you might want to test before deploying.
Gert-Jan
NC3 wrote:
> I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||On Nov 20, 5:22 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> In addition to Kevin's reply: when you create a new index, the entire
> table will be scanned, and you should expect that during this time the
> table will be locked.
> Obviously, it would be best to test both the index creation and the
> index' effectiveness on a test machine.
> And finally, to repeat the previous reply: each addition index adds to
> the cost of each insert and delete, and to all relevant updates. This is
> also something you might want to test before deploying.
> --
> Gert-Jan
>
> NC3 wrote:
>
>
> - Show quoted text -
Thanks for the responses, I will look into getting the db copied to a
test server and try addding the indexes to see if they are useful at
all.

Creating indexes

I need to improve the performance of a query that is scheduled to run
every 5 minutes but is now taking longer than 5 minutes to execute.
I want to add an index or 2 to the table but I'm not sure of the
impact that will have during production hours. Another question, does
an index get used based on the "select" list or the "where" condition.
Lastly I used the database index tuning wizard and would like to know
what people think of the recommedations that the wizard provide.
1) Be wary of ITW.
2) Indexes can be used for both select (if they cover all columns in the
output) and where clauses.
3) index tuning is much more than just "I think i need an index or 2". It
is a very complex subject with lots of variables that come into play. Best
is to get a pro to give you a quick review to help you develop an
appropriate indexing strategy. Failing that, indexes do take up space and
have overhead associated with maintaining them for inserts, updates and
deletes. Sometimes they don't help out at all like you think they should
because the data is very unspecific.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:000e3e3a-936d-41df-95f0-5336b20289f6@.b32g2000hsa.googlegroups.com...
>I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.
|||On Nov 20, 5:22 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> In addition to Kevin's reply: when you create a new index, the entire
> table will be scanned, and you should expect that during this time the
> table will be locked.
> Obviously, it would be best to test both the index creation and the
> index' effectiveness on a test machine.
> And finally, to repeat the previous reply: each addition index adds to
> the cost of each insert and delete, and to all relevant updates. This is
> also something you might want to test before deploying.
> --
> Gert-Jan
>
> NC3 wrote:
>
> - Show quoted text -
Thanks for the responses, I will look into getting the db copied to a
test server and try addding the indexes to see if they are useful at
all.

Creating indexes

I need to improve the performance of a query that is scheduled to run
every 5 minutes but is now taking longer than 5 minutes to execute.
I want to add an index or 2 to the table but I'm not sure of the
impact that will have during production hours. Another question, does
an index get used based on the "select" list or the "where" condition.
Lastly I used the database index tuning wizard and would like to know
what people think of the recommedations that the wizard provide.1) Be wary of ITW.
2) Indexes can be used for both select (if they cover all columns in the
output) and where clauses.
3) index tuning is much more than just "I think i need an index or 2". It
is a very complex subject with lots of variables that come into play. Best
is to get a pro to give you a quick review to help you develop an
appropriate indexing strategy. Failing that, indexes do take up space and
have overhead associated with maintaining them for inserts, updates and
deletes. Sometimes they don't help out at all like you think they should
because the data is very unspecific.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:000e3e3a-936d-41df-95f0-5336b20289f6@.b32g2000hsa.googlegroups.com...
>I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||In addition to Kevin's reply: when you create a new index, the entire
table will be scanned, and you should expect that during this time the
table will be locked.
Obviously, it would be best to test both the index creation and the
index' effectiveness on a test machine.
And finally, to repeat the previous reply: each addition index adds to
the cost of each insert and delete, and to all relevant updates. This is
also something you might want to test before deploying.
--
Gert-Jan
NC3 wrote:
> I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||On Nov 20, 5:22 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> In addition to Kevin's reply: when you create a new index, the entire
> table will be scanned, and you should expect that during this time the
> table will be locked.
> Obviously, it would be best to test both the index creation and the
> index' effectiveness on a test machine.
> And finally, to repeat the previous reply: each addition index adds to
> the cost of each insert and delete, and to all relevant updates. This is
> also something you might want to test before deploying.
> --
> Gert-Jan
>
> NC3 wrote:
> > I need to improve the performance of a query that is scheduled to run
> > every 5 minutes but is now taking longer than 5 minutes to execute.
> > I want to add an index or 2 to the table but I'm not sure of the
> > impact that will have during production hours. Another question, does
> > an index get used based on the "select" list or the "where" condition.
> > Lastly I used the database index tuning wizard and would like to know
> > what people think of the recommedations that the wizard provide.- Hide quoted text -
> - Show quoted text -
Thanks for the responses, I will look into getting the db copied to a
test server and try addding the indexes to see if they are useful at
all.

creating database from sql file

I have an sql file that i am trying to open and execute to create a db with
the corresponding tables. When I execute this file the tables are created
but they are all under databases\system databases\master\tables. They are
not created in the system tables but all are created under the afore
mentioned location. What do I need to do the have the tables created under
another schema?In the script, add the following line before the CREATE TABLE statements:
USE <database> -- where <database> is the name of the newly created
database.
For more information, see
http://msdn2.microsoft.com/en-us/library/ms188366.aspx
Without the USE statement, all statements in the script are created in the
context of the current database, not the new database you created.
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Tim" <gtu@.cablelynx.com> wrote in message
news:%2343NGAPZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have an sql file that i am trying to open and execute to create a db
>with the corresponding tables. When I execute this file the tables are
>created but they are all under databases\system databases\master\tables.
>They are not created in the system tables but all are created under the
>afore mentioned location. What do I need to do the have the tables created
>under another schema?
>|||Also note that in SQL Server, a database and schema are not the same thing.
What version are you running?
Schemas were synonymous with object owners in SQL Server 2000, but in 2005
schemas are separate.
Both users names and schemas are within a database, and a SQL Server
instance can have many databases.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7Uw4PPZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> In the script, add the following line before the CREATE TABLE statements:
> USE <database> -- where <database> is the name of the newly created
> database.
> For more information, see
> http://msdn2.microsoft.com/en-us/library/ms188366.aspx
> Without the USE statement, all statements in the script are created in the
> context of the current database, not the new database you created.
> Regards,
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
>
> "Tim" <gtu@.cablelynx.com> wrote in message
> news:%2343NGAPZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>

creating database from sql file

I have an sql file that i am trying to open and execute to create a db with
the corresponding tables. When I execute this file the tables are created
but they are all under databases\system databases\master\tables. They are
not created in the system tables but all are created under the afore
mentioned location. What do I need to do the have the tables created under
another schema?
In the script, add the following line before the CREATE TABLE statements:
USE <database> -- where <database> is the name of the newly created
database.
For more information, see
http://msdn2.microsoft.com/en-us/library/ms188366.aspx
Without the USE statement, all statements in the script are created in the
context of the current database, not the new database you created.
Regards,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tim" <gtu@.cablelynx.com> wrote in message
news:%2343NGAPZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have an sql file that i am trying to open and execute to create a db
>with the corresponding tables. When I execute this file the tables are
>created but they are all under databases\system databases\master\tables.
>They are not created in the system tables but all are created under the
>afore mentioned location. What do I need to do the have the tables created
>under another schema?
>
|||Also note that in SQL Server, a database and schema are not the same thing.
What version are you running?
Schemas were synonymous with object owners in SQL Server 2000, but in 2005
schemas are separate.
Both users names and schemas are within a database, and a SQL Server
instance can have many databases.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7Uw4PPZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> In the script, add the following line before the CREATE TABLE statements:
> USE <database> -- where <database> is the name of the newly created
> database.
> For more information, see
> http://msdn2.microsoft.com/en-us/library/ms188366.aspx
> Without the USE statement, all statements in the script are created in the
> context of the current database, not the new database you created.
> Regards,
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>
> "Tim" <gtu@.cablelynx.com> wrote in message
> news:%2343NGAPZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>

creating database from sql file

I have an sql file that i am trying to open and execute to create a db with
the corresponding tables. When I execute this file the tables are created
but they are all under databases\system databases\master\tables. They are
not created in the system tables but all are created under the afore
mentioned location. What do I need to do the have the tables created under
another schema?In the script, add the following line before the CREATE TABLE statements:
USE <database> -- where <database> is the name of the newly created
database.
For more information, see
http://msdn2.microsoft.com/en-us/library/ms188366.aspx
Without the USE statement, all statements in the script are created in the
context of the current database, not the new database you created.
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tim" <gtu@.cablelynx.com> wrote in message
news:%2343NGAPZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have an sql file that i am trying to open and execute to create a db
>with the corresponding tables. When I execute this file the tables are
>created but they are all under databases\system databases\master\tables.
>They are not created in the system tables but all are created under the
>afore mentioned location. What do I need to do the have the tables created
>under another schema?
>|||Also note that in SQL Server, a database and schema are not the same thing.
What version are you running?
Schemas were synonymous with object owners in SQL Server 2000, but in 2005
schemas are separate.
Both users names and schemas are within a database, and a SQL Server
instance can have many databases.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7Uw4PPZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> In the script, add the following line before the CREATE TABLE statements:
> USE <database> -- where <database> is the name of the newly created
> database.
> For more information, see
> http://msdn2.microsoft.com/en-us/library/ms188366.aspx
> Without the USE statement, all statements in the script are created in the
> context of the current database, not the new database you created.
> Regards,
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>
> "Tim" <gtu@.cablelynx.com> wrote in message
> news:%2343NGAPZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I have an sql file that i am trying to open and execute to create a db
>>with the corresponding tables. When I execute this file the tables are
>>created but they are all under databases\system databases\master\tables.
>>They are not created in the system tables but all are created under the
>>afore mentioned location. What do I need to do the have the tables
>>created under another schema?
>