ODBC Create a new Data Souce to SQL not displaying

On a Windows XP PC, why doesn't the Create a new Data
Source to SQL Server window pop up when you select to add
a new system dsn from the ODBC data source
administrator? I have one particular PC where this won't
come up, not all XP PCs though. Anyone see this before!
Thanks.
.Did you try reinstalling MDAC? You'd want to at least check
the MDAC configuration on the PC using component checker.
You can download the latest MDAC as well as component
checker from:
http://msdn.microsoft.com/downloads/list/dataaccess.asp
-Sue
On Mon, 1 Dec 2003 08:06:15 -0800, "Nil"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>On a Windows XP PC, why doesn't the Create a new Data
>Source to SQL Server window pop up when you select to add
>a new system dsn from the ODBC data source
>administrator? I have one particular PC where this won't
>come up, not all XP PCs though. Anyone see this before!
>Thanks.
>.
>

---

ODBC 4,099 byte record limit?

I'm connecting to an Access DB through ODBC on a windows server from a PHP
page. The problem is the text retrieved from the select (nfo) is truncated
at
4,099 bytes everytime.
How to I get around this 4k limit?
I read about SET TEXTSIZE but I don't know how to use it in the script?
The full text is in the DB field so it is being truncated somewhere in the
ODBC.
PHP Script:
--
$connect = odbc_connect("datab_nfo", "", "");
$query = "SELECT nfo FROM Titles where ID=15";
$result = odbc_exec($connect, $query);
while(odbc_fetch_row($result)){
$nfo = odbc_result($result, 1);
print("$nfo\n");
}Look for these values in php.ini
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096
"Repo" wrote:

> I'm connecting to an Access DB through ODBC on a windows server from a PHP
> page. The problem is the text retrieved from the select (nfo) is truncated
> at
> 4,099 bytes everytime.
> How to I get around this 4k limit?
> I read about SET TEXTSIZE but I don't know how to use it in the script?
> The full text is in the DB field so it is being truncated somewhere in the
> ODBC.
> php Script:
> --
> $connect = odbc_connect("datab_nfo", "", "");
> $query = "SELECT nfo FROM Titles where ID=15";
> $result = odbc_exec($connect, $query);
> while(odbc_fetch_row($result)){
> $nfo = odbc_result($result, 1);
> print("$nfo\n");
> }
>
>

ODBC 4,099 byte record limit?

I'm connecting to an Access DB through ODBC on a windows server from a PHP
page. The problem is the text retrieved from the select (nfo) is truncated
at
4,099 bytes everytime.
How to I get around this 4k limit?
I read about SET TEXTSIZE but I don't know how to use it in the script?
The full text is in the DB field so it is being truncated somewhere in the
ODBC.
PHP Script:
$connect = odbc_connect("datab_nfo", "", "");
$query = "SELECT nfo FROM Titles where ID=15";
$result = odbc_exec($connect, $query);
while(odbc_fetch_row($result)){
$nfo = odbc_result($result, 1);
print("$nfo\n");
}
Look for these values in php.ini
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096
"Repo" wrote:

> I'm connecting to an Access DB through ODBC on a windows server from a PHP
> page. The problem is the text retrieved from the select (nfo) is truncated
> at
> 4,099 bytes everytime.
> How to I get around this 4k limit?
> I read about SET TEXTSIZE but I don't know how to use it in the script?
> The full text is in the DB field so it is being truncated somewhere in the
> ODBC.
> PHP Script:
> --
> $connect = odbc_connect("datab_nfo", "", "");
> $query = "SELECT nfo FROM Titles where ID=15";
> $result = odbc_exec($connect, $query);
> while(odbc_fetch_row($result)){
> $nfo = odbc_result($result, 1);
> print("$nfo\n");
> }
>
>

OCI-22053: overflow error

I have a report that runs the following in it's query: " (SELECT cast(d
.ACT_DT as TIMESTAMP)
FROM GPROD.VSL_ACT d
WHERE d .ses_num = gprod.vsl_act.ses_num
AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
AND
ROWNUM <= 1 AND d
.ACT_TYP_TXT = 'DEPS') - CAST(act_dt AS TIMESTAMP) ElpsTime,
(to_date(to_char((SELECT cast(d .ACT_DT as TIMESTAMP)
FROM GPROD.VSL_ACT d
WHERE d .ses_num = gprod.vsl_act.ses_num
AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
AND
ROWNUM <= 1 AND d
.ACT_TYP_TXT = 'DEPS'),'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
hh24:mi:ss')-to_date(to_char(act_dt,'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
hh24:mi:ss')) * 24 * 60 * 60 sum_seconds"
Basically, this takes two date fields, subtracting one from the other. It
works 99% of the time, but for some records, it get #ERROR in this calculated
field. I look at the data, and it makes no sense because I get the error
when the difference could be 10 seconds, or 3 minutes, or whatever. I don't
see any logic to it. The database is an Oracle 10g database. I am using VS
2005 sp1. I have created a new project as per a previous post, but still get
the error. Here is the full error:
The data set â'dsShuntâ' contains a definition for the Field â'SUM_SECONDSâ'.
The data extension returned an error during reading the field.
System.Data.OracleClient.OracleException: OCI-22053: overflow error
Thanks for any help.
Darryl.On Apr 11, 7:14 am, Darryl <Dar...@.discussions.microsoft.com> wrote:
> I have a report that runs the following in it's query: " (SELECT cast(d
> .ACT_DT as TIMESTAMP)
> FROM GPROD.VSL_ACT d
> WHERE d .ses_num = gprod.vsl_act.ses_num
> AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
> AND
> ROWNUM <= 1 AND d
> .ACT_TYP_TXT = 'DEPS') - CAST(act_dt AS TIMESTAMP) ElpsTime,
> (to_date(to_char((SELECT cast(d .ACT_DT as TIMESTAMP)
> FROM GPROD.VSL_ACT d
> WHERE d .ses_num = gprod.vsl_act.ses_num
> AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
> AND
> ROWNUM <= 1 AND d
> .ACT_TYP_TXT = 'DEPS'),'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
> hh24:mi:ss')-to_date(to_char(act_dt,'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
> hh24:mi:ss')) * 24 * 60 * 60 sum_seconds"
> Basically, this takes two date fields, subtracting one from the other. It
> works 99% of the time, but for some records, it get #ERROR in this calculated
> field. I look at the data, and it makes no sense because I get the error
> when the difference could be 10 seconds, or 3 minutes, or whatever. I don't
> see any logic to it. The database is an Oracle 10g database. I am using VS
> 2005 sp1. I have created a new project as per a previous post, but still get
> the error. Here is the full error:
> The data set 'dsShunt' contains a definition for the Field 'SUM_SECONDS'.
> The data extension returned an error during reading the field.
> System.Data.OracleClient.OracleException: OCI-22053: overflow error
> Thanks for any help.
> Darryl.
This link might provide some insight:
http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/185b831ab7a267b0/c0106784db18d83f?lnk=st&q=System.Data.OracleClient.OracleException%3A+OCI-22053%3A+overflow+error&rnum=3#c0106784db18d83f
Regards,
Enrique Martinez
Sr. Software Consultant

Obtaining the time

dear all,
I'm looking for a better version of this stuff. I want to obtain the time of
a datetime field:
DuracionUlt is a datetime field.
SELECT DuracionUlt,
Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
+ ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
+ ':' +
Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
AND datediff(dd,duracionult,getdate()) = 1
This query returns:
2006-03-28 00:00:00.000 = 0:0:0:0
2006-03-28 00:00:42.000 = 0:0:42:0
Any comment/suggestion/thought/advice would be very appreciated,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)SELECT CONVERT(VARCHAR(30),GETDATE(),108)
select {fn extract(minute from getdate())}
Select {fn dayname( GetDate()) }
select {fn CURRENT_DATE()}
select {fn CURRENT_time()}
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
> dear all,
> I'm looking for a better version of this stuff. I want to obtain the time
> of
> a datetime field:
> DuracionUlt is a datetime field.
> SELECT DuracionUlt,
> Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
> + ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
> Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
> + ':' +
> Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
> FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
> AND datediff(dd,duracionult,getdate()) = 1
> This query returns:
> 2006-03-28 00:00:00.000 = 0:0:0:0
> 2006-03-28 00:00:42.000 = 0:0:42:0
> Any comment/suggestion/thought/advice would be very appreciated,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> SELECT CONVERT(VARCHAR(30),GETDATE(),108)
> select {fn extract(minute from getdate())}
> Select {fn dayname( GetDate()) }
> select {fn CURRENT_DATE()}
> select {fn CURRENT_time()}
>
>
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
>
>

obtain the result of dynamic query with openrowset

im running a dynamic query with open rowset in it

pseudocode:

@.CMD=declare @. RETURN SELECT @.RETURN =SUM(X) FROM OPENROWSET(....) SELECT @.RETURN

EXEC @.CMD

This pseudocode dipplay the result of @.return

the problem:

capture @.return into @.myvalue outside the dynamic sql scope

something like

Select @.myvalue=exec(@.cmd)

I don't wanna run on ditributed transaction like this

insert mytable

exec(@.cmd)

thanks,

joey

Well, in 2005, you can direct EXEC to execute on a different server, but I think what you want to do is to use sp_executeSQL:

You will have to configure linked servers, (as well as possibly MSDTC for the servers,) but that would be the direction I would head. If you just want a a single row of values, you can use the following type of syntax:

declare @.name sysname

exec [.\sqlexpress].master.dbo.sp_executesql N'select @.name = @.@.servername ',N'@.name sysname output',@.name output

select @.name

I did it with insert into tableName... and it wanted MSDTC to be on.

|||

hi louis,

i'm using sql server 2000

calling sql2k5

regards,

joey

|||sp_executeSQL existed in 2000 in the same manner.|||

thanks

how can i reuse the output parameter

it is within a loop.

got this error

The variable name '@.RESULT' has already been declared. Variable names must be unique within a query batch or stored procedure.

|||

Just declare it once and set it to NULL before you execute the command...

If I am missing the point, can you post the code?

|||

got it.

i have a declare within @.cmd

thanks

Obtain a list of all rows insert via a select statement

With Table A being the parent table and having an identity column as the
primary key.
With Table B being a child of table A
I need to do an insert based on a select statement (multiple row returned)
in Table A. Then I need to insert multiple child rows in Table B for every
row inserted in Table A.
What is the best strategy? Is there a way to have a list of all the rows
inserted?
I'm open to any suggestion.
Thank you in advance
MartinYou can access those within a trigger, and have it populate a temp table
that the calling batch created, if such a temp table exists, e.g.,
create table t(k int not null identity primary key, d varchar(10));
go
create trigger trg_i_t on t for insert
as
if object_id('tempdb..#t') is not null
insert into #t select k from inserted;
go
-- test
create table #t(k int);
insert into t(d)
select 'a'
union all select 'b'
union all select 'c';
select * from #t;
drop table #t;
-- Output:
k
--
4
5
6
In SQL Server 2005 it will be much easier with the DML with results
enhancements.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:35F4A835-AC48-4896-B535-B550A768CF74@.microsoft.com...
> With Table A being the parent table and having an identity column as the
> primary key.
> With Table B being a child of table A
> I need to do an insert based on a select statement (multiple row returned)
> in Table A. Then I need to insert multiple child rows in Table B for every
> row inserted in Table A.
> What is the best strategy? Is there a way to have a list of all the rows
> inserted?
> I'm open to any suggestion.
> Thank you in advance
> Martin|||On Tue, 22 Mar 2005 15:51:02 -0800, Martin Rajotte wrote:

>With Table A being the parent table and having an identity column as the
>primary key.
>With Table B being a child of table A
>I need to do an insert based on a select statement (multiple row returned)
>in Table A. Then I need to insert multiple child rows in Table B for every
>row inserted in Table A.
>What is the best strategy? Is there a way to have a list of all the rows
>inserted?
>I'm open to any suggestion.
>Thank you in advance
>Martin
Hi Martin,
I'm not entirely sure if I understand you. Do you mean that you have
some data in one or more tables that needs to be inserted in two new
tables, where the second table links to the identity column of the
first?
The proper way to find the identity value of inserted rows after a
multirow insert is to query the table with the natural key as search
argument. That should return you the identity values.
If you need more specific advise, then please code DDL, sample data and
desired results. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Object_name of object in one database while running in another..

It seems for this to work, I need to be running in the context of an
existing database to get the object name or else it returns null.
select object_name(s2.objectid) session_current_procedure_name
from sys.dm_exec_requests req
cross apply sys.dm_exec_sql_text(req.sql_handle) s2
So how can I get the object name of the object irrespective of which
database I run this query from ?
Hassan
Since you can have the same object_id in two different databases, SQL Server
needs to know which database to look in.
If you are running SQL 2005 SP2, you can add a second parameter to
object_name which provides the database_id:
SELECT object_name(object_id, db_id)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
> It seems for this to work, I need to be running in the context of an
> existing database to get the object name or else it returns null.
> select object_name(s2.objectid) session_current_procedure_name
> from sys.dm_exec_requests req
> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
> So how can I get the object name of the object irrespective of which
> database I run this query from ?
>
|||Nice..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
> Hassan
> Since you can have the same object_id in two different databases, SQL
> Server needs to know which database to look in.
> If you are running SQL 2005 SP2, you can add a second parameter to
> object_name which provides the database_id:
> SELECT object_name(object_id, db_id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>
|||I thought so... I've been asking for this feature for years, since Sybase
added it in their System 10. Finally, I made an official request through
Connect, and Microsoft responded to that. So I am happy to take all the
credit for this one!
;-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Nice..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>
|||I am sure there's a LOT more in the sql server world for which you can take
credit!! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I thought so... I've been asking for this feature for years, since Sybase
>added it in their System 10. Finally, I made an official request through
>Connect, and Microsoft responded to that. So I am happy to take all the
>credit for this one!
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>
|||The question is what happens when Kalen retires ? We all know she cant do
this forever..
Kalen have you ever thought about how long you are going to do this ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqohil8ca9sdb@.corp.supernews.com...
>I am sure there's a LOT more in the sql server world for which you can take
>credit!! :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||There are so many terrific people answering questions here now, no one would
even realize it if I disappeared quietly into the sunset...
A Mexican beach sounds nice, right about now...
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eP6syqyTIHA.1212@.TK2MSFTNGP05.phx.gbl...
> The question is what happens when Kalen retires ? We all know she cant do
> this forever..
> Kalen have you ever thought about how long you are going to do this ?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqohil8ca9sdb@.corp.supernews.com...
>
|||It kinda sounds good but it's still nice to see you around here ;)
Ekrem nsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OEe%23TC0TIHA.4360@.TK2MSFTNGP06.phx.gbl...
> There are so many terrific people answering questions here now, no one
> would even realize it if I disappeared quietly into the sunset...
> A Mexican beach sounds nice, right about now...
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eP6syqyTIHA.1212@.TK2MSFTNGP05.phx.gbl...
>

Object_name of object in one database while running in another..

It seems for this to work, I need to be running in the context of an
existing database to get the object name or else it returns null.
select object_name(s2.objectid) session_current_procedure_name
from sys.dm_exec_requests req
cross apply sys.dm_exec_sql_text(req.sql_handle) s2
So how can I get the object name of the object irrespective of which
database I run this query from ?Hassan
Since you can have the same object_id in two different databases, SQL Server
needs to know which database to look in.
If you are running SQL 2005 SP2, you can add a second parameter to
object_name which provides the database_id:
SELECT object_name(object_id, db_id)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
> It seems for this to work, I need to be running in the context of an
> existing database to get the object name or else it returns null.
> select object_name(s2.objectid) session_current_procedure_name
> from sys.dm_exec_requests req
> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
> So how can I get the object name of the object irrespective of which
> database I run this query from ?
>|||Nice..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
> Hassan
> Since you can have the same object_id in two different databases, SQL
> Server needs to know which database to look in.
> If you are running SQL 2005 SP2, you can add a second parameter to
> object_name which provides the database_id:
> SELECT object_name(object_id, db_id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>|||I thought so... I've been asking for this feature for years, since Sybase
added it in their System 10. Finally, I made an official request through
Connect, and Microsoft responded to that. So I am happy to take all the
credit for this one!
;-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Nice..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>|||I am sure there's a LOT more in the sql server world for which you can take
credit!! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I thought so... I've been asking for this feature for years, since Sybase
>added it in their System 10. Finally, I made an official request through
>Connect, and Microsoft responded to that. So I am happy to take all the
>credit for this one!
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>|||The question is what happens when Kalen retires ? We all know she cant do
this forever..
Kalen have you ever thought about how long you are going to do this ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqohil8ca9sdb@.corp.supernews.com...
>I am sure there's a LOT more in the sql server world for which you can take
>credit!! :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>

Object_name of object in one database while running in another..

It seems for this to work, I need to be running in the context of an
existing database to get the object name or else it returns null.
select object_name(s2.objectid) session_current_procedure_name
from sys.dm_exec_requests req
cross apply sys.dm_exec_sql_text(req.sql_handle) s2
So how can I get the object name of the object irrespective of which
database I run this query from ?Hassan
Since you can have the same object_id in two different databases, SQL Server
needs to know which database to look in.
If you are running SQL 2005 SP2, you can add a second parameter to
object_name which provides the database_id:
SELECT object_name(object_id, db_id)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
> It seems for this to work, I need to be running in the context of an
> existing database to get the object name or else it returns null.
> select object_name(s2.objectid) session_current_procedure_name
> from sys.dm_exec_requests req
> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
> So how can I get the object name of the object irrespective of which
> database I run this query from ?
>|||Nice..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
> Hassan
> Since you can have the same object_id in two different databases, SQL
> Server needs to know which database to look in.
> If you are running SQL 2005 SP2, you can add a second parameter to
> object_name which provides the database_id:
> SELECT object_name(object_id, db_id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>|||I thought so... I've been asking for this feature for years, since Sybase
added it in their System 10. Finally, I made an official request through
Connect, and Microsoft responded to that. So I am happy to take all the
credit for this one!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Nice..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>>
>|||I am sure there's a LOT more in the sql server world for which you can take
credit!! :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I thought so... I've been asking for this feature for years, since Sybase
>added it in their System 10. Finally, I made an official request through
>Connect, and Microsoft responded to that. So I am happy to take all the
>credit for this one!
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Nice..
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>>
>>
>|||The question is what happens when Kalen retires ? We all know she cant do
this forever..
Kalen have you ever thought about how long you are going to do this ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqohil8ca9sdb@.corp.supernews.com...
>I am sure there's a LOT more in the sql server world for which you can take
>credit!! :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I thought so... I've been asking for this feature for years, since Sybase
>>added it in their System 10. Finally, I made an official request through
>>Connect, and Microsoft responded to that. So I am happy to take all the
>>credit for this one!
>> ;-)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Nice..
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>>
>>
>>
>|||There are so many terrific people answering questions here now, no one would
even realize it if I disappeared quietly into the sunset...
A Mexican beach sounds nice, right about now...
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eP6syqyTIHA.1212@.TK2MSFTNGP05.phx.gbl...
> The question is what happens when Kalen retires ? We all know she cant do
> this forever..
> Kalen have you ever thought about how long you are going to do this ?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqohil8ca9sdb@.corp.supernews.com...
>>I am sure there's a LOT more in the sql server world for which you can
>>take credit!! :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I thought so... I've been asking for this feature for years, since Sybase
>>added it in their System 10. Finally, I made an official request through
>>Connect, and Microsoft responded to that. So I am happy to take all the
>>credit for this one!
>> ;-)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Nice..
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>>
>>
>>
>>
>|||It kinda sounds good but it's still nice to see you around here ;)
--
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OEe%23TC0TIHA.4360@.TK2MSFTNGP06.phx.gbl...
> There are so many terrific people answering questions here now, no one
> would even realize it if I disappeared quietly into the sunset...
> A Mexican beach sounds nice, right about now...
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eP6syqyTIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> The question is what happens when Kalen retires ? We all know she cant do
>> this forever..
>> Kalen have you ever thought about how long you are going to do this ?
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nqohil8ca9sdb@.corp.supernews.com...
>>I am sure there's a LOT more in the sql server world for which you can
>>take credit!! :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I thought so... I've been asking for this feature for years, since
>>Sybase added it in their System 10. Finally, I made an official request
>>through Connect, and Microsoft responded to that. So I am happy to take
>>all the credit for this one!
>> ;-)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Nice..
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>>> It seems for this to work, I need to be running in the context of an
>>> existing database to get the object name or else it returns null.
>>>
>>> select object_name(s2.objectid) session_current_procedure_name
>>> from sys.dm_exec_requests req
>>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>>>
>>> So how can I get the object name of the object irrespective of which
>>> database I run this query from ?
>>>
>>
>>
>>
>>
>

Object_ID and db_id functions

I am using sql functions with some DMVs as follows
SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
NULL)
and getting the following error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
The statement succed if I use variables as follows :
Declare @.test int
Declare @.test2 int
set @.test = db_id()
set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
NULL)
What is strange is when I executed the first one against AdventureWorks it
works correctly without the need to use variables. It sends back info for all
indexes ?......
So am I missing any config param sonewhere
ThanksThis is probably because the database which give you the error isn't in compatibility mode 90.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>I am using sql functions with some DMVs as follows
> SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> NULL)
> and getting the following error :
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '('.
> The statement succed if I use variables as follows :
> Declare @.test int
> Declare @.test2 int
> set @.test = db_id()
> set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> NULL)
> What is strange is when I executed the first one against AdventureWorks it
> works correctly without the need to use variables. It sends back info for all
> indexes ?......
> So am I missing any config param sonewhere
> Thanks|||Exact, I changed compatibilityto 90and it worked,thanks a lot
"Tibor Karaszi" wrote:
> This is probably because the database which give you the error isn't in compatibility mode 90.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
> >I am using sql functions with some DMVs as follows
> >
> > SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> > NULL)
> >
> > and getting the following error :
> > Msg 102, Level 15, State 1, Line 1
> > Incorrect syntax near '('.
> >
> > The statement succed if I use variables as follows :
> >
> > Declare @.test int
> > Declare @.test2 int
> > set @.test = db_id()
> > set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> > SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> > NULL)
> >
> > What is strange is when I executed the first one against AdventureWorks it
> > works correctly without the need to use variables. It sends back info for all
> > indexes ?......
> >
> > So am I missing any config param sonewhere
> >
> > Thanks
>