INFORMATION_SCHEMA.COLUMNS query

Hi,
I'm trying to run the following query but keep getting an error
returned:
SELECT COUNT(*)FROM
[sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[table_name]'
Error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider '[sever_name]' does not contain table
'"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
does not exist or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider does not
contain the table: ProviderName='[sever_name]',
TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
The table does exist and the other server has been added to the
sysservers table and works as other queries can be run. I've run the
exact code on other sql servers and it works without any problems. Any
help to run this query would be much appreciated.
Thanks
SimonAFAIK, you can't use the information_schema views in SQL Server 2000 from a linked server as they
don't exists in each database, they only exist in the master database. Use syscolumns etc instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129802634.977349.10700@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm trying to run the following query but keep getting an error
> returned:
> SELECT COUNT(*)FROM
> [sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = '[table_name]'
> Error:
> Server: Msg 7314, Level 16, State 1, Line 2
> OLE DB provider '[sever_name]' does not contain table
> '"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
> does not exist or the current user does not have permissions on that
> table.
> OLE DB error trace [Non-interface error: OLE DB provider does not
> contain the table: ProviderName='[sever_name]',
> TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
> The table does exist and the other server has been added to the
> sysservers table and works as other queries can be run. I've run the
> exact code on other sql servers and it works without any problems. Any
> help to run this query would be much appreciated.
> Thanks
> Simon
>

INFORMATION_SCHEMA.COLUMNS error

Hi All,

For some reason, whenever I execute the following query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

I get a few rows returned then I get the following error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Is my INFORMATION_SCHEMA.COLUMNS view corrupt somehow?

Querries of other INFORMATION_SCHEMA views are fine, just this one is returning an error. Anyone have any ideas about how to fix this?

John

Can you post the table structure..|||

Manivannan.D.Sekaran wrote:

Can you post the table structure..

The table structure of what? The INFORMATION_SCHEMA.COLUMNS view? This is the default INFORMATION_SCHEMA.COLUMNS view that is created by default in SQL Server. Its what should be there by default. Its not a view that I've created.

So what table structure are you referring to?

John

|||

Ohh yes.. You dont know which table cause the problem.. Simply forget..

Ok try to execute the following query..

At one time you will get an error.. So that is the table causing the issue... Then post the DDL of that table...

sp_msforeachtable'Print ''? is Executing..'';Select Count(Column_Name) TotalColumn, ''?'' TableName fromINFORMATION_SCHEMA.COLUMNS Where object_id(table_name) = object_id(''?'');Print ''? is Completed..'';'

|||

Hi Manivannan,

Ok I executed your query. I didn't get any errors.

I think the issue is with the view in general on my server. I get the error when I execute the query against INFORMATION_SCHEMA.COLUMNS regardless of the database I execute against.

When I execute your script against ALL of my databases (on my server) I get no errors.

|||

Here's a little background on what I think may have caused my server to be in such a state.

I have been tasked with changing all columns, in all tables, in our database that are of varchar type to be nvarchar. I immediately issued a query such as

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE='varchar'

That very simply returned ALL columns in my database that were of varchar type, I think there were hundreds of rows returned. So, I think this is what screwed things up for me, I executed an update statement against that view as a test to see if I could do such a thing. This is the update statement I executed:

UPDATE INFORMATION_SCHEMA.COLUMNS SET DATA_TYPE='nvarchar' WHERE DATA_TYPE='varchar'

I figured something was wrong when upon executing that statement it said that 1 row was updated. I got no error message, but now all querries of that view yields the error I documented.

|||

Its really worst idea changing the system tables directly. You should use the proper alter statement.

The update query you performed, is modified the data in System Table (spt_datatype_info) rather than your original table (on syscolumns).

If you only executed the above query then use the following query to revert back spt_datatype_info into original state..

UPDATE spt_datatype_info

SET local_type_name=type_name

WHERE local_type_name='varchar'

|||

WooHoo, it worked!

I changed your update statement slightly to:

UPDATE spt_datatype_info
SET local_type_name=type_name
WHERE local_type_name!=type_name

It restored all that were messed up (there were two rows actually, the row for "varchar" and the row for "text").

Thank you very much Manivannan!

John

sql

Information/ErrorCode from FireInformation/Error

Hi,
Very low priority this one...

Is there a "free" number range that we should use for the InformationCode/ErrorCode parameter to the FireInformation/FireError methods? Currently I just use 1 and -1 respectively.

On a similar note...I remember someone from MS (might have been Matt) once saying that a list of all the rror codes would be published at some point. Is this still the plan and if so, where will it be?

Thanks
JamieJamie,

I can respond only to the 2nd half of your question, on documentation.

As all of the errors are fields in the Microsoft.SqlServer.Dts.Runtime.HResults class, they are already listed in the API reference, particularly in the HResults Members topic in BOL at ms-help://sql90/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_HResults_Members.htm.

Unfortunately this and similar topics are auto-generated by our doc tools in a format that we can't modify. Therefore this particular list contains the "symbolic name" for each error (eg DTS_E_64BITVARIABLERECAST) and its description...but NOT the actual numeric HRESULT.

For the Web refresh of BOL that we're releasing around RTM, we've added a another similar topic that DOES contain the actual HRESULT codes (in hex, as you'll see them in real life), titled "Integration Services Error Reference."

Hope this helps,

-Doug|||Doug,
Frans Van Bree had a good question here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2254.aspx?CommentPosted=true#commentmessage

Will all the event codes from the OnInformation event also be in this reference?

-Jamie|||It's my understanding that errors, warnings, and informational messages from Microsoft-provided Integration Services components are all included in the HResults class already documented (though without the hex codes) in the API reference, as mentioned in my previous post.

These will also be included, with hexadecimal codes, in the new topic that I mentioned. Its planned title, "Error Reference," may be somewhat misleading, but there could be youngsters out there for whom "hresult" is meaningless. <g>

In other words, the new list, like the existing one, will include not only the messages whose symbolic names begin DTS_E_*, but also DTS_I_*, DTS_MSG_*, etc.

(Off topic: My favorite, to date, is DTS_I_CANTRELIEVEPRESSURE.)

-Doug

Information came from sql error log.

Hi,
Can someone explain the following info came from sql server error log? This
is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS is
Windows 2003 (64 bit, attached to SAN) named instance database server.
11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
specified network name is no longer available. [SQLSTATE 08S01]
Is above error means the net work not available for database instance while
application batch job running from other server connect to database server?
11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle is
0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\market_place\data\mp_orders_dat.ndf] in database [market_place](13). The
OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
0x000000307e4c000
Is above info means the SAN drive is very busy and took long time to
complete the database processes such as query, insert, update and delete, etc?
Thanks!
Chen
Check this out;
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
Also review this KB article:
http://support.microsoft.com/default.aspx/kb/897284
Linchi
"Chen" wrote:

> Hi,
> Can someone explain the following info came from sql server error log? This
> is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS is
> Windows 2003 (64 bit, attached to SAN) named instance database server.
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
> specified network name is no longer available. [SQLSTATE 08S01]
> Is above error means the net work not available for database instance while
> application batch job running from other server connect to database server?
> 11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle is
> 0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
> 11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\market_place\data\mp_orders_dat.ndf] in database [market_place](13). The
> OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
> 0x000000307e4c000
> Is above info means the SAN drive is very busy and took long time to
> complete the database processes such as query, insert, update and delete, etc?
> Thanks!
> Chen
>

Information came from sql error log.

Hi,
Can someone explain the following info came from sql server error log? This
is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS i
s
Windows 2003 (64 bit, attached to SAN) named instance database server.
11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
specified network name is no longer available. [SQLSTATE 08S01]
Is above error means the net work not available for database instance while
application batch job running from other server connect to database server?
11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle
is
0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\market_place\data\mp_orders_dat.ndf] in database [market_place](
13). The
OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
0x000000307e4c000
Is above info means the SAN drive is very busy and took long time to
complete the database processes such as query, insert, update and delete, et
c?
Thanks!
ChenCheck this out;
http://blogs.msdn.com/sqlserverstor.../21/642314.aspx
Also review this KB article:
http://support.microsoft.com/default.aspx/kb/897284
Linchi
"Chen" wrote:

> Hi,
> Can someone explain the following info came from sql server error log? Thi
s
> is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS
is
> Windows 2003 (64 bit, attached to SAN) named instance database server.
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication li
nk
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
> specified network name is no longer available. [SQLSTATE 08S01]
> Is above error means the net work not available for database instance whil
e
> application batch job running from other server connect to database server
?
> 11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handl
e is
> 0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
> 11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\market_place\data\mp_orders_dat.ndf] in database [market_place
](13). The
> OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is
:
> 0x000000307e4c000
> Is above info means the SAN drive is very busy and took long time to
> complete the database processes such as query, insert, update and delete,
etc?
> Thanks!
> Chen
>sql

InfoPath 2003 - insert data in SQLServer?

Hello,

I created an InfoPath 2003 form, with a datasource from SqlServer 2000. When I try to insert a new record the follow error occurs:

"InfoPath cannot connect to the data source.
Safety settings on this computer prohibit accessing a data source on another domain."

I checked Enabled -"Access data source accross domains" from Security Settings->Miscellaneous, but the error still appears.

Have anyone any idea about this error?

Thanks.

This seems like an InfoPath specific configuration issue. You may want to try re-posting (sorry, I couldn't find a forum to which I could simply move this post) your question to the InfoPath newsgroup at http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.infopath or checking out the InfoPath blog at http://blogs.msdn.com/infopath/default.aspx.

sql

Info about Error 8623

Hi,

SQL Server returned me error 8623 "Internal Query Processor Error: The query processor could not produce a query plan." I've looked for info in the SQL books, but there's no info about this error. I would appreciate it a lot if somebody could tell me what this error means, how is it triggered, and if it's possible, how to avoid it.

Thanks a lot,

FedericoWhere are you doing this?

What's the query?

It's dynamic, isn't it...|||It would be better if you quote the query involved as per Brett's reply.
This KBA1 (http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729) and KBA2 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;286255) refers about 863 error.

HTH|||I can't reproduce this error. So I think it's sporadyc. Anyway I'll check my SELECT clauses to see if there's something similar to what Satya's link mention. Again, thanks a lot|||PROFILER may help you to track the process of query against database, just in case if you don't know.

inflectional causing "query contained only ignored words" error

Hi
I am receiving the error "query contained only ignored words" from the word
"best" that is not in the ignored words list. While researching the problem
online I found the following link:
http://www.webservertalk.com/archive.../t-965368.html
In the post the poster states this is a known bug in full-text indexing and
has been written up as a "DOC bug" (not sure what that means) by MS. I am
hoping there is an update and/or fix for this problem.
Any help would be appreciated.
Thanks
Tony
remove the word well from your noise word list. Stop MSSearch before making
this change and restart it after. Then rebuild your index.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tony Vargas" <Tony Vargas@.discussions.microsoft.com> wrote in message
news:8CA64E3E-2645-476C-859B-776BA62108CD@.microsoft.com...
> Hi
> I am receiving the error "query contained only ignored words" from the
word
> "best" that is not in the ignored words list. While researching the
problem
> online I found the following link:
> http://www.webservertalk.com/archive.../t-965368.html
> In the post the poster states this is a known bug in full-text indexing
and
> has been written up as a "DOC bug" (not sure what that means) by MS. I am
> hoping there is an update and/or fix for this problem.
> Any help would be appreciated.
> Thanks
> Tony
|||Tony,
A "DOC bug" is a documentation bug, i.e., a "by design" feature that is not
documented in Books Online (BOL) or in a KB article.
I'll confirm that the DOC bug, however, and to the best of my knowledge, no
KB article for this has been written or made public, I'm sad to say. The
workaround at this time, is not to use the 'FORMSOF(INFLECTIONAL) parameter.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Tony Vargas" <Tony Vargas@.discussions.microsoft.com> wrote in message
news:8CA64E3E-2645-476C-859B-776BA62108CD@.microsoft.com...
> Hi
> I am receiving the error "query contained only ignored words" from the
word
> "best" that is not in the ignored words list. While researching the
problem
> online I found the following link:
> http://www.webservertalk.com/archive.../t-965368.html
> In the post the poster states this is a known bug in full-text indexing
and
> has been written up as a "DOC bug" (not sure what that means) by MS. I am
> hoping there is an update and/or fix for this problem.
> Any help would be appreciated.
> Thanks
> Tony

Infinitive recursion for my AS2000 calculated member

Hi,

Can someone please help me on this. I get following error when I browse my virtual AS2000 cube:

<Infinite recursion detected during execution of calculated member Sum({Descendants....>

I have implemented a 'dummy' utility dimension with one calculated member: (I have used a parentchild dimension because that's only way I know how to get the formula a from source view):

--

view [dbo].[vdimUtilityCalculation] as

select
'CalculationID' = 1,
'ParentID' = 1,
'CalculationName' = 'Currency',
'Formula' = 'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))',
'MemberOption' = 'SOLVE_ORDER=''-1'''
--

Any idea?

Thanks, Christer

You need to change coordinate in the utility dimension in order to prevent infinite recursion. I.e. something like that:

'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", (UtilityDim.DefaultMember,[Amount Fixr]), (UtilityDim.DefaultMember,[Amount Flor])) * ValidMeasure([Rate]))',

|||

Thanks for your reply.

I added a second 'dummy' member to my utility dimension and pointed to in the formula like this:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", ([CalculationUtility].&[2],[Amount Fixr]), ([CalculationUtility].&[2],[Amount Flor])) * ValidMeasure([Rate]))

It works for some accounts (account dimension with calculated members), not accounts with formula like account1/account2, they are not calculated correctly... and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

Not sure how to get this working, is this easier to implement in AS 2005?

Thanks, Christer

|||

and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

It should work. I suggest you start troubleshooting by creating a calculated member in this utility dimension with SOLVE_ORDER=-1 and then sending MDX query with Aggregate over Period dimension. It isn't easy to help through newsgroup, but if you will take it slow, step by step, you should be able to see that it does work.

|||

Thanks! After some struggle I got it to work!

sql

Infinitive recursion for my AS2000 calculated member

Hi,

Can someone please help me on this. I get following error when I browse my virtual AS2000 cube:

<Infinite recursion detected during execution of calculated member Sum({Descendants....>

I have implemented a 'dummy' utility dimension with one calculated member: (I have used a parentchild dimension because that's only way I know how to get the formula a from source view):

--

view [dbo].[vdimUtilityCalculation] as

select
'CalculationID' = 1,
'ParentID' = 1,
'CalculationName' = 'Currency',
'Formula' = 'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))',
'MemberOption' = 'SOLVE_ORDER=''-1'''
--

Any idea?

Thanks, Christer

You need to change coordinate in the utility dimension in order to prevent infinite recursion. I.e. something like that:

'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", (UtilityDim.DefaultMember,[Amount Fixr]), (UtilityDim.DefaultMember,[Amount Flor])) * ValidMeasure([Rate]))',

|||

Thanks for your reply.

I added a second 'dummy' member to my utility dimension and pointed to in the formula like this:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", ([CalculationUtility].&[2],[Amount Fixr]), ([CalculationUtility].&[2],[Amount Flor])) * ValidMeasure([Rate]))

It works for some accounts (account dimension with calculated members), not accounts with formula like account1/account2, they are not calculated correctly... and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

Not sure how to get this working, is this easier to implement in AS 2005?

Thanks, Christer

|||

and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

It should work. I suggest you start troubleshooting by creating a calculated member in this utility dimension with SOLVE_ORDER=-1 and then sending MDX query with Aggregate over Period dimension. It isn't easy to help through newsgroup, but if you will take it slow, step by step, you should be able to see that it does work.

|||

Thanks! After some struggle I got it to work!

Infinite Loop in BOL code!?

This code is from BOL (in index type: "DDLs-SQL Server"):
Take a look at error handling .. what happens if one of the three cmd.execute within the [Done:] hanlde fails?

It looks to me like we would have an infinite loop! .. am I missing something here?

BOL CODE EXAMPLE:
----------
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command

' If the ADOTestTable does not exist, go to AdoError.
On Error GoTo AdoError

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

' Set up command object.
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "DROP TABLE ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute

Done:
Cmd.CommandText = "SET NOCOUNT ON"
Cmd.Execute
Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub

AdoError:
Dim errLoop As Error
Dim strError As String

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next

GoTo Done

End Sub
-----------------Where?|||Originally posted by Brett Kaiser
Where?

If an error occurs within the [Done:] handle then the compiler goes to [AdoError:] handle and when executing the last statement there "GoTo Done" we will end up going back to [Done:] ... then same error occurs .. then back to [AdoError:] .. etc .. that sure looks like an infinite loop!|||If a user who can not create tables runs this, yes, it could run forever. Fortunately, Microsoft makes everything run as "administrator".|||lack of permission to Insert would cause the same problem .. But it's not just lack of permission that might cause this code to run forever .. for instance a network failure might raise the same problem .. a server failure might also do the same thing .. the point is: Error handling is supposed to be very helpful but in this code it is not. Because it is not done right.

Infamous product level insufficient error message

This thing is driving me crazy. I have a simple flat .txt file I'm trying to import into a DB, and this USED to work perfectly with SQL Server 2000. With 2005, I get "product level insufficient."

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=217596

I've read that and I've looked around. It seems like the solution is to actually connect to the Windows box that the server itself is running on (where SSIS is installed) and import there?? But that's a pain.. that means I'd have to Remote Desktop into the server EVERY time I want to import a text file. Surely there's some other way to do this??

Any advice would be great. Thanks!I don't recognise the error message but the error itself sounds very familiar. Is this a double hop problem?|||Not sure what double hop is.

In SQL Server Management Studio, I'm trying to import a flat .txt file into the DB. Here's a screenshot of the error message (I highlighted it):

http://img59.imageshack.us/img59/319/prodinsuffrw4.gif|||Just looked on web. Looks different so I won't be much help. You seen this?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112469&SiteID=1|||Ahh, installing SP1 fixed it. Now I have a new problem but I'll research it a bit before posting :)sql

Infameous 0x80070005 ERROR: Access denied

Hi, my name is Beatriz and I'm another victim of the 0x80070005 from
Hell. I'm running WBEMTEST from a PC to connect to another one with WXP
SP2 and even:

1. The firewall is down.
2. Applied the commands:
netsh firewall set service RemoteAdmin enable
netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135
3. Did the workaround suggested in:
http://support.microsoft.com/kb/909444
4. Ran the workaround in: http://support.microsoft.com/kb/884573
5. Added the account I use to access by Wbemtest with to the Default
Lauch Permissions with Full Control.
6. Checked up the registry
:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\forceguest
were set to 0.

I keep not being able to connect, and receving 0x80070005, what I'm
missing?!?!?!?Are you looking for SQL Server Integration Services help?|||

Did you ever get a response or resolution to this?

Hopefully, your system isn't still DOA 7-8 months later.

We have exactly the same issue with a brand-new server running WinServer 2003x64 R2

Have installed/reinstalled the entire OS three times now. The solution I guess is to do yet another reinstall and then not install the rogue security patch but that would leave us vulnerable to whatever the security patch was trying to fix and who knows what future M/Soft update won't do something similar?

|||Are you having SSIS problems when trying to connect to the SSIS service via Management Studio?

http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/

Infameous 0x80070005 ERROR: Access denied

Hi, my name is Beatriz and I'm another victim of the 0x80070005 from
Hell. I'm running WBEMTEST from a PC to connect to another one with WXP
SP2 and even:

1. The firewall is down.
2. Applied the commands:
netsh firewall set service RemoteAdmin enable
netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135
3. Did the workaround suggested in:
http://support.microsoft.com/kb/909444
4. Ran the workaround in: http://support.microsoft.com/kb/884573
5. Added the account I use to access by Wbemtest with to the Default
Lauch Permissions with Full Control.
6. Checked up the registry
:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\forceguest
were set to 0.

I keep not being able to connect, and receving 0x80070005, what I'm
missing?!?!?!?Are you looking for SQL Server Integration Services help?|||

Did you ever get a response or resolution to this?

Hopefully, your system isn't still DOA 7-8 months later.

We have exactly the same issue with a brand-new server running WinServer 2003x64 R2

Have installed/reinstalled the entire OS three times now. The solution I guess is to do yet another reinstall and then not install the rogue security patch but that would leave us vulnerable to whatever the security patch was trying to fix and who knows what future M/Soft update won't do something similar?

|||Are you having SSIS problems when trying to connect to the SSIS service via Management Studio?

http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/

INF & IND errors

How do I update or delete a record with this floating point error in X
field? And, what the heck causes it?
Thanks!
Chris
Are you seeing 1.#inf values? That generally has something
to do with Microsoft's implementation of the IEEE standard
for floating point values - there are some values where the
representation of the value gets hosed out.
Are you trying to use column X in the where clause of your
delete or update statement?
-Sue
On Fri, 7 Oct 2005 10:43:54 -0400, "Chris Marsh"
<cmarsh@.synergy-intl.com> wrote:

>How do I update or delete a record with this floating point error in X
>field? And, what the heck causes it?
>Thanks!
>Chris
>

INF & IND errors

How do I update or delete a record with this floating point error in X
field? And, what the heck causes it?
Thanks!
ChrisAre you seeing 1.#inf values? That generally has something
to do with Microsoft's implementation of the IEEE standard
for floating point values - there are some values where the
representation of the value gets hosed out.
Are you trying to use column X in the where clause of your
delete or update statement?
-Sue
On Fri, 7 Oct 2005 10:43:54 -0400, "Chris Marsh"
<cmarsh@.synergy-intl.com> wrote:

>How do I update or delete a record with this floating point error in X
>field? And, what the heck causes it?
>Thanks!
>Chris
>

Inexplicable Invalid Syntax error

I created a DB in Access.. then I converted it to work with SQL Server. I have several tables and queries that are working just fine with it.

However, the other day I made a few changes to one of the tables ( Order table), and it seems no matter what kind of query I do with it, it always returns this error:

Invalid syntax near the keyword 'Order'

The query is IDENTICAL to about 10 others like it.. but no matter what kind of query I do to this Order table.. it always returns the same error. I've stepped through it, looked at the query builder, and there's nothing wrong with the format. Here is what the code looks like..

strSQL = "SELECT OrderId FROM Order WHERE SessionId = '"
+ "testfudgeddata" + "' ";
cm.CommandText = strSQL;
dr = cm.ExecuteReader();

while( dr.Read() )
{
orderId = dr.GetInt32(0);
}

It always fails when ExecuteReader() gets called.. with the same error. The syntax is exactly the same as many other queries.. and I even typed in 'testfudgeddata' into the db directly. I am thinking this problem might be related to some proxy-type information that I need to clean out due to the change of the name of this table since it was created in Access.. anyone ever see anything like this?

BrentORDER is a reserved word in SQL Server. change the table name (!)|||Try placing the table name in square brackets.|||AAAAK!

It seems every time I ask for help, I figure out the problem right after. I ran Query Analyzer and was a little confused why my Order table text was blue.. and the other tables were black.

Order is KEYWORD!! DUH!!

Anyways, it's a little funny I guess.. hope you can all get a good laugh from my misery.. heh|||Thanks for the reply guys.. looks like all 3 of us posted at exactly the same time. ;)sql

Inernet Explorer: "Illegal Operation"

Okay, I've tried everything else, so maybe someone out there on the great wi
de web can help!
Just after my system boots I start getting error messages:
IExplorer: This program has performed an illegal operation and will be shut
down.
Details: This program has caused a page fault in flt.dll.
When I try to close the dialog,it just keeps popping back up.
My computer usually freezes somewhere during all of this.
ctrl+alt+delete doesn't help, because then I get the message that IE isn't r
esponding.
My ScanDisk won't run because it says some program is running and it keeps r
estarting. The same thing happens iht the Defragmenter.
Okay someone tell me what to do!
DeloresThis newsgroup has nothing to do with Internet Explorer, it's a SQL Server
newsgroup. You might try asking in an Internet Explorer newsgroup such as
microsoft.public.windows.inetexplorer.ie6.browser , or
http://communities2.microsoft.com/c...&lang=en&cr=US.
Good luck,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Delores" <msdrako223@.aol.com> wrote in message
news:FEF5236E-C8F1-46F3-9F5D-180029181CCA@.microsoft.com...
> Okay, I've tried everything else, so maybe someone out there on the great
wide web can help!
> Just after my system boots I start getting error messages:
> IExplorer: This program has performed an illegal operation and will be
shut down.
> Details: This program has caused a page fault in flt.dll.
> When I try to close the dialog,it just keeps popping back up.
> My computer usually freezes somewhere during all of this.
> ctrl+alt+delete doesn't help, because then I get the message that IE
isn't responding.
> My ScanDisk won't run because it says some program is running and it keeps
restarting. The same thing happens iht the Defragmenter.
> Okay someone tell me what to do!
> Delores
>|||This is, what, the 5th IE request in the last few days? How are these
people finding this group?
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:OqWByM8$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> This newsgroup has nothing to do with Internet Explorer, it's a SQL Server
> newsgroup. You might try asking in an Internet Explorer newsgroup such as
> microsoft.public.windows.inetexplorer.ie6.browser , or
>
http://communities2.microsoft.com/c...s/default.aspx?
dg=microsoft.public.windows.inetexplorer.ie6.browser&cat=en-us-win-winclient
-internetexplorer&lang=en&cr=US.
> --
> Good luck,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Delores" <msdrako223@.aol.com> wrote in message
> news:FEF5236E-C8F1-46F3-9F5D-180029181CCA@.microsoft.com...
great
> wide web can help!
> shut down.
> isn't responding.
keeps
> restarting. The same thing happens iht the Defragmenter.
>|||We occasionally go through periods like this, and have been for the past
couple of years. I suspect it happens every time some new website operator
discovers newsgroups and decides to make them available from their site.
Then when their users run into an IE problem, they start posting to
everything they see with "security" in the name. I've asked 3 or 4 times how
they got to this newsgroup and the only person who ever responded to me
didn't really say how they found it. I think it's just something we're going
to have to live with... :-(
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:%23fJKc3TAEHA.220@.TK2MSFTNGP09.phx.gbl...
> This is, what, the 5th IE request in the last few days? How are these
> people finding this group?
>
> "Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
> news:OqWByM8$DHA.1212@.TK2MSFTNGP12.phx.gbl...
Server
as
>
http://communities2.microsoft.com/c...s/default.aspx?
>
dg=microsoft.public.windows.inetexplorer.ie6.browser&cat=en-us-win-winclient
> -internetexplorer&lang=en&cr=US.
> rights.
> great
> keeps
>

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?
Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced i
n
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustere
d)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the larges
t
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I ca
n
> do? Any suggestions?