Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname = N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>> The server collation is Latin1_General_BIN
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> >> The server collation is Latin1_General_BIN
> >
> > I'm not completely sure about this, but it looks as if the BIN collation
> > means that you're dealing with a SQLServer instance which is case sensitive.
> >
> > It's rare that this is required, as Windows isn't case sensitive.
> >
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> >
> > Griff
> >
> >
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
Reddi
You have a binary collation (very unusual, btw) which is also case sensitive.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>
|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff
|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>
|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>
>
|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the cod
e?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the c
ode?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've nevern">
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you
lose all stuff in the
system databases. Also, this doesn't change the collation for the user datab
ases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitiv
e.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've nev
er
> tried it. I'd be surprised if it were possible though...
> Griff
>[/vbcol]|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means y
ou lose all stuff in the
> system databases. Also, this doesn't change the collation for the user dat
abases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTN
GP10.phx.gbl...
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitive query

I would like to execute query:
select Column1 from table
and I get an error message:
Invalid column name 'Column1'.
If I write query like this:
select column1 from table
than it works.
It looks that server is case sensitive for columns and tables. How can I
change this?
Otherwise I have to rewrite all aplication.
Regards,SYou'll have to do an ALTER DATABASE to change the collation on the DB.
However, you'll have to change the collation on all character columns to get
the intended results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
I would like to execute query:
select Column1 from table
and I get an error message:
Invalid column name 'Column1'.
If I write query like this:
select column1 from table
than it works.
It looks that server is case sensitive for columns and tables. How can I
change this?
Otherwise I have to rewrite all aplication.
Regards,S|||I have already changed collation.
But I still have problems.
If I declare variable in my procedure, for example @.productID and than
somewhere in my procedure I use it, like: set @.productid=1
(not the same case for id), I get an error, because "id" should be "ID".
Any idea?
regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> You'll have to do an ALTER DATABASE to change the collation on the DB.
> However, you'll have to change the collation on all character columns to
> get
> the intended results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> I would like to execute query:
> select Column1 from table
> and I get an error message:
> Invalid column name 'Column1'.
> If I write query like this:
> select column1 from table
> than it works.
> It looks that server is case sensitive for columns and tables. How can I
> change this?
> Otherwise I have to rewrite all aplication.
> Regards,S
>|||How exactly did you change the collation? What commands did you use?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
I have already changed collation.
But I still have problems.
If I declare variable in my procedure, for example @.productID and than
somewhere in my procedure I use it, like: set @.productid=1
(not the same case for id), I get an error, because "id" should be "ID".
Any idea?
regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> You'll have to do an ALTER DATABASE to change the collation on the DB.
> However, you'll have to change the collation on all character columns to
> get
> the intended results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> I would like to execute query:
> select Column1 from table
> and I get an error message:
> Invalid column name 'Column1'.
> If I write query like this:
> select column1 from table
> than it works.
> It looks that server is case sensitive for columns and tables. How can I
> change this?
> Otherwise I have to rewrite all aplication.
> Regards,S
>|||I have changed collation:
ALTER DATABASE dbName COLLATE Slovenian_CI_AS
Queries now works:
select Column1 from table
or
select column1 from table
both works.
But if I declare variable in SP, like :
declare @.id int
and then set the value:
set @.ID=5
I get an error message, that I should declare variable @.ID.
This all is happening because Turkish has different letter for I or i.
But all my procedures are written case insensitive.
What should I do?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> How exactly did you change the collation? What commands did you use?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I have already changed collation.
> But I still have problems.
> If I declare variable in my procedure, for example @.productID and than
> somewhere in my procedure I use it, like: set @.productid=1
> (not the same case for id), I get an error, because "id" should be "ID".
> Any idea?
> regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
>
>|||Are you definitely inside the DB when you run the code or are you in master
or tempdb?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
I have changed collation:
ALTER DATABASE dbName COLLATE Slovenian_CI_AS
Queries now works:
select Column1 from table
or
select column1 from table
both works.
But if I declare variable in SP, like :
declare @.id int
and then set the value:
set @.ID=5
I get an error message, that I should declare variable @.ID.
This all is happening because Turkish has different letter for I or i.
But all my procedures are written case insensitive.
What should I do?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> How exactly did you change the collation? What commands did you use?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I have already changed collation.
> But I still have problems.
> If I declare variable in my procedure, for example @.productID and than
> somewhere in my procedure I use it, like: set @.productid=1
> (not the same case for id), I get an error, because "id" should be "ID".
> Any idea?
> regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
>
>|||I'm definitly inside the DB.
I go to database and click: create new stored procedure and then copy my
procedure from other server into this window and then check sintax failes
because of different letters.
Letter i is not the same as letter I on this server. On all other servers
everything works.
Any idea?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23ckyr$hXGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Are you definitely inside the DB when you run the code or are you in
> master
> or tempdb?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
> I have changed collation:
> ALTER DATABASE dbName COLLATE Slovenian_CI_AS
> Queries now works:
> select Column1 from table
> or
> select column1 from table
> both works.
> But if I declare variable in SP, like :
> declare @.id int
> and then set the value:
> set @.ID=5
> I get an error message, that I should declare variable @.ID.
> This all is happening because Turkish has different letter for I or i.
> But all my procedures are written case insensitive.
> What should I do?
> Regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||How about if you create the proc inside Query Analyzer? I don't trust
Enterprise Manager for most things.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:O8Z9iFkXGHA.196@.TK2MSFTNGP04.phx.gbl...
I'm definitly inside the DB.
I go to database and click: create new stored procedure and then copy my
procedure from other server into this window and then check sintax failes
because of different letters.
Letter i is not the same as letter I on this server. On all other servers
everything works.
Any idea?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23ckyr$hXGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Are you definitely inside the DB when you run the code or are you in
> master
> or tempdb?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
> I have changed collation:
> ALTER DATABASE dbName COLLATE Slovenian_CI_AS
> Queries now works:
> select Column1 from table
> or
> select column1 from table
> both works.
> But if I declare variable in SP, like :
> declare @.id int
> and then set the value:
> set @.ID=5
> I get an error message, that I should declare variable @.ID.
> This all is happening because Turkish has different letter for I or i.
> But all my procedures are written case insensitive.
> What should I do?
> Regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>

Cascading Parameters Unreliable

I find that cascading parameters work intermittently. In addition, a
report with a drop down list will sometimes attempt to execute before
the user has made a selection.make sure you have the parameters in the proper order for cascading to work
properly. i.e. country parm first in your parameter list, state parameter
second, city parameter third... all the cascading parameters have to be
ahead of your other non-cascading parameters. to ensure the report will not
execute before a user selects parms... just leave 1 parm without default
value.
"mjhillman@.msn.com" wrote:
> I find that cascading parameters work intermittently. In addition, a
> report with a drop down list will sometimes attempt to execute before
> the user has made a selection.
>|||I am calling two stored procedures that take the same parameter. I
only have one parameter in the parameter list. Does this suggestion
apply to this scenario as well? Do I have to modify the SP to use a
different parameter name? (I did not write the SPs I am just executing
reports against an existing WMS.). Thanks.

Capturing the results from exec command

Hi,

I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.

exec(select count(*) from @.dynamictable where condition = @.dynamiccond)

But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?

Thanks

2 ways

USE pubs
GO

--sp_executesql
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = N'SELECT @.intTableCount = COUNT(*) FROM ' + @.chvTableName

EXEC sp_executesql @.chvSQL, N'@.intTableCount INT OUTPUT', @.intTableCount OUTPUT

SELECT @.intTableCount
GO

--EXEC (SQL)
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = 'Insert into #temp Select Count(*) from ' + @.chvTableName

EXEC( @.chvSQL)

SELECT @.intTableCount = Totalcount from #temp

SELECT @.intTableCount

DROP TABLE #temp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Or:

DECLARE @.chvTableName VARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'sysobjects'

insert into #temp(totalCount)
EXEC( 'Select Count(*) from ' + @.chvTableName)

SELECT * from #temp

DROP TABLE #temp

Note: it is generally considered a bad practice to do this sort of thing unless you are building some sort of tool. If this is a production application, it would be better to build a procedure per table:

create procedures count_accounts
as
select count(*) from account
go

Yes, it sounds like a lot of maintenance, but unless you build a large quantity of tables, it shouldn't be a big deal.

capturing the output from a stored procedure into a report

I have a stored procedure that takes some parameters. I can execute it
in the data tab in vb.net and get my results. How do i take these
results and form a report? Is there a way to capture the fields that
are returned in order to drop them into a report?
I'm doing all this in vb.net.
Thanks.Are you using the report designer and the data tab? Does the stored
procedure execute and return data from the data tab? If so, sometimes
executing the stored procedure does not fill the field list. Try clicking on
the refresh fields button (look to the right of the ... , it looks like the
fresh button for IE. Hover over it and it will tell you what the button is
for). If this doesn't cause the field list to fill in then you can put in
the fields manually in the list. Right mouse click in the field list, add
field and give it the name of the field name.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"muris" <rmuris@.hotmail.com> wrote in message
news:1112374203.464263.41490@.z14g2000cwz.googlegroups.com...
> I have a stored procedure that takes some parameters. I can execute it
> in the data tab in vb.net and get my results. How do i take these
> results and form a report? Is there a way to capture the fields that
> are returned in order to drop them into a report?
> I'm doing all this in vb.net.
> Thanks.
>|||hitting the refresh button worked!! Thank you.

Capturing results from SPROC

I need to execute a SPROC from another SPROC that returns 1 row with three fields. How do I capture the data out of those?

Have sproc #1 create a #Temp table,

then have sproc #2 populate that #Temp table.

The data will then be available in sproc #1.

|||If the stored procedure will always return 1 or less rows another good alternative is to have the stored procedure return three OUTPUT parameters instead of a row with three columns.|||

Here you can use either Insert Into ...Exec or OUTPUT param.

Code Snippet

--Option 1: If you are not autorized to change the Procedure code.

Create Proc #TestProc1

as

Select 1 Col1, 'Test data' Col2, Getdate() Col3

Go

Create Table #TempData (

Col1 int,

Col2 Varchar(100),

Col3 Datetime

)

Insert Into #TempData

Exec #TestProc1

Select * from #TempData

Code Snippet

--Option 2: Best Fit

Create Proc #TestProc2(

@.Col1 as Int OUTPUT,

@.Col2 as Varchar(100) OUTPUT,

@.Col3 as DateTime OUTPUT

)

as

Select

@.Col1 = 1

,@.Col2 = 'Test data'

,@.Col3 = Getdate()

Go

Declare @.Col1 as Int,

@.Col2 as Varchar(100) ,

@.Col3 as DateTime

Exec #TestProc2 @.Col1 OUTPUT, @.Col2 OUTPUT, @.Col3 OUTPUT

Select @.Col1, @.Col2, @.Col3

Capture Returned Value From Exec(@Build) into another variable

I am building a SQL statement that returns a number.
when I execute the Built SQL statment EXEC(@.Build). What I need to do
now is take that number that comes back and store it in another
variable so I can do some conditional logic. Any ideas? See SQL below.

Something like @.Count=Exec(@.Build) which I know doesnt work.

Thanks,
Phil

DECLARE @.PullDate varchar(12)

SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''

PRINT(@.PullDate)

DECLARE @.COUNTER BIGINT

DECLARE @.SELECT VARCHAR(500)
DECLARE @.SELECT2 VARCHAR(1000)
DECLARE @.BUILD VARCHAR(5000)

SET @.SELECT='

SELECT COUNTER FROM
OPENQUERY(PROD,'

SET @.SELECT2='''
SELECT
COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
FROM
COLLECTOR_RESULTS,
WHERE
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCOUNT.END_DATE ) =
to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
)
GROUP BY
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'

SET @.BUILD=@.SELECT+@.SELECT2
PRINT(@.BUILD)
EXEC(@.BUILD)

--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
INTO @.COUNTER so I can do a conditional statement.)
if @.COUNTER>=1
begin
print('yes')
end<philipdm@.msn.com> wrote in message
news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
>I am building a SQL statement that returns a number.
> when I execute the Built SQL statment EXEC(@.Build). What I need to do
> now is take that number that comes back and store it in another
> variable so I can do some conditional logic. Any ideas? See SQL below.
> Something like @.Count=Exec(@.Build) which I know doesnt work.
> Thanks,
> Phil
>
>
> DECLARE @.PullDate varchar(12)
> SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> PRINT(@.PullDate)
> DECLARE @.COUNTER BIGINT
> DECLARE @.SELECT VARCHAR(500)
> DECLARE @.SELECT2 VARCHAR(1000)
> DECLARE @.BUILD VARCHAR(5000)
>
> SET @.SELECT='
> SELECT COUNTER FROM
> OPENQUERY(PROD,'
> SET @.SELECT2='''
> SELECT
> COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> FROM
> COLLECTOR_RESULTS,
> WHERE
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> )
> GROUP BY
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
>
> SET @.BUILD=@.SELECT+@.SELECT2
> PRINT(@.BUILD)
> EXEC(@.BUILD)
> --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
> INTO @.COUNTER so I can do a conditional statement.)
> if @.COUNTER>=1
> begin
> print('yes')
> end

Instead of EXEC(), you can use sp_executesql with an output parameter:

declare @.sql ntext, @.counter int
set @.sql = 'select @.counter = counter from openquery(...)'
exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
select @.counter

See here for an example:

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Simon|||I am able to get this to return a value but I cant get this to work.
Any ideas?

IF @.Counter>1
Print('Yes')

Simon Hayes wrote:
> <philipdm@.msn.com> wrote in message
> news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
> >I am building a SQL statement that returns a number.
> > when I execute the Built SQL statment EXEC(@.Build). What I need to
do
> > now is take that number that comes back and store it in another
> > variable so I can do some conditional logic. Any ideas? See SQL
below.
> > Something like @.Count=Exec(@.Build) which I know doesnt work.
> > Thanks,
> > Phil
> > DECLARE @.PullDate varchar(12)
> > SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> > +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> > +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> > PRINT(@.PullDate)
> > DECLARE @.COUNTER BIGINT
> > DECLARE @.SELECT VARCHAR(500)
> > DECLARE @.SELECT2 VARCHAR(1000)
> > DECLARE @.BUILD VARCHAR(5000)
> > SET @.SELECT='
> > SELECT COUNTER FROM
> > OPENQUERY(PROD,'
> > SET @.SELECT2='''
> > SELECT
> > COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> > FROM
> > COLLECTOR_RESULTS,
> > WHERE
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> > to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > )
> > GROUP BY
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
> > SET @.BUILD=@.SELECT+@.SELECT2
> > PRINT(@.BUILD)
> > EXEC(@.BUILD)
> > --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD
STORED
> > INTO @.COUNTER so I can do a conditional statement.)
> > if @.COUNTER>=1
> > begin
> > print('yes')
> > end
> Instead of EXEC(), you can use sp_executesql with an output
parameter:
> declare @.sql ntext, @.counter int
> set @.sql = 'select @.counter = counter from openquery(...)'
> exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
> select @.counter
> See here for an example:
> http://www.sommarskog.se/dynamic_sql.html#sp_executesql
> Simon|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. All I need to do is set a
@.variable=@.Output Variable.
Thanks for your help Simon!
Phil|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!

Capture Execution Time Then Rollback Transaction

I would like to execute a stored procedure listed below then capture or
print out the execution time. Lastly rollback the transaction so that data
does not change in the database.
dbo.usp_Manual_toTraint 65823,'2004-08-01','TA_BB','2004-09-01'
Please help me with this procedure.
Thanks,Joe
See if this helps you
declare @.dt datetime
set @.dt =getdate()
begin tran
--do something here
rollback
select datediff(ss,@.dt,getdate())
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:F6B145E9-8520-4DF3-95ED-1E4BC20E5DFE@.microsoft.com...
> I would like to execute a stored procedure listed below then capture or
> print out the execution time. Lastly rollback the transaction so that
> data
> does not change in the database.
> dbo.usp_Manual_toTraint 65823,'2004-08-01','TA_BB','2004-09-01'
> Please help me with this procedure.
> Thanks,|||Uri Dimant (urid@.iscar.co.il) writes:
> Joe
> See if this helps you
> declare @.dt datetime
> set @.dt =getdate()
> begin tran
> --do something here
> rollback
> select datediff(ss,@.dt,getdate())
That's not good. You need to do:
declare @.dt datetime
set @.dt =getdate()
begin tran
--do something here
select datediff(ss,@.dt,getdate())
rollback
ROLLBACK can take considerable time and should not be measured.
Also, in many situations, ms (milliseconds) is better than ss (seconds).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can't use variables in SQL-Task

Hi,

I have a problem in my SSIS-package: If I try to execute a SQL-statement which uses a variable, it say "syntax-error". Here is how I tried:

1. a) Declared a variable. Name: tableName; Bereich (Sorry, I use the german version, no clue whats that on english versions): Package2; Type: STRING; Value: staticDB.StaticData_provider.dbo.C0123

b) Assigned this variable on SQL-Task->"Parameterzuordnung" as VARCHAR, parametername= NewParameterName

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM @.NewParameterName"

d) Running this results in following error: "Der Parametername wird nicht erkannt" Translation: "Parametername was not recognized."

2. a) see 1.a)

b) see 1.b)

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM ?"

d) Running this results in following error: "Syntaxfehler, Berechtigungsversto? oder anderer allgemeiner Fehler" Translation: "Syntaxerror, permision violation or other common error."

Any ideas?

Regards,

Jan

You cannot use a parameter to substitue in the name of a table. They are typically used as arguments in WHERE clause predicates.

If you want to dynamically set the table name then use an expression. This explains how: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Thanks a lot for that link, now my package is working fine :-).

Best Regards,

Jan Wagner

can't use the index tuning wizard wioth a function??

Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.
Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert
|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>

can't use the index tuning wizard wioth a function??

Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>

can't use the index tuning wizard wioth a function??

Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID = A.ID
what can I do?
thanks.
Jerome.Jéjé wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jéjé wrote:
>> Hi,
>> I receive this error when I try to execute the index tuning wizard:
>> "There are no events in the workload. Either the trace
>> file contained no SQL batch or RPC events or the SQL
>> script contained no SQL queries."
>> I have tried from the query analyzer and from a workload trace file,
>> in the 2 cases I receive the error.
>> My query contain a join to a custom function which return a simple
>> list. if I remove the function, then the index tuning works fine.
>> my query:
>> select * from table1 inner join dbo.MyFunction(@.Param) A on
>> table1.ID = A.ID
>> what can I do?
>> thanks.
>> Jerome.
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>

can't update table owned by dbo with impersonation account

I am using an impersonation account to execute a procedure via
executenonquery in asp.net. If the table owner is dbo I get the following
error in .net:
input string was not in a correct format.
When I step through my code it actually is saying that permission is denied
on the table. I've given the impersonation account full priveledges on this
table and it still doesn't work. However, if I change the owner of the tabl
e
to somone else it works fine. Any suggestions on how to resolve this would
be greatly appreciated.Can you show us your query?
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:E5FEB9AA-D527-4AC5-9B08-03FD45EDDFAF@.microsoft.com...
>I am using an impersonation account to execute a procedure via
> executenonquery in asp.net. If the table owner is dbo I get the following
> error in .net:
> input string was not in a correct format.
> When I step through my code it actually is saying that permission is
> denied
> on the table. I've given the impersonation account full priveledges on
> this
> table and it still doesn't work. However, if I change the owner of the
> table
> to somone else it works fine. Any suggestions on how to resolve this
> would
> be greatly appreciated.|||Here you go.
TRUNCATE TABLE dbo.MYTABLE
INSERT INTO dbo. MYTABLE(THEID,ERRORFLAG,ERRORCODE,CREATI
ONDATE)
VALUES(@.IDValue ,'Y',@.LocalError,GETDATE())
When I run my code in asp.net I get the error "input string is not in the
correct format"
If I use
sp_changeobject 'MYTABLE', 'newowner'
and run it again it works fine?
"Uri Dimant" wrote:

> Can you show us your query?
> "ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
> news:E5FEB9AA-D527-4AC5-9B08-03FD45EDDFAF@.microsoft.com...
>
>