Objects related to DB: return

Showing posts with label return. Show all posts

Friday, March 30, 2012

odbc error

Getting the following error:
SQL error. Stmt #: 1568 Error Position: 0 Return: 8602 -
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: Errors occurred] (SQLSTATE
01000) 7312
Scenario: application/database sitting on win nt4 server
running sql7 linked to a Windows 2000 server running sql
2000. Trigger on serverA trying to update table on
ServerB via linked server.Turn on trace flag 7300 to try to get better information.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Friday, March 9, 2012

OCIEnvNlsCreate failed with return code -1

Hi. We're experiencing the exact same problem described in this post: http://tinyurl.com/ynqv7o
We've tried the 11 steps described there and it didn't fix the problem -- just as it didn't fix it for that person.

Help!! Thank you.Anyone have any thoughts on what's causing this problem? Your help is much appreciated

Wednesday, March 7, 2012

obtaining return values from DBCC INDEXDEFRAG in a sp

Hi All,
I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number
of indexes.
I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages Removed). However I cannot seem to find a way to do this
Can anyone help please?
Thanks in advance
DanCreate a table with the same structure as the DBCC returns and then use below technique:
INSERT theTable
EXEC('DBCC...')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number of indexes.
> I wish to then manipulate the values from the result set (Pages Scanned, Pages Moved, Pages
> Removed). However I cannot seem to find a way to do this
> Can anyone help please?
> Thanks in advance
> Dan
>|||You can't do that as the insert/exec creates a user transaction and
INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
the results from INDEXDEFRAG except using an external text file.
Dan - what do you want to do with the values, out of interest?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Create a table with the same structure as the DBCC returns and then use
below technique:
> INSERT theTable
> EXEC('DBCC...')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> >
> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
number of indexes.
> >
> > I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages
> > Removed). However I cannot seem to find a way to do this
> >
> > Can anyone help please?
> >
> > Thanks in advance
> >
> > Dan
> >
>|||Ah, thanks Paul. I didn't consider the transaction aspect of it...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
>> Create a table with the same structure as the DBCC returns and then use
> below technique:
>> INSERT theTable
>> EXEC('DBCC...')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
>> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
>> > Hi All,
>> >
>> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> number of indexes.
>> >
>> > I wish to then manipulate the values from the result set (Pages Scanned,
> Pages Moved, Pages
>> > Removed). However I cannot seem to find a way to do this
>> >
>> > Can anyone help please?
>> >
>> > Thanks in advance
>> >
>> > Dan
>> >
>>
>|||Hi Paul,
I want to put them into a "User Friendly" report, that basically lists the
time, index defragged and the pages scanned, moved, etc.
Regards
Dan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to
> capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
>> Create a table with the same structure as the DBCC returns and then use
> below technique:
>> INSERT theTable
>> EXEC('DBCC...')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
>> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
>> > Hi All,
>> >
>> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> number of indexes.
>> >
>> > I wish to then manipulate the values from the result set (Pages
>> > Scanned,
> Pages Moved, Pages
>> > Removed). However I cannot seem to find a way to do this
>> >
>> > Can anyone help please?
>> >
>> > Thanks in advance
>> >
>> > Dan
>> >
>>
>|||I'm afraid the only way to do it is to output the results to a flat file and
then post-process.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uE0WCTFrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I want to put them into a "User Friendly" report, that basically lists the
> time, index defragged and the pages scanned, moved, etc.
> Regards
> Dan
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> > You can't do that as the insert/exec creates a user transaction and
> > INDEXDEFRAG cannot run inside a user transaction. There's no way to
> > capture
> > the results from INDEXDEFRAG except using an external text file.
> >
> > Dan - what do you want to do with the values, out of interest?
> >
> > Regards
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> >> Create a table with the same structure as the DBCC returns and then use
> > below technique:
> >>
> >> INSERT theTable
> >> EXEC('DBCC...')
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> >> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> >> > Hi All,
> >> >
> >> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> > number of indexes.
> >> >
> >> > I wish to then manipulate the values from the result set (Pages
> >> > Scanned,
> > Pages Moved, Pages
> >> > Removed). However I cannot seem to find a way to do this
> >> >
> >> > Can anyone help please?
> >> >
> >> > Thanks in advance
> >> >
> >> > Dan
> >> >
> >>
> >>
> >
> >
>

obtaining return values from DBCC INDEXDEFRAG in a sp

Obtaining count for identity

Is there a way to query a SQL server such that it will return the
current counter value of a field that increments automatically (an
identity field)? Using a MAX query might not necessarily give you the
correct information because it is possible that the high value records
were deleted. I checked the system tables and none of them appear to
have what I need. I suppose it would be possible to maintain a table
that lists all the incrementing columns, but ideally there would be a
way already built into SQL Server.

Thanks.On 15 Jun 2005 14:00:46 -0700, hartley_aaron@.hotmail.com wrote:

>Is there a way to query a SQL server such that it will return the
>current counter value of a field that increments automatically (an
>identity field)? Using a MAX query might not necessarily give you the
>correct information because it is possible that the high value records
>were deleted. I checked the system tables and none of them appear to
>have what I need. I suppose it would be possible to maintain a table
>that lists all the incrementing columns, but ideally there would be a
>way already built into SQL Server.
>Thanks.

Hi hartley_aaron,

Look up IDENT_CURRENT in Books Online.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Look up IDENT_CURRENT or @.@.IDENTITY in Books Online.

HTH,
Stu|||It worked great.

Thank you both!

Friday, February 24, 2012

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

Monday, February 20, 2012

OBJECTPROPERTY problem

Why does the first query work properly and return 1 for the IsMsShipped
column when the second and third query do not?
use Northwind
go
SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
N'IsMSShipped') as IsMSShipped
FROM sysobjects
WHERE name='dt_adduserobject'
use Model
go
SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
N'IsMSShipped') as IsMSShipped
FROM Northwind..sysobjects
WHERE name='dt_adduserobject'
SELECT convert(varchar,name) as ObjectName, id,
OBJECTPROPERTY(1157579162, N'IsMSShipped') as IsMSShipped
FROM Northwind..sysobjects
WHERE name='dt_adduserobject'
'dt_adduserobject' is just an example of an object that I'd like to
exclude from my results using the IsMSShipped property. I'm querying
sysobjects for every database from a stored proc that uses dynamic sql
to supply the database name. I need to exclude system objects without
resorting to sysobjects.name not like 'dt%'. Unfortunately, this
OBJECTPROPERTY function ignores the database in my FROM clause. Any
ideas?Because OBJECTPROPERTY is resolved to the current datanase. So, it is
trying to find a local object in Model, with whatever id is assigned to
dt_adduserobject in the Northwind database.
<drink.the.koolaid@.gmail.com> wrote in message
news:1140185167.395442.186560@.f14g2000cwb.googlegroups.com...
> Why does the first query work properly and return 1 for the IsMsShipped
> column when the second and third query do not?
> use Northwind
> go
> SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
> N'IsMSShipped') as IsMSShipped
> FROM sysobjects
> WHERE name='dt_adduserobject'
>
> use Model
> go
> SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
> N'IsMSShipped') as IsMSShipped
> FROM Northwind..sysobjects
> WHERE name='dt_adduserobject'
> SELECT convert(varchar,name) as ObjectName, id,
> OBJECTPROPERTY(1157579162, N'IsMSShipped') as IsMSShipped
> FROM Northwind..sysobjects
> WHERE name='dt_adduserobject'
> 'dt_adduserobject' is just an example of an object that I'd like to
> exclude from my results using the IsMSShipped property. I'm querying
> sysobjects for every database from a stored proc that uses dynamic sql
> to supply the database name. I need to exclude system objects without
> resorting to sysobjects.name not like 'dt%'. Unfortunately, this
> OBJECTPROPERTY function ignores the database in my FROM clause. Any
> ideas?
>|||Read under the Remarks of the BOL (OBJECTPROPERTY) what the cause is:
"The Database Engine assumes that object_id is in the current database
context."
HTH, Jens Suessmeyer.|||Is there any way to change the "current database context" from within a
procedure? I think I've tried "USE Northwind GO" without
success...
Can I somehow create my own OBJECTPROPERTY function that is not
sensitive to the current database context?
Can I use some other method to determine if an object is a "system
object" that doesn't depend on the current database context?
Thanks for your help.|||> Is there any way to change the "current database context" from within a
> procedure?
No. Only option would be to construct the query in a string and use dynamic
SQL. This has several
drawbacks, see the articles at www.sommarskog.se.

> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
I don't think that is possible, taking into account the limitations of what
you can do inside a
function (no dynamic SQL etc), and the fact that a function is database spec
ific, not global.

> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
You can check against some undocumented status column in the sysobjects tabl
e. Do some searching etc
and you will surely find out how to do it. But be aware that this code will
*not* port to 2005 as
the system tables has been reworked and undocumented stuff are not ported to
the compatibility
views. In 2005, you have much better ways of doing this though the new catal
og views.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<drink.the.koolaid@.gmail.com> wrote in message
news:1140199085.871112.237720@.g14g2000cwa.googlegroups.com...
> Is there any way to change the "current database context" from within a
> procedure? I think I've tried "USE Northwind GO" without
> success...
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
> Thanks for your help.
>|||I haven't tried it but you can probably use dynamic sql (sp_executesql to be
exact) and an output parameter to get what you want. You can't put a GO but
I think you can do something like this:
USE YourOtherDB
SET @.X = OBJECTPROPERTY()
wrapped in dynamic sql where @.x is the putput param.
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
Andrew J. Kelly SQL MVP
<drink.the.koolaid@.gmail.com> wrote in message
news:1140199085.871112.237720@.g14g2000cwa.googlegroups.com...
> Is there any way to change the "current database context" from within a
> procedure? I think I've tried "USE Northwind GO" without
> success...
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
> Thanks for your help.
>|||I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
I'm also having trouble locating an informative post about
sysobjects.status... :(|||> I'm also having trouble locating an informative post about
> sysobjects.status... :(
Exactly what is it that you need to know? The type column in sysobjects is v
ery informative, for
example. And it id documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<drink.the.koolaid@.gmail.com> wrote in message
news:1140274997.951001.77840@.g43g2000cwa.googlegroups.com...
>I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
> I'm also having trouble locating an informative post about
> sysobjects.status... :(
>|||You have to put the USE and the code you want executed in that db scope all
in the same dynamic sql batch.
DECLARE @.X NVARCHAR(500)
SET @.X = 'USE NORTHWIND
SELECT convert(varchar,[name]) as [ObjectName], [id], OBJECTPROPERTY([id],
N''IsMSShipped'') as [IsMSShipped]
FROM sysobjects
WHERE name=''Customers'''
EXEC(@.x)
Andrew J. Kelly SQL MVP
<drink.the.koolaid@.gmail.com> wrote in message
news:1140274997.951001.77840@.g43g2000cwa.googlegroups.com...
>I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
> I'm also having trouble locating an informative post about
> sysobjects.status... :(
>|||Thank you! That work's great.

Friday, March 30, 2012

odbc error

Friday, March 9, 2012

OCIEnvNlsCreate failed with return code -1

Wednesday, March 7, 2012

obtaining return values from DBCC INDEXDEFRAG in a sp

obtaining return values from DBCC INDEXDEFRAG in a sp

Obtaining count for identity

Friday, February 24, 2012

obtain the result of dynamic query with openrowset

Monday, February 20, 2012

OBJECTPROPERTY problem

Objects related to DB

Blog Archive

About Me