how could I store images in a database?!!

hi everybody,can anybody help me out by telling me how could I store images in a database, and how to insert new images at run time to a database and thanks

If you have C# .NET (Express, and probably other versions too), it comes with a "Movie Collection Starter Kit" example which has a button that loads an image into the database. Just go to File->New Project, and select the "Movie Collection Starter Kit" template. Open ListDetail.cs, and find the ImportImage method.

How come my SQL Server Job won't run?

Hi - I just created a job in SQL Server using Management Studio. This is the
first time I've tried to build a job. The job failed to execute at the
scheduled time and it fails when I start it manually. Looking at the Job
History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
failed."
I tried to pick a different user from the Run as drop down in the Job step
properties window, but the drop down is empty. Does anyone know what might
be going on here?
--
Thanks,
JeffHi Jeff,
What is your job supposed to do? On the job history expand the job steps and
look for additional error messages every step. If applicable also check the
SQL Server error log.
You can also try the Advanced page on the Job Step section and specify an
output file. Then run the job again and see if you can find some additional
error information there.
Perhaps you do not need to change the 'Run as' section until you know what
the problem is.
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Jeff Stroope" wrote:
> Hi - I just created a job in SQL Server using Management Studio. This is the
> first time I've tried to build a job. The job failed to execute at the
> scheduled time and it fails when I start it manually. Looking at the Job
> History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
> failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
> failed."
> I tried to pick a different user from the Run as drop down in the Job step
> properties window, but the drop down is empty. Does anyone know what might
> be going on here?
> --
> Thanks,
> Jeff|||Jeff,
I just ran into someone getting the same error message. Are you by any
change running something like the following TSQL step?
exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
In the other case, it seems that shelling out and then logging back in as a
Trusted Connection raised the error.
In their case, changing the SQL Server service from running as Local System
to running as a domain login resolved the problem. (Of course, you need to
have or create a domain account with the proper permissions.)
RLF
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
> Hi - I just created a job in SQL Server using Management Studio. This is
> the
> first time I've tried to build a job. The job failed to execute at the
> scheduled time and it fails when I start it manually. Looking at the Job
> History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM.
> Login
> failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The
> step
> failed."
> I tried to pick a different user from the Run as drop down in the Job step
> properties window, but the drop down is empty. Does anyone know what
> might
> be going on here?
> --
> Thanks,
> Jeff|||Hi Ben - the job is supposed to run a stored procedure. The job only has one
step, which is "execute sp_storedProcedure". I'll try the output file and
see what happens.
--
Thanks,
Jeff
"Ben Nevarez" wrote:
> Hi Jeff,
> What is your job supposed to do? On the job history expand the job steps and
> look for additional error messages every step. If applicable also check the
> SQL Server error log.
> You can also try the Advanced page on the Job Step section and specify an
> output file. Then run the job again and see if you can find some additional
> error information there.
> Perhaps you do not need to change the 'Run as' section until you know what
> the problem is.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
>
> "Jeff Stroope" wrote:
> > Hi - I just created a job in SQL Server using Management Studio. This is the
> > first time I've tried to build a job. The job failed to execute at the
> > scheduled time and it fails when I start it manually. Looking at the Job
> > History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
> > failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
> > failed."
> >
> > I tried to pick a different user from the Run as drop down in the Job step
> > properties window, but the drop down is empty. Does anyone know what might
> > be going on here?
> > --
> > Thanks,
> >
> > Jeff|||Thanks Russell - no, I'm just trying execute a stored procedure that queries
some tables in a database on a linked server and then populates tables on the
job server.
--
Thanks,
Jeff
"Russell Fields" wrote:
> Jeff,
> I just ran into someone getting the same error message. Are you by any
> change running something like the following TSQL step?
> exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
> In the other case, it seems that shelling out and then logging back in as a
> Trusted Connection raised the error.
> In their case, changing the SQL Server service from running as Local System
> to running as a domain login resolved the problem. (Of course, you need to
> have or create a domain account with the proper permissions.)
> RLF
>
> "Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
> news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
> > Hi - I just created a job in SQL Server using Management Studio. This is
> > the
> > first time I've tried to build a job. The job failed to execute at the
> > scheduled time and it fails when I start it manually. Looking at the Job
> > History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM.
> > Login
> > failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The
> > step
> > failed."
> >
> > I tried to pick a different user from the Run as drop down in the Job step
> > properties window, but the drop down is empty. Does anyone know what
> > might
> > be going on here?
> > --
> > Thanks,
> >
> > Jeff
>
>|||Jeff,
OK, but you still have an execution context issue since you are going to
another server. A couple more questions:
1. Who is the job owner of the SQL Agent job? Your login? The 'sa'
account? NT AUTHORITY\SYSTEM? A domain login? A SQL Server login?
2. How do you have your security set on the linked server? Check the
security tab of the linked server definition. Local login impersonation?
The login's current security context? A specific login?
You might be able to manipulate the linked server definition to get this to
work for you. But, as is, I suspect that the local system account is at the
root of the problem for the job you have defined.
RLF
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:FA4BED9F-1877-4B01-ADA5-3F1B49027EC9@.microsoft.com...
> Thanks Russell - no, I'm just trying execute a stored procedure that
> queries
> some tables in a database on a linked server and then populates tables on
> the
> job server.
> --
> Thanks,
> Jeff
>
> "Russell Fields" wrote:
>> Jeff,
>> I just ran into someone getting the same error message. Are you by any
>> change running something like the following TSQL step?
>> exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
>> In the other case, it seems that shelling out and then logging back in as
>> a
>> Trusted Connection raised the error.
>> In their case, changing the SQL Server service from running as Local
>> System
>> to running as a domain login resolved the problem. (Of course, you need
>> to
>> have or create a domain account with the proper permissions.)
>> RLF
>>
>> "Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
>> news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
>> > Hi - I just created a job in SQL Server using Management Studio. This
>> > is
>> > the
>> > first time I've tried to build a job. The job failed to execute at the
>> > scheduled time and it fails when I start it manually. Looking at the
>> > Job
>> > History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM.
>> > Login
>> > failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The
>> > step
>> > failed."
>> >
>> > I tried to pick a different user from the Run as drop down in the Job
>> > step
>> > properties window, but the drop down is empty. Does anyone know what
>> > might
>> > be going on here?
>> > --
>> > Thanks,
>> >
>> > Jeff
>>|||Hi,
Did you find the solution for this. Even I am facing similar problem.
I am trying to configure a linked server with the following settings
sp_addlinkedserver 'Linked Server Name', 'SQL Server'
sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
, @.useself = 'FALSE'
, @.locallogin = 'Domain\LoginName'
, @.rmtuser = 'DatabaseUserName'
, @.rmtpassword = 'DatabaseUSerPassword'
If I run the query against this linked server I get the perfect result.
Now I have scheduled a Job which runs on sa account. It always throws the
error:
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).
Let me know on what I am doing wrong.|||Praveen,
What you have said is, if the linked server is accessed by login
'Domain\LoginName' then log in remotely as 'DatabaseUserName' using
'DatabaseUSerPassword'. If the linked server is accessed by any other
login, then that login's security context.
If I understood correctly, you said that the job runs as 'sa', which is a
local SQL Server login and has no domain rights. You messages refer to NT
AUTHORITY\SYSTEM and 'DOMAIN\MACHINENAME', to which you do not seem to have
granted any special rights to the 'Linked Server Name' server.
Also, it appears that the SQL Server service is running as the local system
account, but a best practice is to run the SQL Server service as a domain
account and not as local system. You can see that a domain account would be
handier for accessing anything not on the local server. (If I properly
understood your setup.)
Perhaps you could add:
sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
, @.useself = 'FALSE'
, @.locallogin = 'sa'
, @.rmtuser = 'DatabaseUserName'
, @.rmtpassword = 'DatabaseUSerPassword'
FWIW,
RLF
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:67D35848-9BB7-49E8-B128-B26E040F7D8E@.microsoft.com...
> Hi,
> Did you find the solution for this. Even I am facing similar problem.
> I am trying to configure a linked server with the following settings
> sp_addlinkedserver 'Linked Server Name', 'SQL Server'
> sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
> , @.useself = 'FALSE'
> , @.locallogin = 'Domain\LoginName'
> , @.rmtuser = 'DatabaseUserName'
> , @.rmtpassword = 'DatabaseUSerPassword'
>
> If I run the query against this linked server I get the perfect result.
> Now I have scheduled a Job which runs on sa account. It always throws the
> error:
> Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
> 'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).
> Let me know on what I am doing wrong.
>|||Russell,
Now I have removed all the logins and had defaulted to DatabaseUserName and
DatabaseUserPassword for all the logins.
What I did was [this is in SQL Server 2005]
1. Right click Linked Servers -> New Linked Server
2. General tab:
Linked Server: Name of the SQL Server. Let us call this as 'Linked Server'
Server type: SQL Server
3. Security tab:
No Local Server Logins were added. Add selected the option "Be made using
this security context" in the list of options. Remote Login and Password are
DatabaseUserName and DatabaseUserPassword.
This setup I believe that regardless of the access account from the source
machine [machine from which i am querying the linked server] it always trys
to connect as DatabaseUserName to the linked server.
This works perfectly fine on any query made to the linked server. I can
access table objects, sysobjects of Linked Server from the Source Machine.
The same queries if I make them in the Stored Procedure and schedule to
execute the SP as a Job doesn't works.
Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
The difference between direct query and the job is that in direct query is
through the Window authentication mode, which is "Domain\UserName" account
whereas through job in Source machine sa account.
With the setup of Linked server I believe it should always go through the
DatabaseUserName and DatabasePassword credentials.
What is the wrong here?|||Praveen,
Yes, with a hardwired remote Login and Password I would expect all access
to be through that login. What now? Well, you are now getting the message:
Server 'LinkedServerName is not configured for RPC.
Please look at the Linked Server Properties, the Server Options tab. Make
sure the RPC settings are TRUE.
RLF
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:2C20479D-7A59-4910-8647-A334E8EF4A95@.microsoft.com...
> Russell,
> Now I have removed all the logins and had defaulted to DatabaseUserName
> and
> DatabaseUserPassword for all the logins.
> What I did was [this is in SQL Server 2005]
> 1. Right click Linked Servers -> New Linked Server
> 2. General tab:
> Linked Server: Name of the SQL Server. Let us call this as 'Linked
> Server'
> Server type: SQL Server
> 3. Security tab:
> No Local Server Logins were added. Add selected the option "Be made
> using
> this security context" in the list of options. Remote Login and Password
> are
> DatabaseUserName and DatabaseUserPassword.
> This setup I believe that regardless of the access account from the source
> machine [machine from which i am querying the linked server] it always
> trys
> to connect as DatabaseUserName to the linked server.
> This works perfectly fine on any query made to the linked server. I can
> access table objects, sysobjects of Linked Server from the Source Machine.
> The same queries if I make them in the Stored Procedure and schedule to
> execute the SP as a Job doesn't works.
> Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
> configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
> The difference between direct query and the job is that in direct query is
> through the Window authentication mode, which is "Domain\UserName" account
> whereas through job in Source machine sa account.
> With the setup of Linked server I believe it should always go through the
> DatabaseUserName and DatabasePassword credentials.
> What is the wrong here?
>

How come my SQL Server Job won't run?

Hi - I just created a job in SQL Server using Management Studio. This is the
first time I've tried to build a job. The job failed to execute at the
scheduled time and it fails when I start it manually. Looking at the Job
History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
failed."
I tried to pick a different user from the Run as drop down in the Job step
properties window, but the drop down is empty. Does anyone know what might
be going on here?
Thanks,
Jeff
Hi Jeff,
What is your job supposed to do? On the job history expand the job steps and
look for additional error messages every step. If applicable also check the
SQL Server error log.
You can also try the Advanced page on the Job Step section and specify an
output file. Then run the job again and see if you can find some additional
error information there.
Perhaps you do not need to change the 'Run as' section until you know what
the problem is.
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Jeff Stroope" wrote:

> Hi - I just created a job in SQL Server using Management Studio. This is the
> first time I've tried to build a job. The job failed to execute at the
> scheduled time and it fails when I start it manually. Looking at the Job
> History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
> failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
> failed."
> I tried to pick a different user from the Run as drop down in the Job step
> properties window, but the drop down is empty. Does anyone know what might
> be going on here?
> --
> Thanks,
> Jeff
|||Jeff,
I just ran into someone getting the same error message. Are you by any
change running something like the following TSQL step?
exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
In the other case, it seems that shelling out and then logging back in as a
Trusted Connection raised the error.
In their case, changing the SQL Server service from running as Local System
to running as a domain login resolved the problem. (Of course, you need to
have or create a domain account with the proper permissions.)
RLF
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
> Hi - I just created a job in SQL Server using Management Studio. This is
> the
> first time I've tried to build a job. The job failed to execute at the
> scheduled time and it fails when I start it manually. Looking at the Job
> History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM.
> Login
> failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The
> step
> failed."
> I tried to pick a different user from the Run as drop down in the Job step
> properties window, but the drop down is empty. Does anyone know what
> might
> be going on here?
> --
> Thanks,
> Jeff
|||Hi Ben - the job is supposed to run a stored procedure. The job only has one
step, which is "execute sp_storedProcedure". I'll try the output file and
see what happens.
Thanks,
Jeff
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Jeff,
> What is your job supposed to do? On the job history expand the job steps and
> look for additional error messages every step. If applicable also check the
> SQL Server error log.
> You can also try the Advanced page on the Job Step section and specify an
> output file. Then run the job again and see if you can find some additional
> error information there.
> Perhaps you do not need to change the 'Run as' section until you know what
> the problem is.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
>
> "Jeff Stroope" wrote:
|||Thanks Russell - no, I'm just trying execute a stored procedure that queries
some tables in a database on a linked server and then populates tables on the
job server.
Thanks,
Jeff
"Russell Fields" wrote:

> Jeff,
> I just ran into someone getting the same error message. Are you by any
> change running something like the following TSQL step?
> exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
> In the other case, it seems that shelling out and then logging back in as a
> Trusted Connection raised the error.
> In their case, changing the SQL Server service from running as Local System
> to running as a domain login resolved the problem. (Of course, you need to
> have or create a domain account with the proper permissions.)
> RLF
>
> "Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
> news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
>
>
|||Jeff,
OK, but you still have an execution context issue since you are going to
another server. A couple more questions:
1. Who is the job owner of the SQL Agent job? Your login? The 'sa'
account? NT AUTHORITY\SYSTEM? A domain login? A SQL Server login?
2. How do you have your security set on the linked server? Check the
security tab of the linked server definition. Local login impersonation?
The login's current security context? A specific login?
You might be able to manipulate the linked server definition to get this to
work for you. But, as is, I suspect that the local system account is at the
root of the problem for the job you have defined.
RLF
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:FA4BED9F-1877-4B01-ADA5-3F1B49027EC9@.microsoft.com...[vbcol=seagreen]
> Thanks Russell - no, I'm just trying execute a stored procedure that
> queries
> some tables in a database on a linked server and then populates tables on
> the
> job server.
> --
> Thanks,
> Jeff
>
> "Russell Fields" wrote:
|||Hi,
Did you find the solution for this. Even I am facing similar problem.
I am trying to configure a linked server with the following settings
sp_addlinkedserver 'Linked Server Name', 'SQL Server'
sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
, @.useself = 'FALSE'
, @.locallogin = 'Domain\LoginName'
, @.rmtuser = 'DatabaseUserName'
, @.rmtpassword = 'DatabaseUSerPassword'
If I run the query against this linked server I get the perfect result.
Now I have scheduled a Job which runs on sa account. It always throws the
error:
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).
Let me know on what I am doing wrong.
|||Praveen,
What you have said is, if the linked server is accessed by login
'Domain\LoginName' then log in remotely as 'DatabaseUserName' using
'DatabaseUSerPassword'. If the linked server is accessed by any other
login, then that login's security context.
If I understood correctly, you said that the job runs as 'sa', which is a
local SQL Server login and has no domain rights. You messages refer to NT
AUTHORITY\SYSTEM and 'DOMAIN\MACHINENAME', to which you do not seem to have
granted any special rights to the 'Linked Server Name' server.
Also, it appears that the SQL Server service is running as the local system
account, but a best practice is to run the SQL Server service as a domain
account and not as local system. You can see that a domain account would be
handier for accessing anything not on the local server. (If I properly
understood your setup.)
Perhaps you could add:
sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
, @.useself = 'FALSE'
, @.locallogin = 'sa'
, @.rmtuser = 'DatabaseUserName'
, @.rmtpassword = 'DatabaseUSerPassword'
FWIW,
RLF
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:67D35848-9BB7-49E8-B128-B26E040F7D8E@.microsoft.com...
> Hi,
> Did you find the solution for this. Even I am facing similar problem.
> I am trying to configure a linked server with the following settings
> sp_addlinkedserver 'Linked Server Name', 'SQL Server'
> sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
> , @.useself = 'FALSE'
> , @.locallogin = 'Domain\LoginName'
> , @.rmtuser = 'DatabaseUserName'
> , @.rmtpassword = 'DatabaseUSerPassword'
>
> If I run the query against this linked server I get the perfect result.
> Now I have scheduled a Job which runs on sa account. It always throws the
> error:
> Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
> 'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).
> Let me know on what I am doing wrong.
>
|||Russell,
Now I have removed all the logins and had defaulted to DatabaseUserName and
DatabaseUserPassword for all the logins.
What I did was [this is in SQL Server 2005]
1. Right click Linked Servers -> New Linked Server
2. General tab:
Linked Server: Name of the SQL Server. Let us call this as 'Linked Server'
Server type: SQL Server
3. Security tab:
No Local Server Logins were added. Add selected the option "Be made using
this security context" in the list of options. Remote Login and Password are
DatabaseUserName and DatabaseUserPassword.
This setup I believe that regardless of the access account from the source
machine [machine from which i am querying the linked server] it always trys
to connect as DatabaseUserName to the linked server.
This works perfectly fine on any query made to the linked server. I can
access table objects, sysobjects of Linked Server from the Source Machine.
The same queries if I make them in the Stored Procedure and schedule to
execute the SP as a Job doesn't works.
Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
The difference between direct query and the job is that in direct query is
through the Window authentication mode, which is "Domain\UserName" account
whereas through job in Source machine sa account.
With the setup of Linked server I believe it should always go through the
DatabaseUserName and DatabasePassword credentials.
What is the wrong here?
|||Praveen,
Yes, with a hardwired remote Login and Password I would expect all access
to be through that login. What now? Well, you are now getting the message:
Server 'LinkedServerName is not configured for RPC.
Please look at the Linked Server Properties, the Server Options tab. Make
sure the RPC settings are TRUE.
RLF
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:2C20479D-7A59-4910-8647-A334E8EF4A95@.microsoft.com...
> Russell,
> Now I have removed all the logins and had defaulted to DatabaseUserName
> and
> DatabaseUserPassword for all the logins.
> What I did was [this is in SQL Server 2005]
> 1. Right click Linked Servers -> New Linked Server
> 2. General tab:
> Linked Server: Name of the SQL Server. Let us call this as 'Linked
> Server'
> Server type: SQL Server
> 3. Security tab:
> No Local Server Logins were added. Add selected the option "Be made
> using
> this security context" in the list of options. Remote Login and Password
> are
> DatabaseUserName and DatabaseUserPassword.
> This setup I believe that regardless of the access account from the source
> machine [machine from which i am querying the linked server] it always
> trys
> to connect as DatabaseUserName to the linked server.
> This works perfectly fine on any query made to the linked server. I can
> access table objects, sysobjects of Linked Server from the Source Machine.
> The same queries if I make them in the Stored Procedure and schedule to
> execute the SP as a Job doesn't works.
> Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
> configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
> The difference between direct query and the job is that in direct query is
> through the Window authentication mode, which is "Domain\UserName" account
> whereas through job in Source machine sa account.
> With the setup of Linked server I believe it should always go through the
> DatabaseUserName and DatabasePassword credentials.
> What is the wrong here?
>

How check the results of the SQL Backups on serveral servers

Hi, I'm looking for a simple an easy solution to check the sqlserver backups
,
every day we run some backups jobs in diferent servers. How i can verify the
backup jobs everyday without check the enterprise manager of ervery server?
Is posible create a report where the copys are succesfuly per server and per
sql server database?
Thanks in advance
Fernando... Or use xp_smtp_sendmail and remove xp_sendmail and create a version of
xp_sendmail which in turn
calls xp_smtp_sendmail. As described on www.sqldev.net.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:092001c490db$294323c0$a401280a@.phx.gbl...[vbcol=seagreen]
> You could get SQL Server Agent to email you results of the
> backup, but that is dependant upon SQL Agent having a
> domain user id with a mailbox.
> Thanks
> Peter
> "There is no such thing as public opinion. There is only
> published opinion."
> Winston Churchill
>
> the sqlserver backups,
> How i can verify the
> of ervery server?
> per server and per

How check the results of the SQL Backups on serveral servers

Hi, I'm looking for a simple an easy solution to check the sqlserver backups,
every day we run some backups jobs in diferent servers. How i can verify the
backup jobs everyday without check the enterprise manager of ervery server?
Is posible create a report where the copys are succesfuly per server and per
sql server database?
Thanks in advance
FernandoYou could get SQL Server Agent to email you results of the
backup, but that is dependant upon SQL Agent having a
domain user id with a mailbox.
Thanks
Peter
"There is no such thing as public opinion. There is only
published opinion."
Winston Churchill
>--Original Message--
>Hi, I'm looking for a simple an easy solution to check
the sqlserver backups,
>every day we run some backups jobs in diferent servers.
How i can verify the
>backup jobs everyday without check the enterprise manager
of ervery server?
>Is posible create a report where the copys are succesfuly
per server and per
>sql server database?
>Thanks in advance
>Fernando
>.
>|||... Or use xp_smtp_sendmail and remove xp_sendmail and create a version of xp_sendmail which in turn
calls xp_smtp_sendmail. As described on www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:092001c490db$294323c0$a401280a@.phx.gbl...
> You could get SQL Server Agent to email you results of the
> backup, but that is dependant upon SQL Agent having a
> domain user id with a mailbox.
> Thanks
> Peter
> "There is no such thing as public opinion. There is only
> published opinion."
> Winston Churchill
>
> >--Original Message--
> >Hi, I'm looking for a simple an easy solution to check
> the sqlserver backups,
> >every day we run some backups jobs in diferent servers.
> How i can verify the
> >backup jobs everyday without check the enterprise manager
> of ervery server?
> >Is posible create a report where the copys are succesfuly
> per server and per
> >sql server database?
> >
> >Thanks in advance
> >Fernando
> >.
> >

How check the results of the SQL Backups on serveral servers

Hi, I'm looking for a simple an easy solution to check the sqlserver backups,
every day we run some backups jobs in diferent servers. How i can verify the
backup jobs everyday without check the enterprise manager of ervery server?
Is posible create a report where the copys are succesfuly per server and per
sql server database?
Thanks in advance
Fernando
... Or use xp_smtp_sendmail and remove xp_sendmail and create a version of xp_sendmail which in turn
calls xp_smtp_sendmail. As described on www.sqldev.net.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:092001c490db$294323c0$a401280a@.phx.gbl...[vbcol=seagreen]
> You could get SQL Server Agent to email you results of the
> backup, but that is dependant upon SQL Agent having a
> domain user id with a mailbox.
> Thanks
> Peter
> "There is no such thing as public opinion. There is only
> published opinion."
> Winston Churchill
>
> the sqlserver backups,
> How i can verify the
> of ervery server?
> per server and per

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.
I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegro ups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>
|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly because
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, it
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired results.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegro ups.com...

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly becaus
e
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, i
t
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired result
s.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:

>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:
> I can't make head or tail of what you are trying to do here (mostly because
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, it
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired results.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
> >I am trying to run a query that filters using an IN statment, but the
> > IN statement needs to be dynamically generated based upon some
> > criteria. Here is my example:
> >
> > DECLARE @.tempTable TABLE
> > (
> > letter char(1),
> > id int
> > )
> >
> > INSERT INTO @.tempTable VALUES ('A', 1)
> > INSERT INTO @.tempTable VALUES ('B', 2)
> > INSERT INTO @.tempTable VALUES ('C', 3)
> >
> > SELECT * FROM @.tempTable WHERE letter IN
> > (Case 2
> > WHEN 1 THEN ('A')
> > WHEN 2 THEN ('B', 'C')
> > END)
> >
> >
> > If you try to run this code, it gives an error on the line that
> > contains the 'B', 'C' (incorrect syntax near ',').
> >
> > If you remove the C and just leave the two lines, one with A, one with
> > B, then it will work as expected.
> >|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:
>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How can one edit the sysusers table under SQL 2005?

ur database needs to run under a specific user, so we
have it under sysusers table. When we do a restore or a copy,
we used to go into that table rename the user and go into SQL security
and create that user and assign the DB, we no longer can do that.
How can one edit the sysusers table under SQL 2005?
Please help...
Hi
It is strongly recommended to not alter system tables directly
How about CREATE USER command?
Also search om internet for two stored procedures('sp_help_revlogin') that
provided by MS transfer the USERS wi6th their oridinal SID
"API Conektia" <api@.online.nospam> wrote in message
news:zezu4tlp82yz.1l9ba905uokev.dlg@.40tude.net...
> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...
|||You can no longer view the systables in SQL2005 they are hidden now. Look for
compatibility Views in BOL
Regards
"API Conektia" wrote:

> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...
>
|||BOL? I can see the table by doing a select * from sysusers, but not thru
the GUI.
I fixed my issue doing:
USE BD;
GO
EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
GO
thanks for all replies
On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
[vbcol=seagreen]
> You can no longer view the systables in SQL2005 they are hidden now. Look for
> compatibility Views in BOL
> Regards
> "API Conektia" wrote:
|||BOL is Books Online - the SQL Server help file.
Sysusers is not a table in 2005 - it's a view provided for
backwards compatibility. It's listed under the system views
in the GUI. You have to use the built in procedures,
functions or other supported methods to modify system data
in 2005 - no more hacking system tables.
-Sue
On Wed, 22 Feb 2006 15:08:14 +0100, API Conektia
<api@.online.nospam> wrote:
[vbcol=seagreen]
>BOL? I can see the table by doing a select * from sysusers, but not thru
>the GUI.
>I fixed my issue doing:
>USE BD;
>GO
>EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
>GO
>thanks for all replies
>
>On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:

How can one edit the sysusers table under SQL 2005?

ur database needs to run under a specific user, so we
have it under sysusers table. When we do a restore or a copy,
we used to go into that table rename the user and go into SQL security
and create that user and assign the DB, we no longer can do that.
How can one edit the sysusers table under SQL 2005?
Please help...Hi
It is strongly recommended to not alter system tables directly
How about CREATE USER command?
Also search om internet for two stored procedures('sp_help_revlogin') that
provided by MS transfer the USERS wi6th their oridinal SID
"API Conektia" <api@.online.nospam> wrote in message
news:zezu4tlp82yz.1l9ba905uokev.dlg@.40tude.net...
> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...|||You can no longer view the systables in SQL2005 they are hidden now. Look for
compatibility Views in BOL
Regards
"API Conektia" wrote:
> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...
>|||BOL? I can see the table by doing a select * from sysusers, but not thru
the GUI.
I fixed my issue doing:
USE BD;
GO
EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
GO
thanks for all replies
On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
> You can no longer view the systables in SQL2005 they are hidden now. Look for
> compatibility Views in BOL
> Regards
> "API Conektia" wrote:
>> ur database needs to run under a specific user, so we
>> have it under sysusers table. When we do a restore or a copy,
>> we used to go into that table rename the user and go into SQL security
>> and create that user and assign the DB, we no longer can do that.
>> How can one edit the sysusers table under SQL 2005?
>> Please help...|||BOL is Books Online - the SQL Server help file.
Sysusers is not a table in 2005 - it's a view provided for
backwards compatibility. It's listed under the system views
in the GUI. You have to use the built in procedures,
functions or other supported methods to modify system data
in 2005 - no more hacking system tables.
-Sue
On Wed, 22 Feb 2006 15:08:14 +0100, API Conektia
<api@.online.nospam> wrote:
>BOL? I can see the table by doing a select * from sysusers, but not thru
>the GUI.
>I fixed my issue doing:
>USE BD;
>GO
>EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
>GO
>thanks for all replies
>
>On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
>> You can no longer view the systables in SQL2005 they are hidden now. Look for
>> compatibility Views in BOL
>> Regards
>> "API Conektia" wrote:
>> ur database needs to run under a specific user, so we
>> have it under sysusers table. When we do a restore or a copy,
>> we used to go into that table rename the user and go into SQL security
>> and create that user and assign the DB, we no longer can do that.
>> How can one edit the sysusers table under SQL 2005?
>> Please help...

How can one edit the sysusers table under SQL 2005?

ur database needs to run under a specific user, so we
have it under sysusers table. When we do a restore or a copy,
we used to go into that table rename the user and go into SQL security
and create that user and assign the DB, we no longer can do that.
How can one edit the sysusers table under SQL 2005?
Please help...Hi
It is strongly recommended to not alter system tables directly
How about CREATE USER command?
Also search om internet for two stored procedures('sp_help_revlogin') that
provided by MS transfer the USERS wi6th their oridinal SID
"API Conektia" <api@.online.nospam> wrote in message
news:zezu4tlp82yz.1l9ba905uokev.dlg@.40tude.net...
> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...|||You can no longer view the systables in SQL2005 they are hidden now. Look fo
r
compatibility Views in BOL
Regards
"API Conektia" wrote:

> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...
>|||BOL? I can see the table by doing a select * from sysusers, but not thru
the GUI.
I fixed my issue doing:
USE BD;
GO
EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
GO
thanks for all replies
On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
[vbcol=seagreen]
> You can no longer view the systables in SQL2005 they are hidden now. Look
for
> compatibility Views in BOL
> Regards
> "API Conektia" wrote:
>|||BOL is Books Online - the SQL Server help file.
Sysusers is not a table in 2005 - it's a view provided for
backwards compatibility. It's listed under the system views
in the GUI. You have to use the built in procedures,
functions or other supported methods to modify system data
in 2005 - no more hacking system tables.
-Sue
On Wed, 22 Feb 2006 15:08:14 +0100, API Conektia
<api@.online.nospam> wrote:
[vbcol=seagreen]
>BOL? I can see the table by doing a select * from sysusers, but not thru
>the GUI.
>I fixed my issue doing:
>USE BD;
>GO
>EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
>GO
>thanks for all replies
>
>On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
>

How can Internal Activation use more resources?

Hi,

I'm using service broker queue with internal activation to run a stored procedure.
The DB server is windows 2003 R2, 4 cpu, with SQL server 2005 SP2.
When I'm runing the stored procedure directly from the sql management studio it takes about 75% of the cpu and running for about a minute, but when the stored procedure is activated by the queue internal activation (as a background process) it uses only 25% of the machine cpu (my guess it uses only 1 cpu insted of all 4 cpu) and running for much longer time (sometimes even more than one hour).
How can I change this behavior? I want it to run as fast as possible.

The queue decleration is:

CREATE QUEUE [TaskQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ProcessTasksProc,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF);

Thanks in advance,
Shai Brumer

If you have 4 CPU, why are you restricting the MAX_QUEUE_READERS to 1?

How did you measure this? Did you preload the queue with messages and run the procedure? Or are the messages continuosly incomming from some local or remote sender?

Note that activated procedures are looped for you by the activation mechanism, that is if the procedure exists and there are still message in the queue, it is invoked again. You will not see a separate Activation:End and Activationtart events for this, so it may look like is running for hours but in fact is contiuosly re-invoked because there are still message in the queue.

With only 1 activated proc, it is normal to consume only 25% of the 4 CPUs. The only way a procedure could use more than one CPUs (and push CPU utilization above 25%) would be to contain many queries that can be parallelized. Do you have anything in the procedure that could be heavilly paralelized and thus explain the 75% when launched from SSMS?

|||

My intention is to run the stored procedure asynchronously from another stored procedure, and for this I used service broker queues as async mechanism- when I want to run the procedure asynchronously I enter a message into the queue that is configured to active the procedure. The procedure is heavy and using all the cpu’s of the server (I can see parallelization in the execution plan and the server cpu is up to 75% when I’m running the procedure directly from the management studio).

I set up the MAX_QUEUE_READERS to 1 because I want a single procedure to run at any given time- but I want the procedure to take as much resource she need in order to complete the run as fast as it can.

Is this the normal behavior of internal activation? (Useing only 1 CPU).

Is there any way to change this?

|||Activation procedure should behave just as if the procedure was launched from SSMS. If you ahve plans that are heavily parallel, they should continue to be parallel and use 70% CPU. Can you compare the plans when run under activation vs. when run from SSMS?|||

How can I get the execution plan from the SSMS ?

|||

I ment:

How can I get the execution plan from the activation ?

|||Attach the Profiler and monitor the server and monitor the Performance/Showplan XML for Query Compile. The Profiler will show the plans just like SSMS does and each event/plan can be saved as an individual .sqlplan file (right click on the event and select 'Extract Data').|||

It seems the problem was the statistics of the table.

Thanks for the help.

Shai.

how can I throw a custom error code?

I'm trying to run a DTS package, and cant find an easy direct way to run it. It seems like the easiest solution would be to throw a custom error- then the server notices the error and runs a custom job, and the custom job runs the DTS. This is a roundabout way, but seems like it would be the simplest solution.

Anyways, how do I throw the error in my code? Do I just write a throw 3829 statement?

Also maybe this is a very bad way to do this- any suggestions?

You can use dtsrun commandline utility Take a look at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_dtsrun_95kp.asp

BTW, SQL provides a extended stored procedure xp_cmdshell to perform commandline tasks, so you can also call dtsrun utility in T-SQL:

exec('master..xp_cmdshell ''dtsrun -S iori\Athena -E -N test ''')

|||I saw that command line solution a while ago, but I was told that it isnt the best thing to do security wise.

I ended up finding DTS.Packages (DTS.Package dtsp = new DTS.Package();) which seems to work pretty well. The only problem is that the web account does not have the correct permissions. By enabling a trusted connection, and tweaking IIS, it now prompts the user for a username/pswd when they get to the site. If they enter in an account with the correct permissions, then they are able to run the DTS job.

Now I'm trying to get rid of this annoyance. Is it possible to somehow force a login to a specific trusted account? If not then I was thinking of trying to run a job that will just run the DTS package, but I don't know how to do that either. Any suggestions?

How can I tell whether SQL Server is able to listen to port 1433?

I want to know how to check whether SQL Server 2000 can listen to port 1433 or not. If I run netstat -a, am I suppose to see port 1433 regardless of what service pack I have applied to SQL Server 2000?Things get a bit complicated at this level.

If you run netstat without the -n parameter, you'll probably never see port 1433 because it has a named equivalent.

If you run a named instance of SQL Server, you'll find a redirector listening on port 1434 and SQL Server listening on an arbitrary free port (which the redirector will point incoming connections to when they arrive).

If you run an unnamed instance of SQL Server, you should find it listening on port 1433 as you expected.

-PatP|||Look for this line in SQL Server errorlog:

SQL server listening on <IP_Address>: <Port_Number>.|||Hi Pat,

I believe you mean I should see ms-sql-s but I don't see that one too. I'm running netstat -a on a machine with only the default instance of SQL Server 2000 which I can see port 1433 in the Server Network Utility.

I have a feeling that the issue may be related to XP SP2 RC2. I have tried to add port 1433 and 1434 to the Windows Firewall. I have also tried to add the program sqlservr to the Windows Firewall. I have also turned off Windows Firewall. But when I run netstat -a on all these combinations, I don't see port 1433 or ms-sql-s.|||Do any of the errorlogs (SQL, NT, or other) show anything interesting?

-PatP|||Hi Pat,

The interesting thing that I see is that SQL Server is now only listening to Shared Memory and Named Pipes in SQL Server Logs. But I have checked the SQL Server Logs before XP SP2 RC2 is applied, the SQL Server was listening to TCP, Shared Memory, and Named Pipes. I have checked that the current setting in Server Network Utility and in registry are both showing using TCP and port 1433. There is only the default instance in that machine. Since SQL Server is no longer listening to TCP, this explains why I don't see port 1433. However, the problem is why SQL Server is no longer listening to TCP.|||Try this (http://sqljunkies.com/Newsgroups/microsoft.public.sqlserver.connect/2004/4/27/188677.aspx).

-PatP|||Hi Pat,

I have just tried by switching the port to 0 (stop and start) and then switch back to 1433. (stop and start). The SQL Server is still listening to Shared Memory and Named Pipes.|||Boy, then that beats me. I'll try to bounce this off of some of the guys with real GQ (Geek Quotient) in the morning, but I may not get any response for about 24 hours. You've piqued my curiousity with this one!

-PatP|||If this is a problem of XP SP2 RC2, someone should have spotted this and I cannot imagine MS will release RC2 with this kind of issue. But if it is not, it is a mystery so far.

I'm going to listen to the XP SP2 and SQL Server webcast. May be it will tell me something.|||I believe I found out what happen. After I install XP SP2 RC2 on SQL Server 2000 Personal Edition with SP2, TCP/IP and UDP ports are automatically disabled according to Event Viewer log. I will install SP3a to see whether it will resolve this issue.|||Ahhh! A proactive Slammer-stopper. I hadn't thought of that!

-PatP|||I have applied SP3a and now TCP is open. I believe MS should do a better job in logging this information in Event Viewer. It is logged as INFORMATION entry so it will be easily be ignored.

Thank you for your help.|||Yeah, I'd fire that off to your ADC or TAM, or at the very least send it to mswish. The event message should be more informative.

-PatP|||I just read the FAQ: How Windows XP SP2 Affects SQL Server and MSDE. It has the information about this issue but it says it will create an error entry in the Windows Error log. So, MS just needs to change information to error.