Concatenatation with NULL

I am changing the setting od my database to put concatenate null yields null to off.....the following are the statements i run....

exec sp_dboption 'Solumina','concat null yields null','false'

SELECT 'abc' + NULL

I expect 'abc' to be returned after this....But not so..I get NULL

Can any one tell me this setting has to be changed at the connection level. if so, why has this been provided as a db option.

the following works....

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT 'abc' + NULL

abc

-

ODBC and SQL Query Analyzer will turn this ON by default so you need to explicitly turn the behavior OFF if you are using either of these connection mechanisms

Run this: select databaseproperty(''Solumina', 'IsNullConcat')

is the result 0? then it's set to OFF, however you have to change it from QA to make it wok in a query window

go to Tools-->options-->Connection Properties and uncheck set concat_null_yields_null

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Compound Statements

Hello,

How can I stop/prevent SQL server from running compound SQL
statements. I do not want the server to run multiple
update/delete/insert/select statements as a batch. Is there an option?

/Kaf
www.afiouni.comKhaled Afiouni (post@.afiouni.com) writes:
> How can I stop/prevent SQL server from running compound SQL
> statements. I do not want the server to run multiple
> update/delete/insert/select statements as a batch. Is there an option?

No, there is no such option.

Please explain what your real problem is, and maybe we can find a
suggestion. What you are asking for right now does not really make sense?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The usual mechanism to restrict what operations users can perform is to give
them access only through parameterized stored procedures. Does that not meet
your requirements?

--
David Portas
SQL Server MVP
--|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns951F7F821DCD5Yazorman@.127.0.0.1>...
> Khaled Afiouni (post@.afiouni.com) writes:
> > How can I stop/prevent SQL server from running compound SQL
> > statements. I do not want the server to run multiple
> > update/delete/insert/select statements as a batch. Is there an option?
> No, there is no such option.
> Please explain what your real problem is, and maybe we can find a
> suggestion. What you are asking for right now does not really make sense?

Thank you for your reply. Please allow me to simplify it.

Actually I am checking for an ultimate solution to the SQL injection
issues. So in addition to filtering, checking and validating the
input, I would like to stop the compound statements from running and
allowing only the first SQL statements to be executed.

Any suggestions?

/Kaf
www.afiouni.com|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns951F7F821DCD5Yazorman@.127.0.0.1>...
> Khaled Afiouni (post@.afiouni.com) writes:
> > How can I stop/prevent SQL server from running compound SQL
> > statements. I do not want the server to run multiple
> > update/delete/insert/select statements as a batch. Is there an option?
> No, there is no such option.
> Please explain what your real problem is, and maybe we can find a
> suggestion. What you are asking for right now does not really make sense?

Thank you very much for your reply.

I am trying to find an ultimate solution to the SQL injection issues.
In addition to verifying, validating and checking on the data entry
fields, I would like to prevent compound statements from running and
only allowing the first SQL statement to run.

Any Suggestions?

/Kaf
www.afiouni.com|||>> Actually I am checking for an ultimate solution to the SQL injection
issues. <<

Never write dynamic SQL; learn how to program correctly instead. This
is part of any basic Software Engineering course.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Khaled Afiouni (post@.afiouni.com) writes:
> I am trying to find an ultimate solution to the SQL injection issues.
> In addition to verifying, validating and checking on the data entry
> fields, I would like to prevent compound statements from running and
> only allowing the first SQL statement to run.

To do that you would have to add some middleware and have all your
clients talk to that middleware, and this middleware would pass the
code to SQL Server after validation and then pass the data back.

Not for the faint of heart. And it would be a reduction in usability,
since there sometimes be very good reason for an application to submit
two commands one go.

And you would not even be safe. You could intercept dynamic SQL created
client side, but not dynamic SQL created in stored procedures.

First step, is to let the users run the application with as few permissions
as possible. Ideally, all access should be through stored procedures, and
there should not be any dynamic SQL in the SPs as well. The users only
needs EXEC permission to the procedures. Now, this may hamper usability,
since some functions are easier to implement with dynamic SQL, not the
least if you want performance. (Typically this is search functions where
the users can search on a number of criterias.) But if you restrict
access to SELECT on the table, an intruder cannot wreck your database.

Next step is to write the SQL code properly. If you are constructing
SQL code client-side, use prepared statements with placeholds for
the parameters. Never build the entire string with values and all.
You can also call sp_executesql directly through RPC methods, *not*
as EXEC statements!

If you use dynamic SQL in stored procedures, use sp_executesql to run
your dynamic SQL, not EXEC().

For dynamic SQL on the client side, I have some articles on my web site:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||After following this thread for a few go-rounds, perhaps it is worth asking,
what is it that you are trying to achieve by stopping such compound
commands?
In particular, why is there SQL outside your control being posted to your
server?
No criticism of your system, just feels like your respondents could use a
"bigger picture".

"Khaled Afiouni" <post@.afiouni.com> wrote in message
news:a5c90178.0407070152.17be0506@.posting.google.c om...
> Hello,
> How can I stop/prevent SQL server from running compound SQL
> statements. I do not want the server to run multiple
> update/delete/insert/select statements as a batch. Is there an option?
> /Kaf
> www.afiouni.com|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns951FEBF2D734Yazorman@.127.0.0.1>...
> Khaled Afiouni (post@.afiouni.com) writes:
> > I am trying to find an ultimate solution to the SQL injection issues.
> > In addition to verifying, validating and checking on the data entry
> > fields, I would like to prevent compound statements from running and
> > only allowing the first SQL statement to run.
> To do that you would have to add some middleware and have all your
> clients talk to that middleware, and this middleware would pass the
> code to SQL Server after validation and then pass the data back.
> Not for the faint of heart. And it would be a reduction in usability,
> since there sometimes be very good reason for an application to submit
> two commands one go.
> And you would not even be safe. You could intercept dynamic SQL created
> client side, but not dynamic SQL created in stored procedures.
> First step, is to let the users run the application with as few permissions
> as possible. Ideally, all access should be through stored procedures, and
> there should not be any dynamic SQL in the SPs as well. The users only
> needs EXEC permission to the procedures. Now, this may hamper usability,
> since some functions are easier to implement with dynamic SQL, not the
> least if you want performance. (Typically this is search functions where
> the users can search on a number of criterias.) But if you restrict
> access to SELECT on the table, an intruder cannot wreck your database.
> Next step is to write the SQL code properly. If you are constructing
> SQL code client-side, use prepared statements with placeholds for
> the parameters. Never build the entire string with values and all.
> You can also call sp_executesql directly through RPC methods, *not*
> as EXEC statements!
> If you use dynamic SQL in stored procedures, use sp_executesql to run
> your dynamic SQL, not EXEC().
> For dynamic SQL on the client side, I have some articles on my web site:
> http://www.sommarskog.se/dynamic_sql.html
> http://www.sommarskog.se/dyn-search.html

Thank you for taking the time to write that helpfull reply. I appreciate it.

/Kaf
www.afiouni.com|||"Mischa Sandberg" <mischa_sandberg@.telus.net> wrote in message news:<km1Hc.11732$eO.2611@.edtnps89>...
> After following this thread for a few go-rounds, perhaps it is worth asking,
> what is it that you are trying to achieve by stopping such compound
> commands?
> In particular, why is there SQL outside your control being posted to your
> server?
> No criticism of your system, just feels like your respondents could use a
> "bigger picture".
> "Khaled Afiouni" <post@.afiouni.com> wrote in message
> news:a5c90178.0407070152.17be0506@.posting.google.c om...
> > Hello,
> > How can I stop/prevent SQL server from running compound SQL
> > statements. I do not want the server to run multiple
> > update/delete/insert/select statements as a batch. Is there an option?
> > /Kaf
> > www.afiouni.com

You are absolutely right. Allow me to share those documents with
everybody.

cnscenter.future.co.kr/resource/rsc-center/vendor-wp/Spidynamics/WhitepaperSQLInjection2.pdf

http://jo.morales0002.eresmas.net/o...QLInjection.pdf

This issue can get scary especially that I did see it in action :-)

/Kaf
www.afiouni.com|||Joe Celko <jcelko212@.earthlink.net> wrote in message news:<40ec4e5d$0$16505$c397aba@.news.newsgroups.ws>...
> >> Actually I am checking for an ultimate solution to the SQL injection
> issues. <<
> Never write dynamic SQL; learn how to program correctly instead. This
> is part of any basic Software Engineering course.
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

O:-)

/Kaf
www.afiouni.com|||I used to work for Simba.com (now owned by Orbital.com). Simba produced the
SQL engine/driver kit behind about half the ODBC drivers in the world.

We had a tiny ODBC proxy driver that did more or less what you were asking.
It received the SQL commands, applied the engine's parser to them, did some
rulechecking/rewriting of the parse tree based on the customer's
requirements, and either responded with an error, or forwarded the parse
tree (collapsed back to a command string) to the REAL ODBC connection.

You may want to check with Orbital on that.

"Khaled Afiouni" <post@.afiouni.com> wrote in message ...
cnscenter.future.co.kr/resource/rsc-center/vendor-wp/Spidynamics/WhitepaperS
QLInjection2.pdf
> http://jo.morales0002.eresmas.net/o...QLInjection.pdf
> This issue can get scary especially that I did see it in action :-)
> /Kaf
> www.afiouni.com

Composite primary key on a table variable?

Is is possible to create a composite primary key on a table variable?
Neither of these two statements are successful:

DECLARE @.opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL
)

ALTER TABLE @.opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED
(
[jobdetailid],
[cjfid]
)

and

DECLARE @.opmcjf TABLE (
jobdetailid INT PRIMARY KEY,
cjfid INT PRIMARY KEY,
cjfvalue VARCHAR(100) NULL
)

Thanks,
Shaun"Shaun Evans" <sevans2001@.hotmail.com> wrote in message
news:375ac918.0402240644.261460f2@.posting.google.c om...
> Is is possible to create a composite primary key on a table variable?
> Neither of these two statements are successful:
> DECLARE @.opmcjf TABLE (
> jobdetailid INT NOT NULL,
> cjfid INT NOT NULL,
> cjfvalue VARCHAR(100) NULL
> )
> ALTER TABLE @.opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED
> (
> [jobdetailid],
> [cjfid]
> )
> and
> DECLARE @.opmcjf TABLE (
> jobdetailid INT PRIMARY KEY,
> cjfid INT PRIMARY KEY,
> cjfvalue VARCHAR(100) NULL
> )
> Thanks,
> Shaun

This should work - see "table" in Books Online.

DECLARE @.opmcjf TABLE (
jobdetailid INT NOT NULL,
cjfid INT NOT NULL,
cjfvalue VARCHAR(100) NULL,
primary key ([jobdetailid],[cjfid])
)

Simon|||This is the syntax:

DECLARE @.opmcjf TABLE (jobdetailid INTEGER, cjfid INTEGER, cjfvalue
VARCHAR(100) NULL, PRIMARY KEY (jobdetailid,cjfid))

AFAIK it isn't possible to alter a table-variable after it's declared. Since
a table-variable is scoped to a batch I guess that functionality wouldn't be
very useful.

--
David Portas
SQL Server MVP
--

Complicated IIF statements

We have some conditions that are required to be met to process the calculations. For the IIF , we have two conditions that are required to be true and also for two different fields.

There is a field called MEASURE which should be either "Discharges" OR "Panel Discharges".

There is another field called fiscal year which should be "TFY"

If the above conditions are met, then we want to SUM(Fields!Value.Numerator) but if the test fails then we want (Fields!Numerator.Value)/(Fields!NDenominator.Value)

I have tried :

=IIF(Fields!MEASURE.Value ="Panel Discharges", SUM(IIF(Fields!FiscalYear IS "TFY", Fields!Numerator.Value),(Fields!Numerator.Value)/(Fields!NDenominator.Value))

I tried other style of writing but failed.

Any help is appreciated!

I think there are two issues:
1. the outer IIF function call has only two arguments instead of the required three arguments
2. the conditional SUM aggregate can trigger a division by zero

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code):

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

Then, modify the expression accordingly:

=IIF( (Fields!MEASURE.Value="Panel Discharges") OR (Fields!MEASURE.Value="Discharges"), SUM(IIF(Fields!FiscalYear="TFY", Fields!Numerator.Value, Code.Divide(Fields!Numerator.Value, Fields!NDenominator.Value))), 0)

-- Robert

Complete Command of given PID

Hi,
we sometimes have statements which seem to block the whole server.
now i use this to get information of running processes:
SELECT
spid,
kpid,
status,
hostname
, USER_NAME(uid)
, program_name
, DB_NAME(dbid)
, memusage,*
FROM
master.dbo.sysprocesses
WHERE
status = 'runnable'
ORDER BY
status
aSC
the cmd row only shows the single first row of the statement,
so i cant get the information where the statement really comes from.
is there a way to get it all? maybe the whole statment or
sp_ text which is running inside this pid?
thanks,
peppiAs an alternative, have you considered using the SQL Profiler?|||Yes,-)
sometimes bad processes are running on 100% cpu and i cant even start
enterprise manager. only thing i can get running is queryanalyzer, so
i need some handy stuff to get information to kill some tasks and
see where it came from first.
i found fn_get_sql migt be useful,
maybe someone has got a procedure which gets all running tasks and
shows the text in one procedure?
thanks,
mike|||This may help
http://www.sommarskog.se/sqlutil/aba_lockinfo.html|||If you have PID with you,
run =>
DBCC INPUTBUFFER(PID),
to get the query rather than getting it from sysprocess...
If you have to monitor more and need to get the full query use Profiler.
Thanks,
Sree
"markc600@.hotmail.com" wrote:

> As an alternative, have you considered using the SQL Profiler?
>