Hi, Is there a way to return a row multiple times if it matches multiple clauses? For example, say i have a column called myNumber and one of the values in it is 5. I can do this query... SELECT myNumber FROM myTable WHERE myNumber > 4 OR myNumber < 6; ....and it will return a single ...more >>

Dear All, Could anyone please help me with this problem which regarding the record number.i need to display a records as the following: No ID MemName ----- -------- ---------------------------------- 1. 12345 NOVAINA HALIMAH 2. 12346 EDYA PUTRA 3. 12347 R...more >>

How to select random rows in table? ...more >>

I have a stored procedure which runs a simple append query. (See it at the bottom) Since it is running on a server on the other side of the world I do my testing on it when it isn't being used in the middle of the night When I get on and run it the first time (after dropping the existing table ...more >>

Below is a template test sproc using IF ELSE depending whether @sChart = '1' or '2'. With variables set in FIGURE 1, it returns 'shift equal 0, team equal 0' However, when I change @sShiftID to '1' it returns only 'The command(s) completed successfully.' instead of correctly bran...more >>

I am unable to insert into the SQL Server 8.0 through ADO.NET using SQLAdapter for it throw an exception (but works fine with OLEDBAdapter) after adding this new column which is the last column in the table of many column Column Name Type Lengt Notes ...more >>

assuming DTS is not working for the specific scenario , what is another preferred technique for copying a table and its data from one server to another ? by DTS, i am referring to SEM > select db-object > right-click > All Tasks > "Import Data ..." and "Export Data ..." ...more >>

Hi, How to find only the columns which are updated in trigger. My table has got around 60 coulmns. In a trigger i want to know what are the columns updated when user executes an update statement. Thanks in advance. Gopinath M ...more >>

I have a table Say PathTable Create Table PathTable(PathId Int Identity primary key , Paths Varchar(500)) In this I store values like this: PathId Paths Hits Click ------ ----- ---- ----- 1 1,2,3,1 2 ...more >>

Hi all I have the following table which contains a field that is delimited by a pipe (|) id months 1 'Jan|Feb|Dec 2 'Jan|Mar' 3 'Feb' I need help to write some sql that will split each field by the pipe and insert the results into a look up table e.g. idMonth monthDe...more >>

declare @string1 varchar(2000),@string2 varchar(2000 set @string1='http://www.yp.com.hk/product/templates/getpage.asp?prd_master_id=118120&website_id=1&language_id=9&url= set @string2='http://202.153.119.30/IYPMap/geniypmappage.asp?language=9&geo_refno=4258216828&x=842582 &y=816828 &compan...more >>

Hi there, I'm using SQL Server 2000. I've created a database that has the name "SworderNet.ProductAnalysis." Various applications connect to this database using this name. Well, it turns out that certain programs like Excel have a problem accessing SQL databases that have a "." in t...more >>

I have a table that contains stat data... FacilityID StatYear StatMonth Presented SeenByDoc FacilityAdmits 10 1999 12 855 953 207 10 2000 1 927 990 156 4 ...more >>

I have a table that holds a history of agent positions. I need to be able to pull the latest record for a specific agent. I am using the following syntax, but I keep getting all the records. Any ideas? Thank you in advanc SELECT MAX(EffectiveDAte), OldPosition, OldShift,AgentI From AgentHisto...more >>

i am using ASP.NET with sqlserver. i have gotten the following 2 steps from some suggestions. 1)in my program, first i use a execute non-query to select a row from a table. 2)then i change the data values in this row, and use an "if exist then drop row; insert" sequence to update the data (with...more >>

Hello In a scenario like below, is there any way to specify default values for the columns without actually sending it from the DOM. Thanks INSERT TBLUPLOADTABLE select * from openXML(@idoc,'CONTRACTOR/LINEITEM',1) WITH (GROUPID int, CONTRNUM char(9), SSN char(9), LNAME char(30)...more >>

Is their a syntax to check if a update did occur: for example IF NOT UPDATE job SET status=@startLabel, startTime=@time WHERE jobID=@jobID AND (status != @completeLabel) BEGIN UPDATE job SET startTime=@time WHERE jobID=@jobID END ...more >>

Hello, Can the 2nd parm of the CHARINDEX function be a sql statement itself or a UDF. I want to find out where a particular string begins but I need to dynamically generate the string to be searched. Paul ...more >>

In Access, this is perfectly legal: DELETE FROM table1 INNER JOIN table2 ON table1.ID = table2.ID WHERE table2.someField = someValue What would the equivalent synatx be in T-SQL? ...more >>

I have an online price table with 10,000 products. We change prices locally in Access and then upload that table to replace the online price table. Here are some issues that maybe someone can prescribe a better mthod.... 1) When I upload the Access table, all the field types change to nchar, f...more >>

In Access, this is perfectly legal: DELETE FROM table1 INNER JOIN table2 ON table1.ID = table2.ID WHERE table2.someField = someValue What would the equivalent synatx be in T-SQL? ...more >>

Let's say tables A and B both have a column called someCol. If I do: Select A.*, B.* from A,B Then I will have two columns named 'someCol'. The issue is, the .NET datatable object, renames the second 'someCol', to 'someCol1'. So now it has a completely different name. What I would lov...more >>

We're an ISP using SQL 6.5. When the user connects to us, the RADIUS server queries the database to authenticate the user. This is happening all the time. However when I run a query against the database that effects a large no. of rows and runs for a minute or two, often the authentication proces...more >>

hi how can I return columns so that it is not tab, comma or space delemited...I want to return 1 char from the first column then without any space 6 chars from the second column and then export the result it a text file.. Thanks in advance...more >>

Hi I am querying a link server wit select * from linkservername.datab.dbo.produc I am getting an error Error MSg: 7202 could not find linkservername in sysservers execute sp_addlinkedserver to add linkserver The server is already added. I can see the tables from the linkserver in the Ent Ma...more >>

Is there a way to programmatically connect to a DB in C++ without having previously set up a DSN? ...more >>

I'm trying to insert some related items into two sql tables at the same time: CommandText='<%# "INSERT INTO categories,Products (tDate, maincat, submaincat, productprice) VALUES (?, ?, ?, ?)" %>' but I get an incorrect syntax error at "," . This is part of a dreamweaver asp.net type script....more >>

I am testing an ADO OLEDB SQL Server 2K connection just by setting up an UDL first and have the following questions : 1. To which physical directory should the virtual directory within IIS refer ? 2. Should this physical directory contain any necessary files ? 3. How should the servername bee...more >>

According to BOL, smallmoney is stored with 4 bytes of memory and can store values in the range of -214,748.3648 through 214,748.3647. Using my simple mind though, I calculate 4 bytes = 32 bits = 2^31 = 2147483648 (max value that can be stored with 4 bytes). I know it is not just coincidenc...more >>

I'm using SQL Server 2000. I suddenly got an error: "Could not start MSQSQLSERVER on local computer. The service did not return an error. This could be an internal windows error or an internal service error..." Rebooting doesn't help. Does anyone out there know how I might go about troubl...more >>

Has anyone done this using VBScript before? Providing a popup list of the files to restore from as per what is listed in the database backup history. Basically, I want to script the Server Manager Restore abilities for our operations department. Can anyone help??...more >>

Somehow the spt_ tables got deleted from a development server. Is it possible to recreate these tables? Thanks in advance, David Reckner...more >>

Hello All, SQL 2K w/ SP3a I am trying to create a view based on a stored procedure so I can use that view in Crystal Reports. When I execute the stored procedure in Query Analyzer, it returns 1083 rows without an error. I am trying to create the view using the following select statemen...more >>

Is there any way you can backup database to a diff server. meaning I have database on e:\ and I want to copy that file to f:\....more >>

If I attempt to do an aggregation on a subselect, it is extremely slow in SQL2000. It worked just fine in 7.0. If I run the sublect alone in returns all records in seconds. However, if I do a count(*) on that subselect it never comes back select count(*) from select x,y,z from a innerjoin b o...more >>

hi just for test, i wrote this asp code if i include the line of "activeConnection" i get the 800a01fb error : "exception" i reinstalled MDAC again and again i close all objects see the example ONLY open and close a connection connections seems be POOLED ok but executing the asp code sev...more >>

Hi, I have created a recordset in an asp page and then I loop through that recordset to make some changes to the data. This script has been work fine for about 18 months, but now it is stalling as the recordset query is blocking the update that is being attempted while the recordset is still ...more >>

is there anyway that i would be able to obtain the modifications to a DB in a trigger. for example, on an update trigger, would i be able to obtain the "old" data that is being stored in a particular column as well as the "new" data that is replacing it. i know how to obtain the column that has ch...more >>

Can someone explain to me what RangeS-S means? Is this bad? What causes a RangeS-S? Secondly can you tell me how to turn the resource into a key that I can understand? spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- ---...more >>

Hi, Does anyone know how I can turn this SQL program to give me the recodes all in one column, like it is now I get a separate column per recode. SELECT DISTINCT tblDemographics.lname, tblDemographics.pid, vw_HFS_SIPS.ID, tblDemographics.frmdate, First(DateDiff("m",[frmdate], [exa...more >>

Hi, There are some similar tables in my database. These tables name are AAAA01...AAAAxx. I need delete all records of these tables. I writed some code, but it didn't run at all. Anyone could help me? code list as: declare @cTableName char(20) declare @nloop int set @nloop =1 whil...more >>

Hello all. We have a table tblSchool (DDL below), and we need to update the people's IDs (superintendent, superintendent secretary, etc.) via a stored procedure one at a time. In other words, our ASP app lets the user choose a person for the superID, and we want to call it up and fill that positi...more >>

How can I get programmatically (VB.NET) all the field properties (including a description) for a specific table in an SQL Server database? Thank you. ...more >>

Hi all, coming from a VMS powerhouse background, we used to have a count function that we could reset when a repeating value changed. Is there a way to do this in SQL? What I want to do is: with data Head Line 1 A 1 B 1 C 2 A 2 B 3 A ...more >>

Windows 2000, Sql Server 7.0 Hai, In my system sqlservr.exe is consuming Highest CPU Time (100 - System Idle Processess). I am not running any sql It is happenning after 3 to 4 after booting the system ...more >>

I get the following error regularly when running scripts which access my database on msSQL. I can run various scripts with no problem, then suddenly this error is thrown up. If I refresh the page it is fine. This is beginning to drive me mad! The error: Microsoft OLE DB Provider for ODBC Driv...more >>

Hi, Is there a way to get the table name from each result of this query? EXEC sp_msforeachtable 'select top 10 * from ?' Thanks again, ...more >>

A few years ago I read an article on SQL Server data types. The author was one of the MVPs on this site, and the article was on his site (sorry I don't remember either the author or the site - it was a couple of years ago). Anyway, the author stated that for performance reasons the int data type ...more >>

I've got a linked server setup to DB2, and some of the date fields in the DB contain 1/1/0001 values. When I attempt to select that field, I get an error Server: Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime. I assume since valid dates ra...more >>

We're in the process of converting from a Win2K/SQL7/MDAC2.5 SQLServer Box to one with Win2003/SQL2000 SP3/MDAC2.7. In both cases we use a DataLink file to open the connection. sConnectionString = "File name=C:\Data Links\MyDB.UDL;User Id=MyUser;PASSWORD=mypassword;" We have a VB App that ...more >>

I am trying to create a view which I can pass variables to it. Does anyone know how to accomplish this?...more >>

Guys Hunting high and low for the definitive answer to this query. I need a system to enable the import of a csv file (trypically contacts exported from Outlook) into a SQL Server table unique to that user through a ..aspx page. At the first and most basic level I need to simply get the dat...more >>

Hello After migrating a bunch of tables, I added Timestamp columns to them. Is there a good and Q&D way to initialize those rows or was it necessary to have the TS in place before importing the data mklapp...more >>

Yesterday the first time I ran the following stored procedure it took 1.5 hours the second time it took over 9 hours. Can anyone offer advice as to whey the second time took so much longer? I didn't have anything else running at the time. Also, if you see anywhere I could improve my coding...more >>

I want to append records from one table to another - and I suspect this is simple to do, but am having difficulty getting there. Example: Table 1: ID, Data 1, This is a test 2, Mary had a 3, A funny thing happened Table 2: 1, of the emergency broadcasting system. 2, little lamb. 3, ...more >>

How can I combine my two case statements in one under same heading: syntax: SELECT distinct(Div), sum(case when dt between '08-Mar-2004' and '14-Mar- 2004' then '1' else '0' end) as A, sum(case when dt between '01-Mar-2004' and '31-Mar- 2004' then '1' else '0' end) as A from t...more >>

Hello Experts, When the "Rollback (Tran)" can used by its self (without define the Begin Tran)? Thanks in advance....more >>

Hi all, I have three tables: Parent: PID PName 1 John 2 Jane Child: CID PID CName 1 1 Johnson 2 2 Janeson 3 2 Janesdaughter Job: JID PID JTitle 1 1 Father 2 2 Mother 3 1 HouseHusband I'd like the following XML from a query: <Parent PName="John"> <Child CName="Johnson"/...more >>

I need a query to give me all the attributes of one record out of a grou Any record will do whithin the criteria that b = 2 or 3, It is important that it is only one record, it does not matter which one select * from _ where b in (2,3 a b c d ---------...more >>

Hello Experts, I have 2 tables (DDL below): Select 'Cust1' CustName, 2 Code Into #Customers UNION Select 'Cust2', 4 UNION Select 'Cust3', 15 GO Select 'Cand56' CandName Into #CandidateCustomers UNION Select 'Cand67' UNION Select 'Cand800' -- drop table #Customers -- drop table ...more >>

HI, Is it possible to use CASE statement to select a different where clause in a SQL statement? What I try to accomplish is this: I have a table, with a smalldatetime field I want to create a stored proc, which can receive two parameters, start and end date If parameters are present (...more >>

here goes !! The problem is that if i am getting 4 rows of data corresponding to a unique number(PK). Then the first 5 columns of all the rows are same for all the 4 rows but the last column has diffrent values for all the rows. ex 1 name 23 34 45 test1 1 name 23 34 45 test2 1 name ...more >>

Thanks...more >>