Check for Numeric Value using SQL Server 2000
How can I know if a VARCHAR field's value can be successfully converted to an integer? I want to do it massively to insert records from one table to another... ...
sql-server-2000
java 1.4 -sql server 2000:not able to insert multiple records into table.
INSERT INTO UPLOAD_FILE_RECORD_FIELDS_DATA select ?,?,?,? union all select ?,?,?,? union all select ?,?,?,? I have to insert multiple records into one table.So i am using query as below.and setting parameter values. But i am getting error code 77.What is cause? No of records to be inserted are approx 70000.So i am inserting 100 ...
More advanced logic for ordering results of a query in T-SQL?
I am currently writing a SQL query that should display a tree-view of areas inside a building with areas, sub-areas, etc. Unfortunately I have been unable to mimic the ordering used by some of our software tools. I'm restricted to MS SQL 2000 so the question of order becomes much more complicated and I'm just over my head at this point. ...
How do I publish an alteration to a replicated stored procedure (SQL 2000)?
We have several stored procedures marked as articles for replication in our SQL2000 database. If we update any of them via ALTER PROCEDURE, the changes are applied to the master, but never published to subscribers. Am I missing a setting, or does SQL require a complete reinitialization/snapshot to move the changes out? ...
Why cursor show error
work on SQL Server 2000. want to Automated Email Notifications using SQL Server Job Schedular.If i run the bellow cursor syntax in query analyzer than i get no error .But when i set this syntax on SqlServer agent->Jobs then it's create error. -- Script generated on 29-Oct-09 11:57 AM -- By: sa -- Server: (LOCAL) BEGIN TRANSACTION ...
SQL query and datetime parameter takes long time to execute
I have a query which takes datetime as a parameter, what we have observed is that if you supply datetime parameter through a variable, Query takes 2 -3 times more time to execute than if you directly hardcode the parameter, Is there any reason or solution to it Following query takes around 5 mins to return the result Declare @Date as D...
Product version in SQL Server.
Simple situation. I've created an application which uses SQL Server as database. I could add a table to this database which contains the version number of my application, so my application can check if it's talking to the correct version of the database. But since there are no other settings that I store inside a database, this would mea...
Closing all connections to a particular database without downing the whole service
We have some databases in a single SQL Server 2000 instance, one of them being a sandbox. My boss needs to be able to restore fresh data over the sandbox using a utility I don't have the source to. Such restores fail if anyone is connected to the sandbox. Another app I have accessing it uses connection pooling, and also there might be...
Not able to remove injected script from database rows
I've been handed a MS SQL 2000 database which has been injected with malware. The malware script is as follows: <script src=http://www.someAddress.ru/aScript.js></script> Now I want to remove this piece of code from the table rows. As a test, I inputed < h1> Test < /h1> on a row, and successfully ran the following query: UP...
How do I add new articles with transactional replication?
Suppose I'm using transactional replication to replicate articles from one server to another (both running SQL Server 2000). This is setup and working great. Then, I decide to add a new article. First, I add it to the publication via Publication Properties > Articles > Objects to Publish. Then, what do I do? It'd be nice if I could ...
Query xml data from SQL2000 (xml saved directly in a cell, multiple rows)
Hi I get problem retrieve XML data from SQL2000 server. The table structure is as following: ID Name XML 1 Name1 <Root><DATA1>1-Data1</DATA2><DATA1>1-Data2</DATA2></Root> 2 Name2 <Root><DATA1>2-Data1</DATA2><DATA1>2-Data2</DATA2></Root> 3 Name3 <Root><DATA1>3-Data1</DATA2><DATA1>3-Data2</DATA2></Root> How can I get th...
NHibernate paging with SQL Server
When using SetFirstResult(start) and SetMaxResults(count) methods to implement paging I've noticed that the generated query only does a select top count * from some_table and it does not take the start parameter into account or at least not at the database level. It seems that if I instruct NHibernate to execute the following query: var...
Problem with update statement
I am trying to update all records in a column so that they start with 'CD' e.g. DCE206 would become CDE206. UPDATE table SET column = REPLACE(column1, 'DC', 'CD') WHERE column1 LIKE 'DC%' I am using the above update statement however the following error appears 'Subquery returned more than 1 value. This is not permit...
What is an elegant way to make two fields auto increment?
In SQL Server, I have two fields, one is primary and has an identity (AN_ID) then I have another one that for legacy reasons (don't ask) has to be updated reflecting the AN_ID. Currently they have the exact same value. I was wondering if there are pure T-SQL query (no stored procedures) to make it auto incremente. Thank you very much E...
tsql to find status of a database in words
I want to know status of every database across a SQL Server farm. I was using: select name, case status when 32 then 'loading' when 128 then 'recovering' when 512 then 'offline' when 4096 then 'single user' when 64 then 'pre recovery' when 256 then 'not recovered' el...
problem with join SQL Server 2000
I have 3 tables - Items, Props, Items_To_Props i need to return all items that match all properties that i send example items 1 2 3 4 props T1 T2 T3 items_to_props 1 T1 1 T2 1 T3 2 T1 3 T1 when i send T1,T2 i need to get only item 1 ...
Calculate Percentage from two colums and write back to table
With a temporary table as: DECLARE @Results TABLE ( CDA varchar(60), Found int default 0, Accepted int default 0, Percentage decimal(3,0) default 0.0, ) How do you take populated Found and Accepted columns and write it back to the Pecentage column? I have: UPDATE @Results SET Percentage = ( SELECT (((Accepted *...
SQL Server: Count how big a column value is compared to the others
I have table column filled with float values e.g.: id-Values 1- 0.19230769230769232 2- 8.4848484848484854E 3- 0.10823529411764705 4- 0.05459770114942529 ... I would like to know: is there a SQL function that allows me to return a percentage of the selected row position compared to the others? For example: I want to kno...
Concat multiple rows into a comma-delimited value during Update
I have a temporary table with a field called Method, thus: DECLARE @CaseSites TABLE ( BriefID int, Method varchar(60) -- other fields ) Method will be filled from several rows in another table - CaseEventTypeList. Running SELECT * FROM CaseEventTypeList WHERE RefID = 1 Gives RefID TypeID 1 2 1 3 1 6 Turning this into a ...
Mysterious constraints problem with SQL Server 2000
Hi all I'm getting the following error from a VB NET web application written in VS 2003, on framework 1.1. The web app is running on Windows Server 2000, IIS 5, and is reading from a SQL server 2000 database running on the same machine. System.Data.ConstraintException: Failed to enable constraints. One or more rows contain valu...