multi step OLE DB error

Hi All,

I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).

This field in SQL2005 has allow nulls.

I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.

Any ideas?

Hi,

could you please provide the code you are using as well as the exact error message ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The exact error message received is:

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

**************************************************************************

The code that is used, (some cut to save space) is below. All is fine, but breaks on the Physical Address line highlighted.

GetDSN gets the DSN in the DSN settings using ODBC. As I said, this worked in Access, but now in SQL, its not working.

**************************************************************************

Public Function mStaffUpdate(StaffID As Long, Title As String, FirstName As String, LastName As String, _
JobTitle As String, Department As String, Phone As String, _
Fax As String, PhoneExt As String, Mobile As String, _
HomePhone As String, Email As String, Website As String, _
CompanyLink As Long, PhysicalAddress As String, _
PhysicalCity As String, PhysicalState As String, PhysicalZipCode As String, _
PhysicalCountry As String, PostalAddress As String, _
PostalCity As String, PostalState As String, _
PostalZipCode As String, PostalCountry As String, _
UserName As String, Password As String, SecurityLevel As String)

' Open a Connection Object to the database
Dim cnnData As ADODB.Connection
Set cnnData = New Connection

cnnData.Open GetDSN

' Open a Recordset Object
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblStaff WHERE StaffID= " & StaffID
rsData.Open strSQL, cnnData, adOpenStatic, adLockPessimistic

'cut some out for less to see here...
rsData.Fields("HomePhone").Value = HomePhone
rsData.Fields("Email").Value = Email
rsData.Fields("Website").Value = Website
rsData.Fields("PhysicalAddress").Value = PhysicalAddress & " "
rsData.Fields("PhysicalCity").Value = PhysicalCity & " "
rsData.Fields("PhysicalState").Value = PhysicalState & " "

rsData.Update

' Close cnn and recordset
rsData.Close
Set rsData = Nothing
cnnData.Close
Set cnnData = Nothing

End Function

|||any ideas?|||Do you exceed the max possible characters eventually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

No. Actually the address field doesn't need to be that large, it will only have 3-4 lines of an address. But I can't work out why this error is happenning?

|||any suggestions|||

Hi All,

Is there a different group that might be better to help with this question above?

I'm really keen to know what's the cause of this error and how to correct.

|||Moved to Data Access :-)|||can anyone help?|||any ideas?|||

What OLEDB provider are you using?

- Waseem

|||

i have this same problem,

program written in visual basic run properly on access database, but on sql server 2k sp 4 causes errors with

multi step error.

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

best

|||

mrowek wrote:

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

yeah, i just check code in vb with SQLOLEDB.1 provider

when it's saves strings to db it generate error

i put trim(cstr(blah blah)) and now it's write correctly to db without multi step error

[; check code and put this same

it should help

|||

My program occurs this error at the line below:

rs.delete <- occurs "multi-step multi step OLE DB error ......."

I spent two days for finding the resolve way....

then.....I insert "rs.AbsolutePosition = rs.AbsolutePosition" before "rs.delete", like:

.....

rs.AbsolutePosition = rs.AbsolutePosition

rs.delete

rs.update

......

and the error is gone............(maybe this is the most strange thing I have ever seen....@.@.)

multi step OLE DB error

Hi All,

I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).

This field in SQL2005 has allow nulls.

I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.

Any ideas?

Hi,

could you please provide the code you are using as well as the exact error message ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The exact error message received is:

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

**************************************************************************

The code that is used, (some cut to save space) is below. All is fine, but breaks on the Physical Address line highlighted.

GetDSN gets the DSN in the DSN settings using ODBC. As I said, this worked in Access, but now in SQL, its not working.

**************************************************************************

Public Function mStaffUpdate(StaffID As Long, Title As String, FirstName As String, LastName As String, _
JobTitle As String, Department As String, Phone As String, _
Fax As String, PhoneExt As String, Mobile As String, _
HomePhone As String, Email As String, Website As String, _
CompanyLink As Long, PhysicalAddress As String, _
PhysicalCity As String, PhysicalState As String, PhysicalZipCode As String, _
PhysicalCountry As String, PostalAddress As String, _
PostalCity As String, PostalState As String, _
PostalZipCode As String, PostalCountry As String, _
UserName As String, Password As String, SecurityLevel As String)

' Open a Connection Object to the database
Dim cnnData As ADODB.Connection
Set cnnData = New Connection

cnnData.Open GetDSN

' Open a Recordset Object
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblStaff WHERE StaffID= " & StaffID
rsData.Open strSQL, cnnData, adOpenStatic, adLockPessimistic

'cut some out for less to see here...
rsData.Fields("HomePhone").Value = HomePhone
rsData.Fields("Email").Value = Email
rsData.Fields("Website").Value = Website
rsData.Fields("PhysicalAddress").Value = PhysicalAddress & " "
rsData.Fields("PhysicalCity").Value = PhysicalCity & " "
rsData.Fields("PhysicalState").Value = PhysicalState & " "

rsData.Update

' Close cnn and recordset
rsData.Close
Set rsData = Nothing
cnnData.Close
Set cnnData = Nothing

End Function

|||any ideas?|||Do you exceed the max possible characters eventually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

No. Actually the address field doesn't need to be that large, it will only have 3-4 lines of an address. But I can't work out why this error is happenning?

|||any suggestions|||

Hi All,

Is there a different group that might be better to help with this question above?

I'm really keen to know what's the cause of this error and how to correct.

|||Moved to Data Access :-)|||can anyone help?|||any ideas?|||

What OLEDB provider are you using?

- Waseem

|||

i have this same problem,

program written in visual basic run properly on access database, but on sql server 2k sp 4 causes errors with

multi step error.

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

best

|||

mrowek wrote:

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

yeah, i just check code in vb with SQLOLEDB.1 provider

when it's saves strings to db it generate error

i put trim(cstr(blah blah)) and now it's write correctly to db without multi step error

[; check code and put this same

it should help

|||

My program occurs this error at the line below:

rs.delete <- occurs "multi-step multi step OLE DB error ......."

I spent two days for finding the resolve way....

then.....I insert "rs.AbsolutePosition = rs.AbsolutePosition" before "rs.delete", like:

.....

rs.AbsolutePosition = rs.AbsolutePosition

rs.delete

rs.update

......

and the error is gone............(maybe this is the most strange thing I have ever seen....@.@.)

multi lingual where clause

I have a table with nvarchar column. In query analyzer when I run query with foreign language words that are already there in database it dose not return any rows

To test it I returned rows with Select * table. Then from result window I copy chinese characters and put it in SQL where clause in SQL analyzer .When I run query it dose not return results.

What could be the cause..its SQL 2k

found it add N infront of characters|||

As kyus94 indicated, to use UNICODE characters, including Chinese, you must preface the string with the character [ N ].

For example,

SELECT

Column1,

Column2,

etc

FROM MyTable

WHERE Column3 = N'ThisCouldBeChinese'

(Note the character immediately before (no space) the string.)

sql

Multi Column Report

I can't get my table to wrap to column 2. When I run the report it makes the overall width the width of one column. There is plenty of room for this 2nd column. Why isn't it showing?Multi column reports appear as one column in Preview and the HTML renderers.
To render the report so all columns show you must use Print Preview, PDF, or
TIFF.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> I can't get my table to wrap to column 2. When I run the report it makes
the overall width the width of one column. There is plenty of room for this
2nd column. Why isn't it showing?|||I am having a problem with multi-column report displaying the multiple
columns when viewed as a sub-report, whether or not I am in Print Preview
mode or in Preview mode.
When I view the report as a master report, all columns show up as expected
in the Print Preview mode (not in Preview mode).
Does anyone have a work-around or is there a fix for this problem?
"Bruce Johnson [MSFT]" wrote:
> Multi column reports appear as one column in Preview and the HTML renderers.
> To render the report so all columns show you must use Print Preview, PDF, or
> TIFF.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> > I can't get my table to wrap to column 2. When I run the report it makes
> the overall width the width of one column. There is plenty of room for this
> 2nd column. Why isn't it showing?
>
>|||Can you tell us if there are any plans in the future to have the html
renderer display multiple columns?
Can you suggest any workarounds for this?
Thanks,
John
"Bruce Johnson [MSFT]" wrote:
> Multi column reports appear as one column in Preview and the HTML renderers.
> To render the report so all columns show you must use Print Preview, PDF, or
> TIFF.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> > I can't get my table to wrap to column 2. When I run the report it makes
> the overall width the width of one column. There is plenty of room for this
> 2nd column. Why isn't it showing?
>
>sql

Multi column

Hi,
I dont know how to word this but I'll try. I have a report that shows two
field in the output, i.e city and count. When the report is run it displays
about 44 rows on one page, but since there are only two fields the rest of
the page is left blank on the right hand side which can easily accomodate two
more column of same fields of city and count. For example it prints row 1
thru 44 on one page and 45 thru ... I would like it to print the 45 thru ...
on the right side of the page which is blank. I'm pretty sure this can be
done.
Please help!while in Layout tab in Report Designer, go to Report then Report Properties.
Click the Layout tab and play around with the number in Columns box.
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:D65B284A-2C06-4D3E-BC1A-BBF03DD51679@.microsoft.com...
> Hi,
> I dont know how to word this but I'll try. I have a report that shows two
> field in the output, i.e city and count. When the report is run it
> displays
> about 44 rows on one page, but since there are only two fields the rest of
> the page is left blank on the right hand side which can easily accomodate
> two
> more column of same fields of city and count. For example it prints row 1
> thru 44 on one page and 45 thru ... I would like it to print the 45 thru
> ...
> on the right side of the page which is blank. I'm pretty sure this can be
> done.
> Please help!|||I changed the column from 1 to 2 but it still wont move over to the second
column. Any idea?
Thanks
"ME" wrote:
> while in Layout tab in Report Designer, go to Report then Report Properties.
> Click the Layout tab and play around with the number in Columns box.
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:D65B284A-2C06-4D3E-BC1A-BBF03DD51679@.microsoft.com...
> > Hi,
> >
> > I dont know how to word this but I'll try. I have a report that shows two
> > field in the output, i.e city and count. When the report is run it
> > displays
> > about 44 rows on one page, but since there are only two fields the rest of
> > the page is left blank on the right hand side which can easily accomodate
> > two
> > more column of same fields of city and count. For example it prints row 1
> > thru 44 on one page and 45 thru ... I would like it to print the 45 thru
> > ...
> > on the right side of the page which is blank. I'm pretty sure this can be
> > done.
> >
> > Please help!
>
>

Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktfWhat do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||select count(IdentityColumn) from tablename
I get ~87,000,000 in return
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||also to just be sure:
Select count(*) IdentityColumn
group by IdentityColumn
having count(*)>1
Returns 0
Thank you
ktf
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||It is:
SQL enterprise 2000 clustered
NT.5.0.(2195)
8.00.760. SP3
7GB memory
4 processor
"Jasper Smith" wrote:
> What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
> and see if that works. It sounds like this bug
> FIX: A parallel query may return unexpected results
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||Sounds like you're running into the bug then. Did you try doing a count with
option(maxdop 1) ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> It is:
> SQL enterprise 2000 clustered
> NT.5.0.(2195)
> 8.00.760. SP3
> 7GB memory
> 4 processor
>
> "Jasper Smith" wrote:
>> What service pack are you on? Also try doing a count with OPTION(MAXDOP
>> 1)
>> and see if that works. It sounds like this bug
>> FIX: A parallel query may return unexpected results
>> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> > The problem I have is the count(*) or count(table identity column) show
>> > a
>> > much bigger number than the actual number of rows.
>> >
>> > When I run
>> > select count(*) from tablename I get ~87,000,000 in return.
>> >
>> > But the MAX number of table identity is in 10 M range also rowcnt from
>> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
>> > not
>> > help me.
>> >
>> > Why I get such different result. What should I do to correct this.
>> >
>> > Thank you,
>> > ktf
>>|||Before I do that:
Is it going to reconfigure and change the server setting or it is only
within the session?
Is it going to put a big impact on the server?
Because I do not want to make that change yet.
Do I have to install SP4? Because we are not on 64-bit server.
Thank you,
"Jasper Smith" wrote:
> Sounds like you're running into the bug then. Did you try doing a count with
> option(maxdop 1) ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> > It is:
> > SQL enterprise 2000 clustered
> > NT.5.0.(2195)
> > 8.00.760. SP3
> > 7GB memory
> > 4 processor
> >
> >
> > "Jasper Smith" wrote:
> >
> >> What service pack are you on? Also try doing a count with OPTION(MAXDOP
> >> 1)
> >> and see if that works. It sounds like this bug
> >>
> >> FIX: A parallel query may return unexpected results
> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> > The problem I have is the count(*) or count(table identity column) show
> >> > a
> >> > much bigger number than the actual number of rows.
> >> >
> >> > When I run
> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >
> >> > But the MAX number of table identity is in 10 M range also rowcnt from
> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> >> > not
> >> > help me.
> >> >
> >> > Why I get such different result. What should I do to correct this.
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
> >>
>
>|||It's a query hint, it only affects the specific query in question. It won't
impact anything else. To avoid any issues just run
select count(*)
from tablename with(nolock)
option(maxdop 1)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> Before I do that:
> Is it going to reconfigure and change the server setting or it is only
> within the session?
> Is it going to put a big impact on the server?
> Because I do not want to make that change yet.
> Do I have to install SP4? Because we are not on 64-bit server.
> Thank you,
>
> "Jasper Smith" wrote:
>> Sounds like you're running into the bug then. Did you try doing a count
>> with
>> option(maxdop 1) ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> > It is:
>> > SQL enterprise 2000 clustered
>> > NT.5.0.(2195)
>> > 8.00.760. SP3
>> > 7GB memory
>> > 4 processor
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> What service pack are you on? Also try doing a count with
>> >> OPTION(MAXDOP
>> >> 1)
>> >> and see if that works. It sounds like this bug
>> >>
>> >> FIX: A parallel query may return unexpected results
>> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> > The problem I have is the count(*) or count(table identity column)
>> >> > show
>> >> > a
>> >> > much bigger number than the actual number of rows.
>> >> >
>> >> > When I run
>> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >
>> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> > from
>> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
>> >> > will
>> >> > not
>> >> > help me.
>> >> >
>> >> > Why I get such different result. What should I do to correct this.
>> >> >
>> >> > Thank you,
>> >> > ktf
>> >>
>> >>
>> >>
>>|||Jasper,
It gave me the correct number.
the config_value and run_value are set to 0 for "max degree of parallelism.
do you think I should turn it on.
Is it necessary to install sql sp4. The site does not say much about sp4.
Thank you,
ktf
"Jasper Smith" wrote:
> It's a query hint, it only affects the specific query in question. It won't
> impact anything else. To avoid any issues just run
> select count(*)
> from tablename with(nolock)
> option(maxdop 1)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> > Before I do that:
> > Is it going to reconfigure and change the server setting or it is only
> > within the session?
> > Is it going to put a big impact on the server?
> > Because I do not want to make that change yet.
> > Do I have to install SP4? Because we are not on 64-bit server.
> >
> > Thank you,
> >
> >
> > "Jasper Smith" wrote:
> >
> >> Sounds like you're running into the bug then. Did you try doing a count
> >> with
> >> option(maxdop 1) ?
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> >> > It is:
> >> > SQL enterprise 2000 clustered
> >> > NT.5.0.(2195)
> >> > 8.00.760. SP3
> >> > 7GB memory
> >> > 4 processor
> >> >
> >> >
> >> > "Jasper Smith" wrote:
> >> >
> >> >> What service pack are you on? Also try doing a count with
> >> >> OPTION(MAXDOP
> >> >> 1)
> >> >> and see if that works. It sounds like this bug
> >> >>
> >> >> FIX: A parallel query may return unexpected results
> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Jasper Smith (SQL Server MVP)
> >> >> http://www.sqldbatips.com
> >> >> I support PASS - the definitive, global
> >> >> community for SQL Server professionals -
> >> >> http://www.sqlpass.org
> >> >>
> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> >> > The problem I have is the count(*) or count(table identity column)
> >> >> > show
> >> >> > a
> >> >> > much bigger number than the actual number of rows.
> >> >> >
> >> >> > When I run
> >> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >> >
> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
> >> >> > from
> >> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
> >> >> > will
> >> >> > not
> >> >> > help me.
> >> >> >
> >> >> > Why I get such different result. What should I do to correct this.
> >> >> >
> >> >> > Thank you,
> >> >> > ktf
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||The fix for this bug is in SP4. Changing the server wide maxdop settings
will obviously affect all other queries so should only be done after
extensive testing. If you are not seeing any application related issues due
to this bug and it is only affecting "DBA" type activities then you don't
necessarily need to get on SP4 however it's probably worth doing anyway to
keep upto to date with the latest bug fixes
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:30CBAF3D-FC19-4F06-89A8-ED074AF96A98@.microsoft.com...
> Jasper,
> It gave me the correct number.
> the config_value and run_value are set to 0 for "max degree of
> parallelism.
> do you think I should turn it on.
> Is it necessary to install sql sp4. The site does not say much about sp4.
> Thank you,
> ktf
> "Jasper Smith" wrote:
>> It's a query hint, it only affects the specific query in question. It
>> won't
>> impact anything else. To avoid any issues just run
>> select count(*)
>> from tablename with(nolock)
>> option(maxdop 1)
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
>> > Before I do that:
>> > Is it going to reconfigure and change the server setting or it is only
>> > within the session?
>> > Is it going to put a big impact on the server?
>> > Because I do not want to make that change yet.
>> > Do I have to install SP4? Because we are not on 64-bit server.
>> >
>> > Thank you,
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> Sounds like you're running into the bug then. Did you try doing a
>> >> count
>> >> with
>> >> option(maxdop 1) ?
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> >> > It is:
>> >> > SQL enterprise 2000 clustered
>> >> > NT.5.0.(2195)
>> >> > 8.00.760. SP3
>> >> > 7GB memory
>> >> > 4 processor
>> >> >
>> >> >
>> >> > "Jasper Smith" wrote:
>> >> >
>> >> >> What service pack are you on? Also try doing a count with
>> >> >> OPTION(MAXDOP
>> >> >> 1)
>> >> >> and see if that works. It sounds like this bug
>> >> >>
>> >> >> FIX: A parallel query may return unexpected results
>> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >>
>> >> >> Jasper Smith (SQL Server MVP)
>> >> >> http://www.sqldbatips.com
>> >> >> I support PASS - the definitive, global
>> >> >> community for SQL Server professionals -
>> >> >> http://www.sqlpass.org
>> >> >>
>> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> >> > The problem I have is the count(*) or count(table identity
>> >> >> > column)
>> >> >> > show
>> >> >> > a
>> >> >> > much bigger number than the actual number of rows.
>> >> >> >
>> >> >> > When I run
>> >> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >> >
>> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> >> > from
>> >> >> > sysindexes shows the correct number of ~10 M. So DBCC
>> >> >> > UPDATEUSAGE
>> >> >> > will
>> >> >> > not
>> >> >> > help me.
>> >> >> >
>> >> >> > Why I get such different result. What should I do to correct
>> >> >> > this.
>> >> >> >
>> >> >> > Thank you,
>> >> >> > ktf
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>sql

MSXML6.dll fails to register when calling SSIS from win service

Has anyone encountered this error? I get it when I run the installer (c#). I have seen a few posts on google and msdn, but still can't get past the error. I have tried uninstall/resinstalling the parser, registering from the command line, setting the property of the interop to "donotregister" and others with no luck.

Any ideas?

Thanks,
LeeOK got past the installation - but the package shows to fire but doesn't. Might be permissions; I am going to try and point the SSIS package to a local set of folders.

I think the problem was the "donotregister" property of the .dll. The SSIS tools, as you know, must be installed on your local workstation as well.

Lee|||Where are all of the 'experts' out here? I've posted five times out here and haven't received a response that remotely touches anything that I've ever come up with.

Oh well.

Still at the same place. I created another ssis package and pointed it to the local folders and ran via a console app and it works fine. Most likely permissions. Someone suggested turning logging on the package. Not sure which logging they were referring to.

Thanks,
Lee|||

Lee Everest wrote:

Where are all of the 'experts' out here? I've posted five times out here and haven't received a response that remotely touches anything that I've ever come up with.

Oh well.

Still at the same place. I created another ssis package and pointed it to the local folders and ran via a console app and it works fine. Most likely permissions. Someone suggested turning logging on the package. Not sure which logging they were referring to.

Thanks,
Lee

Package configurations. Right click on the control flow, select logging.

MSVCR70.dll

While trying to run Yahoo Browzer, Got I got a message saying MSVCR70.dll was not found. How can I get it fixed?

Rick

This does not look like a SQL Setuo question. Please post with more details if it is.

Thanks

Michelle

MSSQLServerADHelper Service?

Hi,
while going through the service I noticed that the MSSQLServerADHelper Service is set to run manually.
I wanted to know what this service does and should it set it to run automatically?
Thanks.Hey Joozh, did the answer on SQLTeam get you covered for this?|||Yes..almost :)
Thanks ;)sql

mssqlserver account

What account should run mssqlserver and sqlserveragent.
Should it be a local account or a domain account?
Thanks,
MTI like using a Domain account when there may be multiple servers because it
is easier to share file permissions etc... If there is only one server it
doesn't much matter.. If you wish to use mail integration for SQL Server
you may wish to use multiple domain accounts, since email is connected to
the login account - if you wish to separate the email for each server...
The Domain account should have limited permissions (as doc'd in BOL.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mt" <miketownsend33@.yahoo.com> wrote in message
news:028601c37775$9fe611c0$a001280a@.phx.gbl...
> What account should run mssqlserver and sqlserveragent.
> Should it be a local account or a domain account?
> Thanks,
> MT

mssqlserver account

What account should run mssqlserver and sqlserveragent.
Should it be a local account or a domain account?
Thanks,
MTYou often need a domain account. If SQL Server agent executes a job which fetches a file from
another machine, for instance., Or you want to use SQL Mail, where you need a domain account for the
mail account.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"mt" <miketownsend33@.yahoo.com> wrote in message news:0a7201c37775$a0c993f0$a301280a@.phx.gbl...
> What account should run mssqlserver and sqlserveragent.
> Should it be a local account or a domain account?
> Thanks,
> MT

mssql2000 and oracle 10g on same server

I have mssql2000 running on a Windows 2003 server and now have a requirement to run an Oracle 10g database as well. Is it possible to run both mssql2000 and oracle 10g on the same server without running into any conflicts or will the two programs cause errors with each other?

Anyone have any experience with this? Oracle says it's technically possible but the tech had never seen it done.

ScottAlthough I've never run them in production, they run well enough on a test/dev machine. I'm pretty sure that you ought to be fine.

-PatP|||Thanks for the reply. We are a small business and I dont have a testbox to play with. I'll make sure everything gets backed up before install of course just to make sure.|||I'm just being nosy now, but those are the database engine equivalent of oxen. Either one of them is massive overkill for a small business... either one can handle the needs of a small business in their sleep.

Why do you need both of them? Software that is database engine specific?

To clarify on my previous post, I haven't run both SQL Server and Oracle 10g on one machine in production, but the two coexist happily on dev/test boxes.

One thing that I'd very strongly encourage is that you take whatever machine you can spare (whimpy is good in this case) and make it a dev/test box. You really don't want to be trying things out for the very first time on your production machine if you have any choice in the matter. I have actually seen people (more than one) lose their business by making that mistake.

-PatP|||I agree, it is a risky proposition. I will be making an image of the machine and backing up all data before doing the install. I also have to have it operational by tommorow because of business requirements. No pressure!

As to why we are running both you nailed it on the head. Software requirements. We will eventually, a couple years, be migrated completely to Oracle......or at least that's the plan today.

MSSQL/Server setup/running issue/question...

i have a client with a windows 2003 server with 4 processors & 32 GB RAM, then MSSQL 2000 SP3. our software seems to run fine on it until the Task Manager shows the MSSQL using 1.7GB of RAM. At this point the whole system seems to slow down to a snails pace. Any ideas as to why 1.7GB and it never goes over that amount no matter how much activity i run on it. Restart mssql and everything seems to run good up until we see the 1.7GB again.Quite simple: The server is installed with 32-bit edition of the OS and SQL Server 2000. You can increase the memory usage of SQL server to approx 2.7GB by using the /3GB switch in boot.ini, or use AWE to use memory above 4GB, but I won't recommend it since it works more or less like himem.sys and emm386 in the old days. What you should do is really reinstall the server, using 64-bit edition of both the OS and SQL Server software. Unless there is some piece of software or hardware which is not supported in the 64-bit edition.

MSSQL SPROC and VB6

I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?

Here is my complete code:

USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP

GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@.ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE @.ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @.CP int /*Len of String */
DECLARE @.SV varchar(50) /*Holds Result */

While @.ListStr<>''
Begin
Set @.CP=CharIndex(',',@.ListStr) /*Sets length of words - Instr */
If @.CP<>0
Begin
Set @.SV=Cast(Left(@.ListStr,@.CP-1) as varchar) /*Copies Portion of String*/
Set @.ListStr=Right(@.ListStr,Len(@.ListStr)-@.CP) /*Sets up next portion of string*/
End
Else
Begin
Set @.SV=Cast(@.ListStr as varchar)
Set @.ListStr=''
End
Insert into @.ListTbl Values (@.SV) /*Inserts variable into Temp Table*/
End

Select InvUnit From @.ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit

and my VB6 Code:

Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String

Set adoConn = New ADODB.Connection
adoConn.Open connString

Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command

With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"

Set adoRS = .Execute

Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop

End With

Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing

End Sub

Any ideas?

ThanksWhat is the edition of SQL used and its memory settings?
Is SQL Server shared by other applications?|||Thanks for the reply my problem was resolved (http://vbforums.com/showthread.php?t=405134)

MS-SQL Script Question

MS-SQL 2000

Is there any way to run a SQL script against MSDE other than with OSQL? (No Enterprise manager or Query Analyzer)

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comYou will have to have a client app of some sort (osql/isql/etc). Basically, the
client app will open a connection to your server, parse the content of your
script file into batches delimited by GO, then execute the batches against the
server.

It's quite easy to implement an ado connection to sqlserver, parse the script
file and execute it. QALite on the site does just that.

--
-oj
http://www.rac4sql.net

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:LnnKb.753782$Tr4.2103435@.attbi_s03...
MS-SQL 2000

Is there any way to run a SQL script against MSDE other than with OSQL? (No
Enterprise manager or Query Analyzer)

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related
website.
http://www.vehiclewebstudio.com|||The script can be registered as a task and run automatically
without operator intervention.

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:LnnKb.753782$Tr4.2103435@.attbi_s03...
MS-SQL 2000

Is there any way to run a SQL script against MSDE other than with OSQL? (No
Enterprise manager or Query Analyzer)

TIA

--
Tim Morrison

-----------------------
--

Vehicle Web Studio - The easiest way to create and maintain your vehicle
related website.
http://www.vehiclewebstudio.com

mssql remote gui

Can anyone recommend a FREE Microsoft SQL Server GUI that I can use to
run some basic queries and browse tables for a remote MSSQL database.
I installed the freeware from TOAD, but it screwed up my computer, I
am really looking for something light weight, nothing fancy needed,
for some basic queries and browsing. If I connect to the server I use
the Enterprise Manager, but really didn't want to have to install a
copy of MSSQL on my local machine just to use the Enterprise Manager
if there was a good free GUI.
ThanksPerhaps http://rac4sql.net/qalite_main.asp ?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<trpost@.gmail.com> wrote in message news:1186192697.704881.325630@.i13g2000prf.googlegroups.com...
> Can anyone recommend a FREE Microsoft SQL Server GUI that I can use to
> run some basic queries and browse tables for a remote MSSQL database.
> I installed the freeware from TOAD, but it screwed up my computer, I
> am really looking for something light weight, nothing fancy needed,
> for some basic queries and browsing. If I connect to the server I use
> the Enterprise Manager, but really didn't want to have to install a
> copy of MSSQL on my local machine just to use the Enterprise Manager
> if there was a good free GUI.
> Thanks
>|||In addition to QA lite, have you looked at Management Studio Express
Edition?
--
Aaron Bertrand
SQL Server MVP
<trpost@.gmail.com> wrote in message
news:1186192697.704881.325630@.i13g2000prf.googlegroups.com...
> Can anyone recommend a FREE Microsoft SQL Server GUI that I can use to
> run some basic queries and browse tables for a remote MSSQL database.
> I installed the freeware from TOAD, but it screwed up my computer, I
> am really looking for something light weight, nothing fancy needed,
> for some basic queries and browsing. If I connect to the server I use
> the Enterprise Manager, but really didn't want to have to install a
> copy of MSSQL on my local machine just to use the Enterprise Manager
> if there was a good free GUI.
> Thanks
>|||Hello trpost@.gmail.com,
> Can anyone recommend a FREE Microsoft SQL Server GUI that I can use to
> run some basic queries and browse tables for a remote MSSQL database.
> I installed the freeware from TOAD, but it screwed up my computer, I
> am really looking for something light weight, nothing fancy needed,
> for some basic queries and browsing. If I connect to the server I use
> the Enterprise Manager, but really didn't want to have to install a
> copy of MSSQL on my local machine just to use the Enterprise Manager
> if there was a good free GUI.
> Thanks
>
When you start the setup of SQL Server youw ill be presented an option to
just install the Client tools & Connectivity. IT should give you just the
tools, not the whole server.
Jess