sql permissions: select

Showing posts with label select. Show all posts

Thursday, March 29, 2012

Default Instance Named MSSQLSERVER

Does anyone know why, when I install SQL Server 2005 Developer (September
CTP) and select Default Instance during setup that I see the following in th
e
SQL Server 2005 Services area of SQL Server Configuration Manager.
SQL Server Agent (MSSQLSERVER)
SQL Server (MSSQLSERVER)
SQL Server Analysis Server (MSSQLSERVER)
SQL Server Reporting Services (MSSQLSERVER)
The physical box name is DEVDW but it seems the default instance is named
MSSQLServer which I definitely did not specifiy...I'm very confused.That is the service name, and it hasn't changes since version 6.0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:01D3B95C-611D-4EC3-BF67-C5D8D5C46A75@.microsoft.com...
> Does anyone know why, when I install SQL Server 2005 Developer (September
> CTP) and select Default Instance during setup that I see the following in
the
> SQL Server 2005 Services area of SQL Server Configuration Manager.
> SQL Server Agent (MSSQLSERVER)
> SQL Server (MSSQLSERVER)
> SQL Server Analysis Server (MSSQLSERVER)
> SQL Server Reporting Services (MSSQLSERVER)
> The physical box name is DEVDW but it seems the default instance is named
> MSSQLServer which I definitely did not specifiy...I'm very confused.
>|||So you're saying that SQL Server Agent, SQL Server Analysis Services and SQL
Server Reporting Services are all running under the MSSQLServer Service...I
don't think so. This is the "name" of the instance and I'm not sure why.
"Scott" wrote:

> Does anyone know why, when I install SQL Server 2005 Developer (September
> CTP) and select Default Instance during setup that I see the following in
the
> SQL Server 2005 Services area of SQL Server Configuration Manager.
> SQL Server Agent (MSSQLSERVER)
> SQL Server (MSSQLSERVER)
> SQL Server Analysis Server (MSSQLSERVER)
> SQL Server Reporting Services (MSSQLSERVER)
> The physical box name is DEVDW but it seems the default instance is named
> MSSQLServer which I definitely did not specifiy...I'm very confused.
>|||"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:4403E7F6-9F4C-4B51-B0D0-9B950725D36E@.microsoft.com...
> So you're saying that SQL Server Agent, SQL Server Analysis Services and
> SQL
> Server Reporting Services are all running under the MSSQLServer
> Service...I
> don't think so. This is the "name" of the instance and I'm not sure why.
>
This is the "name" of the default instance. If you installed a named
instance of SQL Server you would see the instance name instead. It's really
just there so you can identify all the services related to that instance.
David

Default Instance Named MSSQLSERVER

Does anyone know why, when I install SQL Server 2005 Developer (September
CTP) and select Default Instance during setup that I see the following in the
SQL Server 2005 Services area of SQL Server Configuration Manager.
SQL Server Agent (MSSQLSERVER)
SQL Server (MSSQLSERVER)
SQL Server Analysis Server (MSSQLSERVER)
SQL Server Reporting Services (MSSQLSERVER)
The physical box name is DEVDW but it seems the default instance is named
MSSQLServer which I definitely did not specifiy...I'm very confused.
That is the service name, and it hasn't changes since version 6.0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:01D3B95C-611D-4EC3-BF67-C5D8D5C46A75@.microsoft.com...
> Does anyone know why, when I install SQL Server 2005 Developer (September
> CTP) and select Default Instance during setup that I see the following in the
> SQL Server 2005 Services area of SQL Server Configuration Manager.
> SQL Server Agent (MSSQLSERVER)
> SQL Server (MSSQLSERVER)
> SQL Server Analysis Server (MSSQLSERVER)
> SQL Server Reporting Services (MSSQLSERVER)
> The physical box name is DEVDW but it seems the default instance is named
> MSSQLServer which I definitely did not specifiy...I'm very confused.
>
|||So you're saying that SQL Server Agent, SQL Server Analysis Services and SQL
Server Reporting Services are all running under the MSSQLServer Service...I
don't think so. This is the "name" of the instance and I'm not sure why.
"Scott" wrote:

> Does anyone know why, when I install SQL Server 2005 Developer (September
> CTP) and select Default Instance during setup that I see the following in the
> SQL Server 2005 Services area of SQL Server Configuration Manager.
> SQL Server Agent (MSSQLSERVER)
> SQL Server (MSSQLSERVER)
> SQL Server Analysis Server (MSSQLSERVER)
> SQL Server Reporting Services (MSSQLSERVER)
> The physical box name is DEVDW but it seems the default instance is named
> MSSQLServer which I definitely did not specifiy...I'm very confused.
>
|||"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:4403E7F6-9F4C-4B51-B0D0-9B950725D36E@.microsoft.com...
> So you're saying that SQL Server Agent, SQL Server Analysis Services and
> SQL
> Server Reporting Services are all running under the MSSQLServer
> Service...I
> don't think so. This is the "name" of the instance and I'm not sure why.
>
This is the "name" of the default instance. If you installed a named
instance of SQL Server you would see the instance name instead. It's really
just there so you can identify all the services related to that instance.
David

Default Instance Named MSSQLSERVER

Does anyone know why, when I install SQL Server 2005 Developer (September
CTP) and select Default Instance during setup that I see the following in the
SQL Server 2005 Services area of SQL Server Configuration Manager.
SQL Server Agent (MSSQLSERVER)
SQL Server (MSSQLSERVER)
SQL Server Analysis Server (MSSQLSERVER)
SQL Server Reporting Services (MSSQLSERVER)
The physical box name is DEVDW but it seems the default instance is named
MSSQLServer which I definitely did not specifiy...I'm very confused.That is the service name, and it hasn't changes since version 6.0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:01D3B95C-611D-4EC3-BF67-C5D8D5C46A75@.microsoft.com...
> Does anyone know why, when I install SQL Server 2005 Developer (September
> CTP) and select Default Instance during setup that I see the following in the
> SQL Server 2005 Services area of SQL Server Configuration Manager.
> SQL Server Agent (MSSQLSERVER)
> SQL Server (MSSQLSERVER)
> SQL Server Analysis Server (MSSQLSERVER)
> SQL Server Reporting Services (MSSQLSERVER)
> The physical box name is DEVDW but it seems the default instance is named
> MSSQLServer which I definitely did not specifiy...I'm very confused.
>|||So you're saying that SQL Server Agent, SQL Server Analysis Services and SQL
Server Reporting Services are all running under the MSSQLServer Service...I
don't think so. This is the "name" of the instance and I'm not sure why.
"Scott" wrote:
> Does anyone know why, when I install SQL Server 2005 Developer (September
> CTP) and select Default Instance during setup that I see the following in the
> SQL Server 2005 Services area of SQL Server Configuration Manager.
> SQL Server Agent (MSSQLSERVER)
> SQL Server (MSSQLSERVER)
> SQL Server Analysis Server (MSSQLSERVER)
> SQL Server Reporting Services (MSSQLSERVER)
> The physical box name is DEVDW but it seems the default instance is named
> MSSQLServer which I definitely did not specifiy...I'm very confused.
>|||"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:4403E7F6-9F4C-4B51-B0D0-9B950725D36E@.microsoft.com...
> So you're saying that SQL Server Agent, SQL Server Analysis Services and
> SQL
> Server Reporting Services are all running under the MSSQLServer
> Service...I
> don't think so. This is the "name" of the instance and I'm not sure why.
>
This is the "name" of the default instance. If you installed a named
instance of SQL Server you would see the instance name instead. It's really
just there so you can identify all the services related to that instance.
David

Tuesday, March 27, 2012

default db permissions for account

Hi
I want to allow the windows iusr_computername account exec permissions on
user stored procs and select on views. I have 50 odd stored procs. Is there
a way of assigning permissions so that this account always has those
permissions and the permissions are automatically added when a new view or
sp is added?
I'd also like to easily transfer this to other databases. I'm sure the
answer lies in using roles or scripts or perhaps there's a fundamentally
easy way that I haven't found yet?
Thanks
AndrewHi Andrew,
There is no fundamentally easy way to assign permissions on all the stored
procedures to a user.
You can use the following script to give a user permission on all existing
stored procedures, but you have to re-run it to give permissions to newly
created stored procedures:
DECLARE @.proc_name SYSNAME
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
END
You can use something similar to assign permissions on views using
inforamtion_schema.views.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> Hi
> I want to allow the windows iusr_computername account exec permissions on
> user stored procs and select on views. I have 50 odd stored procs. Is
there
> a way of assigning permissions so that this account always has those
> permissions and the permissions are automatically added when a new view or
> sp is added?
> I'd also like to easily transfer this to other databases. I'm sure the
> answer lies in using roles or scripts or perhaps there's a fundamentally
> easy way that I haven't found yet?
> Thanks
> Andrew
>|||Hi thanks for that.
Just one little problem. I'm getting an error "Invalid parameter 2 specified
for object_id.". I'm afraid my attempts to debug have failed. Can you help?
Thanks again
Andrew
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:Oh%23e9saXDHA.1280@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> There is no fundamentally easy way to assign permissions on all the stored
> procedures to a user.
> You can use the following script to give a user permission on all existing
> stored procedures, but you have to re-run it to give permissions to newly
> created stored procedures:
> DECLARE @.proc_name SYSNAME
> SET @.proc_name = ''
> WHILE 1=1
> BEGIN
> SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
> user stored procedures
> AND ROUTINE_TYPE = 'Procedure'
> AND ROUTINE_NAME > @.proc_name
> ORDER BY ROUTINE_NAME
> )
> IF @.proc_name IS NULL BREAK
> EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
> END
> You can use something similar to assign permissions on views using
> inforamtion_schema.views.
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
> message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> > Hi
> >
> > I want to allow the windows iusr_computername account exec permissions
on
> > user stored procs and select on views. I have 50 odd stored procs. Is
> there
> > a way of assigning permissions so that this account always has those
> > permissions and the permissions are automatically added when a new view
or
> > sp is added?
> >
> > I'd also like to easily transfer this to other databases. I'm sure the
> > answer lies in using roles or scripts or perhaps there's a fundamentally
> > easy way that I haven't found yet?
> >
> > Thanks
> > Andrew
> >
> >
>

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.

Monday, March 19, 2012

DecryptByCert performance

I have bunch of encrypted rows in the table and have stored procedure to select those rows.
It looks like this
SELECT CAST(DecryptByCert(Cert_ID('CertId'), field1) AS VARCHAR) AS f1,
CAST(DecryptByCert(Cert_ID('CertId'), field2) AS VARCHAR) AS f2,
CAST(DecryptByCert(Cert_ID('CertId'), field3) AS VARCHAR(255)) AS f3
FROM [table]

This stored procedure takes really long time even with hundreds of rows, so I suspect that I do something wrong. Is there any way to optimize this stored procedure?

Encryption/decryption by an asymmetric key (i.e. certificate) is much slower than when using a symmetric key.The recommended way to encrypt data is by using a symmetric key (or set of keys if you prefer) for protecting the data (i.e. AES or 3DES key), and protect these key using the certificate.

Another limitation you should consider is that asymmetric key encryption in SQL Server 2005 is limited to only one block of data. This means that the maximum amount of plaintext you can encrypt is limited by the modulus of the private key. In the case of RSA 1024 (in case you are using SQL Server 2005-generated certificates), the maximum plaintext is 117 bytes.

I am also including an additional thread from this forum that talk about this topic:

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

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

DECODE please help

Hi-

I am trying to accomplish this in my SELECT statement...

If the length of the retreived string data is more than 10 characters, it should return the first 10 characters followed by a literal string '..' else return the string data as is

I tried to use IIF, CASE but didn't got it work, kept getting errors...

SELECT FinalName = IIF ( DATALENGTH ( NameString ) > 10, SUBSTRING ( NameString, 0, 10 ) + '..' , NameString ) FROM SomeTable

Any help is highly appreciated... Thanks for your quick responses...T-SQL does not provide an IIF function. You need to use CASE.


SELECT
 CASE
 WHEN LEN(NameString) > 10 THEN SUBSTRING ( NameString, 0, 10 ) + '..'
 ELSE NameString
 END AS FinalName
FROM
 SomeTable

Terri|||And actually, for the SUBSTRING function you should be using a 1,10 not 0,10. And note that the LEN function would be more correct for your purposes than DATALENGTH.

Terri|||Thank you so very much Terri... This worked perfect...
:)

DECODE in MS SQL

In Oracle we have expression

SELECT
....
DECODE (T.LANGUAGE, 'E', T.DESC_ENG, T.DESC)
FROM
TABLE T

if acts like that :
if T.LANGUAGE = 'E' it returns T.DESC_ENG. If it is different - T.DESC.

Is there some way to make the same in MS SQL Server ?Use a CASE statement.

blindman|||why not just give it to him/her...

SELECT CASE WHEN T.LANGUAGE = 'E' THEN T.DESC_ENG ELSE T.DESC END

DECODE is such a pain in the neck...

I swear Oracle 8i...for as powerful as it is, has NOTHING close to Case...|||Really? I've heard Oracle people (should we call them "Sibyl"s?) complain many times that SQL Server does not have a DECODE equivalent, but I didn't know that Oracl doesn't have a CASE equivalent. That does seem to be a major shortcoming.|||Originally posted by blindman
Really? I've heard Oracle people (should we call them "Sibyl"s?) complain many times that SQL Server does not have a DECODE equivalent, but I didn't know that Oracl doesn't have a CASE equivalent. That does seem to be a major shortcoming.

I don't know about > 8i...but there is no CASE

Complain that SQL doesn't have DECODE?

Looks like 9i has come up to snuff in a lot of areas...

http://www.praetoriate.com/oracle_tips_case_statement.htm|||COALESCE is somewhat similar to DECODE.|||you can also write your own function to emmulate decode|||Originally posted by rdjabarov
COALESCE is somewhat similar to DECODE.

Similar...barely though...as long as you're limited to NULL

you can also write your own function to emmulate decode

Really? I was going to give it a shot...but I realized it's not worth it...giving the fact that we already have CASE (which 9i now has).

You have to have the ability to evaluate ANY expression...I think (and now mind you, only on rare occasions) it would be a big job...

Sunday, March 11, 2012

Decode funktion

Hi!

I have a question about the "Decode" funktion. is it only avaiable in Oracle? The reason for my question is that I need to SELECT a colum in a table based on a int value. So if the uservalue is lower then that take that colum, lower then that take colum...etc.

Can I use decode? or/and is there better funktion (I'm using MS SQL 2005)

Thanks in advance

Mark

MSSQL does not have a DECODE function and I'm not familiar with a function that performs the same functionality.

One way to implement what you want is to use the case statement. Example.

Select

Case when @.UserInput between 1 and 5 then 'A'

Case when @.UserInput between 6 and 10 then 'B'

Case else 'C'

end as MyColumn

From

MyTable

I believe Oracle also supports the case syntax, so if you modify your

SQL statement inside Oracle and it works, then it should easily migrate

over to MSSQL.

Larry Pope|||Thanks a lot, I think that will work just fine!

I have sub question then, is it possible to use the a case in a loop? Because I have a table where I don't know the values of the intervals.

Thanks, Mark|||I'm not entirely sure what you are trying to accomplish. I kind

of have an idea but before I offer a suggestion, I'd like to get a

little more detail.

Could you provide some background and a quick example?

Larry Pope|||yeah, its

kinda fuzzy my questions.<br><br>I have a table with

"over", "under", and "price". this table contains intervals

over under Price
0 100 400
101 250 600
251 357 800

And

continues like that. I have to get the user value and run through the

table to find the right price (between over and under), and use the price value for a calculation

in my query. Did that make any sense at all ? :)|||No, that makes sense now. You want to return the correct price

for a given quantity. At least that's my take on it. I'm

curious, Is this for a SSIS package or for some transactional

system?

The following SQL statement would be good if your issuing the statement for a transactional system one at a time.

Select

Price

From

PriceTable

Where

@.UserValue Between Over and Under

You will of course need to build logic for when the input parameter

doesn't match any records, or worse yet there is bad data and returns

multiple records.

If your trying to do the lookup process in bulk, there are other ways (conditional joins) that might perform better.

Larry Pope|||I should have seen that myself, but I didn't so thanks :) No, its a online cargo price calculater. There are many values and options involved in cargo, so it makes some bad bad statements.

I have to see about the performance... I hope it will work, otherwise I'll just have to optimize along the way. I'm so lucky this is a pilot project, so they just want it to work.

Thanks again, Mark

Declaring explicit transaction for a select stmt.

Does it make sense to declare a transaction for a query that is only
performing a data read.
for example:
BEGIN tran
select * from pubs..authors
if @.@.error <> 0
Begin
ROLLBACK tran
raiserror('blah', 16,1)
RETURN
END
COMMIT
My thinking is that if there is an error on reading, than the client will
anyway get the error message, so adding an explicit transaction may be
addding overhead.Why would you need a transaction? You have nothing to rollback?
-- Jesse
"MG" <y4forums.t.mdgoyal@.xoxy.net> wrote in message
news:%235nPdDsDFHA.148@.TK2MSFTNGP14.phx.gbl...
> Does it make sense to declare a transaction for a query that is only
> performing a data read.
> for example:
> BEGIN tran
> select * from pubs..authors
> if @.@.error <> 0
> Begin
> ROLLBACK tran
> raiserror('blah', 16,1)
> RETURN
> END
> COMMIT
> My thinking is that if there is an error on reading, than the client will
> anyway get the error message, so adding an explicit transaction may be
> addding overhead.
>|||> Does it make sense to declare a transaction for a query that is only
> performing a data read.
No, It does not make sense.
AMB
"MG" wrote:

> Does it make sense to declare a transaction for a query that is only
> performing a data read.
> for example:
> BEGIN tran
> select * from pubs..authors
> if @.@.error <> 0
> Begin
> ROLLBACK tran
> raiserror('blah', 16,1)
> RETURN
> END
> COMMIT
> My thinking is that if there is an error on reading, than the client will
> anyway get the error message, so adding an explicit transaction may be
> addding overhead.
>
>|||On Wed, 9 Feb 2005 11:03:19 -0500, MG wrote:

>Does it make sense to declare a transaction for a query that is only
>performing a data read.
Hi MG,
Not in the default isolation mode (read committed). In repeatable read or
serializable mode, it does make sense.
You don't need the rollback, though.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

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.

Declaring a cursor on a temporary table

How do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :
Select Name, Age Into #TempPerson From Personcan i ask especially what your trying to do more in detail please.|||I hjave NEVER done this...and only did this for myself to see if it works (didn't see why it wouldn't), but I highly DO NOT recommed this...

USE Northwind
GO
SELECT * INTO #TEMP FROM Orders

DECLARE
@.OrderID int
, @.CustomerID nchar(10)
, @.EmployeeID int
, @.OrderDate datetime
, @.RequiredDate datetime
, @.ShippedDate datetime
, @.ShipVia int
, @.Freight money
, @.ShipName nvarchar(80)
, @.ShipAddress nvarchar(120)
, @.ShipCity nvarchar(30)
, @.ShipRegion nvarchar(30)
, @.ShipPostalCode nvarchar(20)
, @.ShipCountry nvarchar(30)
, @.LoopCounter int

DECLARE myCursor99 CURSOR
FOR
SELECT OrderID
, CustomerID
, EmployeeID
, OrderDate
, RequiredDate
, ShippedDate
, ShipVia
, Freight
, ShipName
, ShipAddress
, ShipCity
, ShipRegion
, ShipPostalCode
, ShipCountry
FROM #Temp

OPEN myCursor99

SELECT @.LoopCounter = 0

FETCH NEXT FROM myCursor99 INTO
@.OrderID
, @.CustomerID
, @.EmployeeID
, @.OrderDate
, @.RequiredDate
, @.ShippedDate
, @.ShipVia
, @.Freight
, @.ShipName
, @.ShipAddress
, @.ShipCity
, @.ShipRegion
, @.ShipPostalCode
, @.ShipCountry

WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Some Code
SELECT @.LoopCounter = @.LoopCounter + 1
FETCH NEXT FROM myCursor99 INTO
@.OrderID
, @.CustomerID
, @.EmployeeID
, @.OrderDate
, @.RequiredDate
, @.ShippedDate
, @.ShipVia
, @.Freight
, @.ShipName
, @.ShipAddress
, @.ShipCity
, @.ShipRegion
, @.ShipPostalCode
, @.ShipCountry
END
CLOSE myCursor99
DEALLOCATE myCursor99
DROP TABLE #Temp
SELECT 'Loops incurred: ' + CONVERT(varchar(15),@.LoopCounter)
GO|||I've simplified a stored proc
It's the "declare Age dynamic scroll cursor for select Age from #TempPerson
" that doesn't work

create procedure GetAges
as
begin
declare @.Age char(20),
declare @.PreviousAge char(20),

declare Age dynamic scroll cursor for select Age from #TempPerson

select Name,Age into #TempPerson From Person
insert into #TempPerson (Name,Age) Select LastName,Age From OtherPerson Where TypePerson = '1'

select @.PreviousAge=' '

open Age
while 1=1
begin
fetch next Age into @.Age
if @.Age <> @.PreviousAge
...
select @.Age=@.PreviousAge
close Age
end|||OK Brett thank you -- AGAIN --
that principle will work
I'll give you some news tomorrow
I'm fed up with stored proc for today !!!!!|||Good Luck, but I bet you, if you tell me what you're trying to do, we can find a set based solution...|||I'm migrating the Sybase structure to SQL Server
and I've go to rewrite
Very-Stupidly-And-Badly-Written-Sybase Watcom-Stored proc

I hate working with programs written by spagetti-minded-programmers

So you don't want to see to stored proc (REALLY don't !!!)|||Yup, sometimes it's not an option...

I will go feel bad for you now....

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advanced
This would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>
|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>
|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be[vbcol=seagreen]
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
exercise
>
|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
David Gugick
Imceda Software
www.imceda.com

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advancedThis would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
exercise[vbcol=seagreen]
>|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
David Gugick
Imceda Software
www.imceda.com

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advancedThis would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>> DECLARE @.x varchar
>> Set @.x = (SELECT COUNT(*)FROM #t)
>> DECLARE '@.date' + @.x datetime
>> Lets say the count is 5
>> I want to DECLARE a datetime variable called @.date5
>> How do I do this?
>> Thanks in advanced
>>
>|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> > This would require dynamic sql, which would be either a pointless
exercise
> > or a dynamic coding nightmare. What are you trying to accomplish? Why
> > is
> > it important that the name also convey information/data?
> >
> > "Jim Campau" <jim_campau@.bausch.com> wrote in message
> > news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> >> DECLARE @.x varchar
> >> Set @.x = (SELECT COUNT(*)FROM #t)
> >>
> >> DECLARE '@.date' + @.x datetime
> >>
> >> Lets say the count is 5
> >> I want to DECLARE a datetime variable called @.date5
> >> How do I do this?
> >>
> >> Thanks in advanced
> >>
> >>
> >
> >
>|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
--
David Gugick
Imceda Software
www.imceda.com

DECLARE SYNTAX

I am trying to convert a declare syntax from SQL to MySQL
the syntax is as follows:
declare @.x int ;
set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
UPDATE bugevent
SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
ixBug = (SELECT max(ixBug) FROM bug)
WHERE ixBugEvent = @.x;
but it is giving me an error. can anyone help.
thanks for your time in advance...Since people here generally speak SQL, not MySQL, you might
have better luck asking your question in a MySQL newsgroup.
(If you want to know how to same something in French, you'd ask
someone who speaks French, right?)
Steve Kass
Drew University
harpalshergill@.gmail.com wrote:

>I am trying to convert a declare syntax from SQL to MySQL
>the syntax is as follows:
>
>declare @.x int ;
>set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
>UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
>
>but it is giving me an error. can anyone help.
>thanks for your time in advance...
>
>|||(harpalshergill@.gmail.com) writes:
> I am trying to convert a declare syntax from SQL to MySQL
> the syntax is as follows:
>
> declare @.x int ;
> set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
> UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
> but it is giving me an error. can anyone help.
> thanks for your time in advance...
All I can say is hat I don't think that @.x variables are available in
MySQL. I would only expect those to work with SQL Server and Sybase.
There is a comp.databases.mysql. You should have better luck there.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not only are variables available but they can used along with
columns in a query! :)
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97EFB333256EYazorman@.127.0.0.1...
> (harpalshergill@.gmail.com) writes:
> All I can say is hat I don't think that @.x variables are available in
> MySQL. I would only expect those to work with SQL Server and Sybase.
> There is a comp.databases.mysql. You should have better luck there.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||A more precise analogy might be to ask: if you want to know how to name
something in Creole French, you'd ask some one who speaks it, non?
Speak de patetois, non?
Steve Kass wrote:
> Since people here generally speak SQL, not MySQL, you might
> have better luck asking your question in a MySQL newsgroup.
> (If you want to know how to same something in French, you'd ask
> someone who speaks French, right?)
> Steve Kass
> Drew University
> harpalshergill@.gmail.com wrote:
>|||harpalshergill@.gmail.com wrote:
> I am trying to convert a declare syntax from SQL to MySQL
> the syntax is as follows:
>
> declare @.x int ;
> set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
> UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
>
> but it is giving me an error. can anyone help.
> thanks for your time in advance...
>
Two things that might help you get a response:
1. Include the error message, don't make us guess what error you're
getting.
2. Post to the proper newsgroup

Declare dynamic Cursor from String

Hi,
is it possible to create a cursor from a dynamic string?
Like:

DECLARE @.cursor nvarchar(1000)
SET @.cursor = N'SELECT product.product_id
FROM product WHERE fund_amt > 0'

DECLARE ic_uv_cursor CURSOR FOR @.cursor

instead of using this

--SELECT product.product_id
--FROM product WHERE fund_amt > 0 -- AND mpc_product.status
= 'aktiv'

Havn't found anything in the net...
Thanks,
PeppiNot within the stored procedure, but I do know their are some undocumented
sps - such as "sp_cursoropen" and a few others with "sp_cursor*" which might
be abloe to do the job for you.

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<peppi911@.hotmail.com> wrote in message
news:1146129244.595060.254470@.v46g2000cwv.googlegr oups.com...
> Hi,
> is it possible to create a cursor from a dynamic string?
> Like:
>
> DECLARE @.cursor nvarchar(1000)
> SET @.cursor = N'SELECT product.product_id
> FROM product WHERE fund_amt > 0'
> DECLARE ic_uv_cursor CURSOR FOR @.cursor
> instead of using this
> --SELECT product.product_id
> --FROM product WHERE fund_amt > 0 -- AND mpc_product.status
> = 'aktiv'
> Havn't found anything in the net...
> Thanks,
> Peppi|||(peppi911@.hotmail.com) writes:
> is it possible to create a cursor from a dynamic string?
> Like:
>
> DECLARE @.cursor nvarchar(1000)
> SET @.cursor = N'SELECT product.product_id
> FROM product WHERE fund_amt > 0'
> DECLARE ic_uv_cursor CURSOR FOR @.cursor
> instead of using this
> --SELECT product.product_id
> --FROM product WHERE fund_amt > 0 -- AND mpc_product.status
>= 'aktiv'

Yes, this is possible, but the question remains: why?

See here for details: http://www.sommarskog.se/dynamic_sql.html.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your answers.
I'll have a look at the link.
The WHY ist that once a day the cursor should affect all products and
during the day every 5 minutes reclculate for inaktive ones.
Thats the reason.

Thanks,
mike|||peppi911@.hotmail.com wrote:
> The WHY ist that once a day the cursor should affect all products and
> during the day every 5 minutes reclculate for inaktive ones.
> Thats the reason.

That doesn't explain why you are using a cursor. It also doesn't
explain the need for dynamic SQL. Both are things you should avoid when
you can, I think that was what Erland was trying to get at.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(peppi911@.hotmail.com) writes:
> Thanks for your answers.
> I'll have a look at the link.
> The WHY ist that once a day the cursor should affect all products and
> during the day every 5 minutes reclculate for inaktive ones.
> Thats the reason.

That does not explain the cursor - but could be that there is some
calculations are too complex to be carried out set-based. But there is
all reason to avoid the iteration if possible and handle all rows at
once. If there are many products this could mean serious reduction in
execution time.

On the other hand, there is enough information for me to tell that you
don't need any dynamic SQL. There are two possible solutions:

DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND (@.runforall = 1 OR fund_amt > 0)

If there is an index on the selection column for active products, it's
better to do:

IF @.runforall = 1
BEGIN
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
END
ELSE
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND fund_amt > 0
END

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thursday, March 29, 2012

Default Instance Named MSSQLSERVER

Default Instance Named MSSQLSERVER

Default Instance Named MSSQLSERVER

Tuesday, March 27, 2012

default db permissions for account

Sunday, March 25, 2012

Default Database?

Default Database?

Default Database?

Wednesday, March 21, 2012

Decrypting and Encrypted Stored Procedure

Monday, March 19, 2012

DecryptByCert performance

DECODE please help

DECODE in MS SQL

Sunday, March 11, 2012

Decode funktion

Declaring explicit transaction for a select stmt.

Declaring default value for a variable

Declaring a cursor on a temporary table

Declare variable problem

Declare variable problem

Declare variable problem

DECLARE SYNTAX

Declare dynamic Cursor from String

sql permissions

Blog Archive

About Me