Querying Information_Schema.Routines Shows Incomplete Information

Oct 14, 2007

We have a legacy app where some of our web page urls were hardcoded into the stored procs (SQL 2000 SP4 database). We have changed the system and so changed the hardcoded strings with a value stored in a config table. We used the following query to identify the hardcoded urls (say LegacyPage.asp) €“

select routine_name
from information_schema.routines
where routine_definition like '%LegacyPage.asp?%'

However, even after this we keep getting issues with LegacyPage.asp being referenced. Tracing the code, I found that there is at least one SP (say spHardCoded) which does not turn up in the query, but does have the string LegacyPage.asp? in the routine definition. When I run the following query €“

select routine_name
from information_schema.routines
where
routine_name = 'spHardCoded'
and routine_definition like '%LegacyPage.asp?%'

0 rows are returned!

Am I missing something obvious here or is INFORMATION_SCHEMA.Routines metadata not always updated? Is there any way to force the metadata to be updated, before we query it? Is there a better system catalog view which lets me do the same thing? Any help would be really appreciated.

View 3 Replies


ADVERTISEMENT

Querying Information_schema

Oct 24, 2007

Hi,

I'd like to get information about tables defined in the SqlCe20 database. So I tried to use the following which doesn't work:


string sql = "SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"cmd = new SqlCeCommand(sql, connection);cmd.Parameters.Add(newSqlCeDataParameter("A", DbTypes.NVarChar);cmd.Prepare();//later in codecmd.Parameters[0].Value = tableName;reader = cmd.ExecuteReader();while (reader.Read())....
Here the reader.Read() returns always false indepent of the table name.
In contrast doing the following without parameter works as expected:


reader = cmd.ExecuteReader(string.Format("SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName));
while (reader.Read())

Because I have to execute this statemen often I have to use prepared statement with parameters.

How can I get it working?

Miroslaw

View 2 Replies View Related

SQL 2012 :: Agent Job History Shows Step Still Running But Shows Start And End Times?

Jul 1, 2015

I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.

I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?

what conditions a job step my be showing in the job history as both running AND completed successfully?

View 6 Replies View Related

RAISERROR In CLR Routines

Jan 9, 2007

When I use the
following code to execute a RAISERROR from within a CLR Routine (Stored
Procedure), and I call this CLR stored procedure from T-SQL within a
TRY/CATCH block, the error is not caught in the CATCH block. Why is
this happening? Is there any way around this? Any help much appreciated.try { SqlContext.Pipe.ExecuteAndSend(cmd); } catch { } 

View 3 Replies View Related

Backup Routines

Sep 26, 2006

Hi there,

I am looking for some advice and opinions on daily backup routines on SQL2000 and SQL2005, I want to know what peoples best practices are for nightly full backups. Currently I have the following in place,

job: daily backup

step1. truncate log
step2. shrink log
step3. backup
step. updateusage

job weekly admin

step1. defrag indexes (sometimes re-index + update stats)
step2. truncate log
step3. shrink db
step4. checkdb


Does this look good enough?
is there anything else I should add?
What do you have in place currently?

Thanks for your help in advance

View 4 Replies View Related

RAISERROR In CLR Routines

Jan 9, 2007

When I use the following code to execute a RAISERROR from within a CLR Routine (Stored Procedure), and I call this CLR stored procedure from T-SQL within a
TRY/CATCH block, the error is not caught in the CATCH block. Why is
this happening?







try { SqlContext.Pipe.ExecuteAndSend(cmd); } catch { }

View 10 Replies View Related

Maintenance Routines Failing

Nov 20, 2001

Hi, I recently migrated from SQL*Server v7.0 to v2000. The maintenance routines on the new v2000 was set up (this is also a new server - completely new machine) are not running. When they attempt to run, I get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

Consequently, no backups no repair, the only thing in the maintenance routine that does work is the rebuilding of indexes. Any ideas would be greatly appreciated.

Thanks in advance,
Nancy

View 4 Replies View Related

Incomplete Rs Returned From Sp - Where's The Beef?

Aug 23, 2000

HI ALL,

I am executing a stored procedure (SQL7) which works fine in Query Analyser, but returns only a partial result set to my ASP application:

Query:

SELECT <some fields> FROM <table> WHERE <field> IN ('<text1>','<text2>','<text3>')

My ASP renders the returned recordset, but only results for the <text1> match are displayed!

I had an idea that I may be receiving multiple recordsets from the sp, so I added:

<% Do Until rS Is Nothing
Do While Not rS.Eof %>
----render rows to table here
<% rS.MoveNext
Loop
set rS = rS.NextRecordset
Loop %>

... to no avail.

I even tried changing my SELECT to use <field>='<text1>' OR <field>='<text2>' OR <field>='<text3>'

... same problem

Anyone know where my data is 'hiding'???

Cheers,
Graeme

View 1 Replies View Related

HELP! Incomplete Data In SQL Server Table.

Jul 20, 2005

Hi SQL Server Experts,I really need some help ASAP.I thank you in advance.We have an ASP/MS SQL Server Shopping cart application,developed by anindependent developer.We really have a problem where the Order_detailsare not getting stored completely.The concerned Tables are:Table1 Order_InfoStores Order_ID,customer data,Total_Amount,Shipping details etc.(One Record per Order.)Table2 Order_detailsStores Order_ID,Product_Id,Price and Quantity.(Multiple Records per Order.Basically stores the shopping cart items,one by one.)Table3 Payment_InfoStores Order_ID and Transaction Details(One Record per Order.)Now after a customer checks out and the payment transaction has beenapproved the order details are stored this way.Step 1: First a new record is added to Table1 (Order_Info).Step 2: A 'For' loop adds new records - one for each item in the cart-to Table2(Order_details).Step 3: A new record is added to the Table3 (Payment_Info).Recently, we have been having problems with Step 2, where only apartial list of the item details are stored in the Database and Step 3is not executed at all.And we don't have the problem all the time.What could be the problem with Step 2 where a series of record areadded to a table continuosly? does this make SQL Server "too busy" sothat subsequent Add operations are not done?Can someone see an apparent problem?Any solution is greatly appeciated.Please HELP ME!Thanks.Sriram

View 6 Replies View Related

Error Message Setting Up System DSN: Setup Routines Not Found. Please Reinstall Driver.

Apr 24, 2006

Hi,

I'm running W2K3 SP1. The MDAC Configuration Checker reports MDAC 2.8 SP2. The only discrepancy is MSADCO.DLL, where the expected version is 2.82.1830.0 and my actual version is 2.82.2651.0 (svr03_sp1_gdr.060301-1546).

The error message I'm getting when I use the ODBC Administrator dialog to set up a new entry is: "The setup routines for the SQL Server ODBC driver could not be found. Please reinstall the driver."

This is followed by a dialog titled: "Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed." The body text of the dialog is "Component not found in the registry."

Does anyone have any idea how I can repair this?

Thanks,

View 13 Replies View Related

Why Can't I Connect To SQLServer With An Incomplete Conn. String ?

Dec 23, 2004

I found out that I could use SQLDMO to enumerate objects in a SQL server. However, I can't connect to my server, even though I have both "windows and SQL server" authentication on "(local) Windows NT".

Here is the code (it does not contain the validation in this snippet):


Imports System.Data.SqlClient
Imports SQLDMO

Public Class ADO_2
Inherits System.Web.UI.Page
Protected WithEvents txtQuery As System.Web.UI.WebControls.TextBox
Protected WithEvents txtresults As System.Web.UI.WebControls.TextBox
Protected WithEvents lstDB As System.Web.UI.WebControls.DropDownList
Protected WithEvents lblResult As System.Web.UI.WebControls.Label
Protected WithEvents cmdExecute As System.Web.UI.WebControls.Button
Protected WithEvents cmdNext As System.Web.UI.WebControls.Button

Private m_objConn As New SqlConnection()
Private m_objSQLServer As SQLDMO.SQLServer

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

EnumerateDatabases()

'TEMP
lstDB.Items.Add("Northwind")
lstDB.Items.Add("Pubs")
'END TEMP
End Sub

Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload

m_objConn.Close() 'Just in case.....
End Sub


Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click

Dim objCommand As SqlCommand
Dim objDataReader As SqlDataReader
Dim intField As Integer

txtresults.Text = String.Empty
m_objConn.ConnectionString = "server=radu;database=" & lstDB.SelectedItem.Text & ";integrated security=SSPI;"
m_objConn.Open()

objCommand = New SqlCommand(txtQuery.Text, m_objConn)
objDataReader = objCommand.ExecuteReader

While objDataReader.Read
txtresults.Text &= vbNewLine
For intField = 0 To objDataReader.FieldCount - 1
txtresults.Text &= objDataReader(intField).ToString.PadRight(15)
Next
End While

objDataReader.Close()
objDataReader = Nothing
objCommand = Nothing
m_objConn.Close()

End Sub

Private Sub EnumerateDatabases()

On Error GoTo EnumerateDatabasesError

Dim objSQLDatabase As SQLDMO.Database

m_objSQLServer = New SQLDMO.SQLServer()
m_objSQLServer.Connect("radu")

'Iterate through all databases on the target server:
For Each objSQLDatabase In m_objSQLServer.Databases
lstDB.Items.Add(objSQLDatabase.Name)
Next objSQLDatabase

EnumerateDatabasesExit:
objSQLDatabase = Nothing
m_objSQLServer.Close()
m_objSQLServer = Nothing
Exit Sub

EnumerateDatabasesError:
lblResult.Text = Err.Number.ToString & ", " & Err.Description
Resume EnumerateDatabasesExit
End Sub
End Class


Why can't I connect with m_objSQLServer.Connect("radu") ? I also tried m_objSQLServer.Connect("radu", "", ""). Otherwise, I can connect just fine with m_objConn.Open() (after I select a db from the dropdownlist, which is for now populated by hand)....

Thanks a lot for your time.

Alex.

View 1 Replies View Related

Report Manager Home Page Is Incomplete

May 11, 2007



First off, forgive the ignorance that I'm sure to display in this post. I'm a total newbie with SRSS and have been using it to follow along with the tutorials offered via the SQL Express website. I'm using it strictly for learning on my local machine for the time being.



All had been working well with Reporting Services until I upgraded Vista Ultimate 64-bit (which was running in free 30-day trial unactivated Evaluation mode) sideways to Vista Ultimate Upgrade Edition 64-bit Edition. (Sounds confusing, I know.) I also subsequently installed a SQL Server 2k5 critical update which was recommended by Windows Update.



Now when I attempt to access Report Manager via localhost the "Home page" is incomplete, showing only the SSRS "Home" page header. There are no properites, data sources, config options, etc. outside of links for "Home|Help". I am running IE as an Admin and all the Reporting Services Configuration settings are green.



Also, when I attempt to deploy from VS 2005 I now receive the error: "Permissions granted to user 'NT AuthorityNetwork Service' are insufficient ..."



Some of the settings inside the Report Sever Config are as follows:



Windows Service Identity:

Service Acct: NT AuthorityNetwork Service; Built-in Account: Network Service



Web Service Identity:

Asp.net Service Account; NT AuthorityNetwork Service; Report Server and Manager are both using Classic.Net AppPool



I've attempted for hours to weed through Google and this forum but have repeatedly wound up back where I started and more confused than when I started. I would deeply appreciate any help in resolving this matter.

View 4 Replies View Related

Incomplete Print Output From ReportViewer Control

Dec 29, 2006

We are experiencing a problem where a server report is rendered in the
ReportViewer control successfully but when the user chooses to print the
content, the print output is incomplete (ex. only 18 of 23 pages are printed,
etc.).

This is happening consistently and has been reproduced for multiple
different reports. It does not happen if the same report is printed via the
Report Manager web interface.

I have also found that if I switch to Print Layout mode and then initiate
the print from the ReportViewer that the problem does not occur. This is our
current workaround, but it would be nice to know what is causing this and
have it fixed.

Has anybody else seen this? Is this a known issue with my current version
(SQLRS 2005 with SP1 applied).

Thanx in advance.

View 3 Replies View Related

Asynchronous Outputs Technet Example Wrong Or Incomplete

Oct 22, 2007

On URL: http://technet.microsoft.com/en-us/library/ms135931.aspx among other things there is an VB example for "Creating and Configuring Output Columns" of Custom Transformation Component with Asynchronous Outputs:

Public Overrides Sub OnInputPathAttached(ByVal inputID As Integer)


Dim input As IDTSInput90 = ComponentMetaData.InputCollection.GetObjectByID(inputID)
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection(0)

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()


For Each vCol As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection


Dim outCol As IDTSOutputColumn90 = output.OutputColumnCollection.New()

outCol.Name = vCol.Name

outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage)


Next
End Sub


When I copy-paste given code into ScriptMain of Ascynchronous Script Transformation Component I receive error message "sub 'OnInputPathAttached' cannot be declared 'Overrides' because it does not override a sub in a base class."

View 1 Replies View Related

SQL 2000 Report Services Incomplete Export To Excel

Dec 10, 2005

I have a report that I am trying to export to excel.  The export seems to work but the data in the spread sheet is incomplete.  The document map is completely exported but the work sheets are not.  The links in the document map stop being links after the first occurrence of the lowest level detail.   All the data is visible in Report services and all the data exports to PDF.   There are 5 levels and 124 pages in the report in the report.   I have other reports on the same server that are larger (7 levels and 512 pages) and they work just fine.  They export to excel with no problem.   Any ideas or input would be very appreciated.   Ayla

View 8 Replies View Related

Information_schema

Mar 28, 2008

 Hi to all,

                I'm using SQL Server 2005. May I know where the INFORMATION_SCHEMA
is found, can we view this by object explorer.

 

View 2 Replies View Related

Information_schema ?

Feb 24, 2004

Hello,

When the procedure : sp_tables is executed (master db), the table owners are either dbo or INFORMATION_SCHEMA,
any detail about this last ? (to be precise, table_type is view and not table).

Thanks

View 1 Replies View Related

Information_schema.columns

Oct 18, 2005

Hi:

I need to change a column default to '' and not null for 1500 databases accross 10 servers.

if exists (select * from information_schema.columns
where table_name = 'tblABC'
and column_name = 'columnX'
and data_type = 'VARCHAR'
and is_nullable = 'No'
and column_default = '('')'
)
begin
--do something to implement
end

Here, there is a problem for and column_default = '('')'
I have tried ''''+'('')' + '''' or "'('')'", neither works.
Do you have any idea to deal with the ('') ?

thanks
-D

View 3 Replies View Related

Using INFORMATION_SCHEMA With An ACCESS DB

Feb 13, 2007

I'm having a really hard time displaying the structure of my database using INFORMATION_SCHEMA. Can this be used with Access? I've used it before for sql databases but when I try it with this access one I get an error message telling me: Can't find file c:...INFORMATION_SCHEMA.mdb

I'm hoping maybe there's another command that works just as well or some other way to get around it.

I'm using Visual Web Developer 2005 Express
and my .aspx pages are coded using VB so any insights would be awesome!

Thanks much,
~UNI

View 1 Replies View Related

Information_schema Permissions

Jul 20, 2005

Hi I need to see all the indexes in a database. The ID has dbo rightsto the database, but not to the master. I can't see anything inINFORMATION_SCHEMA.CHECK_CONSTRAINTS orINFORMATION_SCHEMA.KEY_COLUMN_USAGEAn sa ID for the master sees everything however.Thanks for your helpPachydermitis

View 5 Replies View Related

SQL 2000 And 2005 Report Services Incomplete Export To Excel Because Of Page Break

Apr 4, 2007

I have a Sales report with 3 lists each being nested inside of the other and they also each contain a Matrix. Top list is for Region, followed by District and then State. I have page breaks setup so that each region appears on it's own page, followed by each District summary appearing at the top of the page with a breakdown on that page for that Districts states. In total the report exported to PDF or printed runs around 19 pages. This report when exported to PDF or printed works fine.

When I export it to excel I get a full document map on the first worksheet. I then get 10 worksheets of information breaking off the last 9. The document map links reflect the absence of this data by not actually being links from that point on. If I remove page breaks so that the number of pages created are less than 10 excel worksheets the export works fine and my document map links are created.

So does the export to Excel have a limitation on the number of Worksheets that can be created? In my case it appears to happen at 10 each time. Oh and by the way I've attempted this in VS.2003 and 2005 and SQL Reporting Services 2000 and 2005 for the same reports.


Thanks for your assistance.

View 1 Replies View Related

INFORMATION_SCHEMA Login In SysUsers?

Dec 26, 2001

Hiya,
I'm trying to run reports, some of which have UserEntered as a criteria. So I'm filling up a list with (SELECT * FROM SysUsers WHERE IsLogin = 1), which is also pulling up INFORMATION_SCHEMA as a login. I'm not really sure why this would be an islogin = 1 or issqluser = 1. Is this a server default login,and if yes,why, and what can I do about it?

Thanks,
Sarah

View 1 Replies View Related

SQL2005 Information_schema && Default

May 10, 2006

Hi:

from SQL2005 information_schema, I don't see anything related to default constraint.

So, I still need the old way to use old stuff such as
from sysobjects sobj inner join syscolumns scolumn.
I need to check existing 3000 databases to drop any possible default for tableA.columnB's default and then add a new one.

I thought SQL2005 will stop let us to query system table to retrieve table structure info including default, but only via information_schema? :rolleyes:
thanks
David

View 3 Replies View Related

Information_schema For Temp Table ?

Nov 26, 2007

Hi there!

I'm trying to find how can I get the information_schema for a temp table.

I'm trying to find all columns of a temp table.

So it will be something like this SELECT * FROM information_schema.columns

But it doesn't work for temp table, I tried tempdb.dbo.information_schema.columns .... nada....


Please help!


Thanks,

Or Tho

View 9 Replies View Related

How To View INFORMATION_SCHEMA.TABLES

Jun 20, 2007

Hi:

I am new to SQLSERVER, so I am trying to learn from all these database views, in Oracle it use 'desc all_tables ' to database dictionary view, can some one tell me how to view SQLserver dictionary view like INFORMATION_SCHEMA.TABLES, or sys.tables?

IF I login as sa, but I only want to view the table list one schema at a time? how do I do that?

I tried :

select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT'....

select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT.DBO'.... give 0 result too.

it give me 0 result, but if I login as CIT, then

'select table_name from INFORMATION_SCHEMA.TABLES' will give 14 tables.

Plus, When I log into Query analyzer, all the tables has dbo. prefix. , why is that?

Thanks a lot

View 3 Replies View Related

Information_schema.tables, Triggers, Cursors

Jul 8, 2003

how do i return only the tables created by the user?
in three of my databases i am inserting one record per 5 secs. in all the tables. how good is using triggers for 'insert instead of' for these tables?

View 5 Replies View Related

Need To Access INFORMATION_SCHEMA On Linked Server

Apr 5, 2006

Hi,
I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
On the local server, I can do
select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local

select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked

but not

select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]

How can I access the INFORMATION_SCHEMA on the linked server ?

Thanks,
Helena

View 3 Replies View Related

Invalid Object Name 'INFORMATION_SCHEMA.tables'

Nov 30, 2007

The following code example to returns table space usage in my databases, except for AdventureWorks.

When I "USE AdventureWorks", I get the following message. "Invalid object name 'INFORMATION_SCHEMA.tables'."

Why is AdventureWorks different? Thanks. Bill


USE AdventureWorks

-- Declare variables

DECLARE @AWTables TABLE (SchemaTable varchar(100))

DECLARE @TableName varchar(100)

--Insert Table names into the TABLE variable

INSERT @AWTables

(SchemaTable)

SELECT TABLE_SCHEMA + '.' + TABLE_NAME

FROM INFORMATION_SCHEMA.tables

WHERE TABLE_TYPE = 'BASE TABLE'

ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME

-- Report on each table using sp_spaceused

WHILE (SELECT COUNT(*) FROM @AWTables) > 0

BEGIN

SELECT TOP 1 @TableName = SchemaTable

FROM @AWTables

ORDER BY SchemaTable



EXEC sp_spaceused @TableName

DELETE @AWTables

WHERE SchemaTable = @TableName

END

View 4 Replies View Related

ForeignKey Using INFORMATION_SCHEMA.TABLES As PrimaryKeyTable

Oct 12, 2006

Hi,

I have a "master" table that holds the names of data tables (one record in the "master" table for each "data" table).

Can I create a ForeignKey constraint that will prevent the "master" table records from being removed if the cooresponding "data" table exists? Is the way to do this to use INFORMATION_SCHEMA.TABLES as the PrimaryKeyTable for the ForeignKey?

Thanks!

View 1 Replies View Related

INFORMATION_SCHEMA Query Question: Constraint Columns

Jul 20, 2005

Hi Folks:I'm a little new to SQLServer, so please pardon my ignorance!I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, andTABLE_CONSTRAINTS. I'm looking for the views that will give me the list ofcolumns by constraint.For instance, if Table1 has a unique key called Table1_UK01, I can find thatunder INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know thecolumns in that UK constraint. I've triedINFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE andINFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this usertable doesn't seem to show up in either of those views.Can anyone point me in the right direction? Any sample queries would betremendously appreciated. I'm going to be using this meta-data toautomatically generate quite a bundle of stored procs that do updates basedon finding rows via unique keys...TIA,Dave

View 3 Replies View Related

Restricting Access To Sys And INFORMATION_SCHEMA Views In ODBC

Oct 23, 2007

Hi
I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).

However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.

How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?

I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects).
I'm stuck - help!

View 4 Replies View Related

SQL2005 Collation Problem Between Fn_ListExtendedProperty And Information_schema.Tables ?

Apr 25, 2008

Hi,

we've just installed SQL2005 and, as expected, are hitting problems with collation settings.

We have made sure that the collation of our new server is the same as the old (working) SQL2000 server (both are Latin1_General_CI_AS) but a function that works fine on the 2000 box just doesn't work on the 2005 box.

The problem is this...




Code Snippet
select *
from information_schema.[tables] as t
left join :: fn_ListExtendedProperty('MS_Description','user','dbo','table',null,null,null) as xp on ( xp.objname = t.table_name )






...where that string comparison on "table_name" results in...

"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation."


Now... I can't see where the "Latin1_General_CI_AI" is coming from. The two data sources are both "system" sources, so should, I would have thought, been the same. If I do...

execute sp_help "information_schema.tables"


...all the columns are Latin1_General_CI_AS. If I do...

execute sp_help fn_ListExtendedProperty


...the same collation is returned for all columns.

I've checked everything I can think of (server, master, tempdb, etc.), and everything seems to be "AS".

Where might this "AI" collation be defined ? We have only just installed 2005, so a re-install, while a pain, is not a massive problem, but what should we check/change ?

Thanks

View 1 Replies View Related

COLID Discrepancy In Syscolumns / Information_Schema.Columns - SQL Server 2000

Jul 20, 2005

I ran the following query in Query Analyzer for a 7 column table.SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BYc.colidThe results were:I_CSD 1X_STE_XML2I_USR_LCK4T_CRT_RCD5I_USR_CRT_RCD6T_UDT_RCD7I_USR_UDT_RCD8If I use the information_schema view (SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_name ='CSD_XML') I get the same results.The problem is that the colids go from 2 to 4 and the colids gothrough 8 when there are only 7 columns.At one time there was another column in the table, but it has sincebeen dropped and isn't there anymore. It seems that the colids insyscolumns did not update when the column was dropped.Is this because of the way I dropped the column? Is there anything Ican do now that it has happened?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved