How to execute a dynamic sql command in user define func

Hi,
I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
thank you.
regards,
florencelee
If you want the output of this dynamic SQL in a table,
you can use "insert into #Temp exec(@.finalstring)" and then process the temp
table. The #Temp table should be defined before.
"florencelee" wrote:

> Hi,
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> thank you.
> regards,
> florencelee
>
|||Hi,
But we can not run the exec inside a user define
function, am i right?
For eg, in the store procedure, we can put exec
(@.finalstring) but how we write in the user define
function?

>--Original Message--
>If you want the output of this dynamic SQL in a table,
>you can use "insert into #Temp exec(@.finalstring)" and
then process the temp[vbcol=seagreen]
>table. The #Temp table should be defined before.
>"florencelee" wrote:
the
>.
>
|||See my other reply...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:38a301c49f9f$406bf590$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> But we can not run the exec inside a user define
> function, am i right?
> For eg, in the store procedure, we can put exec
> (@.finalstring) but how we write in the user define
> function?
>
> then process the temp
> the

How to execute a dynamic sql command in user define func

Hi,
I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
thank you.
regards,
florenceleeIf you want the output of this dynamic SQL in a table,
you can use "insert into #Temp exec(@.finalstring)" and then process the temp
table. The #Temp table should be defined before.
"florencelee" wrote:
> Hi,
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> thank you.
> regards,
> florencelee
>|||Hi,
But we can not run the exec inside a user define
function, am i right?
For eg, in the store procedure, we can put exec
(@.finalstring) but how we write in the user define
function?
>--Original Message--
>If you want the output of this dynamic SQL in a table,
>you can use "insert into #Temp exec(@.finalstring)" and
then process the temp
>table. The #Temp table should be defined before.
>"florencelee" wrote:
>> Hi,
>> I have 3 string
>> select @.string = 'SELECT'
>> select @.string2 ='colname'
>> select @.string3 ='tablename'
>> select @.finalstring = @.string + @.string2 + @.string3
>> inside the user define function, we can not put in
>> exec command, how we run this dynamic sql command in
the
>> user define function?
>> thank you.
>> regards,
>> florencelee
>.
>|||See my other reply...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:38a301c49f9f$406bf590$a301280a@.phx.gbl...
> Hi,
> But we can not run the exec inside a user define
> function, am i right?
> For eg, in the store procedure, we can put exec
> (@.finalstring) but how we write in the user define
> function?
>
> >--Original Message--
> >If you want the output of this dynamic SQL in a table,
> >you can use "insert into #Temp exec(@.finalstring)" and
> then process the temp
> >table. The #Temp table should be defined before.
> >
> >"florencelee" wrote:
> >
> >> Hi,
> >> I have 3 string
> >>
> >> select @.string = 'SELECT'
> >> select @.string2 ='colname'
> >> select @.string3 ='tablename'
> >>
> >> select @.finalstring = @.string + @.string2 + @.string3
> >>
> >> inside the user define function, we can not put in
> >> exec command, how we run this dynamic sql command in
> the
> >> user define function?
> >>
> >> thank you.
> >> regards,
> >>
> >> florencelee
> >>
> >.
> >sql

How to exectue sql Function in asp.net

I want to exectue sql user define function in my asp.net application
I have one user define function in sql server 2000 that give good logic for my database access now i want to execute that function in asp.net

check this article

How to exec SQL user defined function?

Hi,
How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write
EXEC nameofstored procedure abc (@.abc is the parameter passed).. But How to run a SQL function ?
Thanks

assume function GetUser takes ID as input parameter , write the following in query analyzer and execute

BEGIN
DECLARE @.ID int
set @.ID = 45
SELECT dbo.GetUser(@.ID) AS 'User Name'
END

|||You need to either use it in a stored procedure and then call thestored procedure, or use it in some other statement, such as a SELECTstatement:
SELECT MyFunc(someField) FROM MyTable
There's no way to call it directly as you want.
Don
sql

How to encrypt the data of a field

I have a field aubot the password of a user.
How can I encrypt the field?
In my opinion, for most purposes you are probably better off NOT to store
encrypted passwords in the database. Store a secure hash of the password
instead. It's easy to compute a hash in your client application (take a look
the SHA1 classes in .NET for example).
If you really think you have a good reason to encrypt passwords and if you
are sure you how to do it securely then Google for "encryption" in these
SQLServer newsgroups and you will find links to various third-party
encryption tools.
David Portas
SQL Server MVP
|||Thank,
What is the difference between hash and encrypted password?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> glsD
:EYCdnaLqKue9RQrcRVn-jg@.giganews.com...
> In my opinion, for most purposes you are probably better off NOT to store
> encrypted passwords in the database. Store a secure hash of the password
> instead. It's easy to compute a hash in your client application (take a
look
> the SHA1 classes in .NET for example).
> If you really think you have a good reason to encrypt passwords and if you
> are sure you how to do it securely then Google for "encryption" in these
> SQLServer newsgroups and you will find links to various third-party
> encryption tools.
> --
> David Portas
> SQL Server MVP
> --
>
|||http://msdn.microsoft.com/library/en...simplified.asp
For password verification purposes an important advantage of hashing over
encryption is that there are no associated problems of key management.
Encryption requires keys, hashing does not.
David Portas
SQL Server MVP
|||ad wrote:
> Thank,
> What is the difference between hash and encrypted password?
>
A has is one-way; meaning, there is no way to reconstruct the original
data from the hash. En encrypted password can be decrypted. That's not
to say that a hash is completely secure.
David Gugick
Imceda Software
www.imceda.com
|||David,
"No way to reconstruct the original data" assumes there is no context
for the original data, but that is untrue for most of the world's
passwords. If a password is a name or word in any dictionary or
magazine, or a combination of two simple words or names, possibly with a
digit appended, the password is trivial to find from the hash. If the
hash is "plain", it's as easy as a lookup in a table of pre-computed
hashes (those are easy to find or build for SHA-1, MD5, or other
well-known hash functions - you could keep a few on your keychain). If
the hash is "salted," and the salt is known or easy to spot, it's only a
little harder - you need to hash a few million possibilities and see if
you get the hash in hand.
Steve Kass
Drew University
David Gugick wrote:

> ad wrote:
>
> A has is one-way; meaning, there is no way to reconstruct the original
> data from the hash. En encrypted password can be decrypted. That's not
> to say that a hash is completely secure.
>
|||David,
I agree that it's best not to store passwords at all. But
I've seen enough people think they're secure if they store
plain hashes to believe that any suggestion to store a hash
should be made with a caveat against weak passwords.
"Hashing alone won't protect weak passwords against
dictionary attacks." -- David Portas, November 2, 2004
SK
David Portas wrote:

>In my opinion, for most purposes you are probably better off NOT to store
>encrypted passwords in the database. Store a secure hash of the password
>instead. It's easy to compute a hash in your client application (take a look
>the SHA1 classes in .NET for example).
>If you really think you have a good reason to encrypt passwords and if you
>are sure you how to do it securely then Google for "encryption" in these
>SQLServer newsgroups and you will find links to various third-party
>encryption tools.
>
>
|||Since we're talking security I guess it is wisest to assume little or no
knowledge of the subject and to spell out all the caveats. That could be a
long haul though and it takes us a bit off-topic here. My initial reply
prompted the response I might have expected - that the OP didn't have a
well-defined reason for choosing encryption and hadn't considered the
alternatives, so I posted the article, which includes a mention of salting
passwords. An FAQ is the only way to deal with this one, I think.
David Portas
SQL Server MVP
|||Steve Kass wrote:[vbcol=seagreen]
> David,
> "No way to reconstruct the original data" assumes there is no context
> for the original data, but that is untrue for most of the world's
> passwords. If a password is a name or word in any dictionary or
> magazine, or a combination of two simple words or names, possibly
> with a digit appended, the password is trivial to find from the hash.
> If the hash is "plain", it's as easy as a lookup in a table of
> pre-computed hashes (those are easy to find or build for SHA-1, MD5,
> or other well-known hash functions - you could keep a few on your
> keychain). If the hash is "salted," and the salt is known or easy to
> spot, it's only a little harder - you need to hash a few million
> possibilities and see if you get the hash in hand.
> Steve Kass
> Drew University
>
> David Gugick wrote:
Well, I still think what I said is accurate. Notwithstanding the actual
security of using one technique over another, you can only try and hash
a value and see if you have a match. You're not going to try and somehow
to reverse engineer the hash itself. So it really is one way. It may not
be very secure. Simply enforcing a strong password standard can help
mitigate any security problems.
David Gugick
Imceda Software
www.imceda.com
|||Thank,
The password can be use c to hash.
I have another field aoubt the user's salary, my boss ask me it must be
encrypted too,
Are there any good method to encrypted , and need be recover.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> glsD
:V62dnf6F0INooAXcRVn-qg@.giganews.com...
> Since we're talking security I guess it is wisest to assume little or no
> knowledge of the subject and to spell out all the caveats. That could be a
> long haul though and it takes us a bit off-topic here. My initial reply
> prompted the response I might have expected - that the OP didn't have a
> well-defined reason for choosing encryption and hadn't considered the
> alternatives, so I posted the article, which includes a mention of salting
> passwords. An FAQ is the only way to deal with this one, I think.
> --
> David Portas
> SQL Server MVP
> --
>

How to encrypt the data of a field

I have a field aubot the password of a user.
How can I encrypt the field?In my opinion, for most purposes you are probably better off NOT to store
encrypted passwords in the database. Store a secure hash of the password
instead. It's easy to compute a hash in your client application (take a look
the SHA1 classes in .NET for example).
If you really think you have a good reason to encrypt passwords and if you
are sure you how to do it securely then Google for "encryption" in these
SQLServer newsgroups and you will find links to various third-party
encryption tools.
David Portas
SQL Server MVP
--|||Thank,
What is the difference between hash and encrypted password?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> glsD
:EYCdnaLqKue9RQrcRVn-jg@.giganews.com...
> In my opinion, for most purposes you are probably better off NOT to store
> encrypted passwords in the database. Store a secure hash of the password
> instead. It's easy to compute a hash in your client application (take a
look
> the SHA1 classes in .NET for example).
> If you really think you have a good reason to encrypt passwords and if you
> are sure you how to do it securely then Google for "encryption" in these
> SQLServer newsgroups and you will find links to various third-party
> encryption tools.
> --
> David Portas
> SQL Server MVP
> --
>|||http://msdn.microsoft.com/library/e...osimplified.asp
For password verification purposes an important advantage of hashing over
encryption is that there are no associated problems of key management.
Encryption requires keys, hashing does not.
David Portas
SQL Server MVP
--|||ad wrote:
> Thank,
> What is the difference between hash and encrypted password?
>
A has is one-way; meaning, there is no way to reconstruct the original
data from the hash. En encrypted password can be decrypted. That's not
to say that a hash is completely secure.
David Gugick
Imceda Software
www.imceda.com|||David,
"No way to reconstruct the original data" assumes there is no context
for the original data, but that is untrue for most of the world's
passwords. If a password is a name or word in any dictionary or
magazine, or a combination of two simple words or names, possibly with a
digit appended, the password is trivial to find from the hash. If the
hash is "plain", it's as easy as a lookup in a table of pre-computed
hashes (those are easy to find or build for SHA-1, MD5, or other
well-known hash functions - you could keep a few on your keychain). If
the hash is "salted," and the salt is known or easy to spot, it's only a
little harder - you need to hash a few million possibilities and see if
you get the hash in hand.
Steve Kass
Drew University
David Gugick wrote:

> ad wrote:
>
> A has is one-way; meaning, there is no way to reconstruct the original
> data from the hash. En encrypted password can be decrypted. That's not
> to say that a hash is completely secure.
>|||David,
I agree that it's best not to store passwords at all. But
I've seen enough people think they're secure if they store
plain hashes to believe that any suggestion to store a hash
should be made with a caveat against weak passwords.
"Hashing alone won't protect weak passwords against
dictionary attacks." -- David Portas, November 2, 2004
SK
David Portas wrote:

>In my opinion, for most purposes you are probably better off NOT to store
>encrypted passwords in the database. Store a secure hash of the password
>instead. It's easy to compute a hash in your client application (take a loo
k
>the SHA1 classes in .NET for example).
>If you really think you have a good reason to encrypt passwords and if you
>are sure you how to do it securely then Google for "encryption" in these
>SQLServer newsgroups and you will find links to various third-party
>encryption tools.
>
>|||Since we're talking security I guess it is wisest to assume little or no
knowledge of the subject and to spell out all the caveats. That could be a
long haul though and it takes us a bit off-topic here. My initial reply
prompted the response I might have expected - that the OP didn't have a
well-defined reason for choosing encryption and hadn't considered the
alternatives, so I posted the article, which includes a mention of salting
passwords. An FAQ is the only way to deal with this one, I think.
David Portas
SQL Server MVP
--|||Steve Kass wrote:[vbcol=seagreen]
> David,
> "No way to reconstruct the original data" assumes there is no context
> for the original data, but that is untrue for most of the world's
> passwords. If a password is a name or word in any dictionary or
> magazine, or a combination of two simple words or names, possibly
> with a digit appended, the password is trivial to find from the hash.
> If the hash is "plain", it's as easy as a lookup in a table of
> pre-computed hashes (those are easy to find or build for SHA-1, MD5,
> or other well-known hash functions - you could keep a few on your
> keychain). If the hash is "salted," and the salt is known or easy to
> spot, it's only a little harder - you need to hash a few million
> possibilities and see if you get the hash in hand.
> Steve Kass
> Drew University
>
> David Gugick wrote:
>
Well, I still think what I said is accurate. Notwithstanding the actual
security of using one technique over another, you can only try and hash
a value and see if you have a match. You're not going to try and somehow
to reverse engineer the hash itself. So it really is one way. It may not
be very secure. Simply enforcing a strong password standard can help
mitigate any security problems.
David Gugick
Imceda Software
www.imceda.com|||Thank,
The password can be use c to hash.
I have another field aoubt the user's salary, my boss ask me it must be
encrypted too,
Are there any good method to encrypted , and need be recover.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> glsD
:V62dnf6F0INooAXcRVn-qg@.giganews.com...
> Since we're talking security I guess it is wisest to assume little or no
> knowledge of the subject and to spell out all the caveats. That could be a
> long haul though and it takes us a bit off-topic here. My initial reply
> prompted the response I might have expected - that the OP didn't have a
> well-defined reason for choosing encryption and hadn't considered the
> alternatives, so I posted the article, which includes a mention of salting
> passwords. An FAQ is the only way to deal with this one, I think.
> --
> David Portas
> SQL Server MVP
> --
>sql

How to encrypt the data of a field

I have a field aubot the password of a user.
How can I encrypt the field?In my opinion, for most purposes you are probably better off NOT to store
encrypted passwords in the database. Store a secure hash of the password
instead. It's easy to compute a hash in your client application (take a look
the SHA1 classes in .NET for example).
If you really think you have a good reason to encrypt passwords and if you
are sure you how to do it securely then Google for "encryption" in these
SQLServer newsgroups and you will find links to various third-party
encryption tools.
--
David Portas
SQL Server MVP
--|||Thank,
What is the difference between hash and encrypted password?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¼¶¼g©ó¶l¥ó·s»D
:EYCdnaLqKue9RQrcRVn-jg@.giganews.com...
> In my opinion, for most purposes you are probably better off NOT to store
> encrypted passwords in the database. Store a secure hash of the password
> instead. It's easy to compute a hash in your client application (take a
look
> the SHA1 classes in .NET for example).
> If you really think you have a good reason to encrypt passwords and if you
> are sure you how to do it securely then Google for "encryption" in these
> SQLServer newsgroups and you will find links to various third-party
> encryption tools.
> --
> David Portas
> SQL Server MVP
> --
>|||http://msdn.microsoft.com/library/en-us/dnnetsec/html/cryptosimplified.asp
For password verification purposes an important advantage of hashing over
encryption is that there are no associated problems of key management.
Encryption requires keys, hashing does not.
--
David Portas
SQL Server MVP
--|||ad wrote:
> Thank,
> What is the difference between hash and encrypted password?
>
A has is one-way; meaning, there is no way to reconstruct the original
data from the hash. En encrypted password can be decrypted. That's not
to say that a hash is completely secure.
--
David Gugick
Imceda Software
www.imceda.com|||David,
"No way to reconstruct the original data" assumes there is no context
for the original data, but that is untrue for most of the world's
passwords. If a password is a name or word in any dictionary or
magazine, or a combination of two simple words or names, possibly with a
digit appended, the password is trivial to find from the hash. If the
hash is "plain", it's as easy as a lookup in a table of pre-computed
hashes (those are easy to find or build for SHA-1, MD5, or other
well-known hash functions - you could keep a few on your keychain). If
the hash is "salted," and the salt is known or easy to spot, it's only a
little harder - you need to hash a few million possibilities and see if
you get the hash in hand.
Steve Kass
Drew University
David Gugick wrote:
> ad wrote:
>> Thank,
>> What is the difference between hash and encrypted password?
> A has is one-way; meaning, there is no way to reconstruct the original
> data from the hash. En encrypted password can be decrypted. That's not
> to say that a hash is completely secure.
>|||David,
I agree that it's best not to store passwords at all. But
I've seen enough people think they're secure if they store
plain hashes to believe that any suggestion to store a hash
should be made with a caveat against weak passwords.
"Hashing alone won't protect weak passwords against
dictionary attacks." -- David Portas, November 2, 2004
SK
David Portas wrote:
>In my opinion, for most purposes you are probably better off NOT to store
>encrypted passwords in the database. Store a secure hash of the password
>instead. It's easy to compute a hash in your client application (take a look
>the SHA1 classes in .NET for example).
>If you really think you have a good reason to encrypt passwords and if you
>are sure you how to do it securely then Google for "encryption" in these
>SQLServer newsgroups and you will find links to various third-party
>encryption tools.
>
>|||Since we're talking security I guess it is wisest to assume little or no
knowledge of the subject and to spell out all the caveats. That could be a
long haul though and it takes us a bit off-topic here. My initial reply
prompted the response I might have expected - that the OP didn't have a
well-defined reason for choosing encryption and hadn't considered the
alternatives, so I posted the article, which includes a mention of salting
passwords. An FAQ is the only way to deal with this one, I think.
--
David Portas
SQL Server MVP
--|||Steve Kass wrote:
> David,
> "No way to reconstruct the original data" assumes there is no context
> for the original data, but that is untrue for most of the world's
> passwords. If a password is a name or word in any dictionary or
> magazine, or a combination of two simple words or names, possibly
> with a digit appended, the password is trivial to find from the hash.
> If the hash is "plain", it's as easy as a lookup in a table of
> pre-computed hashes (those are easy to find or build for SHA-1, MD5,
> or other well-known hash functions - you could keep a few on your
> keychain). If the hash is "salted," and the salt is known or easy to
> spot, it's only a little harder - you need to hash a few million
> possibilities and see if you get the hash in hand.
> Steve Kass
> Drew University
>
> David Gugick wrote:
>> ad wrote:
Well, I still think what I said is accurate. Notwithstanding the actual
security of using one technique over another, you can only try and hash
a value and see if you have a match. You're not going to try and somehow
to reverse engineer the hash itself. So it really is one way. It may not
be very secure. Simply enforcing a strong password standard can help
mitigate any security problems.
David Gugick
Imceda Software
www.imceda.com|||Thank,
The password can be use c to hash.
I have another field aoubt the user's salary, my boss ask me it must be
encrypted too,
Are there any good method to encrypted , and need be recover.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¼¶¼g©ó¶l¥ó·s»D
:V62dnf6F0INooAXcRVn-qg@.giganews.com...
> Since we're talking security I guess it is wisest to assume little or no
> knowledge of the subject and to spell out all the caveats. That could be a
> long haul though and it takes us a bit off-topic here. My initial reply
> prompted the response I might have expected - that the OP didn't have a
> well-defined reason for choosing encryption and hadn't considered the
> alternatives, so I posted the article, which includes a mention of salting
> passwords. An FAQ is the only way to deal with this one, I think.
> --
> David Portas
> SQL Server MVP
> --
>

how to encrypt and decypt as a IUSR with read and write only rights

How to decrypt or encrypt without making user a db_owner. It is for a web application and I do not want make the web user a db_owner. Is there a way to make this work without making the user a db_owner. Currently the user is a db_datareader and db_datawriter.

I created an asymmetric key for encryption by password. I am not using a master key because I want to keep the password seperately on the web server, so a hacker cannot get access to both if database gets hacked.

These are the steps I took when I logged in to SQL server management studio using windows authentication:

CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password';

INSERT INTO Payments (CreditCardNumber,enc_CreditCardNumber)
values( '458724124',
EncryptByAsymKey(AsymKey_ID('ccnumber'), '458724124') )

SELECT CONVERT(varchar(50), DecryptByAsymKey( AsymKey_Id('ccnumber'), enc_CreditCardNumber, N'password' ))
AS Creditcardnumber , Creditcardnumber
FROM payments where Creditcardnumber = '458724124'

When I use the above select statement it works if I make the user a db_owner but I get null if the user is just db_reader and db_writer.

Is there a way to do encryption without making the user a db_owner?

Yes, you can encrypt without being a db_owner. Note that you should not use asymmetric key encryption for encrypting data. You should use symmetric keys to encrypt data and asymmetric keys to protect other keys or for signing code.

To encrypt and decrypt with an asymmetric key, you need to grant CONTROL permission on the key (VIEW is sufficient for encryption, but for decryption you need CONTROL and knowledge of the password).

For encryption and decryption with a symmetric key, the user must be able to open the symmetric key (see permissions section of this BOL article: http://msdn2.microsoft.com/en-us/library/ms190499.aspx). You may also find the following blog post useful: http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

sql

How to enable full text search for symbols?

scenario:
I have a user interface done in Coldfusion that access SQL server 2K store
procedure on server 2003 that perform full text search on a field of a table.
I have remove all content of noise.enu as user can use anything as a search
index.
Why is it that SQL server or SQL Analyser still return err/msg when I use
symbol as serach index (e.g. {/}/?/>/</./,/@./!/$):
"Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained
only ignored words." - error msg from SQL Analyser
At minimun SQL server should return no recors. Instead it return err/msg,
that hangs my SQL service when run from Coldfusion and i had to manual
restart my service. Any ideas ?
These are invalid characters which cannot be searched upon. This should not
be hanging your SQL Server however. You should be trapping for such
characters on the client and removing them if the user is trying to search
on them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:95366FB3-4621-421E-9F5B-DD3118798A5F@.microsoft.com...
> scenario:
> I have a user interface done in Coldfusion that access SQL server 2K
> store
> procedure on server 2003 that perform full text search on a field of a
> table.
> I have remove all content of noise.enu as user can use anything as a
> search
> index.
> Why is it that SQL server or SQL Analyser still return err/msg when I use
> symbol as serach index (e.g. {/}/?/>/</./,/@./!/$):
> "Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. A clause of the query contained
> only ignored words." - error msg from SQL Analyser
> At minimun SQL server should return no recors. Instead it return err/msg,
> that hangs my SQL service when run from Coldfusion and i had to manual
> restart my service. Any ideas ?
|||well, i can code a filter at the coldfusion side. But i have to test all
possible invalid chars for SQL server 2K Full text search.
Is there any easy way out whereby I can configure SQL server 2K to return at
least no records or trap the err/msg return in SQL server as a result of
invalid chars in full text search.
xxx
"Hilary Cotter" wrote:

> These are invalid characters which cannot be searched upon. This should not
> be hanging your SQL Server however. You should be trapping for such
> characters on the client and removing them if the user is trying to search
> on them.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "eslim" <eslim@.discussions.microsoft.com> wrote in message
> news:95366FB3-4621-421E-9F5B-DD3118798A5F@.microsoft.com...
>
>

how to enable a user account

I found the the status of guest account in model database is abnormal,but i don't know how to make it active.

any suggestions?

thanks

You can make it active using

use model

go

GRANT CONNECT to guest

Attention, all new databases created will have the guest active; remember if a login hasn't an user in a database it use guest to acces database objects.

|||

If you mean that is disabled (as Gigi Ciubuc suggested), then it is the default behavior. Please notice that enabling guest access to model DB is highly discouraged as it will grant guest access on model, but on any newly created DB (as GigiCiubuc also pointed out).

If our guess is incorrect regarding the description of your problem; can you please explain with more detail what do you mean with an abnormal guest account? Thanks a lot.

I hope your question was answer as expected, but let us know if you have any further questions or feedback.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

thanks all.

The problem is solved . I just want to know how to enable user account

Although I can't find any way to enable the use in SQL SERVER Manage Studio, the "GRANT CONNECT to guest" command works

thanks

how to enable a user account

I found the the status of guest account in model database is abnormal,but i don't know how to make it active.

any suggestions?

thanks

You can make it active using

use model

go

GRANT CONNECT to guest

Attention, all new databases created will have the guest active; remember if a login hasn't an user in a database it use guest to acces database objects.

|||

If you mean that is disabled (as Gigi Ciubuc suggested), then it is the default behavior. Please notice that enabling guest access to model DB is highly discouraged as it will grant guest access on model, but on any newly created DB (as GigiCiubuc also pointed out).

If our guess is incorrect regarding the description of your problem; can you please explain with more detail what do you mean with an abnormal guest account? Thanks a lot.

I hope your question was answer as expected, but let us know if you have any further questions or feedback.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

thanks all.

The problem is solved . I just want to know how to enable user account

Although I can't find any way to enable the use in SQL SERVER Manage Studio, the "GRANT CONNECT to guest" command works

thanks

How to enable a non-SysAdmin user to start a job

I thought I could do this by granting permission to a few MSDB
procedures:
grant execute on sp_help_jobhistory to UserRole -- view job history
grant execute on sp_help_job to UserRole -- view job
grant execute on sp_start_job to UserRole -- start job
but I get an error:
Server: Msg 14262, Level 16, State 1, Line 1
The specified @.job_name ('Name Of Job') does not exist.
I looked at the sysjobs_view in MSDB (below) it looks like only the
owner, SysAdmins and TargetServersRole (what is this?) can view the
jobs. If I alter this view to include my UserRole this allows them to
start the job, but I don't want to do this because it is a hack.
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
What is a better solution?
We got around this by using a queue to disconnect the security.
1) create a queue table that takes the name of a job and a status value.
2) create a stored procedure that can read the queue, find any pending jobs,
run sp_startjob for any pending jobs, mark started jobs as complete.
3) create SQL Agent job owned by SA that runs once every minute and runs
that stored procedure.
4) user inserts job name in queue and waits ~1 min.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Laurence" <laurencen@.eurostop.co.uk> wrote in message
news:1161622953.615964.21000@.k70g2000cwa.googlegro ups.com...
>I thought I could do this by granting permission to a few MSDB
> procedures:
> grant execute on sp_help_jobhistory to UserRole -- view job history
> grant execute on sp_help_job to UserRole -- view job
> grant execute on sp_start_job to UserRole -- start job
> but I get an error:
> Server: Msg 14262, Level 16, State 1, Line 1
> The specified @.job_name ('Name Of Job') does not exist.
> I looked at the sysjobs_view in MSDB (below) it looks like only the
> owner, SysAdmins and TargetServersRole (what is this?) can view the
> jobs. If I alter this view to include my UserRole this allows them to
> start the job, but I don't want to do this because it is a hack.
> SELECT *
> FROM msdb.dbo.sysjobs
> WHERE (owner_sid = SUSER_SID())
> OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
>
> What is a better solution?
>

How to enable a non-SysAdmin user to start a job

I thought I could do this by granting permission to a few MSDB
procedures:
grant execute on sp_help_jobhistory to UserRole -- view job history
grant execute on sp_help_job to UserRole -- view job
grant execute on sp_start_job to UserRole -- start job
but I get an error:
Server: Msg 14262, Level 16, State 1, Line 1
The specified @.job_name ('Name Of Job') does not exist.
I looked at the sysjobs_view in MSDB (below) it looks like only the
owner, SysAdmins and TargetServersRole (what is this?) can view the
jobs. If I alter this view to include my UserRole this allows them to
start the job, but I don't want to do this because it is a hack.
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'),
0) = 1)
What is a better solution?We got around this by using a queue to disconnect the security.
1) create a queue table that takes the name of a job and a status value.
2) create a stored procedure that can read the queue, find any pending jobs,
run sp_startjob for any pending jobs, mark started jobs as complete.
3) create SQL Agent job owned by SA that runs once every minute and runs
that stored procedure.
4) user inserts job name in queue and waits ~1 min.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Laurence" <laurencen@.eurostop.co.uk> wrote in message
news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
>I thought I could do this by granting permission to a few MSDB
> procedures:
> grant execute on sp_help_jobhistory to UserRole -- view job history
> grant execute on sp_help_job to UserRole -- view job
> grant execute on sp_start_job to UserRole -- start job
> but I get an error:
> Server: Msg 14262, Level 16, State 1, Line 1
> The specified @.job_name ('Name Of Job') does not exist.
> I looked at the sysjobs_view in MSDB (below) it looks like only the
> owner, SysAdmins and TargetServersRole (what is this?) can view the
> jobs. If I alter this view to include my UserRole this allows them to
> start the job, but I don't want to do this because it is a hack.
> SELECT *
> FROM msdb.dbo.sysjobs
> WHERE (owner_sid = SUSER_SID())
> OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> OR (ISNULL(IS_MEMBER(N'TargetServersRole'),
0) = 1)
>
> What is a better solution?
>|||I see there is a way to do this by adding the user to the
TargetServersRole in MSDB, although it has a downside. See here:
http://www.mcse.ms/message638764.html
Daniel Jameson wrote:
[vbcol=seagreen]
> We got around this by using a queue to disconnect the security.
> 1) create a queue table that takes the name of a job and a status value.
> 2) create a stored procedure that can read the queue, find any pending job
s,
> run sp_startjob for any pending jobs, mark started jobs as complete.
> 3) create SQL Agent job owned by SA that runs once every minute and runs
> that stored procedure.
> 4) user inserts job name in queue and waits ~1 min.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
> "Laurence" <laurencen@.eurostop.co.uk> wrote in message
> news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...sql

How to enable a non-SysAdmin user to start a job

I thought I could do this by granting permission to a few MSDB
procedures:
grant execute on sp_help_jobhistory to UserRole -- view job history
grant execute on sp_help_job to UserRole -- view job
grant execute on sp_start_job to UserRole -- start job
but I get an error:
Server: Msg 14262, Level 16, State 1, Line 1
The specified @.job_name ('Name Of Job') does not exist.
I looked at the sysjobs_view in MSDB (below) it looks like only the
owner, SysAdmins and TargetServersRole (what is this?) can view the
jobs. If I alter this view to include my UserRole this allows them to
start the job, but I don't want to do this because it is a hack.
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
What is a better solution?We got around this by using a queue to disconnect the security.
1) create a queue table that takes the name of a job and a status value.
2) create a stored procedure that can read the queue, find any pending jobs,
run sp_startjob for any pending jobs, mark started jobs as complete.
3) create SQL Agent job owned by SA that runs once every minute and runs
that stored procedure.
4) user inserts job name in queue and waits ~1 min.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Laurence" <laurencen@.eurostop.co.uk> wrote in message
news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
>I thought I could do this by granting permission to a few MSDB
> procedures:
> grant execute on sp_help_jobhistory to UserRole -- view job history
> grant execute on sp_help_job to UserRole -- view job
> grant execute on sp_start_job to UserRole -- start job
> but I get an error:
> Server: Msg 14262, Level 16, State 1, Line 1
> The specified @.job_name ('Name Of Job') does not exist.
> I looked at the sysjobs_view in MSDB (below) it looks like only the
> owner, SysAdmins and TargetServersRole (what is this?) can view the
> jobs. If I alter this view to include my UserRole this allows them to
> start the job, but I don't want to do this because it is a hack.
> SELECT *
> FROM msdb.dbo.sysjobs
> WHERE (owner_sid = SUSER_SID())
> OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
>
> What is a better solution?
>|||I see there is a way to do this by adding the user to the
TargetServersRole in MSDB, although it has a downside. See here:
http://www.mcse.ms/message638764.html
Daniel Jameson wrote:
> We got around this by using a queue to disconnect the security.
> 1) create a queue table that takes the name of a job and a status value.
> 2) create a stored procedure that can read the queue, find any pending jobs,
> run sp_startjob for any pending jobs, mark started jobs as complete.
> 3) create SQL Agent job owned by SA that runs once every minute and runs
> that stored procedure.
> 4) user inserts job name in queue and waits ~1 min.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
> "Laurence" <laurencen@.eurostop.co.uk> wrote in message
> news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
> >I thought I could do this by granting permission to a few MSDB
> > procedures:
> >
> > grant execute on sp_help_jobhistory to UserRole -- view job history
> > grant execute on sp_help_job to UserRole -- view job
> > grant execute on sp_start_job to UserRole -- start job
> >
> > but I get an error:
> >
> > Server: Msg 14262, Level 16, State 1, Line 1
> > The specified @.job_name ('Name Of Job') does not exist.
> >
> > I looked at the sysjobs_view in MSDB (below) it looks like only the
> > owner, SysAdmins and TargetServersRole (what is this?) can view the
> > jobs. If I alter this view to include my UserRole this allows them to
> > start the job, but I don't want to do this because it is a hack.
> >
> > SELECT *
> > FROM msdb.dbo.sysjobs
> > WHERE (owner_sid = SUSER_SID())
> > OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> > OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
> >
> >
> > What is a better solution?
> >

How to email one set of data from app database.

Hi,
My vb.net app will use msde for database needs. The app is a tool in which the app user (companyA) will do some planning for their clients. So basically, the database will have information about all the clients (Sam, Julia, Peter, Nancy etc) of our cust
omer (companyA). Now suppose there is some error happening in the application for user Nancy, and we want the companyA to send us only Nancy's data so that we can duplicate the error and debug it. How do we do that...
In previous versions of our app where we used the flat files for data storage, we will just tell the companyA to email us Nancy.ourFile so that we can have a look at the data which is causing the problem. How to extract only Nancy's data from the whole d
atabase...
Thanks
dev
No replies anyone... Andrea/Aaron where are you all...
Thanks
dev
"dev_kh" wrote:

> Hi,
> My vb.net app will use msde for database needs. The app is a tool in which the app user (companyA) will do some planning for their clients. So basically, the database will have information about all the clients (Sam, Julia, Peter, Nancy etc) of our cu
stomer (companyA). Now suppose there is some error happening in the application for user Nancy, and we want the companyA to send us only Nancy's data so that we can duplicate the error and debug it. How do we do that...
> In previous versions of our app where we used the flat files for data storage, we will just tell the companyA to email us Nancy.ourFile so that we can have a look at the data which is causing the problem. How to extract only Nancy's data from the whole
database...
> Thanks
> dev
sql

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")

Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.

works:
[build @.RestoreCmd]
exec (@.RestoreCmd)

doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder

Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.

"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.c om...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]

Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder

Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.

Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.

Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.
Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.c om...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.
|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056
.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.com...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056
.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.com...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

How to edit a custom property on an external metadata column

I am using external metadata columns in a destination data component to match sharepoint fields to input columns. I want to add a flag that the user can set to specify if a column is a lookup column (indicates that the component should look for a list item with the specified input column value, and do an update if the item already exists). I added a custom boolean property to the medata column, but the property editor does not allow me to edit the column. It appears to be enabled, but when I choose a value other than the default, the editor immediately replaces the value I chose with the default.

How do I set the property so that it is editable in the property editor? Here is the code that I am using to create the property:

private void AddLookupProperty(IDTSExternalMetadataColumn90 externalColumn)

{

IDTSCustomProperty90 lookupProperty = null;

try

{

//Check to see if the property exists. If it does, don't do anything. If it doesn't, an exception will be thrown

lookupProperty = externalColumn.CustomPropertyCollection[LOOKUP_PROPERTY_NAME];

}

catch (System.Runtime.InteropServices.COMException ex)

{

//This is the exception that is thrown if the property does not exist

if (ex.ErrorCode == -1073676279)

{

lookupProperty = externalColumn.CustomPropertyCollection.New();

lookupProperty.Description = "When saving data, look for a row with this field, and if found, do an update instead of an insert.";

lookupProperty.Value = false;

lookupProperty.Name = LOOKUP_PROPERTY_NAME;

}

//Something unexpected happened

else

{

throw;

}

}

}

External metadata columns are not supposed to have editable properties. I do not completely understand your scenario. Why would you allow users of your component to be able to change this property?

Could you possibly put this property on an associated input column?

Thanks.

|||

It doesn't make sense in the input column, unfortunately. It's basically a way to create sort of a foreign key relationship to the destination, without actually creating a foreign key. When you save data to the destination, I want to check any columns in the destination that are marked as lookup columns, then if there are rows in the destination that match the value in the input buffer, I perform an update on those rows, otherwise I do an insert.

As a last resort, I was thinking that it may be possible to create another input to the destination that contained a list of input columns. Using variables may be another way to do this.

It would be cool if I could create a multi-choice variable at design time from the destination, but I don't think that is possible, so I may must use a semi-colon delimited list.

Eventually, I may need to do some custom UI to make this all easier, I suppose

|||

I see now why you need to edit external columns. You may want to submit request for this feature on the product feedback site or in the Kirk's thread on top of this forum.

For now, I would rather have a single custom property on the single input with delimited list of input columns than play with two inputs. The second input might bring you some additional headache.

Thanks.

|||

This is exactly what i needed too... I need to map an external input and a inside input that comes from an inside especified connection and table...

It's really important for me to have this done... If anyone have any idea how to add a custom input based not on a outside path but from an inside connection that would be great... using that i would be able to virtually map the lookup columns...

Best Regards,