Database Overwritten: procedures

Showing posts with label procedures. Show all posts

Thursday, March 22, 2012

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

Wednesday, March 21, 2012

Database Migration from MS SQL Server 2000 to DB2 on Z/OS

Hello,

We have to migrate the database from MS SQL 2000 to DB2 on Z/OS, Including the tables,triggers,store procedures,etc...
1. What will be the approach ?
2. Whehter any one did in this area ?
3. What is the pros and cons?
4. What is the best possbile tool can be use.

regs
ArunThis belongs in another forum i.e. MS SQL. Will be transferred.

moderator :cool:

Saturday, February 25, 2012

database mail Vs. xp_smtp_sendmail

Hi,
we are in the process of upgrading SQL Server 2000 to 2005,
Now, a lot of emails are sent within stored procedures using
xp_smtp_sendmail.
Now this procedure is quite nice because it lets the caller
define diffrent From names and ReplyTo depending on certain conditions.
Since I can't reuse this procedure in SQL 2005, I am testing the
Database Mail using the sp_send_dbmail stored procedure along with
some profiles I created. It works great, but...
My problem here is that sp_send_dbmail doesn't let you dynamically change
the FROM and REPLYTO parameters, it uses the ones on the profile
sp_send_dbmail
recieves in parameter. This leaves me with a dilemna because the from and
replyTo
adresses possibility are too great (that is why it was made dynamic in our
SQL 2000 using xp_smtp_sendmail)
How should I proceed if I do not want to create 1 profile per possibility?
Is there another way or another SMTP send mail procedure or technique I
could use in SQL 2005
that will allow me to do the same thing as in XP_smtp_SendMail ?
Ideas anyone ?
Thanks> Since I can't reuse this procedure in SQL 2005
Why not? As long as you are on 32-bit, you can install the DLL on 2005 the
same way you did on 2000.
A|||> Since I can't reuse this procedure in SQL 2005, I am testing the
You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi,
> we are in the process of upgrading SQL Server 2000 to 2005,
> Now, a lot of emails are sent within stored procedures using
> xp_smtp_sendmail.
> Now this procedure is quite nice because it lets the caller
> define diffrent From names and ReplyTo depending on certain conditions.
>
> Since I can't reuse this procedure in SQL 2005, I am testing the
> Database Mail using the sp_send_dbmail stored procedure along with
> some profiles I created. It works great, but...
>
> My problem here is that sp_send_dbmail doesn't let you dynamically change
> the FROM and REPLYTO parameters, it uses the ones on the profile
> sp_send_dbmail
> recieves in parameter. This leaves me with a dilemna because the from and
> replyTo
> adresses possibility are too great (that is why it was made dynamic in our
> SQL 2000 using xp_smtp_sendmail)
>
> How should I proceed if I do not want to create 1 profile per possibility?
> Is there another way or another SMTP send mail procedure or technique I
> could use in SQL 2005
> that will allow me to do the same thing as in XP_smtp_SendMail ?
>
> Ideas anyone ?
>
> Thanks
>
>|||Thanks, But it is a x64 bit SQL Server..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>> Since I can't reuse this procedure in SQL 2005, I am testing the
> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "msnews.microsoft.com" <spam@.spam.com> wrote in message
> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> we are in the process of upgrading SQL Server 2000 to 2005,
>> Now, a lot of emails are sent within stored procedures using
>> xp_smtp_sendmail.
>> Now this procedure is quite nice because it lets the caller
>> define diffrent From names and ReplyTo depending on certain conditions.
>>
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> Database Mail using the sp_send_dbmail stored procedure along with
>> some profiles I created. It works great, but...
>>
>> My problem here is that sp_send_dbmail doesn't let you dynamically change
>> the FROM and REPLYTO parameters, it uses the ones on the profile
>> sp_send_dbmail
>> recieves in parameter. This leaves me with a dilemna because the from
>> and
>> replyTo
>> adresses possibility are too great (that is why it was made dynamic in
>> our
>> SQL 2000 using xp_smtp_sendmail)
>>
>> How should I proceed if I do not want to create 1 profile per
>> possibility?
>> Is there another way or another SMTP send mail procedure or technique I
>> could use in SQL 2005
>> that will allow me to do the same thing as in XP_smtp_SendMail ?
>>
>> Ideas anyone ?
>>
>> Thanks
>>
>>
>|||> Thanks, But it is a x64 bit SQL Server..
Well, you can't use a 32 bit DLL in 64 bit code. See Russell's reply to Fred's post for suggestion.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:euuEqS8aHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Thanks, But it is a x64 bit SQL Server..
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Server.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "msnews.microsoft.com" <spam@.spam.com> wrote in message
>> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> we are in the process of upgrading SQL Server 2000 to 2005,
>> Now, a lot of emails are sent within stored procedures using
>> xp_smtp_sendmail.
>> Now this procedure is quite nice because it lets the caller
>> define diffrent From names and ReplyTo depending on certain conditions.
>>
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> Database Mail using the sp_send_dbmail stored procedure along with
>> some profiles I created. It works great, but...
>>
>> My problem here is that sp_send_dbmail doesn't let you dynamically change
>> the FROM and REPLYTO parameters, it uses the ones on the profile
>> sp_send_dbmail
>> recieves in parameter. This leaves me with a dilemna because the from and
>> replyTo
>> adresses possibility are too great (that is why it was made dynamic in our
>> SQL 2000 using xp_smtp_sendmail)
>>
>> How should I proceed if I do not want to create 1 profile per possibility?
>> Is there another way or another SMTP send mail procedure or technique I
>> could use in SQL 2005
>> that will allow me to do the same thing as in XP_smtp_SendMail ?
>>
>> Ideas anyone ?
>>
>> Thanks
>>
>>
>|||> Thanks, But it is a x64 bit SQL Server..
In that case, you could learn the format and write a stored procedure that
creates a properly-formatted text file with a .msg extension and drops it
into the pickup folder of a local SMTP server (which doesn't necessarily
have to live on the same server, so could be 32-bit). This would allow you
to have full control over FROM/REPLYTO etc.
Another option is to set up a 32-bit edition of Express or Workgroup on
another server (even a web server), and route messages through it. As long
as the network path is reliable, not a huge difference between 'EXEC
master.dbo.xp_smtp_sendmail' and 'EXEC
linked_server_name.master.dbo.xp_smtp_sendmail'...
A|||BTW, I submitted an enhancement request about this issue. You can vote on
it if you have a passport and feel strongly enough about it to have it
addressed in a future version of SQL Server:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264498
A

Friday, February 24, 2012

Database mail attachments are filling the hard disk

My company started using database mail recently.Most emails sent have relatively large attachments. We've set up procedures for removing emails older than a given period of time from the MSDB database, however we've now had a server completely run out of the disk space due to attachments being stored in the C:\Documents and Settings\User\Local Settings\Temp folder!?! sysmail_delete_mailitems_sp sproc only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.

Somebody claims that those temp files are needed in case the file being attached is deleted before it has been sent for example. I'm agree with the procedure, but... Why SQL server does not delete the temporary file after a sucessfull delivery of the message?

It is not too hard to code some job to delete these files daily, but ... Is it too hard for Microsoft Team to develop this simple functionality?

Thanks,

Philip

I think that temp directory is associated to operating system for such actions its better to code a seperate steps to drop those temp files too. Also if you have any anti-virus or anti-spyware tool installed on the server then these will be in exclusive usage hence operating system may not delete them, hence the explicit deletion is required.

I feel this is a good feature to have, you could provide such feedback on http://connect.microsoft.com site in thsi case to involve on next Service pack or in SQL 2008 release.

Database Mail and Linked Servers

We have a stored procedure that executes sp_send_dbmail with an @.query parameter. The @. query executes a stored procedures that has a linked server query in it. The linked server is SQL 2000. The login/user used by the link is valid and running the sproc by itself will return the expected results but it fails with the following error if run by the sp_send_dbmail sproc:

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: OLE DB provider "SQLNCLI" for linked server "Link_Server" returned message "Communication link failure".

HResult 0x2746, Level 16, State 1

TCP Provider: An existing connection was forcibly closed by the remote host.

HResult 0x4818, Level 14, State 1

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Any help will be greatly appreciated.

What selection are you using under the security option for the Linked Server setup in SQL Server Management?|||use logins' current security conttext. The user id and password are the same on both servers.|||

I started to get the same message as you, the first part that is: (I'm not using linked servers). So I don't know if this will help you or not.

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

This site was helpful to me.

http://forums.databasejournal.com/archive/index.php/t-42981.html

Apparently, the problem is with the SET QUOTED_IDENTIFIER. I applied his logic to my script and now it seems to be working. I also needed to stop and start dbmail to release the queue. I don't know why but it gets clogged. I have begun to put the stop and start near the beginning of all my scripts.

--These two lines will release any emails "clogged" in SS queue

EXEC msdb.dbo.sysmail_stop_sp;

EXEC msdb.dbo.sysmail_start_sp;

--new logic begin

SETQUOTED_IDENTIFIEROFF

set @.myqry ='SET QUOTED_IDENTIFIER ON '

set @.myqry = @.myqry +char(13)

--new logic end

set @.myqry = @.myqry

SELECT * FROM PPI_Reporting.DBRS.HpSyncCompareInvDate'

EXECUTE msdb.dbo.sp_send_dbmail @.profile_name=@.profile, @.recipients=@.recipients, @.importance='Normal'

,@.subject=@.subject, @.body=@.body

,@.query=@.myqry

|||I don't know how it affects the login failure but it is a linked server afterall

The sproc currently set nocount and ansi_warnings so we'll give quoted_identifiers a try. Thanks for the suggestion.|||

I'm sorry, but my solution only worked for about 10 times yesterday and then no longer works. I was excited to see it work after adding the SET QUOTED_IDENTIFIER statement so I posted here.

So I am back to square one. Let us know if you find a solution.

-Doug

|||See this http://sqlserver-qa.net/blogs/tools/archive/2007/04/20/tcp-provider-an-existing-connection-was-forcibly-closed-by-the-remote-host.aspx is any help.|||I appreciate the responses but none of them quite fit the issue. We found a workaround and have the sprocs running now. Rather than returning the output of the called sproc to the calling sproc, the data is inserted in a table and the calling sproc now queries the table.

Friday, February 17, 2012

Database log file

hi all

my database log file has been full and the H.D partition not have any space

what's the procedures to remove or empty the log file

thanks & regards

Are you running SQL Server 2000 or SQL Server 2005? What recovery model is your database using (Full or Simple)?

If you are using the Full Recovery model, you have to periodically backup and truncate the log. Otherwise, it will fill up. Most likely, you have a long-running transaction that is causing this.

If you are running SQL 2005, you can run this query to find out why the transaction log cannot be backed up and truncated.

-- Find the problem with the transaction log

SELECT name, database_id, log_reuse_wait_desc

FROM sys.databases

|||

I guess the question to be asked is, do you need to recover your database to a point in time or to the last full backup.

1. Point in time

a. Your recovery model needs to be set to 'Full' - which it is already because it's filling up.

b. You need to backup you log file routinely, the frequency is determined by how much data you are prepared to lose in the event of a failure i.e. if you only want a maximum 15 minutes of data lost then you need to backup your log file every 15 minutes. By backing up your log file the inactive portion of the log file will be truncated hence it will free up space within the log file for new transactions (note however this will not decrease the log file size on the disk) and reduce growth in the log file.

2. Recover to the last full database backup

a. Set your recovery model to 'simple', your log file will not fill up anymore and sql won't fall over because of disk space issues relating to the log file..

b. Backup you database each day (or as per your backup procedures/policies)

The first step is to reduce the size of your log file to make it more manageable:

1. Backup your log file to disk (or just truncate it if you're not interested in retaining the back file)

To Disk:

BACKUP LOG { database_name } TO DISK='your backup location'

Truncate:

BACKUP LOG { database_name } WITH TRUNCATE_ONLY

Both methods will remove the inactive portion of the log file. Once this has completed you need to reduce the size of the file on the disk, to do this have a look at the DBCC SHRINKFILE command in BOL, there are a few options that may be of interest.

Thursday, March 22, 2012

database mirroring -- failing over in stored procedures

database mirroring -- failing over in stored procedures

database mirroring -- failing over in stored procedures

Wednesday, March 21, 2012

Database Migration from MS SQL Server 2000 to DB2 on Z/OS

Saturday, February 25, 2012

database mail Vs. xp_smtp_sendmail

database mail Vs. xp_smtp_sendmail

database mail Vs. xp_smtp_sendmail

Friday, February 24, 2012

Database mail attachments are filling the hard disk

Database Mail and Linked Servers

Friday, February 17, 2012

Database log file

Database Overwritten

Blog Archive

About Me