Problem Notifying Operator SS2005
Jun 29, 2006
Hi there!
I expercience some problems in sending the alert mails to the operator when an Agent Failure Alert happens.
I have setup MSSQL 2005 with merge replication and have setup mail the ss2005 way. I also can send test mails via the Database mail context menu.
I have setup a operator with a valid emailadress and enabled is checked
On the notifications page 'Agent Failure Alert' is checked.
When this alert fires the number of occurences in the history of this alert is incremented (as I can see via the alertsection-Agentfailure-history) this alert is also enabled. The problem is that the operator does not get notified!
So I can send test email from the DB, have setup an Operator with emailadress, have enabled an alert and see the number of occurences of this alert increment. I don't get any alert mails. The notifications attempt in the history page of the operator does not increment. It says: [never emailed]
Well I hope you can help!
Sincerley
Edward
View 3 Replies
ADVERTISEMENT
Mar 4, 2005
I need to create a trigger or procedure that will notify users to follow up on a report they produce to other departments after 120 days. The report will be based off the date it was presented to the department "DateRptPresent" and the the 120 days will be calculated off the date it was reviewed by management. Is this possible??
View 1 Replies
View Related
Aug 10, 2006
hi all,
i have 20 replication task per day
i wan't to notify my end-user that all
these task has sucessfully completed
using a single email.
I know each task can send email individually
but thats not what i need
TIA
thanks,
joey
View 1 Replies
View Related
Jul 20, 2005
Hello,I am writing an application using Visual Studio .NET (C#). Theapplication will be run on several workstations simultaneously, andall of them will be accessing a single sql server database. I amtrying to have it so that when one of the applications updates thefields of a table in the database, all of the other applications arenotified (Without polling the DB).I have been trying to do this by using a SQL Trigger and callingRaisError from within. This will create an exception for the callingapplication (what i wanted), but I can get any of the otherapplications to see the error.Is there a way of accomplishing this using triggers? Is there anotherway of notifying all applications of a database change without the useof polling?James ChangJoin Bytes!
View 1 Replies
View Related
Sep 13, 2007
I have SS 2005 express* working(yrs) on a XP home ver. laptop. I got a new Acer w/ Vista hm/premium and I registered/can't get SS 2005 express* to download from MSDN.
Norton's gives a file block, I click the 'download file' and nothing happens. No dialog, hd drv activity, nothing - just 'Done'
on IE7 status. I have Visual Web Developer beta v2 2008** running on the Acer and don't want to trash the install.
.NET fwrk v2.0 is listed required for SS2005, but .NET fwrk v3.5 says it includes v2.0 + WCF,...(right? - install both?)
I need a SS for VWD 2008. I've ask/tried several times. What am I doing wrong?
Mark
View 1 Replies
View Related
Jun 19, 2006
I downloaded the DVD image to a PC and the DVD drive fails.
Now, when I try to download the executable for install instead, nothing downloads.
Is SQL SVR 2005 eval limited to one download?
View 3 Replies
View Related
Jun 2, 2004
I get this error when I attempt to read from a datareader using the following sql statement:
Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"
my reader command list as follows:
Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader
Any ideas on where I am going wrong?
Thanks in advance
View 3 Replies
View Related
May 18, 2004
I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @gdo and @gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:
View 3 Replies
View Related
Apr 30, 2007
I have MS SQL Server 2005 Developer Ed. and try to make a database diagram of remote MS SQL Server2000 database in MS SQL Server Management Studio.
I receive an error "Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."
View 1 Replies
View Related
Feb 14, 2007
Hello,
I have installed SSs2005 - Standard Edition.
During this installation, also Visual Studio is installed.
I do not understand what i can do with this Studio version.
I mean, i understand that with SS2005 there is a connectivity between sql server and visual studio. For example i could create store procedures with Visual Studio. (clr applications).
But when i start the Viual Studio screen i can only select Business Inteligence Projects.
What do i need, when i want to create functions, stored procedures and what do i need to develop SMO applications?
Please give me some light on this matter.
View 1 Replies
View Related
Nov 14, 2007
Every time a transaction log is dumped we see the following message in the log file:
BackupDiskFile:penMedia: Backup device '\s-sqlbkups-1g$myserverlogmy_databasemydatabase_backup_200711071430.trn' failed to open. Operating system error 2(The system cannot find the file specified.).
Source spid139
Message
Error: 18204, Severity: 16, State: 1.
And yet, the actual log dump appears fine and the file is found on the share. The dump is done with a maintenance plan.
Any ideas?
View 2 Replies
View Related
Oct 13, 2006
Hello!
I have been trying to find a T-SQL function that would calculates a Median statistical value for me. I am runnnig on SS 2005? Any examples of using this function would be greatly appreciated.
Thanks for any responses!
View 7 Replies
View Related
May 29, 2008
I'm trying to migrate/upgrade some databases from 2000 to 2005 and am having a problem. Apparently, the Upgrade Advisor can't analyze a SS2000 database if it is in a named instance. (see below)
This problem occurs because the SQL Server 2005 Upgrade Advisor cannot connect to the named instance of SQL Server 2000.
The SQL Server 2005 Upgrade Advisor uses information that the SQL Server Browser service returns when the SQL Server 2005 Upgrade Advisor tries to connect to an instance of SQL Server 2000. However, the SQL Server Browser service cannot return the correct information about the connection request. Therefore, the connection fails.
http://support.microsoft.com/kb/908454
Doesn't this make the UA tool useless for named instances on SS2000? Are there any plans to correct this issue? Or, is there a workaround available?
Keith
View 7 Replies
View Related
Jan 27, 2007
SQL Server 2005
Hi Everyone. We have a stored proc that performs several bulk inserts. I need to find a way to allow the bulk insert to truncate data. Also, I would like to be able to send back to .NET the exact line that failed if a failure does occur.
Right now, the stored proc fails because of truncation. Could this be because there's a check for @@ERRROR <> 0 right after the bulk insert? Does anyone know if a truncation occurs, if that will "throw" an error to the catch block? This is NOT what we want. Can anyone help me to understand how to do this correctly?
Thanks,
Angel
View 5 Replies
View Related
Mar 18, 2008
I'm currently using the evaluation version of SS2005 and would like to use PerformancePoint with Analysis Services. PerformancePoint requires the cumulative update package 3 for SQL Server 2005 Service Pack 2, or Build 3186, according to the documentation. The version for SS2005 is the following:
MSSQLSERVER
Analysis Services
[Version: 9.00.1399.06 Edition: Enterprise Evaluation Edition Patch level: 9.00.1399.06 Language: English (United States)]
The same version, patch level is repeated for all of the SS2005 components. Do I have a compatible SS2005 version for PerformancePoint.
Thanks,
Tom
View 5 Replies
View Related
Dec 20, 2007
ok, so sp_processmail is deprecated and should no longer be used in SS2005.
how do you process and execute a SS2005 query using an incoming email??
can it even be done in SS2005??
thanks for any ideas,
chester
View 4 Replies
View Related
Feb 21, 2008
I'm trying to create a SQL Agent job and schedule that executes an SSIS Package, but when I try to run the job I get the following error:
Executed as user: adApp1. The package could not be loaded.
The App1 user is the Identity used in the Credential for the Proxy which has the SSIS Packages Subsystem.
The package encryption is set EncryptAllWithPassword and that password is included in the Command line with the /DECRYPT option.
The package is stored on the server in the SSIS Package Store and was placed there via the Import package option. Does the method matter?
One thing that I haven't been able to track down is exactly what permissions the domain account adApp1 needs on the server or in SQL Server, if any, in order to run the package. Not sure if that has any affect anyway.
Any information would be greatly appreciated.
Thanks
View 10 Replies
View Related
Sep 7, 2007
I understand that only Enterprise Edition has support for Fuzzy Lookup and Fuzzy Grouping transforms, but we will likely be migrating to Standard shortly - Enterprise would be complete overkill for our organisation and needs, and is beyond our budget constraints too.
I have been using these in the CTP of Katmai, and have found them exceptionally useful for my work.
Any chance of these transforms being made available as an "add-on" for Standard (or other versions)?
It does not seem that it is something inextricably tied to the different versions - just a licencing decision, right?
Or would the proposal be to go with Standard and one additional purchase of Developer Edition?
Sham
View 1 Replies
View Related
Mar 11, 2008
Hello,
Is it possbile to migrate SS2000 DTS packages to SS2005 SSIS involving databases with compatibility mode set to 80. If yes, what are the potential issues, which may occur?
Sajish
View 6 Replies
View Related
Jul 9, 2007
I have an SSIS package that takes data from a table in Access and puts it into a fact table in SS2005. Very little data manipulation is done. It processes approximately 1.5 million rows when it runs weekly. The process is run in an SSIS package that is called by a parent package, and all of that (including the use of the config files and accessing the parent variables) is working fine.
The issue is there is one field in the Access table that must be put into a different SS2005 fact table.
When I run the data flow task that loads the first fact table, it completes in less than two minutes. However, if I either (a) put a multicast step in the dataflow task to redirect a copy of the key data and remaining field to the second fact table, or (b) copy that step in the package to have it perform the same tasks with the different target (and using just the key and the remaining field), the execution time suddenly jumps to 30 minutes. In the case of (b), it remains true whether the copied step remains in the package or is executed in its own package, and also remains true if the package is loading against a table that starts out empty or with data already in it.
Has anyone ever bumped into a situation like this?
View 3 Replies
View Related
Jan 11, 2007
We have a two SQL Server 2005 databases set up using Transactional Replication. My manager has asked me to set it up to replicate constraints (default, fk, et) on the tables in the main publication. I said sure, I can do it, but I will have to re-initialize the subscription. He said there was a way to do it without re-initializing the subscription. I cannot find anything in my research to indicate that there is a method to do so (via call sp_ functions, etc). Is it indeed possible to replicate constraints on replicated tables without re-initializing the who subscription? The reason he does not want me to perform a re-initialization is that we are a few weeks before moving into production, and a full re-initialization takes 1.5 days, which would impact system availability.
Thanks in advance for any advice.
View 1 Replies
View Related
Nov 1, 2007
Hi,
We are implementing push transaction replication from Production SQL Server 2000 database to SQL Server 2005 reporting server and SQL Server 2000 Test server.
SQL Server 2000 PROD to 2000 Test is simple but from SS2000 to SS2005 I have following questions:
Is it feasible to create push subscription from SQL Server 2000 to SQL Server 2005 database?
When I am trying to setup publisher/subscription properties, It only give me options to pick SQL Server 7.0 or 2000 or heterogeneous databases.
When I am trying to register SQL Server 2005 database in EM of SS2000 it don't allow me to add it and through exception that I need to use Management studio, Is there any alternate way to register?
Thanks
--rubs
View 1 Replies
View Related
Jun 22, 2006
I am executing sp_dropmergesubscription, but the rows are still in dbo.msmerge_subscriptions, and are still shown in the replication monitor as expired; the last synch dates were in april (expiration is set to 10 days). The 'expired subscription clean up' job appears to be running okay. I need to remove these subscriptions from the publisher as the subscribers are mobile devices, which sometimes are lost.
View 5 Replies
View Related
Jun 28, 2007
Windows XP Pro - Sql Server 2005 Management Studio (current) - current visual foxpro oledb driver
I have an origin data folder with many foxpro dbf files
When I go through the "wizard" to import data from the origin folder using the "free table" option, I can locate the folder just fine. The form shows me a list of the files in the folder - but I cannot scroll through the list.
When I select the folder and continue, I test the connection and get a message that it is fine.
Then I go on to actually import tables. The list that is presented of tables that I can import does not include all the dbf files in the folder. There are 207 files in the origin folder, but only a fraction (about 120) of those show up in the list as candidates to be imported. I cannot see a pattern to the ones that are excluded from the list.
Does anyone know what could be causing this strange behavior?
Thanks.
View 3 Replies
View Related
Jan 1, 2008
I access remote MSSQL Server2005 (SS2005) databse over internet
through SQL Server Management Studio (SSMS) in order to fine tune
ASP.NET website using remote SS2005 database.
Today I could not launch my SSMS
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2616564&SiteID=1
Is it possible that remote server machine or SS2005 damaged my SSMS.
Can it be due to some incompatibilities, virus (I have Norton Antivirus locally) or something else?
I do not have much experience with using SS2005 over internet.
Can you share with me possible practical stories or theory on it?
View 3 Replies
View Related
Feb 18, 2008
Hi!
I have a database where I add some new Filegroups and moved some Gb's of
tables and indexes to new file groups.
Now I would like to shrink the source of these tables/indexes: the mdf file.
This means there should be much empty space now in the mdf file.
I tried to use dbcc shrinkfile, but it run for more than one day.
Are there some good strategies about shrinking the mdf file?
I had to restart the instanse because got problem using the db while dbcc runned...
Thank you for help
View 2 Replies
View Related
Dec 22, 2007
Hi!
I try to find out how to write an tsql program that sends dmx-ddl to SSAS.
this works:
select * from openquery(bdjOLAP, 'select <col> from <modelname>.content')
because it returns an resultset.
but how to something similar this (would fail, because no result set is returned):
select * from openquery(bdjOLAP, 'create mining structure...')
Best regards
Bjorn
View 1 Replies
View Related
Feb 22, 2008
...we understand that perhaps some xp's might come with the product, but for sp's that we author ourselves, are they two different things in 2005, and if so, why would one be chosen over the other?
View 1 Replies
View Related
Jul 18, 2007
Hi ,
I am trynig to insert an image from the Windows mobile to the SQL database.
Is it possible to perform this using RDA ?
After some search in this forum , I got an impression that the only way to do this by storing the image in a local database and then do a merge replication.
I do not want to do the merge replcation just fo rthis reason.
Is it possible in RDA?
Debugging shows only some part of the data is getting updated.
While trying to retrieve the image in a webpage error is poping up.
My code is :
string strbytes = (Convert.ToBase64String(bytes)).ToString();
DbObj.GetSubmitSql("Update Odrs set SignedBy ='" + txtSign.Text + "', Signature = Convert(Image,'" + bytes + "') where OrderId=" + orderNo + "");
Please help....
View 1 Replies
View Related
May 10, 2006
Hiya, last one (for a while), I swear. I'm getting the hang of this now (famous last words).
I've got a data file saved as an image type in my SQL Mobile database,
and I'm trying to send it over to my SS2005 Server via RDA, which seems
the simplest way possible. The application will only have
periodic rows to send over and insert into the Server's DB, so it seems
like doing a merge or a push is unnecessary overhead.
Is there some way to include parameters in the SQL string argument to
the method SubmitSQL? Or, embed the byte array into the SQL string? For
the life of me, I can't figure out how to do this.
So, if column data has type image, my statement would look like what?
string sSQL = "INSERT INTO sensor_stream (data) VALUES (what goes here?)";
rda.SubmitSql(SQL, strSQLConn);
.....
I've seen the solution being able to assign the image by using
parameters, but it seems that solution is not an option in this case?
Thank you in advance for your time and effort, I appreciate any and all help very much.
-Dana
View 5 Replies
View Related
Nov 29, 2006
Hi, has anyone got the following error message before? It seems like it occurred when the snapshot creation agent reached the UDF fn_getOwner():
StartIndex cannot be less than zero.
Parameter name: startIndex
Error messages:
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Command Text:
select 'number' = convert(int, 0), 'definition' = definition
from sys.sql_modules
where object_id = object_id(@qualified_object_name)
union all
select 'number' = convert(int, procedure_number), 'definition' = definition
from sys.numbered_procedures
where object_id = object_id(@qualified_object_name)
Parameters: @qualified_object_name = [dbo].[fn_getOwner]
Stack: at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.Script()
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonTableArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, SqlSmoObject smoObject, Boolean quotedIdentifierOn, Boolean ansiNullsOn, Boolean textMode)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateFunctionArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 52006)
Get help: http://help/52006
Source: mscorlib
Target Site: System.Text.StringBuilder Remove(Int32, Int32)
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Stack: at System.Text.StringBuilder.Remove(Int32 startIndex, Int32 length)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.ProcessGetObjectScriptResult(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) (Source: mscorlib, Error number: 0)
Get help: http://help/0
Here's the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/* various comments here. */
ALTER
FUNCTION [dbo].[fn_getOwner] (@moduleID integer, @FY char(4))
RETURNS varchar(5)
AS
BEGIN
declare @tin char(9),
@owner varchar(5),
@ownerCount integer
-- Get owner via ModuleID
select @owner = owner
from tblControlPage
where moduleid = @moduleID and AuditFY = @FY;
-- Get owner via ATIN (This is when the moduleId is not on control page or no owner.
-- Check to see if the ATIN is on control page and use ATIN's Owner)
if @owner is null
begin
-- get module's TIN.
select @tin = tin from tblModule where moduleid = @moduleID
-- count the number of owners by ATIN those owner is not null via entity association.
select @ownerCount = count(*) from
(
select owner from vwControlPage c inner join vwEntityAssociation e
on c.tin = e.atin and e.tin = @tin
and auditfystart <= @FY and @FY < auditfyend
where owner is not null
group by owner
) owners
if @ownerCount = 1
begin
-- good, there's only one owner, get the ATIN owner.
select top 1 @owner = owner from vwControlPage c inner join vwEntityAssociation e
on c.tin = e.atin and e.tin = @tin
and auditfystart <= @FY and @FY < auditfyend
where owner is not null
group by owner
end
-- else there are multiple owners, set owner value to 'Multi'
if @ownerCount > 1
begin
set @owner = 'Multi'
end
end
-- Get owner via TIN if @owner is still null
if @owner is null --or len(@owner) = 0
begin
-- get TIN of module (Commented out the following because it was retrieved on prior "if" condition)
-- select @tin = tin from tblModule where moduleid = @moduleID
-- count number of owners by that TIN those not null.
select @ownerCount = count(*) from
(
select owner from vwControlPage
where tin = @tin and auditFY = @FY and owner is not null
group by owner
) owners
if @ownerCount = 1
begin
-- good, there's only one owner.
select top 1 @owner = owner from vwControlPage
where tin = @tin and auditFY = @FY and owner is not null
group by owner
end
-- else there are multiple owners, set owner value to 'Multi'
if @ownerCount > 1
begin
set @owner = 'Multi'
end
end
return @owner
end
View 1 Replies
View Related
Oct 5, 2007
Hi There
I have found no real documentation on this, so any links would be greatly appreciated.
We have an existing 2 node active active cluster.
A default and and named instance SS2005 Enterprise Edition are installed on the cluster.
Recently we acquired new hardware, 2 servers, which we added to the cluster so we now have a 4 node cluster.
The 2 new nodes have OS etc installed.
Now i have installed sql on a new multi node cluster, i have upgraded sql server in an existing cluster BUT i have never installed sql on an existing cluster for new nodes for multiple instances.
Now i presume you simply run the install on one of the new nodes, do this for a default instance, selecting both 2 new nodes to installl on. And do this again for the named instance.
But in the past I have never been promoted for which nodes to install sql on, it simply does all the nodes BUT i do not want this.
And how will the cluster know where the binary files are for each instance on the new nodes?
In a nutshell, how do i install sql server 2005 for 2 new nodes in an existing 4 node cluster for multiple instances ?
Thanx
View 5 Replies
View Related
Mar 24, 2006
We are experiencing a problem in SQL Server 2005 Standard Edition (on x86 & x64, RTM & SP1 CTP1). The problem is we have a view which does something like "CREATE VIEW myView;SELECT * FROM MyTable WHERE ISNumeric(MyVal)=1" when you do "SELECT * FROM myView" you see a dataset which only contains numeric values.
However it's clear that if you do "SELECT * FROM myView WHERE MyVal>5" that it is evaluating the >5 before the IsNumeric function (I assume as > is less costly than IsNumeric and thus it is more efficient this way). This didn't happen in Sql Server 2000 & 7.0.
My concern here is that how can you trust views if when you put evaluations on them they're working against a different dataset to that which you view if you do SELECT * ?
I am currently working with a workaround which is to simply put TOP in the sub-queries to force the execution order to that which I've defined. However this is nasty as I can't do TOP 100% as it gets optimised out and so instead I have to do TOP 999999999 or similar.
However my biggest concern by far is that even in "SQL Server 2000 (80)" compatibility mode the behaviour is not consistent wtih SS2000.
CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))
INSERT INTO #Problem (numinastr) values ('1')
INSERT INTO #Problem (numinastr) values ('10')
INSERT INTO #Problem (numinastr) values ('25')
INSERT INTO #Problem (numinastr) values ('40')
INSERT INTO #Problem (numinastr) values ('>500')
INSERT INTO #Problem (numinastr) values ('600')
INSERT INTO #Problem (numinastr) values ('1000')
INSERT INTO #Problem (numinastr) values ('error!')
-- Note Lack of any non-numeric rows
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15
--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15
-- Creating a view of SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 and
-- then querying that also gives the same error
DROP TABLE #Problem
I have been told (by an MVP) that you can't assume a specific execution order for queries. Do any DBA's out there really think this acceptable? I consider this a bug. If I put a query in as a sub-query or view, or if I bracket my where statement in such a way I expect it to respect what I've told it!
View 5 Replies
View Related
