DB2OLEDB Query Parameters in OLE DB Sources

Hi

I'm using Microsoft DB2 OLE DB Driver to access a DB2 database, and I have a problem when I create an OLE DB Source using a parameterized query. Everytime I push the "Parameters" button, I get this error:

TITLE: Microsoft Visual Studio

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

ADDITIONAL INFORMATION:

El proveedor no puede derivar la información del parámetro, no se llamó a SetParameterInfo. (Microsoft DB2 OLE DB Provider)

I would really appreciatte any help.

Thanks.

Well, the message looks pretty clear to me. The provider you are using does not support an interface used to derive parameters from queries so parametrized queries cannot be used in that source adapter.

The workaround is to use "Sql command from variable" access mode and build your query in a single variable.

Thanks,

Bob

|||

Hi

First of all, thanks for your answer. I know I can use the "Sql command from variable" workaround, for that matter I could also use a DataReader Source instead of an OLE DB Source and use an expression to build the query. The thing is, I was wondering if there was a more recent version of the driver that would allow me to build parametrized queries the same way I build them when accessing a MS SQL Server database. I know I should have been more specific when I posted my question, for that I apologize.

Right now I'm evaluating Microsoft's and IBM's DB2 providers, and both have certain limitations. For instance when I use MS provider and the code page of the DB2 database is improperly configured, the development studio simply vanishes when I try to create a Data Flow. I know is not that big of a problem, I just need to be more careful when setting up the connections properties, but still it took me a while to realize what was going on, probably because I have little experience with MS SQL Server.

My point is, I was hoping to access DB2 the same way I access any other database, instead of having to change the way I do things depending on the provider used to set up the connection, but I supposse this is the way things are.

Thanks again for your answer.

Cheers.

|||

Hi

It looks like it is possible to use parametrized queries. I just had to change the value of "Derive parameters" to true in the "All" tab.

Thanks again.

sql

Db2, Nolock

Hi guys,

I am a new user for DB2.

In DB2 V8, i have a table with some no of columns.

table test
col1
col2
col3
col4

Now i want to issue a query like this.

select col1 from test with(NOLOCK) where col1 = <some value>

when i am executing this query on that time i am getting a error message like this...

SQL0158N The number of columns specified for "MT.TEST" is not the
same as the number of columns in the result table. SQLSTATE=42811

Anyone can help me?

i tried to find out.

I did the same thing with SQL server 2000.
I am not facing any problem there.

Thanks & Regards,
MuthuYou should post it on DB2 forum and this is related to SQL Server.

DB2 query in an expression (Slow ?)

I am using DB2 as my datasource.
I have a query that takes 10 sec.
I needed to add report parameters to this query, so I converted it
into an expression. However, now the report is taking longer than the
original query - just because I converted the query into an
expression...
Has anyone seen this? What would be the work around for this?
ThanksJust to be sure. When you added parameters did you add it to the query or
did you do a filter. If you are using a filter then it brings over all the
data before applying the filter. Also, it is very unlikely that you needed
to change it into an expression.
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408181551.5abb1d88@.posting.google.com...
> I am using DB2 as my datasource.
> I have a query that takes 10 sec.
> I needed to add report parameters to this query, so I converted it
> into an expression. However, now the report is taking longer than the
> original query - just because I converted the query into an
> expression...
> Has anyone seen this? What would be the work around for this?
> Thanks|||I am using it in a filter... so I guess you are right. It brings the
whole data over.. and it's slow.
Now I am doing as below:
select col1, col2 from tab1 where col3=?
But I want to be able to change the filter from col3=? to col4=?
How do I do that?
Thanks in advance.|||I'm confused. Is the issue that you want to dynamically craete the sql
string. Sometimes having it be col3 and sometimes col4.
You can put an expression in the generic designer. Or, you can have a
selection of all for the parameters where when they select all the parameter
value is % (is the wildcard for db2 a *, if so then use a * instead of a %).
Then change it to use like.
select col1, col2 from tab1 where col3 like ? and col4 like ?
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408201037.59d458ce@.posting.google.com...
> I am using it in a filter... so I guess you are right. It brings the
> whole data over.. and it's slow.
> Now I am doing as below:
> select col1, col2 from tab1 where col3=?
> But I want to be able to change the filter from col3=? to col4=?
> How do I do that?
> Thanks in advance.|||Thanks for the reply.
isn't "Like" only for string data types?
My col3 and col4 are numeric columns...
what would I do there?|||OK, then what you need to do is have a dynamic query. This means you make
the query an expression. Use the generic query designer and put in the
expression. I suggest first assigning the expression to a textbox to debug
what you are doing, make sure you get the SQL string you want. Here is an
example posted yesterday by Donovan Smith of MS:
="select Col1, Col2 from Table" & iif (Parameters!FilterByDate.Value ==true, " where Date > '" & Parameters!FilterDate.Value & "'", "")
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408201905.447fb7d6@.posting.google.com...
> Thanks for the reply.
> isn't "Like" only for string data types?
> My col3 and col4 are numeric columns...
> what would I do there?|||Bruce,
Expressions are bad performance!! Which is why I started this posting.
Here is what I am doing now. Hopefully this will work good:
select col 1, col2 from table1
where
((1 = ? AND col4 = ?) OR (1=? AND col5 = ?) OR (1=? AND col6 = ?))
This way I can say "1" / "0" to any parameter that I want to pass...
It doubles the number of my internal parameters... but it seems to work...
What do you think?|||No, expression do not give bad performance, filters give bad performance
because filters bring over all the data prior to filtering. It is two
different things. However, I only use expressions as a last alternative and
what you have below is definitely a good way to solve the problem.
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408211025.46ab1122@.posting.google.com...
> Bruce,
> Expressions are bad performance!! Which is why I started this posting.
> Here is what I am doing now. Hopefully this will work good:
>
> select col 1, col2 from table1
> where
> ((1 = ? AND col4 = ?) OR (1=? AND col5 = ?) OR (1=? AND col6 = ?))
>
> This way I can say "1" / "0" to any parameter that I want to pass...
> It doubles the number of my internal parameters... but it seems to work...
> What do you think?

DB2 lookup with parameters

Hi,

We have a package using a lookup query on DB2 to validate data from a file. Everything works fine, except for the lookup query that has to cache about 1,5 million rows.

Now I would like to specify parameters to that query to minimize the data being cached. I tried using parameters in the query, but I get an error:

"Provider cannot derive parameter information and SetParameterInfo has not been called."

Anyone had that problem?

I am using Microsoft's OLE DB Provider for DB2.

Thanks

Can you elaborate a litle more about how you are planning to use parameter to reduce the number of rows?

To reduce the number of rows, just add a where clause in the query (do not select a table from the dropdown list); also limit the number of columns in the select part of the query. 1.5 million rows should not be a problem; unless there is a limited amount of RAM and/or the row size of the lookup query is too big.

|||

One of the lookup column is the transaction date/time (transaction_ts).

I want to cache only the rows where transaction_ts is between the min and max values of the transaction_ts in the text file.

There are only about 15000 rows in the text file and 1,5 million in the lookup. Currently the lookup operation represents approx. 97% of the total execution time so I'm trying to see if it could be optimized...

Thanks

|||

Fleo,

Using paramters in the Lookup is not what you want. By using paramters in the lookup, the component would be using partial cache; which translate on issuing a query against the lookup table for each row passing trhough; hence degradating performance even more. A work around could be to build a view (with a filter in transaction_ts) using Execute SQl task and then configure the lookup component to use the view instead.

BTW, can you provide more details on the current settings of the lookup (query, data type of each column in the query, cache mode, etc)? I still think 1.5 million rows is not that much...

|||

Hi Rafael,

Thanks for the suggestion, it's a good idea. I could also build a view which is using fields in a "configuration" table and update those fields.

I know 1.5 million rows is not that much. Width of the row is int + datetime + char(19) = 31 bytes.

Can't SSIS use full cache even with parameters? I mean the values of the parameters do not depend upon the value of the current row. SSIS would be retrieving the same data every time...

|||

Fleo,

No, you cannot use parameter and full cache at the same time. If you think about it, a parameter gives the avility of running a new query for each row in the data pipeline; so even with full cache, the next row will require a new query to be issued; which would not make too much sense. A solution though wuld be to make the query property of the LK transformation 'expressionable' so the querye gets set dinamically at run time (1 query for all rows in a single package execution though). I Think the SSIS team has already thought about it, but I don't know when they could actualy make it available.

|||

Rafael Salas wrote:

If you think about it, a parameter gives the avility of running a new query for each row in the data pipeline; so even with full cache, the next row will require a new query to be issued; which would not make too much sense.

I'm not sure I get it. Or maybe I didn't explain it well...

Right now, without parameters and full caching, the data in cache corresponds to something like "SELECT col1, col2, col3 FROM Table1 WHERE account like '123%'.

What I would like to have in cache is something similar to "SELECT col1,... FROM Table1 WHERE ... AND TRANSACTION_TS BETWEEN ? AND ?. The 2 parameters would correspond to the MIN and MAX values of TRANSACTION_TS for the rows in the data pipeline.

The parameters are not set with values extracted from the current row so this query could be runned once.

I guess I don't understand the way SSIS handles lookups.

Thanks

|||fleo,
You can't cache records if the lookup cache depends on SQL to be dynamically built via the data flow stream. Lookups using full-cache are built BEFORE the data flow executes.|||

Ok...

The full cache is built before the execution while partial cache is issuing a query for each row in the data pipe.

The SQL for the lookup query cannot be configured.

So I conclude that what I'm trying to do is technologically impossible with the current implementation of lookups (?).

Thanks for your help guys

|||No, you can use lookups, just with no caching. Partial caching works by caching results, and if no match is found, then the SQL is executed against the table again to look for a match.|||

fleo wrote:

Ok...

The full cache is built before the execution while partial cache is issuing a query for each row in the data pipe.

The SQL for the lookup query cannot be configured.

So I conclude that what I'm trying to do is technologically impossible with the current implementation of lookups (?).

Thanks for your help guys

Don't forget you still have the create/drop view option....it is a intrusive aproach but it should do the trick.

db2 linked server

I'm trying to get the syntax for building a query in sql server that accesses a linked server in db2, using a variety of paramaters and variables.
Thanksmoving thread to sql server forum

DB2 Connection Custom Assembly

Have a small problem. I have written a custom assembly to query a db2
database through a dsn datasource. It works fine in Report Designer but
shows the #error message in the fields on the report once it is published.
Following is my rssrvpolicy.config entry:
<CodeGroup
class="UnionCodeGroup"
version="1.0.0.0"
PermissionSetName="FullTrust"
Name="DB2Link"
Description="This assembly connects to the DB2 database server and
retrieves information">
<IMembershipCondition
class="UrlMembershipCondition"
version="1.0.0.0"
Url="C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
/>
</CodeGroup>
I have also added the following code to my Class Library prior to compiling
and deploying:
Dim Permission As New
Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
Permission.Assert()
but to no avail. Any help would be greatly appreciated. BTW, the ODBC
connection information is all housed within the library. It takes a string
value passed from SRS and returns a single string value. Nothing fancy...
Do I need to create an access group to the DSN File?
Thanks,Asserting the ODBC Permission might not be sufficient. You may want to try
asserting full trust in the custom assembly:
[PermissionSet(SecurityAction.Assert, Unrestricted=true)]
public foo()
{
// Your code:
// open database connection
// ...
}
See also this thread related to connecting to Oracle from a custom assembly:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=3cd1a1de-15d4-41fe-bc9b-3c9df93ac2ee&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JamesH" <JamesH@.discussions.microsoft.com> wrote in message
news:84982C3D-32DD-4D3B-B46C-2766810046F0@.microsoft.com...
> Have a small problem. I have written a custom assembly to query a db2
> database through a dsn datasource. It works fine in Report Designer but
> shows the #error message in the fields on the report once it is published.
> Following is my rssrvpolicy.config entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1.0.0.0"
> PermissionSetName="FullTrust"
> Name="DB2Link"
> Description="This assembly connects to the DB2 database server and
> retrieves information">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1.0.0.0"
> Url="C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
> />
> </CodeGroup>
> I have also added the following code to my Class Library prior to
compiling
> and deploying:
> Dim Permission As New
> Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
> Permission.Assert()
> but to no avail. Any help would be greatly appreciated. BTW, the ODBC
> connection information is all housed within the library. It takes a
string
> value passed from SRS and returns a single string value. Nothing fancy...
> Do I need to create an access group to the DSN File?
> Thanks,|||Thanks, I will look at it, I left the code on another computer and won't be
able to try it until tomorrow, will let you know.
"JamesH" wrote:
> Have a small problem. I have written a custom assembly to query a db2
> database through a dsn datasource. It works fine in Report Designer but
> shows the #error message in the fields on the report once it is published.
> Following is my rssrvpolicy.config entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1.0.0.0"
> PermissionSetName="FullTrust"
> Name="DB2Link"
> Description="This assembly connects to the DB2 database server and
> retrieves information">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1.0.0.0"
> Url="C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
> />
> </CodeGroup>
> I have also added the following code to my Class Library prior to compiling
> and deploying:
> Dim Permission As New
> Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
> Permission.Assert()
> but to no avail. Any help would be greatly appreciated. BTW, the ODBC
> connection information is all housed within the library. It takes a string
> value passed from SRS and returns a single string value. Nothing fancy...
> Do I need to create an access group to the DSN File?
> Thanks,|||Robert, I've tried about everything I can think of: I even tried a sample
that has worked for others using an assembly to read a text file but to no
avail. Sorry for all of the code below but I'm at a loss. I've also set the
version to be 1.0.0.0.0 in my assembly and added the : <Assembly:
AllowPartiallyTrustedCallers()> although I haven't setup strong naming. If
you see anything that I've messed up, please let me know...I'm stumped.
This is my Class Code:
Imports System.IO
Imports System.Security.Permissions
Public Class ReadResultsFile
<FileIOPermissionAttribute(SecurityAction.Assert,
Read:="C:\JHHTST2.txt")> _
Public Shared Function GetResults() As String
Dim reader As StreamReader = New StreamReader("c:\JHHTST2.txt")
Dim hello As String = reader.ReadToEnd()
reader.Close()
Return hello
End Function
End Class
This as the PermissionSet Code:
<PermissionSet
class="NamedPermissionSet"
version="1"
Name="ReadObjectFilePermissionSet"
Description="A special permission set that grants read access to my hello
file.">
<IPermission
class="FileIOPermission"
version="1"
Read="C:\JHHTST2.txt"
/>
<IPermission
class="SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
</PermissionSet>
This is my Codegroup:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="ReadObjectFilePermissionSet"
Name="ReadScriptFile"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReadScriptFile.dll"
/>
</CodeGroup>
Thanks,
JamesH.|||Make sure the the CodeGroup section you added is right below the
Report_Expressions_Default_Permissions CodeGroup. Position in the file
does have an effect.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||I added it in there, no change.
JamesH.
"Donovan R. Smith [MS]" wrote:
> Make sure the the CodeGroup section you added is right below the
> Report_Expressions_Default_Permissions CodeGroup. Position in the file
> does have an effect.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Never Mind, got it working today by adding a linked server to SQL and then
using the SQLClient connection in the assembly, worked like a champ... Is
there anybody that has used ODBC with Custom assemblies?
JamesH.
"JamesH" wrote:
> This weekend I rebuilt everything at least 10 times and then built a virtual
> pc image and reloaded everything to no avail. I've added Strong_name to the
> .dlls and still no luck. I'm not seeing any errors in the logs at all, just
> the #error message on my report screen. I've tried deleting the reports,
> re-adding the custom assembly each time and then re-deploying along with
> re-copying the .dll. I even tried to get it to work with another .dll that
> still doesn't work. Any help now would be greatly appreciated on just
> getting a single custom assembly working. Are there any walk-throughs
> (cradle to grave) at all by MS?
> THanks,
> "JamesH" wrote:
> > I added it in there, no change.
> >
> > JamesH.
> >
> > "Donovan R. Smith [MS]" wrote:
> >
> > > Make sure the the CodeGroup section you added is right below the
> > > Report_Expressions_Default_Permissions CodeGroup. Position in the file
> > > does have an effect.
> > >
> > > --
> > > Thanks.
> > >
> > > Donovan R. Smith
> > > Software Test Lead
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >sql

DB2 Connect and SSIS | Error in OLE DB Source Task

Hi All,

I am trying to connect to DB2 database via OLE DB connection manager in SSIS. But when I enter the SQL Query and press OK it gives following error

"Error at Data Flow Task - Header Load [OlE DB Source - Header_Load[1]]": An OLE DB Error has occured. Error Code: 0x80040E21

Additional Information:

Exception from HRESULT: 0xC0202009(Microsoft.SqlServer.DTSPipelineWrap)"

I followed following steps: -

1. I created OLE DB provider and tested the connection, it was successful(with give username and password)

2. Created query in Build query as following and tried executing it. It worked! Query used was

SELECT SRC_ID, ORG_ID FROM DB123.DEAL_HEADER

3. But when, in OLE DB Source Provider Task, when I press preview, It thorws the above error!

Kindly let me know, Because I am stuck at that point.

Thanks

Sid

What happens if you don't preview the results? Just run the package normally after building the query.|||In that case; when I press "OK" in OLE DB Source Task, the same Error appears. In short, I am not able to save the task and move futher with my implementation.|||What driver are you using to connect to DB2? IBM's DB2Connect?|||Yes I am using IBM's DB2 Connect|||

sidzone123 wrote:

Yes I am using IBM's DB2 Connect

Well, if you are on SQL Server Developer or Enterprise edition, you can download the Microsoft OLE DB for DB2 driver. That works really well for me.

Never-the-less, you can try some of the techniques in here to see if they'll help your situation, even though it doesn't deal with DB2 directly:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=142282&SiteID=1|||

Hi Phil,

I am using SQL Server Standard Edition.

However, I tried all methods suggested by the link suggested by you.

But unfortunately nothing work!

Thanks

Sid

|||

Hi,

I found the workaround for this!

1) In OLE DB Source, Open the Advance Editor and in the Custom property, set the Access Mode to "OpenRowset"

2) In the OpenRowset property, write the table that you want to access i.e. say "CZ123"."DEAL_HEADER"

Thats it and press OK!

To my surpise it worked great. I was able to connect to DB2 and transfer the data.

However I am not sure about why I was getting the errors that I mentioned earlier and why the above solution worked. Still trying to find the aswer to this.

Hope the same works for all.

Thanks

Sid

DB_Name + Object_Name

Is there a way to list an object name in a query where it's in another
database?
What I would like to do is have a select statement that lists processes
running (list locks actually) and have a column in the query for dbname
+ object name.
I'm not sure if you can do this in the select list though cause
Object_Name(ID) is current db specific. Is there a way to get
Object_Name() to look in a different database for the object's name
such as SELECT DB_Name(ID) + '..' + Object_Name(ID) AS DBAndObjectCan't you join on otherdb..sysobjects?
"Paul Sinclair" <paul.sinclair@.gmails.com> wrote in message
news:eNXwM2dTGHA.2156@.tk2msftngp13.phx.gbl...
> Is there a way to list an object name in a query where it's in another
> database?
> What I would like to do is have a select statement that lists processes
> running (list locks actually) and have a column in the query for dbname +
> object name.
> I'm not sure if you can do this in the select list though cause
> Object_Name(ID) is current db specific. Is there a way to get
> Object_Name() to look in a different database for the object's name
> such as SELECT DB_Name(ID) + '..' + Object_Name(ID) AS DBAndObject|||Aaron Bertrand [SQL Server MVP] wrote:
> Can't you join on otherdb..sysobjects?
>
>
> "Paul Sinclair" <paul.sinclair@.gmails.com> wrote in message
> news:eNXwM2dTGHA.2156@.tk2msftngp13.phx.gbl...
>
>
>
I could yes, but I don't know what database to join to. It could be any
of them. So I'm wondering if there is a way to dynamically

DB_E_ERRORSINCOMMAND ( HRESULT = -2147217900 )

Hai all,

I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
recordset or execute query in VC++. But when I run the same query in
Query Analyzer, it is working fine. I am sure the connection string is
correct. I am running a collection of queries and finally commit the
transaction, but everytime it not throwing the exception on same query,

eachtime different queries throw exception randomly.

Can anyone tell whats the problem?
Urgent, Please help...
Looking forward for the response..
Thanx in advance...Prince (princevictor.moses@.gmail.com) writes:
> I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
> recordset or execute query in VC++. But when I run the same query in
> Query Analyzer, it is working fine. I am sure the connection string is
> correct. I am running a collection of queries and finally commit the
> transaction, but everytime it not throwing the exception on same query,
> eachtime different queries throw exception randomly.

It's more likely that there is some error in the calling sequence, rahter
than the SQL code. Without seeing your code, it's impossible to be more
detailed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Thanx for ur reply..
Is there is any prob in the following query?

INSERT INTO jobhistory(schObjId, BaseLineId, ActionTaken, UserId,
historyDateTime, remarks) VALUES ('_2036', '', 'CREATED', 'Guest',
getDate(), 'Job Created by Guest -- Guest')

looking forward for the response...|||Prince (princevictor.moses@.gmail.com) writes:
> Thanx for ur reply..
> Is there is any prob in the following query?
> INSERT INTO jobhistory(schObjId, BaseLineId, ActionTaken, UserId,
> historyDateTime, remarks) VALUES ('_2036', '', 'CREATED', 'Guest',
> getDate(), 'Job Created by Guest -- Guest')
> looking forward for the response...

If it wasn't clear: I believe the problem is in the C++ code and the call
to ADO, not in the SQL itself.

But let's make a shot in the dark: att SET NOCOUNT ON.

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

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

DB_E_ERRORSINCOMMAND ( HRESULT = -2147217900 )

Hai all,
I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
recordset or execute query in VC++. But when I run the same query in
Query Analyzer, it is working fine. I am sure the connection string is
correct. I am running a collection of queries and finally commit the
transaction, but everytime it not throwing the exception on same query,
eachtime different queries throw exception randomly.
Can anyone tell whats the problem?
Urgent, Please help...
Looking forward for the response..
Thanx in advance...Vodafone (princevictor.moses@.gmail.com) writes:
> I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
> recordset or execute query in VC++. But when I run the same query in
> Query Analyzer, it is working fine. I am sure the connection string is
> correct. I am running a collection of queries and finally commit the
> transaction, but everytime it not throwing the exception on same query,
> eachtime different queries throw exception randomly.
There is answer in comp.databases.ms-sqlserver. Please do not post the
same question independently to several newsgroups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

DB_E_ERRORSINCOMMAND ( HRESULT = -2147217900 )

Hai all,
I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
recordset or execute query in VC++. But when I run the same query in
Query Analyzer, it is working fine. I am sure the connection string is
correct. I am running a collection of queries and finally commit the
transaction, but everytime it not throwing the exception on same query,
eachtime different queries throw exception randomly.
Can anyone tell whats the problem?
Urgent, Please help...
Looking forward for the response..
Thanx in advance...
Hi
Have you printed out the SQL Statement before you send it to the server? You
may want to use SQL Profiler to see what/if anything is being sent to the
server.
Have you also checked out
http://support.microsoft.com/default...b;en-us;18189?
John
"Prince" wrote:

> Hai all,
> I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
> recordset or execute query in VC++. But when I run the same query in
> Query Analyzer, it is working fine. I am sure the connection string is
> correct. I am running a collection of queries and finally commit the
> transaction, but everytime it not throwing the exception on same query,
> eachtime different queries throw exception randomly.
> Can anyone tell whats the problem?
> Urgent, Please help...
> Looking forward for the response..
> Thanx in advance...
>
|||Hai,
Thanx for ur reply...
In ran the query from Profiler, it works fine... and the URL that u
provide doesnt works...
Looking forward for the response...
John Bell wrote:
[vbcol=seagreen]
> Hi
> Have you printed out the SQL Statement before you send it to the server? You
> may want to use SQL Profiler to see what/if anything is being sent to the
> server.
> Have you also checked out
> http://support.microsoft.com/default...b;en-us;18189?
> John
> "Prince" wrote:
|||Hi
Try http://support.microsoft.com/kb/181890
You may want to check MDAC versions.
John
"Prince" wrote:

> Hai,
> Thanx for ur reply...
> In ran the query from Profiler, it works fine... and the URL that u
> provide doesnt works...
> Looking forward for the response...
>
> John Bell wrote:
>
>

DB_E_ERRORSINCOMMAND ( HRESULT = -2147217900 )

Hai all,
I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
recordset or execute query in VC++. But when I run the same query in
Query Analyzer, it is working fine. I am sure the connection string is
correct. I am running a collection of queries and finally commit the
transaction, but everytime it not throwing the exception on same query,
eachtime different queries throw exception randomly.
Can anyone tell whats the problem?
Urgent, Please help...
Looking forward for the response..
Thanx in advance...Hi
Have you printed out the SQL Statement before you send it to the server? You
may want to use SQL Profiler to see what/if anything is being sent to the
server.
Have you also checked out
http://support.microsoft.com/defaul...kb;en-us;18189?
John
"Prince" wrote:

> Hai all,
> I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
> recordset or execute query in VC++. But when I run the same query in
> Query Analyzer, it is working fine. I am sure the connection string is
> correct. I am running a collection of queries and finally commit the
> transaction, but everytime it not throwing the exception on same query,
> eachtime different queries throw exception randomly.
> Can anyone tell whats the problem?
> Urgent, Please help...
> Looking forward for the response..
> Thanx in advance...
>|||Hai,
Thanx for ur reply...
In ran the query from Profiler, it works fine... and the URL that u
provide doesnt works...
Looking forward for the response...
John Bell wrote:
[vbcol=seagreen]
> Hi
> Have you printed out the SQL Statement before you send it to the server? Y
ou
> may want to use SQL Profiler to see what/if anything is being sent to the
> server.
> Have you also checked out
> http://support.microsoft.com/defaul...kb;en-us;18189?
> John
> "Prince" wrote:
>|||Hi
Try http://support.microsoft.com/kb/181890
You may want to check MDAC versions.
John
"Prince" wrote:

> Hai,
> Thanx for ur reply...
> In ran the query from Profiler, it works fine... and the URL that u
> provide doesnt works...
> Looking forward for the response...
>
> John Bell wrote:
>
>sql

DB_E_ERRORSINCOMMAND ( HRESULT = -2147217900 )

Hai all,
I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
recordset or execute query in VC++. But when I run the same query in
Query Analyzer, it is working fine. I am sure the connection string is
correct. I am running a collection of queries and finally commit the
transaction, but everytime it not throwing the exception on same query,
eachtime different queries throw exception randomly.
Can anyone tell whats the problem?
Urgent, Please help...
Looking forward for the response..
Thanx in advance...Hi
Have you printed out the SQL Statement before you send it to the server? You
may want to use SQL Profiler to see what/if anything is being sent to the
server.
Have you also checked out
http://support.microsoft.com/default.aspx?scid=kb;en-us;18189?
John
"Prince" wrote:
> Hai all,
> I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
> recordset or execute query in VC++. But when I run the same query in
> Query Analyzer, it is working fine. I am sure the connection string is
> correct. I am running a collection of queries and finally commit the
> transaction, but everytime it not throwing the exception on same query,
> eachtime different queries throw exception randomly.
> Can anyone tell whats the problem?
> Urgent, Please help...
> Looking forward for the response..
> Thanx in advance...
>|||Hai,
Thanx for ur reply...
In ran the query from Profiler, it works fine... and the URL that u
provide doesnt works...
Looking forward for the response...
John Bell wrote:
> Hi
> Have you printed out the SQL Statement before you send it to the server? You
> may want to use SQL Profiler to see what/if anything is being sent to the
> server.
> Have you also checked out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;18189?
> John
> "Prince" wrote:
> > Hai all,
> >
> > I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
> > recordset or execute query in VC++. But when I run the same query in
> > Query Analyzer, it is working fine. I am sure the connection string is
> > correct. I am running a collection of queries and finally commit the
> > transaction, but everytime it not throwing the exception on same query,
> > eachtime different queries throw exception randomly.
> >
> > Can anyone tell whats the problem?
> > Urgent, Please help...
> > Looking forward for the response..
> > Thanx in advance...
> >
> >|||Hi
Try http://support.microsoft.com/kb/181890
You may want to check MDAC versions.
John
"Prince" wrote:
> Hai,
> Thanx for ur reply...
> In ran the query from Profiler, it works fine... and the URL that u
> provide doesnt works...
> Looking forward for the response...
>
> John Bell wrote:
> > Hi
> >
> > Have you printed out the SQL Statement before you send it to the server? You
> > may want to use SQL Profiler to see what/if anything is being sent to the
> > server.
> >
> > Have you also checked out
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;18189?
> >
> > John
> >
> > "Prince" wrote:
> >
> > > Hai all,
> > >
> > > I am getting an DB_E_ERRORSINCOMMAND exception when I try to open a
> > > recordset or execute query in VC++. But when I run the same query in
> > > Query Analyzer, it is working fine. I am sure the connection string is
> > > correct. I am running a collection of queries and finally commit the
> > > transaction, but everytime it not throwing the exception on same query,
> > > eachtime different queries throw exception randomly.
> > >
> > > Can anyone tell whats the problem?
> > > Urgent, Please help...
> > > Looking forward for the response..
> > > Thanx in advance...
> > >
> > >
>

db/server or query performance tuning?

I need to begin doing performance tuning on my servers. Nobody has ever done
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisR
yes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon

db/server or query performance tuning?

I need to begin doing performance tuning on my servers. Nobody has ever done
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisRyes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon

db/server or query performance tuning?

I need to begin doing performance tuning on my servers. Nobody has ever done
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisRyes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon

DB Size

How do I list all the Database and it's Size in Query Analyzer?
Use the system stored procedure:-
sp_databases
Thanks
Hari
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uvwYazZsHHA.1096@.TK2MSFTNGP06.phx.gbl...
> Hi
> run sp_helpdb
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "msnews.microsoft.com" <allblacks15@.hotmail.com> wrote in message
> news:%23OwAruZsHHA.508@.TK2MSFTNGP02.phx.gbl...
>

DB Size

How do I list all the Database and it's Size in Query Analyzer?Hi
run sp_helpdb
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"msnews.microsoft.com" <allblacks15@.hotmail.com> wrote in message
news:%23OwAruZsHHA.508@.TK2MSFTNGP02.phx.gbl...
> How do I list all the Database and it's Size in Query Analyzer?
>|||Use the system stored procedure:-
sp_databases
Thanks
Hari
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uvwYazZsHHA.1096@.TK2MSFTNGP06.phx.gbl...
> Hi
> run sp_helpdb
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "msnews.microsoft.com" <allblacks15@.hotmail.com> wrote in message
> news:%23OwAruZsHHA.508@.TK2MSFTNGP02.phx.gbl...
>

DB Size

How do I list all the Database and it's Size in Query Analyzer?Hi
run sp_helpdb
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"msnews.microsoft.com" <allblacks15@.hotmail.com> wrote in message
news:%23OwAruZsHHA.508@.TK2MSFTNGP02.phx.gbl...
> How do I list all the Database and it's Size in Query Analyzer?
>|||Use the system stored procedure:-
sp_databases
Thanks
Hari
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uvwYazZsHHA.1096@.TK2MSFTNGP06.phx.gbl...
> Hi
> run sp_helpdb
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "msnews.microsoft.com" <allblacks15@.hotmail.com> wrote in message
> news:%23OwAruZsHHA.508@.TK2MSFTNGP02.phx.gbl...
>> How do I list all the Database and it's Size in Query Analyzer?
>

DB query log

Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.ThanksProbably one of the ten most requested things for SQL. Unfortunately, no; natively SQL does not have this capability. There are 3rd party tools for doing this sort of thing, but nothing native to SQL Server 7.0 or 2000 (maybe in 2005, but I haven't seen that yet).

Some things that you CAN do to minimize/mitigate the problem:
1. Establish a proper security model. Each user has an assigned username / password (I prefer integrated security). Each user should be assigned the minimum privileges necessary to complete there assigned task(s). No one (not even you) should be using the sa account.

2. Establish a backup and recovery process that meets your specific requirements. Suggested profile would be a complete backup daily for system and user databases, plus transaction log backups every 1-3 hours depending on your requirements and the traffic on your server. Be sure that your backups are written to another server so that you can recover in the event of a failure of the disk on the primary server.

3. Audit the access to your SQL server; at a minimum log failed access requests.

4. Periodically audit the privileges for each user; check in particular for sysadmin or dbowner privileges.

I'm sorry about your loss and I hope that you are able to recover. I wish I had a better answer (and I hope MS comes up with a better one soon!). Hopefully it's not one that will cost anyone their job and you can chalk it up to experience.

Kindest regards,

hmscott

PS. I should mention that one thing many experts recommend is to run a continuous trace file of the most recent commands issued to SQL server. This IS a native capability of SQL server. I think there is an article on how to set one up on http://www.sqlservercentral.com. This would tell you who had done what and when (provided it happened within the window of the log file.

Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.Thanks|||Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.ThanksRestore from yesterday's backups, and follow Scott's suggestions...You DO have backups, right?|||Thanks for your reply...We do have backups