checking validity

Hello there
I've build store proecdure that create dinamic sql sentences for updating
data.
Is there a way to check if the sencence is valid before running it?Roy,shalom
Yes it is
CREATE TABLE #Test (col INT)
INSERT INTO #Test VALUES (1)
DECLARE @.str VARCHAR(50),@.col INT
SET @.col=5
SET @.str='UPDATE #Test SET col='+CAST(@.col AS VARCHAR(10))
--EXEC (@.str)
PRINT (@.str)
SELECT * FROM #Test
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OVHrU1LUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
>|||[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Roy Goldhammer (roy@.hotmail.com) writes:
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
In SQL 2005 you could embed the query in SET PARSEONLY ON and put it in
a TRY/CATCH handler. But that will not catch all errors, like misspelled
column names or misspelled table names. I guess you can catch these if
you use SET FMTONLY ON instead, but that will produce a result set with
metadata to the client, which is likely confuse it.
Working with dynamic SQL means that you have to test carefully, and by
other means ensure that you do not generate syntax errors at run-time.
A very important tool to achieve this is that you build parameterised
queries that you run with sp_executesql. If you interpolate all values
into the SQL string and run with EXEC(), there are more risk for problems.
Also, make sure that you use quotename for all object names you interpolate
into the string.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Consider SET FMTONLY ON and SET PARSEONLY ON. However, if the programming to
create the T-SQL statements is correct, then this should not be a recuring
problem.
If you are talking about dynamic SQL as in the entire structure of statement
(not just parameters) is created on the fly, then perhaps this programming
would be easier to implment on the application side. A class can be written
that exposes properties for table names, joins, column names, filter
expressions, etc. and then a few hundred lines of C# coding could assemble a
properly formatted select statement.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OVHrU1LUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
>

checking the SELECT statement for an SqlDataAdapter

I'm trying to pass a querystring to an SqlDataAdapter object. To check if the query is a valid SELECT statement, I simply use a try-catch. But dispite the try-catch it still accepts valid INSERT statements. However, in the parameterlist of the SqlDataAdapter the required parameter is a Transact SQL SELECT statement or a stored procedure... Am I doing something wrong?

 Here is my code:

try{ my_conn = conn_open(); da =new SqlDataAdapter(query, my_conn); da.Fill(result.resultDataset); my_conn.Dispose();}catch (Exception e){ result.errMsg ="Database Error: " + e.Message; result.success =false;}

Kehil

Kehil:

However, in the parameterlist of the SqlDataAdapter the required parameter is a Transact SQL SELECT statement or a stored procedure... Am I doing something wrong?

How do you know the parameterlist makes the SqlDataAdapter accpets only SELECT statement or SP? Actually when you create a SqlDataAdapter with a SqlCommand, the SqlCommand is used as SqlDataAdapter.SelectCommand, but this does not mean the SqlCommand must be a SELECT statement. If you want to validate SqlDataAdapter.SelectCommand, you need to validate the SqlCommand.CommandText to make sure it doesn't cotain any words like INSERT (but if there is a column named "INSERT" returned by a SELECT query, the statement will also considered invalid). So, such validation seems to make no sense.

Check validity of NT username

Is there a stored procedure that can be run where you pass in a NT username
and it tells you if it is valid or not? I would like to do this because we
have users setup with access to our SQL Servers and as somebody leaves the
company we want to remove their login. Something like
sp_ntusername 'domain\username''
ThanksIt kinda old, but you can use that:
read/74014dd5ea712297/a9ad008666f52801?q=check+ntusers+sqlserver&rnum=1&hl=de#a9ad
008666f52801" target="_blank">http://groups.google.de/group/micro...>
008666f52801
Just changing the "net user" to "net user /domain YourdomainName
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9F2B48E4-2C7C-4CB4-BA36-CAD604D9401A@.microsoft.com...
> Is there a stored procedure that can be run where you pass in a NT
> username
> and it tells you if it is valid or not? I would like to do this because
> we
> have users setup with access to our SQL Servers and as somebody leaves the
> company we want to remove their login. Something like
> sp_ntusername 'domain\username''
> Thanks|||Just a remark: But you should prefer using groups instead of single accounts
because they can be easier managed than those accounts, accounts wont be
"automagically" deleted if a account is deleted in the domain. Group
memebership but always applies if a user leave the company or the domain
group.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9F2B48E4-2C7C-4CB4-BA36-CAD604D9401A@.microsoft.com...
> Is there a stored procedure that can be run where you pass in a NT
> username
> and it tells you if it is valid or not? I would like to do this because
> we
> have users setup with access to our SQL Servers and as somebody leaves the
> company we want to remove their login. Something like
> sp_ntusername 'domain\username''
> Thanks|||sp_validatelogins
From BOL:
Reports information about orphaned Microsoft Windows NT users and groups
that no longer exist in the Windows NT environment but still have entries in
the Microsoft SQL ServerT system tables.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:9F2B48E4-2C7C-4CB4-BA36-CAD604D9401A@.microsoft.com...
> Is there a stored procedure that can be run where you pass in a NT
> username
> and it tells you if it is valid or not? I would like to do this because
> we
> have users setup with access to our SQL Servers and as somebody leaves the
> company we want to remove their login. Something like
> sp_ntusername 'domain\username''
> Thanks|||Thanks for the post. I should actually rephrase my question. We have a
table in our data warehouse that stores a user's email address and the NT ID
.
When a job completes we send out emails to the users in this table. If we
do not remove people from this table when they leave, the job fails when it
tries to send an email to an email address that does not exist. Is there
some stored procedure that can be run that takes the NT ID as a input
parameter and then can return whether it is valid or not?
Thanks a lot for your help!
"Mike Epprecht (SQL MVP)" wrote:

> sp_validatelogins
> From BOL:
> Reports information about orphaned Microsoft? Windows NT? users and grou
ps
> that no longer exist in the Windows NT environment but still have entries
in
> the Microsoft SQL ServerT system tables.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:9F2B48E4-2C7C-4CB4-BA36-CAD604D9401A@.microsoft.com...
>
>|||IF SUSER_SID('NTdomain\NTuser') IS NOT NULL
PRINT 'Valid'
ELSE
PRINT 'Bad'
Tim S