sql permissions: code

Showing posts with label code. Show all posts

Thursday, March 29, 2012

Default language and dates

I've just moved servers - WK3 to WK3 - installed SQLserver 2005 and uploaded the database. Code base has not changed but now I'm getting

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

I also ran

EXEC @.ret=sp_defaultlanguage'sa','British English'

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

ashaig:
"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

Yes it may be related to the language setting. Actually there are some differences between English (United States) and British English, including date format. You can use the following command to check details of all language settings:

EXEC sp_helplanguage

From the result we can see the dateformat of us_en is mdy, while the dateformate of British is dmy.

ashaig:
as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

You can change the default language setting of a SQL instance by using such command:

EXEC sp_configure 'default language',0
reconfigure with override

Where 0 is the id of the language. Here are some useful links about the language setting in SQL Server:

Default Language option:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3xny.asp

sp_configure:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_0put.asp

sql

Sunday, March 25, 2012

Default Database?

I got the following after trying to connect to SQLEXPRESS
from a remote computer:
Code:
SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
1> select * from users
1> go
MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
Invalid object name 'Users'
and, after querying the tables in the DB, I find that I am in
"maser", not my intended DB. I have changed (what I thought was) the
relevant Logins to "my_db" from "master", but I still get this error.
The Logins I have changed from master to my intended database are:
"sa"
"(servername)/aspnet"
"builtin/users"
"sqlserver2005ms..."
What am i doing wrong?
Thanks!> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
-E means you login through your windows account. You need to change default database for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>I got the following after trying to connect to SQLEXPRESS
> from a remote computer:
> Code:
> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> 1> select * from users
> 1> go
> MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> Invalid object name 'Users'
>
> and, after querying the tables in the DB, I find that I am in
> "maser", not my intended DB. I have changed (what I thought was) the
> relevant Logins to "my_db" from "master", but I still get this error.
> The Logins I have changed from master to my intended database are:
> "sa"
> "(servername)/aspnet"
> "builtin/users"
> "sqlserver2005ms..."
> What am i doing wrong?
> Thanks!
>|||Thanks.
Even when I take out " -E " the same thing happens. I get the Master
database.
Could you (somebody) kindly tell me what I need to do to change the
default DB
to my intended DB?
Thanks again.
Tibor Karaszi wote:
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> -E means you login through your windows account. You need to change default database for this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
> >I got the following after trying to connect to SQLEXPRESS
> > from a remote computer:
> >
> > Code:
> >
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> > 1> select * from users
> > 1> go
> >
> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> > Invalid object name 'Users'
> >
> >
> >
> > and, after querying the tables in the DB, I find that I am in
> > "maser", not my intended DB. I have changed (what I thought was) the
> > relevant Logins to "my_db" from "master", but I still get this error.
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> > "(servername)/aspnet"
> > "builtin/users"
> > "sqlserver2005ms..."
> >
> > What am i doing wrong?
> > Thanks!
> >|||When you take out the -E, you are still using -E (since that is the
default).
So, you still need to set sp_defaultdb for your windows login.
Or, use SQL authentication by specifying a username and password, and set
sp_defaultdb for that login.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177092683.312878.324120@.b58g2000hsg.googlegroups.com...
> Thanks.
> Even when I take out " -E " the same thing happens. I get the Master
> database.
> Could you (somebody) kindly tell me what I need to do to change the
> default DB
> to my intended DB?
> Thanks again.
> Tibor Karaszi wote:
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> -E means you login through your windows account. You need to change
>> default database for this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "pbd22" <dushkin@.gmail.com> wrote in message
>> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>> >I got the following after trying to connect to SQLEXPRESS
>> > from a remote computer:
>> >
>> > Code:
>> >
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> > 1> select * from users
>> > 1> go
>> >
>> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
>> > Invalid object name 'Users'
>> >
>> >
>> >
>> > and, after querying the tables in the DB, I find that I am in
>> > "maser", not my intended DB. I have changed (what I thought was) the
>> > relevant Logins to "my_db" from "master", but I still get this error.
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> > "(servername)/aspnet"
>> > "builtin/users"
>> > "sqlserver2005ms..."
>> >
>> > What am i doing wrong?
>> > Thanks!
>> >
>|||> The Logins I have changed from master to my intended database are:
> "sa"
I strongly recommend you change the default database for 'sa' back to
master:
ALTER LOGIN sa
WITH DEFAULT_DATABASE = master;
Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
mentioned. Also, you can specify an alternative database context using
SQLCMD -d command-line argument rather than relying on the login's default
database:
SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>I got the following after trying to connect to SQLEXPRESS
> from a remote computer:
> Code:
> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> 1> select * from users
> 1> go
> MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> Invalid object name 'Users'
>
> and, after querying the tables in the DB, I find that I am in
> "maser", not my intended DB. I have changed (what I thought was) the
> relevant Logins to "my_db" from "master", but I still get this error.
> The Logins I have changed from master to my intended database are:
> "sa"
> "(servername)/aspnet"
> "builtin/users"
> "sqlserver2005ms..."
> What am i doing wrong?
> Thanks!
>|||Dan -
Thank you. I appreciate your advice.
I know how to access my default database.
I didn't know the command you suggested, but
it seems to be the same as using "use MyDefaultDB"
on the command line.
The problem is, I want "MyDefaultDB" to be the default
database when I connect from VisualWebDeveloper and
right now VisualWebDeveloper is telling me that the user
doesn't exist when he tries to log on which means that
it is searching for my USERS table in the master table.
So:
> I strongly recommend you change the default database
> for 'sa' back to master:
How can I do what you are saying and allow my users to
login?
Thanks.
Dan Guzman ote-wray:
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> I strongly recommend you change the default database for 'sa' back to
> master:
> ALTER LOGIN sa
> WITH DEFAULT_DATABASE = master;
> Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
> mentioned. Also, you can specify an alternative database context using
> SQLCMD -d command-line argument rather than relying on the login's default
> database:
> SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
> >I got the following after trying to connect to SQLEXPRESS
> > from a remote computer:
> >
> > Code:
> >
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> > 1> select * from users
> > 1> go
> >
> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> > Invalid object name 'Users'
> >
> >
> >
> > and, after querying the tables in the DB, I find that I am in
> > "maser", not my intended DB. I have changed (what I thought was) the
> > relevant Logins to "my_db" from "master", but I still get this error.
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> > "(servername)/aspnet"
> > "builtin/users"
> > "sqlserver2005ms..."
> >
> > What am i doing wrong?
> > Thanks!
> >|||> The problem is, I want "MyDefaultDB" to be the default
> database when I connect from VisualWebDeveloper and
> right now VisualWebDeveloper is telling me that the user
> doesn't exist when he tries to log on which means that
> it is searching for my USERS table in the master table.
Just to be clear, are you getting the "Invalid object name 'Users'" message
in VisualWebDeveloper? Perhaps your VisualWebDeveloper connection is
specifying server or database context other than the one desired. Another
possible cause for the invalid object name error is that the object is not
in your default schema. The Best Practice is to schema-qualify object names
(e.g. SELECT * FROM dbo.Users).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177170707.388662.126830@.b58g2000hsg.googlegroups.com...
> Dan -
> Thank you. I appreciate your advice.
> I know how to access my default database.
> I didn't know the command you suggested, but
> it seems to be the same as using "use MyDefaultDB"
> on the command line.
> The problem is, I want "MyDefaultDB" to be the default
> database when I connect from VisualWebDeveloper and
> right now VisualWebDeveloper is telling me that the user
> doesn't exist when he tries to log on which means that
> it is searching for my USERS table in the master table.
> So:
>> I strongly recommend you change the default database
>> for 'sa' back to master:
> How can I do what you are saying and allow my users to
> login?
> Thanks.
> Dan Guzman ote-wray:
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> I strongly recommend you change the default database for 'sa' back to
>> master:
>> ALTER LOGIN sa
>> WITH DEFAULT_DATABASE = master;
>> Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
>> mentioned. Also, you can specify an alternative database context using
>> SQLCMD -d command-line argument rather than relying on the login's
>> default
>> database:
>> SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "pbd22" <dushkin@.gmail.com> wrote in message
>> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>> >I got the following after trying to connect to SQLEXPRESS
>> > from a remote computer:
>> >
>> > Code:
>> >
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> > 1> select * from users
>> > 1> go
>> >
>> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
>> > Invalid object name 'Users'
>> >
>> >
>> >
>> > and, after querying the tables in the DB, I find that I am in
>> > "maser", not my intended DB. I have changed (what I thought was) the
>> > relevant Logins to "my_db" from "master", but I still get this error.
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> > "(servername)/aspnet"
>> > "builtin/users"
>> > "sqlserver2005ms..."
>> >
>> > What am i doing wrong?
>> > Thanks!
>> >
>|||> I didn't know the command you suggested, but
> it seems to be the same as using "use MyDefaultDB"
> on the command line.
No, it is not. If your user has a default database of 'foo' but the user
cannot access foo, they won't have a chance to say 'use bar' because they
won't be able to log in. It depends on the tool you are using, but this is
the case in many tools...|||Dan -
Thanks for your reply. I thought I had fixed this (hence the delay)
but the error
has returned.
> Just to be clear, are you getting the "Invalid object name 'Users'" message
> in VisualWebDeveloper?
I "am" getting the "Invalid object name "Users" " error when I
try to connect using the SQLCMD. It is obvious that the command is
trying to
validate my login using the master DB and there is no Users table
there.
My connection string looks like the following:
<add name="myConnectionString" connectionString="Data
Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
Integrated Security=True; uid=sa; pwd=mypassword;"
providerName="System.Data.SqlClient"/>
I am using the 'sa' account for access and, if the 'sa' default
database is master
I am still a bit lost as to how to validate against a table that isn't
in the master DB.
> Another possible cause for the invalid object name error is that the object is
> not in your default schema. The Best Practice is to schema-qualify object
names
> (e.g. SELECT * FROM dbo.Users).
When I am in the database, I can use select * from users or select *
from dbo.users - they both work.
This is taking me a while but - how do i change the default database
for when
my users login?|||> trying to
> validate my login using the master DB and there is no Users table
> there.
I think you're confusing a users table that you created with SQL Server's
internal tables for maintaining login and user information.
> This is taking me a while but - how do i change the default database
> for when
> my users login?
Take a look at sp_defaultdb in Books Online.|||> This is taking me a while but - how do i change the default database
> for when
> my users login?
As Aaron mentioned, you can specify the default database for a login with
sp_default_db. Since you are using SQL 2005, you can alternatively use DCL:
ALTER LOGIN MyLogin
WITH DEFAULT_DATABASE = MyLogin;
However, the login's default database is used only if you do not specify a
database context when connecting. Try this, which I would expect this to
succeed as long as your Windows account has access to the Trezoro database:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT * FROM
dbo.users"
GO
If the above failes with error "invalid object name dbo.users", run the
following to verify database and user context:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT DB_NAME(),
USER"
If the database and user are as expected, verify the table actually exists:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT name FROM
sys.tables WHERE name = 'users'"
> <add name="myConnectionString" connectionString="Data
> Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
> Integrated Security=True; uid=sa; pwd=mypassword;"
> providerName="System.Data.SqlClient"/>
> I am using the 'sa' account for access and, if the 'sa' default
> database is master
> I am still a bit lost as to how to validate against a table that isn't
> in the master DB.
Because you specified 'Integrated Security=True' in the connection string,
the user and password specification are ignored. The connection is instead
done under the context of a Windows account. I'm not an web guy but I
believe your Windows account is used when you run from the IDE. This
doesn't explain why you would get the invalid object error, though. I would
first troubleshoot the problem with SQLCMD before you try the application.
In any case, you should never use 'sa' for routine development or
application access.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177879467.944332.115100@.n59g2000hsh.googlegroups.com...
> Dan -
> Thanks for your reply. I thought I had fixed this (hence the delay)
> but the error
> has returned.
>> Just to be clear, are you getting the "Invalid object name 'Users'"
>> message
>> in VisualWebDeveloper?
> I "am" getting the "Invalid object name "Users" " error when I
> try to connect using the SQLCMD. It is obvious that the command is
> trying to
> validate my login using the master DB and there is no Users table
> there.
> My connection string looks like the following:
> <add name="myConnectionString" connectionString="Data
> Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
> Integrated Security=True; uid=sa; pwd=mypassword;"
> providerName="System.Data.SqlClient"/>
> I am using the 'sa' account for access and, if the 'sa' default
> database is master
> I am still a bit lost as to how to validate against a table that isn't
> in the master DB.
>> Another possible cause for the invalid object name error is that the
>> object is
>> not in your default schema. The Best Practice is to schema-qualify
>> object
> names
>> (e.g. SELECT * FROM dbo.Users).
> When I am in the database, I can use select * from users or select *
> from dbo.users - they both work.
> This is taking me a while but - how do i change the default database
> for when
> my users login?
>sql

Wednesday, March 21, 2012

Decrypting and Encrypted Stored Procedure

Hello,
In SQL 2000,
I have created a Stored Procedure as follows,

Code Snippet

CREATE PROCEDURE MyTest
WITH RECOMPILE, ENCRYPTION
AS
Select * From Customer

Then after this when i run this sp it giving me the perfect results wht i want, BUT when i want to change something in sp then for I am using the below line of code.

Code Snippet

sp_helptext mytest

But its displaying me that this sp is encrypted so you can't see the details and when i am trying to see trhe code of this sp from enterprise manager then also its not displaying me the details and giving me the same error,
So i want to ask that if there is a functionality of enrypting the sp code then is there any functionality for decrypting the Stored Procedure also,
or not,
If yes then wht it is and if NO then wht will be the alternative way for this,
?

You won’t retrieve back the source using sp_helptext /SMO, when you say WITH ENCRYPT.

You have to maintain your procedure source (like in File system or VSS). The encryption is very useful when you launch a product along with your database to public. So they can see the table schema but they can’t change or edit or view your programmability source code.

Sunday, March 11, 2012

Decode

I am new to SQL+ and Oracle. I am trying to create a cross tabular file. It sums all the ded_code Totals, but does not sum ded_code ANN.
My code looks like this: HELP!!
column EMPLOYEE format 9999999999 heading 'Employee'
COLUMN ANN FORMAT 99999999.99 heading 'Ann'
COLUMN FOUR format 99999999.99 heading 'FOUR'
COLUMN TOTALS format 99999999.99 heading 'Totals'
compute sum of FOUR on employee
compute sum of ANN on employee
compute sum of TOTALS on employee
SPOOL bc.TXT
SELECT employee,
SUM(DECODE(ded_code, 'ANN', ded_amt,0)) ANN,
SUM(DECODE(ded_code, '4', ded_amt,0)) FOUR,
SUM(DED_AMT)TOTALS
FROM PAYDEDUCTN
WHERE check_id IN (SELECT CHECK_ID
FROM PAYMASTR
WHERE CHECK_DATE = '22-Mar-02')
and ded_code in ('4', '5','403F', '403X', 'ANN')
GROUP by employee
/

SPOOL OUT

The output looks like this:

5639 .00 .00 267.05
:confused:The query seems to work fine without the WHERE clause for check_id.

SELECT employee,
SUM(DECODE(ded_code, 'ANN', ded_amt,0)) ANN,
SUM(DECODE(ded_code, '4', ded_amt,0)) FOUR,
SUM(DED_AMT) TOTALS
FROM PAYDEDUCTN
WHERE
ded_code in ('4', '5','403F', '403X', 'ANN')

A you sure you have records in table PAYDEDUCTN having check_id at 22-Mar-02 for the ded_code 'ANN' ?

Declaring default value for a variable

I have a variable in some code.
DECLARE @.LastExportDateTime datetime
I then set the variable.
SET @.LastExportDateTime = (SELECT TOP 1 ExportDatetime FROM MyTable ORDER BY
ExportDatetime DESC)
The first time I run this in production @.LastExportDateTime will be null and
I'm concerned that my code will not like this. I rather set it to date in
the past, '20000101'
Can I set the default value in the DECLARE statement?
I see the default clause in BOL but I would like a syntax example.
Thanks> Can I set the default value in the DECLARE statement?
No.
But you can say
SELECT @.LastExportDateTime = MAX(ExportDateTime) FROM MyTable;
SET @.LastExportDateTime = COALESCE(@.LastExportDateTime, '20000101');|||"Terri" <terri@.cybernets.com> wrote in message
news:duhoc2$rf4$1@.reader2.nmix.net...
>I have a variable in some code.
> DECLARE @.LastExportDateTime datetime
> I then set the variable.
> SET @.LastExportDateTime = (SELECT TOP 1 ExportDatetime FROM MyTable ORDER
> BY
> ExportDatetime DESC)
> The first time I run this in production @.LastExportDateTime will be null
> and
> I'm concerned that my code will not like this. I rather set it to date in
> the past, '20000101'
> Can I set the default value in the DECLARE statement?
> I see the default clause in BOL but I would like a syntax example.
> Thanks
No.
Here are 2 ways you can do this:
SET @.LastExportDateTime = (SELECT TOP 1 ExportDatetime FROM MyTable ORDER BY
ExportDatetime DESC)
IF @.LastExportDateTime IS NULL
SET @.LastExportDateTime = '20000101'
SET @.LastExportDateTime = ISNULL((SELECT TOP 1 ExportDatetime FROM MyTable
ORDER BY ExportDatetime DESC), '20000101')|||> But you can say
> SELECT @.LastExportDateTime = MAX(ExportDateTime) FROM MyTable;
Is MAX preferable to TOP 1...ORDER BY DESC from a performance perspective?|||> Is MAX preferable to TOP 1...ORDER BY DESC from a performance perspective?
I don't think it will make a difference, but it is a lot shorter to type.

Declare Variable Dynamically

I'm attempting to modify some Crosstab generating code, and I need some
advice/examples.

Currently, the code uses a single string variable to store the
dynamically generated query (www.johnmacintyre.ca). The problem is that
I am trying to pivot biological taxonomy information, and may end up
with a table containing over 200 columns. This takes the dynamic string
well over the 8000char limit for variables.

>From my understanding, the EXEC() command does not have the 8000char
limit if the execution string is broken into chunks, and concatenated
e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
to:

1) start a counter at the beginining of the dynamic generation
2) append the counter value to the end of a string variable name
3) DECLARE the new variable and attach that loop cycle of text to it,
or attach each chunk of characters < 8000
4) build the EXEC() string by concatenating each dynamic varible

Can this be done? Should it be done? Is there a better way to address
this type of problem?

Thanks for any ideas or insights

Tim Pascoe>> Can this be done? <<

Maybe, maybe not.

>> Should it be done? <<

No, this is a mis-use of SQL.

>> Is there a better way to address this type of problem? <<

Crosstabs (thank you for not calling them "pivot tables") are a report
and not a query. you ought to use a report tool and not SQL for this
kind of job.|||Hi
You may want to look at previous posts regarding crosstab queries, as Joe
says it is better to do this in the reporting tool such as RAC
http://www.rac4sql.net/ and others.

Here are a few links you may want to read:
http://www.windowsitpro.com/SQLServ...5608/15608.html
http://support.microsoft.com/defaul...b;EN-US;q175574

http://www.sqlteam.com/item.asp?ItemID=2955

John

<tim.pascoe@.cciw.ca> wrote in message
news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
> I'm attempting to modify some Crosstab generating code, and I need some
> advice/examples.
> Currently, the code uses a single string variable to store the
> dynamically generated query (www.johnmacintyre.ca). The problem is that
> I am trying to pivot biological taxonomy information, and may end up
> with a table containing over 200 columns. This takes the dynamic string
> well over the 8000char limit for variables.
>>From my understanding, the EXEC() command does not have the 8000char
> limit if the execution string is broken into chunks, and concatenated
> e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
> to:
> 1) start a counter at the beginining of the dynamic generation
> 2) append the counter value to the end of a string variable name
> 3) DECLARE the new variable and attach that loop cycle of text to it,
> or attach each chunk of characters < 8000
> 4) build the EXEC() string by concatenating each dynamic varible
> Can this be done? Should it be done? Is there a better way to address
> this type of problem?
> Thanks for any ideas or insights
> Tim Pascoe|||RAC was my next point of investigation. The problem is that I'm not
actually producing a report of the data (although I agree this is
generally what crosstabs are for). Instead, the data is required in
this format so it can be fed into statistical software, which requires
the crosstabulated structure. I'll look into RAC and the other sites
you listed.

Thanks.

John Bell wrote:
> Hi
> You may want to look at previous posts regarding crosstab queries, as
Joe
> says it is better to do this in the reporting tool such as RAC
> http://www.rac4sql.net/ and others.
> Here are a few links you may want to read:
http://www.windowsitpro.com/SQLServ...5608/15608.html
> http://support.microsoft.com/defaul...b;EN-US;q175574
> http://www.sqlteam.com/item.asp?ItemID=2955
> John
> <tim.pascoe@.cciw.ca> wrote in message
> news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
> > I'm attempting to modify some Crosstab generating code, and I need
some
> > advice/examples.
> > Currently, the code uses a single string variable to store the
> > dynamically generated query (www.johnmacintyre.ca). The problem is
that
> > I am trying to pivot biological taxonomy information, and may end
up
> > with a table containing over 200 columns. This takes the dynamic
string
> > well over the 8000char limit for variables.
> >>From my understanding, the EXEC() command does not have the
8000char
> > limit if the execution string is broken into chunks, and
concatenated
> > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
is
> > to:
> > 1) start a counter at the beginining of the dynamic generation
> > 2) append the counter value to the end of a string variable name
> > 3) DECLARE the new variable and attach that loop cycle of text to
it,
> > or attach each chunk of characters < 8000
> > 4) build the EXEC() string by concatenating each dynamic varible
> > Can this be done? Should it be done? Is there a better way to
address
> > this type of problem?
> > Thanks for any ideas or insights
> > Tim Pascoe|||Hi

You may want to consider Analysis services then?

John

<tim.pascoe@.cciw.ca> wrote in message
news:1103032212.575699.29580@.c13g2000cwb.googlegro ups.com...
> RAC was my next point of investigation. The problem is that I'm not
> actually producing a report of the data (although I agree this is
> generally what crosstabs are for). Instead, the data is required in
> this format so it can be fed into statistical software, which requires
> the crosstabulated structure. I'll look into RAC and the other sites
> you listed.
> Thanks.
>
> John Bell wrote:
>> Hi
>> You may want to look at previous posts regarding crosstab queries, as
> Joe
>> says it is better to do this in the reporting tool such as RAC
>> http://www.rac4sql.net/ and others.
>>
>> Here are a few links you may want to read:
>>
> http://www.windowsitpro.com/SQLServ...5608/15608.html
>> http://support.microsoft.com/defaul...b;EN-US;q175574
>>
>> http://www.sqlteam.com/item.asp?ItemID=2955
>>
>> John
>>
>> <tim.pascoe@.cciw.ca> wrote in message
>> news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
>> > I'm attempting to modify some Crosstab generating code, and I need
> some
>> > advice/examples.
>>> > Currently, the code uses a single string variable to store the
>> > dynamically generated query (www.johnmacintyre.ca). The problem is
> that
>> > I am trying to pivot biological taxonomy information, and may end
> up
>> > with a table containing over 200 columns. This takes the dynamic
> string
>> > well over the 8000char limit for variables.
>>> >>From my understanding, the EXEC() command does not have the
> 8000char
>> > limit if the execution string is broken into chunks, and
> concatenated
>> > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
> is
>> > to:
>>> > 1) start a counter at the beginining of the dynamic generation
>> > 2) append the counter value to the end of a string variable name
>> > 3) DECLARE the new variable and attach that loop cycle of text to
> it,
>> > or attach each chunk of characters < 8000
>> > 4) build the EXEC() string by concatenating each dynamic varible
>>> > Can this be done? Should it be done? Is there a better way to
> address
>> > this type of problem?
>>> > Thanks for any ideas or insights
>>> > Tim Pascoe
>|||(tim.pascoe@.cciw.ca) writes:
> I'm attempting to modify some Crosstab generating code, and I need some
> advice/examples.
> Currently, the code uses a single string variable to store the
> dynamically generated query (www.johnmacintyre.ca). The problem is that
> I am trying to pivot biological taxonomy information, and may end up
> with a table containing over 200 columns. This takes the dynamic string
> well over the 8000char limit for variables.
>>From my understanding, the EXEC() command does not have the 8000char
> limit if the execution string is broken into chunks, and concatenated
> e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
> to:
> 1) start a counter at the beginining of the dynamic generation
> 2) append the counter value to the end of a string variable name
> 3) DECLARE the new variable and attach that loop cycle of text to it,
> or attach each chunk of characters < 8000
> 4) build the EXEC() string by concatenating each dynamic varible
> Can this be done? Should it be done? Is there a better way to address
> this type of problem?

I think it can be done, but I would not like to do it. You would have
to generate dynamic SQL which in its turn generates the dynamic SQL
that executes the query.

It may be a better alternative to use a client language to generate the
SQL. If you use a language like Perl or Visual Basic which has unlimited
strings, you are saved the restriction of varchar(8000). Note that all
that you would bring to the client would be the meta data needed to
form the SQL statement.

In SQL 2005 there is a new data type varchar(MAX) which is akin to text,
but works more like varchar, and thus in SQL 2005 you would also be saved
from the varchar(8000) restriction. SQL 2005 is currently in beta, with
release planned next year.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Declare in a view

hello,
I have a quick question, can you declare a varchar within a view?
the code at the bottom generate error: Incorrect syntax near the keyword
'declare'.
CODE:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*----
--
05/25/06 - RA : starting code to query data from shamrock db
----
--*/
ALTER VIEW vw_customer_usage_bgcolor
AS
declare @.loc_east varchar (20)
declare @.loc_west varchar (20)
set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
'0%' ) AND
( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
'_____-___' ) AND
( inv_mast.delete_flag = 'N' ) AND
(inv_loc.location_id='102230' )
)
set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
'0%' ) AND
( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
'_____-___' ) AND
( inv_mast.delete_flag = 'N' ) AND
(inv_loc.location_id='100001' )
)
declare @.B_color bit
set @.B_color =
(select case when @.loc_east > @.loc_west
then 0 --EAST
else 1 --WEST
end)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GONo, you cannot declare variables in a view. I think you want a stored
procedure or a table-valued function.
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:F8A6A242-7A31-4C7B-A629-A54C97124604@.microsoft.com...
> hello,
> I have a quick question, can you declare a varchar within a view?
> the code at the bottom generate error: Incorrect syntax near the keyword
> 'declare'.
> CODE:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /*---
--
> 05/25/06 - RA : starting code to query data from shamrock db
> ----
--*/
> ALTER VIEW vw_customer_usage_bgcolor
> AS
> declare @.loc_east varchar (20)
> declare @.loc_west varchar (20)
> set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='102230' )
> )
> set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='100001' )
> )
> declare @.B_color bit
> set @.B_color =
> (select case when @.loc_east > @.loc_west
> then 0 --EAST
> else 1 --WEST
> end)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO|||ITDUDE27 wrote:
> hello,
> I have a quick question, can you declare a varchar within a view?
> the code at the bottom generate error: Incorrect syntax near the keyword
> 'declare'.
> CODE:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /*---
--
> 05/25/06 - RA : starting code to query data from shamrock db
> ----
--*/
> ALTER VIEW vw_customer_usage_bgcolor
> AS
> declare @.loc_east varchar (20)
> declare @.loc_west varchar (20)
> set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='102230' )
> )
> set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='100001' )
> )
> declare @.B_color bit
> set @.B_color =
> (select case when @.loc_east > @.loc_west
> then 0 --EAST
> else 1 --WEST
> end)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
No, you can't declare variables inside a view... Try this instead:
SELECT
CASE WHEN loc_west > loc_east THEN 1 ELSE 0 END
FROM
(
SELECT
SUM(CASE WHEN inv_loc.location_id = '102230' THEN inv_loc.qty_on_hnd
ELSE 0 END) AS loc_west,
SUM(CASE WHEN inv_loc.location_id = '100001' THEN inv_loc.qty_on_hnd
ELSE 0 END) AS loc_east
FROM inv_loc
INNER JOIN inv_mast
ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid
WHERE inv_mast.item_id NOT LIKE 0%
AND inv_mast.item_id LIKE '_____-___-___'
AND inv_mast.delete_flag = 'N'
) sums_table|||Check out in BOL index the topic "create function"
Under that you have something called
"Multi-statement Table-valued Functions"
That can handle your requirement.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||>> I have a quick question, can you declare a varchar within a view? <<
If you had ever read the first five pages of a chapter on VIEWs in any
SQL, you would know that a VIEW is a virtual table constructed from a
SELECT statement with some options.
So that did not answer your question? If you couldnot be bothered to
read a definition, wouldn't an error message a "strong hint"?
I also see that you write with bits and delete flags, just like
assembly language. Just like we did in the 1960's before RDBMS. You
also put the silly "volkwagen" suffix on view names to violate
ISO-11179 rules. All of those things are signs of really bad DDL and
DML.
Do you notice anything interesting about this predicate? Like it is
redundant?
(Inv_Mast.item_id NOT LIKE '0%'
AND
Inv_Mast.item_id NOT LIKE '0%' )
What you have posted here implies a LOT of serious errors. Stop
programming, do a full data audit and get some help from an SQL
porgrammer.

Friday, March 9, 2012

Declare an array of int in SQL Server

Hi All,
I want declare an array of int in SQL Server
please help me to convert the following code from VB to TSQL
----
Dim md_mon(12) As Integer
md_mon(1) = 31
----
ThanksThere are no arrays in SQLServer. Maybe if you explain your requirements
someone can suggest another alternative.

--
David Portas
SQL Server MVP
--|||Majid Mohammadian (Mohammadian59@.yahoo.com) writes:
> I want declare an array of int in SQL Server
> please help me to convert the following code from VB to TSQL
> ----
> Dim md_mon(12) As Integer
> md_mon(1) = 31
> ----

SQL and Visual Basic are languages that work from very different mindsets,
and for a successful rewrite from VB to SQL it is often best to start from
the beginning. In VB you would typically to things in loops, to iterate
over items in a set of data, but in SQL you should always strive to use
commands that operate on the entire set at the same time. That is when
you actually win performance.

SQL does not have arrays, but there are tables, and tables are a lot broader
concept than array. So in this case:

CREATE TABLE md_mon (monthno int NOT NULL PRIMARY KEY,
noofdays tinyint NOT NULL)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If the purpose of your array was to store the number of days in each month
then as an alternative in TSQL you could calculate the number of days for a
given month like this:

DECLARE @.dt DATETIME
SET @.dt = '20040101' -- January 2004

SELECT DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@.dt),@.dt))))

--
David Portas
SQL Server MVP
--|||Erland Sommarskog <sommar@.algonet.se> wrote in message
> SQL does not have arrays, but there are tables, and tables are a lot broader
> concept than array. So in this case:
> CREATE TABLE md_mon (monthno int NOT NULL PRIMARY KEY,
> noofdays tinyint NOT NULL)

FYI: You can also create a table variable, like:

DECLARE @.tblMonth TABLE (
MonthNo int,
NumDays int
)

... and then use it like any real table:

INSERT INTO @.tblMonth...
SELECT * FROM @.tblMonth...

Declarative SQL problem

I can't figure out what is wrong with the code below.

The first line prints out correctly when I am logged in

If I substitute the actual username in the SQL, the Repeater works fine, printing out the item from the db.
But using
WHERE USERNAME='<%= User.Identity.Name%>'
in the SelectCommand returns no rows.

Can anyone help?

Thanks,

Michael

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:NEWWEB %>"ProviderName="<%$ ConnectionStrings:NEWWEB.ProviderName %>"SelectCommand="SELECT USERNAME FROM ASPNET_MEMBERSHIP WHERE USERNAME='<%= User.Identity.Name%>' "></asp:SqlDataSource><asp:RepeaterID="FirmAlertsRepeater"runat="server"DataSourceID="SqlDataSource2">

</asp:Repeater>

YOu should use an expression for username. I wrote a blog post on how to do that here. Basically, you need to an expression builder to your app_code directory. It's very straight forward.

http://peterkellner.net/2006/09/18/expressionbuilderidentity/

HTH's.

|||

Interesting way of handling it, however, this is what I do:

Inside global.aspx.vb, add:

Protected

Sub Application_PreRequestHandlerExecute(ByVal senderAsObject,ByVal eAs System.EventArgs)If User.Identity.IsAuthenticatedAndAlso Context.SessionIsNotNothingAndAlso Session("Username")IsNothingThen

Session("Username")=user.Identity.Name

end if

end sub

Then just create a session parameter using the Username session property for your SqlDatasource.

|||

Hello Peter,

Thanks, I tried that and it works.

But I am at a loss as to why that is neccessary. Can you point me to anything that explains why the variable can't be used as is?

Michael

|||It's a databinding issue. <%#, that is expression generate code at runtime that has to be executed as an event. Expression are done at compile time so the code is their already. HTH's.|||

There are many reasons why.

First you used ASP syntax to try and insert a value into a server control property. That won't work because the server control property is initialized long before your expression is evaluated.

You COULD use a databinding syntax <%# %> instead, however, the server control must support databinding, and the property you are databinding must support being databound.

Lastly, if you were going to use databinding (assuming the SqlDatasource's SelectCommand property was databindable, which it isn't), you must surround the attribute with single quotes, not double quotes or the databinding expression won't work.

|||

Thanks Peter and Motley,

So much to learn.

So there is no general Eval function that asp.net uses to make sure the variable is resolved before compiling? We have to build our own?

Michael

|||

I tried Peter's method first. It worked very nicely.

But since I am a contractor, and someone else will have to try to understand it later, I switched over to Motley's method - re-done in C#:

In the declarative code:

SelectParameters><asp:SessionParameterName="USERNAME"SessionField="USERNAME"DefaultValue=""/></SelectParameters>

In the cs:

protected

void Page_PreInit(object sender,EventArgs e)

{

string strUsername = HttpContext.Current.User.Identity.Name;

Session[

"username"] = strUsername;

}

Wednesday, March 7, 2012

Decimal division results do not round as expected

Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
Using this
round((Sev3Met/(Sev3Total + 0.0)),2),
I would expect .50
However when I code in SQL 2005 Express manager against a SQL 2005
database, the results display as 0.5000000000
Using this
round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
I get 0.500000
is there some setting I am missing? How can I get 0.50 'Try something like:
CONVERT(DECIMAL(5.2),<expression you want to display with 2 decimals> )
Roy Harvey
Beacon Falls, CT
On 28 Jun 2006 14:30:31 -0700, wxbuff@.aol.com wrote:

>Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
>Using this
>round((Sev3Met/(Sev3Total + 0.0)),2),
>I would expect .50
>However when I code in SQL 2005 Express manager against a SQL 2005
>database, the results display as 0.5000000000
>Using this
>round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
>I get 0.500000
>is there some setting I am missing? How can I get 0.50 '|||<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>
Here are the rules for how the precision and scale of decimals is increased
through arithmetic.
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
David|||convert( decimal(3,2), round(Sev3Met/cast(Sev3Total as decimal(3,0)),2) )
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>|||It actually does round it to two decimal places. It's just displaying extra
zeroes. Try this to confirm:
DECLARE @.Sev3Met NUMERIC(10, 5)
DECLARE @.Sev3Total NUMERIC(10, 5)
SELECT @.Sev3Met = 1.0
SELECT @.Sev3Total = 3.0
SELECT round((@.Sev3Met/(@.Sev3Total + 0.0)),2)
i.e., Make Sev3Total = 3 instead of 2. The result is:
.33000000000000000
The extra zeroes are a display issue.
<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>

Saturday, February 25, 2012

Decare @A

Hi all! I'm trying to set the value of @.d to a value from col1 in
tblTest. I have tried the following code but it didn't work.
DECLARE @.d VARCHAR(25)
SELECT * FROM tblTest
SET @.d = tblTest.col1
Someone please help me! Thanks!SELECT @.d = col1 FROM tblTest
or
SET @.d = SELECT col1 FROM tblTest
Where col1 is THE COLUMN NAME not the first column.
--
Nik Marshall-Blank MCSD/MCDBA
"Stanley" <xstanley@.gmail.com> wrote in message
news:1128081085.771682.230680@.f14g2000cwb.googlegroups.com...
> Hi all! I'm trying to set the value of @.d to a value from col1 in
> tblTest. I have tried the following code but it didn't work.
> DECLARE @.d VARCHAR(25)
> SELECT * FROM tblTest
> SET @.d = tblTest.col1
> Someone please help me! Thanks!
>|||DECLARE @.d VARCHAR(25)
SELECT @.d = col1 FROM tblTest
http://sqlservercode.blogspot.com/
"Stanley" wrote:
> Hi all! I'm trying to set the value of @.d to a value from col1 in
> tblTest. I have tried the following code but it didn't work.
> DECLARE @.d VARCHAR(25)
> SELECT * FROM tblTest
> SET @.d = tblTest.col1
> Someone please help me! Thanks!
>|||Thanks so much, that's what I really need!!

Decare @A

Hi all! I'm trying to set the value of @.d to a value from col1 in
tblTest. I have tried the following code but it didn't work.
DECLARE @.d VARCHAR(25)
SELECT * FROM tblTest
SET @.d = tblTest.col1
Someone please help me! Thanks!
SELECT @.d = col1 FROM tblTest
or
SET @.d = SELECT col1 FROM tblTest
Where col1 is THE COLUMN NAME not the first column.
Nik Marshall-Blank MCSD/MCDBA
"Stanley" <xstanley@.gmail.com> wrote in message
news:1128081085.771682.230680@.f14g2000cwb.googlegr oups.com...
> Hi all! I'm trying to set the value of @.d to a value from col1 in
> tblTest. I have tried the following code but it didn't work.
> DECLARE @.d VARCHAR(25)
> SELECT * FROM tblTest
> SET @.d = tblTest.col1
> Someone please help me! Thanks!
>
|||DECLARE @.d VARCHAR(25)
SELECT @.d = col1 FROM tblTest
http://sqlservercode.blogspot.com/
"Stanley" wrote:

> Hi all! I'm trying to set the value of @.d to a value from col1 in
> tblTest. I have tried the following code but it didn't work.
> DECLARE @.d VARCHAR(25)
> SELECT * FROM tblTest
> SET @.d = tblTest.col1
> Someone please help me! Thanks!
>
|||Thanks so much, that's what I really need!!

Decare @A

Hi all! I'm trying to set the value of @.d to a value from col1 in
tblTest. I have tried the following code but it didn't work.
DECLARE @.d VARCHAR(25)
SELECT * FROM tblTest
SET @.d = tblTest.col1
Someone please help me! Thanks!SELECT @.d = col1 FROM tblTest
or
SET @.d = SELECT col1 FROM tblTest
Where col1 is THE COLUMN NAME not the first column.
Nik Marshall-Blank MCSD/MCDBA
"Stanley" <xstanley@.gmail.com> wrote in message
news:1128081085.771682.230680@.f14g2000cwb.googlegroups.com...
> Hi all! I'm trying to set the value of @.d to a value from col1 in
> tblTest. I have tried the following code but it didn't work.
> DECLARE @.d VARCHAR(25)
> SELECT * FROM tblTest
> SET @.d = tblTest.col1
> Someone please help me! Thanks!
>|||DECLARE @.d VARCHAR(25)
SELECT @.d = col1 FROM tblTest
http://sqlservercode.blogspot.com/
"Stanley" wrote:

> Hi all! I'm trying to set the value of @.d to a value from col1 in
> tblTest. I have tried the following code but it didn't work.
> DECLARE @.d VARCHAR(25)
> SELECT * FROM tblTest
> SET @.d = tblTest.col1
> Someone please help me! Thanks!
>|||Thanks so much, that's what I really need!!

debugging stored procedures

Hello,

I am doing a lot of work with stored procedures at work now and am wondering
if there is a way that I can step through the code line by line and set
breakpoints on it like I do in VB/VBA to test variables/parameters.

Regards,

JayneOn Tue, 08 Mar 2005 21:38:46 +0000, Little PussyCat wrote:

>I am doing a lot of work with stored procedures at work now and am wondering
>if there is a way that I can step through the code line by line and set
>breakpoints on it like I do in VB/VBA to test variables/parameters.

Hi Jayne,

In Query Analyzer, hit F8 to bring up the object browser. Find the
stored procedure, right-click it and select "Debug".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> On Tue, 08 Mar 2005 21:38:46 +0000, Little PussyCat wrote:
>>I am doing a lot of work with stored procedures at work now and am
>>wondering if there is a way that I can step through the code line by
>>line and set breakpoints on it like I do in VB/VBA to test
>>variables/parameters.
> In Query Analyzer, hit F8 to bring up the object browser. Find the
> stored procedure, right-click it and select "Debug".

That's the theory.

In practice it appears that there is always something that prevents it
from working. To start with SQL Server must be running from a domain
account, and not local server. If you have Windows XP SP2 on the client,
you need at least 8.00.944 or the beta of SQL 2000 SP4, and you must
apply it on server and client. On top of that you must open port 135
in Windows firewall for the SQL box. (Do NOT open this port generally.)

And when all is done, you sysadm may get the idea that the Windows users
under which SQL Server runs is not permitted access to the workstations.
This how the last attempt ended in our shop.

I should add if you run SQL Server on your own machine, debugging
usually works.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You can also step into T-SQL stored procs from the VB or .NET development
environment. But personally, I have not found stepping into stored procs to
be all that useful since I could not see a way to examine the result sets of
queries. I have found using SQL profiler to be extremely useful for seeing
what parameters the calling app is passing in and which statements are
getting executed within the procedure. However, I think you need SA rights
to run profiler. To examine the result sets of queries, I usually just run
the guts of the procedure in Query Analyzer and use select statements to
dump the intermediate results. .

"Little PussyCat" <SPAMSPAM@.NOSPAM.com> wrote in message
news:ccm1g2-n1c.ln1@.tiger.sphynx...
> Hello,
> I am doing a lot of work with stored procedures at work now and am
wondering
> if there is a way that I can step through the code line by line and set
> breakpoints on it like I do in VB/VBA to test variables/parameters.
> Regards,
> Jayne|||Miss Livvy (XeveryidiwantistakenX@.yahoo.com) writes:
> You can also step into T-SQL stored procs from the VB or .NET
> development environment. But personally, I have not found stepping into
> stored procs to be all that useful since I could not see a way to
> examine the result sets of queries.

Yeah, I agree. Occassionally if a I have procedure with lot of procedural
logic, single-stepping through it can be helpful. The same is true if
want to look at the values of some variables.

But often I find too much hassle to start the debugger, so I rather
modify the procedure with some debug SELECT:s in strategic places.

Also, if your procedure raises an error and you want to debug that
happens after the error, the debugger does not appear to be very
co-operative.

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

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

Friday, February 24, 2012

Debugging Stored Procedure

Some days ago i asked here why my debugger in query analyzer never stops at code when i tried to debug a sp. I found the same question to be asked here again and again without any decent answer.
I found out my problem and would like to share it here :

hhtp://support.microsoft.com/?kbid=328151

My sql server was updated with Service Pack 3 but the client wasn't updated.
What i did was to turn on debugging on server for "older" clients with:

exec dbo.sp_sdidebug 'legacy_on'

Hope this will help other ppl too.Thanks for the link!

Debugging SQL Server statements in code

How can I view the raw SQL code that ASP.NET sends to SQL Server when using parameters?
The code below doesn't display the actual SQL statement, but instead:System.Data.SqlClient.SqlCommand.

string SQL = "INSERT INTO [BLAH] VALUES (@.startDate)";
SqlConnection mySqlConn = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
SqlCommand mySqlCmd = new SqlCommand(SQL, mySqlConn);
mySqlCmd.Parameters.Add(new SqlParameter("@.startDate", SqlDbType.DateTime));
mySqlCmd.Parameters["@.startDate"].Value = Request.Form["start_date"];
lblSQLDebug.Text = mySqlCmd.ToString();
TIA.
Use SQL Profiler. It allows you to see all the SQL sent to the SQL Server. Very useful tool.|||Hi Douglas.
Following your suggestion, I setup the profiler. Unfortunately, itdoesn't receive my query before I receive the same ambigious error fromASP.NET.
Is it possible to gather the dynamic string assembled and sent to the database before it's actually executed?

|||If the query does not make it into SQL Profiler, then the problem is not a SQL Server problem. Whatexactlyis the error you are getting?|||

String was not recognized as a valid DateTime.

mySqlCmd.Parameters.Add(new SqlParameter("@.startDate", SqlDbType.DateTime));
mySqlCmd.Parameters["@.startDate"].Value = Request.Form["start_date"];
I'm using a third party component to generate the date via a pop-up calendar.
The line # of the error points here:
mySqlCmd.ExecuteNonQuery();
which seems to indicate that the SQL Query being sent to SQL Server isbad. I can't fix it until I know what the actual value of @.startDate isthat is being sent to the database server.
Thanks for helping me out.

|||This doesn't answer myquestion, but did help me debug. I enabled trace="true" and it allowedme to see how the control was submitting the values (different thanexpected).
I'm just surprised that ASP.NET doesn't have a method like RawQuerythat you could use to display the SQL before it's sent to the databaseprogramatically.
E.g., mySqlCmd.RawQuery.ToString();
I appreciate your support Douglas.
|||ASP.NET does not have such a facility. SQL Server does, but again, if the info does not get to SQL Server, of course it will not show up in the Profiler. You can, of course, debug the application and inspect values as they are added to the Parameters collection, or examine the entire Parameters collection in the debugger.

Debugging SQL

Hi All
win 2k(pro0 sql server 2k (dev ed) asp-vbscript
I'm still learning how to code in SQL... I've found the "Debug"
command in QA for debuging stored procs but I cant seem to debug other
stuff... functions, triggers etc. I cant even debug code in the QA
that I write to test before I put the final version in a store proc.
Is there anyway of debugging at least the batch sql code I write in
the QA before I put it in the store proc... any 3rd party software if
cant be done in QA? or do I have to write it and put it in a stored
proc then debug it? how do you pros do it?
thanks for any info.
AlYou can debug triggers is you execute the DML statement (Insert, Update or
Delete) that fires the trigger inside a SP - then you can debug the SP. The
same is true for a UDF - call if from a SP. VS.NET IDE has debugger as well.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Harag" <harag@.softhome.net> wrote in message
news:v703mvgc4h59u8hcn0d32k8fk8p777kle0@.4ax.com...
> Hi All
> win 2k(pro0 sql server 2k (dev ed) asp-vbscript
> I'm still learning how to code in SQL... I've found the "Debug"
> command in QA for debuging stored procs but I cant seem to debug other
> stuff... functions, triggers etc. I cant even debug code in the QA
> that I write to test before I put the final version in a store proc.
> Is there anyway of debugging at least the batch sql code I write in
> the QA before I put it in the store proc... any 3rd party software if
> cant be done in QA? or do I have to write it and put it in a stored
> proc then debug it? how do you pros do it?
> thanks for any info.
> Al

Debugging problem with break points not being picked up

I have Team suite VS2005 sp1 installed and I can't debug my SSIS packages. If I put any break points in the Script code when I try to debug I get the message.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

I click on close and it runs the package but doesn't stop at any break point. If I take out all break points it will run without this error. A couple of times I have managed to put in a single break point early in the package within a different script component which causes it to break... but at a completely different point within a completely different script component. It's so weird. Any help would be appreciated.

Hoots.

I've moved this thread to the SSIS forum. The core VS Debugger team does not cover this area,

John

|||

An excert from Books Online -

Debugging the Script Task

To debug the code in your Script task, set at least one breakpoint in the code, and then close the VSA IDE to run the package in Business Intelligence Development Studio. When package execution enters the Script task, the VSA IDE reopens and displays your code in read-only mode. After execution reaches your breakpoint, you can examine variable values and step through the remaining code.

Note: You must execute the package to debug into your Script task. If you execute only the individual task, breakpoints in the Script task code are ignored.

Note:

You cannot debug a Script task when you run the Script task as part of a child package that is run from an Execute Package task. Breakpoints that you set within the Script task in the child package are disregarded in these circumstances. You can debug the child package normally by running it separately.

In addition -

You cannot debug Script tasks in event handlers.

When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.

|||I know how to debug a script task, it just doesn't. Clicking on Debug instead of close at the error message that's received brings up the Visual Studio Just in time debugger dialog which says:

An unhandled exception ('System.Runtime.InteropServices.COMException') occured in DTAttach.exe [3148]

It then gives me options to continue debugging in a new instance of Microsoft CLR debugger 2005 or a new instance of Visual Studio 2005. Choosing the CLT debugger brings up the dialog box saying:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Additional information: File position not valid for new breakpoint."

But the CLR debugger for DTAttach just reports that "(Disassembly cannot be displayed in run mode.)"

This seems to be a problem that has been around for a while, see this other post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=889400&SiteID=1

I too get the "Microsoft Visual Studio has lost its link to..." message. I have searched and searched, it seems quite a few other people are having the same problem but so far I haven't found a fix yet which is why I posted here.

Please help, is there a fix, can anyone tell me which file break points are stored in so that I can delete it manually as it could be corrupt?

thanks

Hoots.

|||I got a similar error without reference to the 'DTAttach'. Clicked the Debug button (Debug, Send Error Report, Don't Send) and continued from the break point that caused the error. Hope it works because that's all I got.|||Thanks for the response, unfortunately I think it just depends on what it feels like!! I can't get round it cos the whole debug thing just seems to be really flakey and am having to do it the old fashioned way with lots of msgbox commands. Would really appreciate a comment from a MVP or Microsoft itself on this one.

|||

Was having this problem just now. realized I had a breakpoint in a disabled script task. Once I took it out, not only did it stop throwing an error, it stopped on my other breakpoints.

Hope this helps.

|||Non of my script tasks are disabled. I think it's just a problem with the BIDS debugger. I'd love to have that confirmed and to find out when a fix is going to be available though.

|||

Hi there,

The problem you're seeing can be related to the fact that the precompiled script is out of sync with the code. One way to fix it is to remove the breakpoints on that script, regenerate the script binary ( either by changing the script by adding a comment or by flipping the precompiled property on the task off and back on), add the breakpoints back and save the script task and see if it works.

This assumes that you are not running on Windows Vista for which the debugging is currently broken due to an incompatibility between VSA and the CLR installed with the OS. On Vista the problem should be fixed at a later time but only for SQL Server SP2.

Let me know if refreshing the script binary caused the breakpoints to be valid again.

Thanks,

Silviu

|||Thanks for the info, unfortunately it didn't work. My package is made up predominantly of script components and contains a Foreach Loop and an additional Foreach loop within the other Loop, both are variable enumerators.

I went through each script component, took out any break points and put in additional comments. I then put a single break point in one of the scripts and ran it again but it still comes up with the same problem.

thanks

Hoots.
|||But break points are not supported in SSIS 2005... You have to use message boxes instead. This is a well documented "feature."|||Well documented... Really? Could you point me to the document that describes the features that causes BIDS to come up with the exception:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Seriously, I really would like to be able to read something definitive on break points in any script tasks not being supported for SSIS. After a large amount of searching and finding other people with the same problem, this is the first I've heard of them not being supported.

thanks

Hoots.

|||Have you read through this document? I see it claims support for script tasks, but not script components. I was under the assumption that breakpoints inside either script element were not supported.

http://msdn2.microsoft.com/en-us/library/ms140033.aspx|||Sorry, I meant script task rather than script component although I can't even see anything called a script component, what is it? Just as a note, all my script tasks are all set with precompiled as false.

Hoots.

|||Yep, read it now, I seem to be doing everything right, it just doesn't work.