@@ERROR & CREATE TABLE

Hi

I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:

DECLARE @.ERRCODE INT

CREATE TABLE x
SET @.ERRCODE = @.@.ERROR

This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.

Is there a way that I can capture the error using @.@.ERROR and still let the script run ?

Thanks in advance

MicksterWell, you shouldn't. "On Error Resume Next" is not present in T-SQL. What you should do is perform a validation for presence of object before attempting to create it.if objectproperty(object_id('dbo.x'), 'isusertable')=1 drop table dbo.x

create table dbo.x (f1 int, ...)|||Thanks, but I already understand that I should check for the existance of the object - as stated in my last mail. I want to check the table is being created correctly incase of other events, like permissions, file full, etc.

: xp_smtp_sendmail error on windows server 2005 (x64) n sql server 2005 (64 bit)

hi, i have an application that was running successfully on sql server 2000 x86 machine but now its been migrated to sql server 2005(64 bit) with windows 2003 server x64 machine. And now the xp_smtp_sendmail is not working when called through sql server even declare @.rc int exec @.rc = master.dbo.xp_smtp_sendmail @.server = 'my.server.com', @.port = 25, @.ping = 1 select RC = @.rc wont work it would give error that could not load the dll or one of its module not a valid win32 application. but its working fine in x86 win server 2003 n sql server 2005 machine when i run dependency walkthrough it, it shows some dlls missing i have also read that in syswow64 can run only those application that are not running in kernel mode. what might be the problem...or should i look for another alternative.. thanks for help in advance

Hi,

I have also had some problems with xp_smtp_sendmail in SQL 2005.

But now SQL 2005 supports the use of SMTP email with out the need for a MAPI client. There is minor set up and then you can use the procedure msdb.dbo.sp_send_dbmail. It works basically like xp_smtp_sendmail.

To set up database mail you need to first make sure that Database Mail is enabled in the MS SQL server 2005 Surface Area configuration. Then go into SQL server management studio under Management and right click on Database Mail and select Configure Database Mail.

Here are a couple of links http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp

http://www.databasejournal.com/features/mssql/article.php/3626056

|||Very helpful information, it helped me a lot.. Thank you|||

can you really to send e-mails on 64-bit version?

could you help me in this case?

answer me plz!

|||

I use msdb.dbo.sp_send_dbmail.

It is easier and works better...

|||"msdb.dbo.sp_send_dbmail" works on 64-bit without any errors?
how do you tune environment of SQL Server 2005 (64-bit) for working with "msdb.dbo.sp_send_dbmail"?

could you get me helping article?
|||

This is easy, I will look for an article tomorrow, but for now just do this

Get a SMTP server that you can connect to and you know you have privledges on use outlook express to test it, or some other pop client.

Once you know you have a 'working' smtp server that you can connect to. Set up DBMAIL

to do this just open up the 'Management' folder and right click on dbmail, stomp thru the menu's to set up an account and a profile.

Test this profile by again right clicking on the dbmail icon under the managment folder of your server, and do a "test email" and select your profile check your reciepent's mailbox for the mail. If it doesn't work check the history on the same icon.

Once you KNOW that works....

Then go to the msdb database and drill down to the msdb.dbo.sp_send_dbmail script it to a query window so that you can find all the variables you can give to the stored procedure when you call it.. Like @.profile, @.recepients, @.body, @.subject etc...

Then try to send a message thru your query window and you are done....

That is the short version... I will look for 8X10 glossies tomorrow, and post...

Best Regards,

Dow

|||i've done like you wrote.
outlook has sent the e-mails from this computer (64-bit).

but i've got this error:

Date 2007-04-19 10:00:41
Log Database Mail (Database Mail Log)

Log ID 49
Process ID 4556
Mail Item ID 20
Last Modified 2007-04-19 10:00:41
Last Modified By sa

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-04-19T10:00:40). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine).
)

i cannot find in internet some clarifying answer about this problem
|||

Okay, so you KNOW you can send mail thru your SMTP server from the very same box that you have sql loaded on.

Therefore, what dbmail is complaining about, if you are doing the send test message step, then the account you are using cannot

authenticate with the SMTP server. Since you KNOW you have sent SMTP mail from the same nic card to the smtp server you know that there is no network issues or server issues. The problem MUST be with the way that you have answered the questions on the ACCOUNT.

Now is your SMTP server a unix type or a windows type? If it is a unix type then make sure you set the username and password are set exactly like you did for the test with the outlook client. and you don't use the secure socket layer setting. If you smtp server will allow anonymous use that.

notice your error is telling you that it cannot connect. Also make sure you have put the correct NAME for the server and you are using the correct port, the default port is 25...

You are almost there, don't give up...

|||GUYS!!!!!!

It WORK!!!

See:

Database Mail in SQL Server 2005
www.databasejournal.com/features/mssql/article.php/3626056
|||

Hi, guys!

I have another problem with SQL Server 2005 & Mail & 64-bit.

I can sent e-mail from SQL Server 2005, but how can i receive e-mail from internet to SQL Server 2005?

I've got the answer:

Msg 17938, Level 16, State 0, Line 0
SQL Mail does not work with the 64-bit version of SQL Server

Help me plz.

|||DatabaseMail is an SMTP client only, which means that is can send but not receive emails. Receiving emails requires POP3 or IMAP support. SQLMail has that built-in, but is being depreciated in coming versions of SQL Server so you'll want to stay away from that implementation.

SQLMail is available in the 32-bit version of SQL Server 2005, but not in the 64-bit version.

HTH...

Joe|||I was wondering if you have a fix to the error on sending mail using the windows vista edition using the windows mail. I love the mail program but can only receive. Get an error report about the port 25. Thanks for any help I can get.

**Deny**

Hi
I am working with SQL 2000 and I want to prevent some users of some
editing action and I could successfully do it by following statement,
DENY SELECT, INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom
Now, I want to know how can I automatically deny some new tables or views
which will be created after the time I performed Deny command. Mary, John,
Tom are all db_owner and automatically will be granted to all actions when
a new table created, but I want to perform above command automatically for
each new table which begins with 'au', How is it possible?
I would be grateful if you help me.Hi
If those users are not members of dbo_owner database role so they would not
have an access to the new object created as dbo or other user
<R> wrote in message news:ops7g4arqwmw7tkz@.system109.parskhazar.net...
> Hi
> I am working with SQL 2000 and I want to prevent some users of some
> editing action and I could successfully do it by following statement,
> DENY SELECT, INSERT, UPDATE, DELETE
> ON authors
> TO Mary, John, Tom
> Now, I want to know how can I automatically deny some new tables or views
> which will be created after the time I performed Deny command. Mary, John,
> Tom are all db_owner and automatically will be granted to all actions when
> a new table created, but I want to perform above command automatically for
> each new table which begins with 'au', How is it possible?
> I would be grateful if you help me.|||DENY is only needed is when you want to negate a previous GRANT and is
usually done when permissions are inherited via role membership. However,
db_owner is a special role that allows members to have the same permissions
as the object owner. Since permissions are not checked for object owners,
GRANT and DENY have no affect on db_owner role members.
The bottom line is that Mary, John and Tom should not be db_owner role
members if you need to restrict their permissions in the database.
Hope this helps.
Dan Guzman
SQL Server MVP
<R> wrote in message news:ops7g4arqwmw7tkz@.system109.parskhazar.net...
> Hi
> I am working with SQL 2000 and I want to prevent some users of some
> editing action and I could successfully do it by following statement,
> DENY SELECT, INSERT, UPDATE, DELETE
> ON authors
> TO Mary, John, Tom
> Now, I want to know how can I automatically deny some new tables or views
> which will be created after the time I performed Deny command. Mary, John,
> Tom are all db_owner and automatically will be granted to all actions when
> a new table created, but I want to perform above command automatically for
> each new table which begins with 'au', How is it possible?
> I would be grateful if you help me.|||Even if you could deny these people access, if they are in the db_owner
role, they could just grant the rights back to themselves!
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OC6CJg9VGHA.1160@.TK2MSFTNGP09.phx.gbl...
> DENY is only needed is when you want to negate a previous GRANT and is
> usually done when permissions are inherited via role membership. However,
> db_owner is a special role that allows members to have the same
> permissions as the object owner. Since permissions are not checked for
> object owners, GRANT and DENY have no affect on db_owner role members.
> The bottom line is that Mary, John and Tom should not be db_owner role
> members if you need to restrict their permissions in the database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <R> wrote in message news:ops7g4arqwmw7tkz@.system109.parskhazar.net...
>

(provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

I am getting the following error when i try to connect to the my web site using froma different server.

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

i am using sql express and i attach the database through the connection string in the web config.

Any ideas

The error indicates the connection was eatablished but then closed for some reason, this can happen if the SQL2005 instance is out of the available connections when the instance is configured to use a nonzero maximum number of concurrent connections. Check the SQL ERRORLOGs for detailed information, which you can get by executing xp_readerrorlog in Management Studio. If you find something like "Could not connect because the maximum number of '5' user connections has already been reached.", you can execute such command:

sp_configure 'user connections',0