Error when updating

I get the following error when I execute the statement below. 'Invalid
length parameter passed to the substring function'
Update Tbl_Txn_UT
Set Fxd_Units =
CASE
WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%' and TxnUnitsDur like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%')
THEN TxnUnitsBase
WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
TxnUnitsBase
Else TxnUnits
END
Where TxnUnits is not Null
It took a while to get this code correct with help and now evidently one of
the answers from the math causes an error. Any help is appreciated.Hi
Posting DDL an example data would help to see what you are trying to achive
and help re-create the problem. The statement is assuming that TxnUnitsDur i
s
at least one character which could is probably causing the problem.
John
"Job" wrote:

> I get the following error when I execute the statement below. 'Invalid
> length parameter passed to the substring function'
> Update Tbl_Txn_UT
> Set Fxd_Units =
> CASE
> WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%' and TxnUnitsDur like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
> WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%')
> THEN TxnUnitsBase
> WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
> TxnUnitsBase
> Else TxnUnits
> END
> Where TxnUnits is not Null
>
> It took a while to get this code correct with help and now evidently one o
f
> the answers from the math causes an error. Any help is appreciated.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Ju
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Hi
You seem to have some problem posting?
John
"Job" <Job@.nomail.com> wrote in message
news:uUH$jpCoFHA.708@.TK2MSFTNGP09.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>|||John, your correct. I included the case statement if len(TxnUnitsDur )
= 0 then TxnUnitsBase and it worked perfectly.
Cheers!
Job wrote:
> Ju
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>
>
>

Error when trying to modify a table in Sql Server 2005 beta 2

Hi there.
All the times when I try to modify a table, I receive a COM error and the
modify windows is not showed. Below you can see the exception message. That
way, I am unable to modify a table using Sql Server 2005 beta 2. Any
suggestions?
************** Exception Text **************
System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT
E_FAIL has been returned from a call to a COM component.
at
Microsoft.VisualStudio.DataTools.UI.Grid.IDTGridSt orage.GetCellDataAsString(Int64 nRowIndex, Int32 nColIndex)
at
Microsoft.VisualStudio.DataTools.UI.Grid.DTGridSto rageWrapper.GetCellDataAsString(Int64 nRowIndex, Int32 nColIndex)
at
Microsoft.SqlServer.Management.UI.Grid.GridTextCol umn.DrawCell(Graphics g,
Brush bkBrush, Brush textBrush, Font textFont, Rectangle rect, IGridStorage
storage, Int64 nRowIndex)
at
Microsoft.SqlServer.Management.UI.Grid.GridControl .DoCellPainting(Graphics g,
SolidBrush bkBrush, SolidBrush textBrush, Font textFont, Rectangle cellRect,
GridColumn gridColumn, Int64 rowNumber, Boolean enabledState)
at
Microsoft.SqlServer.Management.UI.Grid.GridControl .PaintOneCell(Graphics g,
Int32 nCol, Int64 nRow, Int32 nEditedCol, Int64 nEditedRow, Rectangle& rCell,
Rectangle& rCurrentCellRect, Rectangle& rEditingCellRect)
at Microsoft.SqlServer.Management.UI.Grid.GridControl .PaintGrid(Graphics g)
at
Microsoft.SqlServer.Management.UI.Grid.GridControl .OnPaint(PaintEventArgs pe)
at System.Windows.Forms.Control.PaintWithErrorHandlin g(PaintEventArgs e,
Int16 layer, Boolean disposeEventArgs)
at System.Windows.Forms.Control.WmPaint(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at Microsoft.SqlServer.Management.UI.Grid.GridControl .WndProc(Message& m)
at Microsoft.VisualStudio.DataTools.UI.Grid.GridContr ol.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage (Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(M essage& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)
Thanks.
Carlos
Senior Developer
www.byteshift.com
Regarding SQL Server 2005, you should probably post to the beta newsgroups
http://communities.microsoft.com/new...r2005&slcid=us
If you want to access the newsgroups via your newsreader just scroll through
the top right hand frame for connection information
Keith
"Carlao" <Carlao@.discussions.microsoft.com> wrote in message
news:0646E034-A0F4-4692-9B3A-48A790A48FAC@.microsoft.com...
> Hi there.
> All the times when I try to modify a table, I receive a COM error and the
> modify windows is not showed. Below you can see the exception message.
That
> way, I am unable to modify a table using Sql Server 2005 beta 2. Any
> suggestions?
> ************** Exception Text **************
> System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT
> E_FAIL has been returned from a call to a COM component.
> at
>
Microsoft.VisualStudio.DataTools.UI.Grid.IDTGridSt orage.GetCellDataAsString(
Int64 nRowIndex, Int32 nColIndex)
> at
>
Microsoft.VisualStudio.DataTools.UI.Grid.DTGridSto rageWrapper.GetCellDataAsS
tring(Int64 nRowIndex, Int32 nColIndex)
> at
> Microsoft.SqlServer.Management.UI.Grid.GridTextCol umn.DrawCell(Graphics g,
> Brush bkBrush, Brush textBrush, Font textFont, Rectangle rect,
IGridStorage
> storage, Int64 nRowIndex)
> at
> Microsoft.SqlServer.Management.UI.Grid.GridControl .DoCellPainting(Graphics
g,
> SolidBrush bkBrush, SolidBrush textBrush, Font textFont, Rectangle
cellRect,
> GridColumn gridColumn, Int64 rowNumber, Boolean enabledState)
> at
> Microsoft.SqlServer.Management.UI.Grid.GridControl .PaintOneCell(Graphics
g,
> Int32 nCol, Int64 nRow, Int32 nEditedCol, Int64 nEditedRow, Rectangle&
rCell,
> Rectangle& rCurrentCellRect, Rectangle& rEditingCellRect)
> at
Microsoft.SqlServer.Management.UI.Grid.GridControl .PaintGrid(Graphics g)
> at
> Microsoft.SqlServer.Management.UI.Grid.GridControl .OnPaint(PaintEventArgs
pe)
> at System.Windows.Forms.Control.PaintWithErrorHandlin g(PaintEventArgs
e,
> Int16 layer, Boolean disposeEventArgs)
> at System.Windows.Forms.Control.WmPaint(Message& m)
> at System.Windows.Forms.Control.WndProc(Message& m)
> at Microsoft.SqlServer.Management.UI.Grid.GridControl .WndProc(Message&
m)
> at
Microsoft.VisualStudio.DataTools.UI.Grid.GridContr ol.WndProc(Message& m)
> at System.Windows.Forms.ControlNativeWindow.OnMessage (Message& m)
> at System.Windows.Forms.ControlNativeWindow.WndProc(M essage& m)
> at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
> IntPtr wparam, IntPtr lparam)
> Thanks.
> Carlos
> --
> Senior Developer
> www.byteshift.com

error when trying to delete record

Windows 2000 Server sp4, SQL Server 2000 sp3
I get the error below when attempting to delete a record from a table called
Monitor directly from Enterprise Manager GUI.
error:
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Obje
ct Name]
'tsvw_KJDKFAJDFKJAKLFDJKLAJDFKJASKFDJKAJ
DF'
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Obje
ct
Name]UserA.Monitor
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Obje
ct Name]
'ctsv_DKFJDKJFKDJFKDKFJDKJFIEJIEJIEJFIEJ
FKJDKJ'
The KDJFKDJFKJDF above is obviously not exact. The actual error contains a
long string of random characters though.. If i'm not mistaken these are
system generated names for views related to replication.
1) could this be replication related?
2) Important note: UserA is NOT the current owner of the Monitor table!
UserA used to own the table but due to replication issues the owner was
changed to dbo a long time ago. ' Can I safely run the sp_changeobjectowner
procedure specifying dbo again or would I need to drop the merge replication
in order to do that?
3) I verified that the tsvw_ect... and the ctsv_ect... do not exist in the
database?
any suggestions about how to get to the bottom of this would be GREATLY
appreciated. Thanks.It seems you have some "invalid" merge triggers (probably from a previous
publication). Did you republish after changing your table owner name? If
you do have invalid triggers, you should consider dropping and recreating
your publication.
A. Drop the subscription
B. Drop the publication.
C. Delete the triggers that reference the non-existant views
D. Recreate the publication and subscription.
E. Run the snapshot.
reference:
How to manually remove a replication in SQL Server 2000 - ID: 324401
http://support.microsoft.com/?id=324401
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||Thank you! I did not completely drop and recreate the merge replication
setup but what you said is exactly what happened. I had an issue removing
repl before due to the owner of the tables not being dbo. I had to manually
clean out repl.. change table owners to dbo.. and recreate the replication.
It looks like one of the triggers from the older repl setup was still there.
I found the old trigger and deleted it. It was obvious it was old because it
referenced the old user name. There were the 3 correct triggers on the table
as well. One each for insert, update, and delete. So what was happening was
this: there was the old delete trigger on this table and it was listed first
so it executed and failed. The correct trigger never fired then. Now that
old trigger is gone and all works fine.
thanks again.
"Fany Vargas [MSFT]" <fanyv@.online.microsoft.com> wrote in message
news:Yj9qhVuzEHA.3388@.cpmsftngxa10.phx.gbl...
> It seems you have some "invalid" merge triggers (probably from a previous
> publication). Did you republish after changing your table owner name? If
> you do have invalid triggers, you should consider dropping and recreating
> your publication.
> A. Drop the subscription
> B. Drop the publication.
> C. Delete the triggers that reference the non-existant views
> D. Recreate the publication and subscription.
> E. Run the snapshot.
>
> reference:
> How to manually remove a replication in SQL Server 2000 - ID: 324401
> http://support.microsoft.com/?id=324401
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>

error when trying to delete record

Windows 2000 Server sp4, SQL Server 2000 sp3
I get the error below when attempting to delete a record from a table called
Monitor directly from Enterprise Manager GUI.
error:
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name]
'tsvw_KJDKFAJDFKJAKLFDJKLAJDFKJASKFDJKAJDF'
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object
Name]UserA.Monitor
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name]
'ctsv_DKFJDKJFKDJFKDKFJDKJFIEJIEJIEJFIEJFKJDKJ'
The KDJFKDJFKJDF above is obviously not exact. The actual error contains a
long string of random characters though.. If i'm not mistaken these are
system generated names for views related to replication.
1) could this be replication related?
2) Important note: UserA is NOT the current owner of the Monitor table!
UserA used to own the table but due to replication issues the owner was
changed to dbo a long time ago. ? Can I safely run the sp_changeobjectowner
procedure specifying dbo again or would I need to drop the merge replication
in order to do that?
3) I verified that the tsvw_ect... and the ctsv_ect... do not exist in the
database?
any suggestions about how to get to the bottom of this would be GREATLY
appreciated. Thanks.
It seems you have some "invalid" merge triggers (probably from a previous
publication). Did you republish after changing your table owner name? If
you do have invalid triggers, you should consider dropping and recreating
your publication.
A. Drop the subscription
B. Drop the publication.
C. Delete the triggers that reference the non-existant views
D. Recreate the publication and subscription.
E. Run the snapshot.
reference:
How to manually remove a replication in SQL Server 2000 - ID: 324401
http://support.microsoft.com/?id=324401
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thank you! I did not completely drop and recreate the merge replication
setup but what you said is exactly what happened. I had an issue removing
repl before due to the owner of the tables not being dbo. I had to manually
clean out repl.. change table owners to dbo.. and recreate the replication.
It looks like one of the triggers from the older repl setup was still there.
I found the old trigger and deleted it. It was obvious it was old because it
referenced the old user name. There were the 3 correct triggers on the table
as well. One each for insert, update, and delete. So what was happening was
this: there was the old delete trigger on this table and it was listed first
so it executed and failed. The correct trigger never fired then. Now that
old trigger is gone and all works fine.
thanks again.
"Fany Vargas [MSFT]" <fanyv@.online.microsoft.com> wrote in message
news:Yj9qhVuzEHA.3388@.cpmsftngxa10.phx.gbl...
> It seems you have some "invalid" merge triggers (probably from a previous
> publication). Did you republish after changing your table owner name? If
> you do have invalid triggers, you should consider dropping and recreating
> your publication.
> A. Drop the subscription
> B. Drop the publication.
> C. Delete the triggers that reference the non-existant views
> D. Recreate the publication and subscription.
> E. Run the snapshot.
>
> reference:
> How to manually remove a replication in SQL Server 2000 - ID: 324401
> http://support.microsoft.com/?id=324401
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>

error when trying to delete record

Windows 2000 Server sp4, SQL Server 2000 sp3
I get the error below when attempting to delete a record from a table called
Monitor directly from Enterprise Manager GUI.
error:
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name]
'tsvw_KJDKFAJDFKJAKLFDJKLAJDFKJASKFDJKAJDF'
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object
Name]UserA.Monitor
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name]
'ctsv_DKFJDKJFKDJFKDKFJDKJFIEJIEJIEJFIEJFKJDKJ'
The KDJFKDJFKJDF above is obviously not exact. The actual error contains a
long string of random characters though.. If i'm not mistaken these are
system generated names for views related to replication.
1) could this be replication related?
2) Important note: UserA is NOT the current owner of the Monitor table!
UserA used to own the table but due to replication issues the owner was
changed to dbo a long time ago. ' Can I safely run the sp_changeobjectowner
procedure specifying dbo again or would I need to drop the merge replication
in order to do that?
3) I verified that the tsvw_ect... and the ctsv_ect... do not exist in the
database?
any suggestions about how to get to the bottom of this would be GREATLY
appreciated. Thanks.It seems you have some "invalid" merge triggers (probably from a previous
publication). Did you republish after changing your table owner name? If
you do have invalid triggers, you should consider dropping and recreating
your publication.
A. Drop the subscription
B. Drop the publication.
C. Delete the triggers that reference the non-existant views
D. Recreate the publication and subscription.
E. Run the snapshot.
reference:
How to manually remove a replication in SQL Server 2000 - ID: 324401
http://support.microsoft.com/?id=324401
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||Thank you! I did not completely drop and recreate the merge replication
setup but what you said is exactly what happened. I had an issue removing
repl before due to the owner of the tables not being dbo. I had to manually
clean out repl.. change table owners to dbo.. and recreate the replication.
It looks like one of the triggers from the older repl setup was still there.
I found the old trigger and deleted it. It was obvious it was old because it
referenced the old user name. There were the 3 correct triggers on the table
as well. One each for insert, update, and delete. So what was happening was
this: there was the old delete trigger on this table and it was listed first
so it executed and failed. The correct trigger never fired then. Now that
old trigger is gone and all works fine.
thanks again.
"Fany Vargas [MSFT]" <fanyv@.online.microsoft.com> wrote in message
news:Yj9qhVuzEHA.3388@.cpmsftngxa10.phx.gbl...
> It seems you have some "invalid" merge triggers (probably from a previous
> publication). Did you republish after changing your table owner name? If
> you do have invalid triggers, you should consider dropping and recreating
> your publication.
> A. Drop the subscription
> B. Drop the publication.
> C. Delete the triggers that reference the non-existant views
> D. Recreate the publication and subscription.
> E. Run the snapshot.
>
> reference:
> How to manually remove a replication in SQL Server 2000 - ID: 324401
> http://support.microsoft.com/?id=324401
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>

Error when running RedGate SQL Backup Job in SQL Server 2005

I have 4 SQL Server 2005 production servers that I installed RedGate SQL Bac
k
up on. All but one run fine. The one that doesn't I get the below error.
Executed as user : AD-DomainName\UserName. SQL Backup job failed with
exitcode 0 SQL Error code 18456[SQLState 42000] (Error 50000) The step f
ailed.
The back up is backing up to the AD server which is different than the
server the job runs on. It is a shared folder on the AD and the permissions
grant the owner of the job Full Access.
I do not know what else to do with this issue. It mirrors the other 3 that
work fine.
Any and all help is appreciated.
-ChrisChris,
SQL Server caches login information from active directory. The one
troublesome machine has apparently cached out of date AD information. In
David Grey's BLOG he discusses a similar problem (not with Red Gate) that
may help you resolve.
http://www.codefounders.com/communi...entication.aspx
RLF
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2B7A5543-C258-4BA2-8CF4-BF2A7F4F1434@.microsoft.com...
>I have 4 SQL Server 2005 production servers that I installed RedGate SQL
>Back
> up on. All but one run fine. The one that doesn't I get the below error.
> Executed as user : AD-DomainName\UserName. SQL Backup job failed with
> exitcode 0 SQL Error code 18456[SQLState 42000] (Error 50000) The step
> failed.
> The back up is backing up to the AD server which is different than the
> server the job runs on. It is a shared folder on the AD and the
> permissions
> grant the owner of the job Full Access.
> I do not know what else to do with this issue. It mirrors the other 3 that
> work fine.
> Any and all help is appreciated.
> -Chris
>

Error when installing SQL 2000 clustering

See error below
"An error occurred while creating virtual server
resources for clustering. The system cannot find the file
specified."
The failure occured during the creation of the virtual server resources (which this error indicates). You must view Cluster.log (in the WINNT\Cluster folder) or Sqlclstr.log (or both). At this stage, you are past the
unattended setup processes, so you will typically see the "Installation Succeeded" messages in the Sqlstp<N>.log files.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

error when I run script

Hi All,

I can't not run the query to update data but it raice an error as below:

"Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

HDD free space: 5GB
RAM: 1GB
CPU: 2.8MHz

What should I do?
Please advice me on this.

Thanks in advance,
TNLooks like your TempDb is running out of space.

Are you the database administrator?

The Fonzi approach (not reccomended) would be to restart the SQL Server Service on the server because the tempdb is recreated each time SQL Server is restarted.

In the Enterprise Manager you can right click on the tempdb and check the file growth propeties and adjust them as necessary.

Finally, I wonder what your query might be doing to fill up the tempdb. Do you want to post your script or tell us what you are trying to do?

Error when field in ORDER BY does not match SELECT DISTINCT fields

This error crept up in SQL 2005 and does not appear in earlier versions of
SQL Server. The below code looks like:
SELECT DISTINCT 'Test' As TestName, ...
FROM ...
ORDER BY 'Test'
I had to change the ORDER BY to the alias TestName to get is to compile and
run in SQL2005. Is there a SQL command I can run to prior to adding my view
to prevent these messages? Or do I lump it and change all occurrences to us
e
the alias in the ORDER BY?
Thanks for your help,
TomOn Mon, 20 Mar 2006 10:27:30 -0800, Tom Kelley <Tom
Kelley@.discussions.microsoft.com> wrote:

>This error crept up in SQL 2005 and does not appear in earlier versions of
>SQL Server. The below code looks like:
>SELECT DISTINCT 'Test' As TestName, ...
>FROM ...
>ORDER BY 'Test'
>I had to change the ORDER BY to the alias TestName to get is to compile and
>run in SQL2005.
Hi Tom,
SQL Server 2000 was very forgiving WRT what it allowed you to put in an
ORDER BY clause. That had some great advantages, but some divantages
as well, since it allowed you to write rather ambiguous things, and it
would often interpret it different from what you meant. For instance, if
you still have access to a SQL Server 2000 installation, run the code
below and try to explain the results.
CREATE TABLE Persons
(FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL
)
go
INSERT INTO Persons (FirstName, LastName)
SELECT 'Hugo', 'Kornelis'
UNION ALL
SELECT 'Tom', 'Kelly'
go
SELECT LastName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName, FirstName AS LastName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY Persons.FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY FirstName + ''
go
DROP TABLE Persons
go
Anyway, SQL Server 2005 is more strict (though not nearly as strict as
what the official ANSI standards for SQL allow <g> ). You might have to
change some ORDER BY clauses.

> Is there a SQL command I can run to prior to adding my view
>to prevent these messages? Or do I lump it and change all occurrences to u
se
>the alias in the ORDER BY?
The ORDER BY clause doesn't only accept the alias - it accepts column
names and expressions as well (though apparently not an "expression"
that is a string constant).
Though I personally fail to see ANY reason why you would want to include
a constant value in your ORDER BY list.
Hugo Kornelis, SQL Server MVPsql

Error when deleting a folder in Report Manager

Hi
I received the error message pasted below, when I tried to delete a folder
through Report Manager. There have been no problems deleting folders, until
now. (SP1 has not, as of yet, been applied)
Does anyone know what could cause this error and what the best solution is
in the event it happens again?
"System.Web.Services.Protocols.SoapException: Server was unable to process
request. --> System.Data.SqlClient.SqlException: The COMMIT TRANSACTION
request has no corresponding BEGIN TRANSACTION. at
System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
TdsParserState state) at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
TdsParserState state) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at
System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler,
SqlDataReader dataStream) at
System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String
sqlBatch, String method) at
System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch,
String method) at System.Data.SqlClient.SqlTransaction.Commit() at
Microsoft.ReportingServices.Library.ConnectionManager.CommitTransaction()
at Microsoft.ReportingServices.Library.RSService.DisconnectStorage() at
Microsoft.ReportingServices.Library.RSService.CreateBatch() at
Microsoft.ReportingServices.WebServer.ReportingService.CreateBatch(String&
BatchID) -- End of inner exception stack trace --"
Thanks,
MarkThe problem was that the report server was out of disk space.
Mark
"MCC" <cmc_za@.hotmail.com> wrote in message
news:%23ClGvUpWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> Hi
> I received the error message pasted below, when I tried to delete a folder
> through Report Manager. There have been no problems deleting folders,
until
> now. (SP1 has not, as of yet, been applied)
> Does anyone know what could cause this error and what the best solution is
> in the event it happens again?
> "System.Web.Services.Protocols.SoapException: Server was unable to process
> request. --> System.Data.SqlClient.SqlException: The COMMIT TRANSACTION
> request has no corresponding BEGIN TRANSACTION. at
> System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> TdsParserState state) at
> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception,
> TdsParserState state) at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at
> System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand
cmdHandler,
> SqlDataReader dataStream) at
> System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String
> sqlBatch, String method) at
> System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch,
> String method) at System.Data.SqlClient.SqlTransaction.Commit() at
> Microsoft.ReportingServices.Library.ConnectionManager.CommitTransaction()
> at Microsoft.ReportingServices.Library.RSService.DisconnectStorage() at
> Microsoft.ReportingServices.Library.RSService.CreateBatch() at
> Microsoft.ReportingServices.WebServer.ReportingService.CreateBatch(String&
> BatchID) -- End of inner exception stack trace --"
> Thanks,
> Mark
>

Error when connecting to SQL Server

Hi

when i tried connecting to SQL Server Express I got this error, be it connecting via Windows Authentication or SQL Authentication. Pls see below for the error message:

===================================

Cannot connect to JOHNSON\SQLEXPRESS.

===================================

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

Error Number: -1
Severity: 20
State: 0

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

wats wrong with my setup? Previously it can connect. till i upgraded to SQL Server 2005 then uninstall my SQL Server 2005 then it become like tt....

what shld i do?

Pls advise.

Thanks

Johnson

Did you enable remote connections ? This is disabled by default. See the Screencast on my site for more information.

Jens K. Suessmeyer.

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

Hi,

THanks for the reply but when i look into ur screencast and followed the steps to enable remote connection, I got the below error when try to click on the Surface Area Configureation.

"===================================

No SQL Server 2005 components were found on the specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLSAC)

Program Location:

at Microsoft.SqlSac.MainPanel.FormServicesAndProtocols.ProcessClassConstructor()"

By the way y do i nd to enable remote connection when i am trying to connect to only my local SQLExpress?

I am using SQL Express and not SQL Server 2005.

Thanks

Johnson

|||Are you trying to configure the local computer ?|||

ya i m trying to access my SQL Mobile database using my SQL Server Management Studio Express. But when i tried connecting using SQL Server Management Studio Express to my local machine, I got thrown the error.

As mentioned I previously upgraded my SQL Server Express to SQL Server 2005. Thereafter I uninstall my SQL Server 2005 and reinstall ym VS 2005. So i duno is it cos of this. If so, wat shld I do to resolve this?

Can u advise wat's wrong?

Thanks

Johnson

|||

Hi Johnson,

I'm trying to understand what you're really trying to do here because your information is conflicting, maybe you can clarify a bit...

Are you trying to work with SQL Mobile or SQL Express? They are two different products. Do you have SQL Express installed or not? You've claimed that you installed it, then uninstalled it. You can't connect to it if you've uninstalled it. Do you have an entry on your Start menu for Microsoft SQL Server 2005? What is on it?|||

SQL Network Interfaces, error: 26 - Error Locating Server/Instance SpecifiedSQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

This usually means the application cannot contact your SQL Browser. So, make sure SQL Browser is running and it's firewalled(put sqlbrowser.exe or UDP 1434 into exception).

|||

Hi Thanks..

I resolve the problem already. cos there isnt any SQL Server instance installed

Thanks

Johnson

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Query

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Query

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Query

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Query

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when connecting to server via SQL 2005 Management Studio in Object Explorer but not Qu

Hi,

I'm getting the error below when I attempt to connect to a remote Sql 2005 server using Management Studio and the Object Explorer. The same error does not occur if I go through "New > Database Engine Query" and fill out the same connection dialog with the exact same information. FYI, I am connecting to the IP of an external SQL 2005 server via SQL Server Authentication, and there is some level of connection going on as connecting with the wrong authentication information gives me a standard authentication error even while using the Object Explorer dialog. I am able to do all operations I need in the database engine query window without problem.

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

---

Any ideas on why this would occur? I know this is a standard "network issue" type problem but I'm not aware of any difference that would allow the connection to work fine in one and not the other.

Thanks!

I have this exact error too. Please post if you have made progress on this.

-H

|||Same error here as well. I can connect using 2005 management studio from my XP workstation, but my Vista machine throws this error.|||OK - Same error - also from a Vista machine. Has anyone found the solution|||I think there is some problem with the object explorer dlls. I got the error after installing MDAC 2.7. When i connect it says some dll does registered, but i can connect thru database engine query.|||This shouldn't be related to MDAC at all. Management Studio doesn't use MDAC for any connections. How are you establishing your connections (using server name or IP)?|||

I suspect you are running into a firewall issue on Vista. The built-in firewall on Vista is much more capable than the XP firewall. The Vista firewall can block outbound connections as well as the inbound connections the XP firewall can block.

Make sure you add a firewall exception for sqlwb.exe (the management studio executable). You might need to open up port 1433 TCP as well.

Hope this helps,

Steve

Error when connecting for the first time

Hi, I get the below error (after clicking login in my home page, so home page works well) when I open my website for the first time after I restart my server or my client machine.

" Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

From the next time onwards it is connecting without any problems.

can anyone figure out what could be the problem here?

Are you running SqlExpress?

Did you set the service to start when the server starts? Otherwise it may be getting started on first call and that adds overhead (time) and may be the issue.

|||

Yes I use SQLExpress.

Services are fine. It is in autostart mode and so running automatically everytime i restart.

|||

Hmm... more then likely either some service or some precompilation is in need of being done.

If you leave your site untouched for an hour and come back, does it timeout again? Trying to see if it's a service or simply the first-hit drain on the system thats causing the issue.

|||

this happens only when i use for the first timewww.XXXXXX.com...

I missed the below in my first post and added now

"after clicking login in my home page, so home page works well"

|||

Sounds like that's the first call to the DB... so that's most likely the issue.

You may want to hit up the SqlExpress forum...perhaps someone who watches that one will know more but I know I've had it happen in the past but not with regularity... try prefiring a call to the DB from the Application_Start in the global.asax perhaps, to take that initial hit...

Error when checking if column exists before running query.

I am getting this error when the below query is run "Invalid column
name 'CellPhone'"
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
UPDATE Customers SET PhoneNumber = CellPhone
GO
The Customers table exists but the column CellPhone doesn't.
SQL seems to try to validate the update query even if its not going to
be run.
If I change the table to something that doesn't exist, it will skip
over the line without any errors.
eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
So if the table does exist it must try to validate the columns against
the table.
Any ideas on how to get around this? I don't want it to run the query
if the column doesn't exist.
ThanksThere might be better ways but this splits the statement into two compiles:
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
EXEC ('UPDATE Customers SET PhoneNumber = CellPhone')
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<focus.nz@.gmail.com> wrote in message
news:1151547434.570100.97390@.m73g2000cwd.googlegroups.com...
>I am getting this error when the below query is run "Invalid column
> name 'CellPhone'"
>
> if exists (select * from information_schema.columns where table_name
> ='Customers' AND COLUMN_NAME = 'CellPhone')
> UPDATE Customers SET PhoneNumber = CellPhone
> GO
>
> The Customers table exists but the column CellPhone doesn't.
> SQL seems to try to validate the update query even if its not going to
> be run.
> If I change the table to something that doesn't exist, it will skip
> over the line without any errors.
> eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
> So if the table does exist it must try to validate the columns against
> the table.
> Any ideas on how to get around this? I don't want it to run the query
> if the column doesn't exist.
> Thanks
>|||There might be better ways but this splits the statement into two compiles:
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
EXEC ('UPDATE Customers SET PhoneNumber = CellPhone')
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<focus.nz@.gmail.com> wrote in message
news:1151547434.570100.97390@.m73g2000cwd.googlegroups.com...
>I am getting this error when the below query is run "Invalid column
> name 'CellPhone'"
>
> if exists (select * from information_schema.columns where table_name
> ='Customers' AND COLUMN_NAME = 'CellPhone')
> UPDATE Customers SET PhoneNumber = CellPhone
> GO
>
> The Customers table exists but the column CellPhone doesn't.
> SQL seems to try to validate the update query even if its not going to
> be run.
> If I change the table to something that doesn't exist, it will skip
> over the line without any errors.
> eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
> So if the table does exist it must try to validate the columns against
> the table.
> Any ideas on how to get around this? I don't want it to run the query
> if the column doesn't exist.
> Thanks
>|||Thanks, that did the trick.

Error when checking if column exists before running query.

I am getting this error when the below query is run "Invalid column
name 'CellPhone'"
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
UPDATE Customers SET PhoneNumber = CellPhone
GO
The Customers table exists but the column CellPhone doesn't.
SQL seems to try to validate the update query even if its not going to
be run.
If I change the table to something that doesn't exist, it will skip
over the line without any errors.
eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
So if the table does exist it must try to validate the columns against
the table.
Any ideas on how to get around this? I don't want it to run the query
if the column doesn't exist.
ThanksThere might be better ways but this splits the statement into two compiles:
if exists (select * from information_schema.columns where table_name
='Customers' AND COLUMN_NAME = 'CellPhone')
EXEC ('UPDATE Customers SET PhoneNumber = CellPhone')
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<focus.nz@.gmail.com> wrote in message
news:1151547434.570100.97390@.m73g2000cwd.googlegroups.com...
>I am getting this error when the below query is run "Invalid column
> name 'CellPhone'"
>
> if exists (select * from information_schema.columns where table_name
> ='Customers' AND COLUMN_NAME = 'CellPhone')
> UPDATE Customers SET PhoneNumber = CellPhone
> GO
>
> The Customers table exists but the column CellPhone doesn't.
> SQL seems to try to validate the update query even if its not going to
> be run.
> If I change the table to something that doesn't exist, it will skip
> over the line without any errors.
> eg. UPDATE CustomersXXX SET PhoneNumber = CellPhone
> So if the table does exist it must try to validate the columns against
> the table.
> Any ideas on how to get around this? I don't want it to run the query
> if the column doesn't exist.
> Thanks
>|||Thanks, that did the trick.

Error using MAX() - linked server to Orcl

I have a linked server to an Oracle 9 db using MS OLE DB Provider for Orcl.
When I query "select MAX(IntColumn) from ..." I get the error below when
IntColumn is NUMBER(5) or NUMBER(38) in Orcl, but not when it is NUMBER(9).
No error for simple "select IntColumn from ..." on same tables.
The problem appeared only after SQL 2000 was uninstalled and reinstalled.
The problem persisted after reapplying SP3 for both SQL and for AS.
Also uninstalled/re-installed Orcl 9 client.
I can get around the problem, but I would like to know what DLL/component
got out of sync with all the uninstall/reinstalling.
Thanks,
Les McPheeI should have added details of the error I get...
OLE DB Provider 'MSDAORA" reported an error.
[OLE/DB provider returned message: ORA-01455: converting column overflows
integer datatype]
"lmcphee" wrote:

> I have a linked server to an Oracle 9 db using MS OLE DB Provider for Orcl
.
> When I query "select MAX(IntColumn) from ..." I get the error below when
> IntColumn is NUMBER(5) or NUMBER(38) in Orcl, but not when it is NUMBER(9)
.
> No error for simple "select IntColumn from ..." on same tables.
> The problem appeared only after SQL 2000 was uninstalled and reinstalled.
> The problem persisted after reapplying SP3 for both SQL and for AS.
> Also uninstalled/re-installed Orcl 9 client.
> I can get around the problem, but I would like to know what DLL/component
> got out of sync with all the uninstall/reinstalling.
> Thanks,
> Les McPhee