There is a citydate field with string data type in SQL 2K, 2004-04-04 20-45-08. I'd like to convert it to a datetime data type which should be 12/07/2004 1:34:30PM. Please help. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for ...more >>

Is it better to have one table with lots of fields or many tables containing sets of fields? For example, I have a tree structure with a table for adjacency information and a table for "node properties". I can ask questions about the structure of the tree and generally manipulate nodes w...more >>

Hello: We currently have the following configuration. Our web and vb apps are sitting on a WinNT box (MDAC 2.5). We have SQL 7 which is also on WinNT box. We are migrating to SQL 2000, which will be on a Windows 2000 machine. Our web and vb apps will not be migrated and will stay on the Wi...more >>

I have 4 tables People, address, Payments and invoices the latter 3 link back to people. I need to pull out the total of all payments and invoices for each person but only if the sum of paymenst is less than the sum of invoices. and everything from addresses. I can do most of it ie get ...more >>

Hi everyone, I have a stored procedure which I use to query a table. The first part of the stored procedure uses a cursor to update a temp table whilst the second part of the query actually retrieves information from a database table utilising information based on the temp table. My problem ...more >>

Just wondering if there is any advantage to to specifically stating dbo.TableName in stored procedures. Is there any reason why I should write this: SELECT dbo.Contacts.LastName FROM dbo.Contacts Instead of just: SELECT LastName from Contacts All tables are owned by dbo in this databas...more >>

Just wondering if there is any advantage to to specifically stating dbo.TableName in stored procedures. Is there any reason why I should write this: SELECT dbo.Contacts.LastName FROM dbo.Contacts Instead of just: SELECT LastName from Contacts All tables are owned by dbo in this databas...more >>

I'm trying to delete database which I restored to this PC before from a full backup. This database had publication and when I try to delete database I get errors saying that it is used for replication. But the problem is that I can't delete publication either because I get error "could not find ...more >>

Hello all. SQL 2000 newbie here. I've done a lot of development in Access, but the limitations of that program have me looking at upsizing to SQL 2000. In my current project I need to import some data on a regular basis from an Oracle database. I've installed the Oracle client and used the ...more >>

Hi all, This might be a very stupid question: To do an upgrade of the SQL Server driver, would it be sufficient to just copy the latest version of the SQLSRV32.dll to the C:\Windows\system32 directory (overwriting the existent one). Or would I need to do more? Thanks in advance, BoB ...more >>

Hi gurus, May be my question is funny to all of you guys but I don't know why we have to keep the backup and recovery history of databases in msdb. I try to read BOL & other documents but no clue. Thanks a bunch ...more >>

Hi there - i'm hoping someone can help me! I'm having a problem with a live database that i'm running on MSDE - It seems to have slowed down quite considerably from the test environment (even when all the data is the same). The is notably different on one particular query that takes 1 sec on t...more >>

SQL 2000 I have a table with 5,100,000 rows. The table has three indices. The PK is a clustered index and has 5,000,000 rows - no other constraints. The second index has a unique constraint and has 4,950,000 rows. The third index has no constraints and has 4,950,000 rows. Why the row ...more >>

Hello Everybody I run the following query against to identical databases. Execution time on the first DB is 0 seconds, on the other 6 seconds! SELECT dbo.HRMABZ.EMPKEY , dbo.HRMABZ.CONNUMB , dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXT FROM dbo.HRM_CALENDER INNER JOI...more >>

Not PARSING "..." is this a BUG!!!!! [ BEGIN RAISERROR ('The employee is already in the list of overtime for this date!.......................................', 16,1) RETURN -1 END ] = [ BEGIN RAISERROR ('The employee is already in the list of overtime for this date!', 16,1) RETURN -1...more >>

Hi All, I already have a web site running with SQL Server as a backend (in english)For future growth, I would like to make it localized. Regarding the database, I have come up with several approaches. 1) just simply add the column in those table which needs different language. 2) a...more >>

I earlier posted this quetion to the Oracle group, but I am curious to find the solution to this problem in SQLserver. I want to find the second highest salary earner in each department in the example below: ID Department Salary 1 101 400 2 101 550 3 101 ...more >>

Hi, Is there any way to insert the output of xml_auto into a table for eg: select * from categories for xml auto i need the output of the abouve query to be inserted into another table the destination table has one column, ...more >>

Hello all, Quick and easy question for ye all: what's the syntax to add data into a table in SQL Server? I've only learned it in Access - i'm pretty sure it involves declaring the fields or something like that. Maybe it's easier with an example: this is how to create a table in MS Acc...more >>

Hi, Twice this has happened..... WinXP Pro SP2. SQL server 2000 When I have used a third party app (.net tool) to select my sql box from the "available list" it pauses for a while and the drop down list is empty. I manually type the name of the server in and it goes ahead just fine. The same ...more >>

I have an ASP (IIS 4.0) based website fronting a SQL Server 2000 database. I am trying to avoid using dynamic SQL for queries, and I am considering prepared statements as an alternative... All the advice I have seen strongly suggests that connection objects should not be explicitly cached, eit...more >>

In EM that is, in QA if I use: use master select * from sysdatabases I get: (6 row(s) affected) Server: Msg 220, Level 16, State 1, Line 1 Arithmetic overflow error for data type smallint, value = 42840....more >>

Hello everybody, ------------------------------------------------- CREATE TABLE [T1] ( [IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [DateEvt] [datetime] NOT NULL, [Value] [varchar] (10) NOT NULL , [DataX] [varchar] (10) NULL , CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ( [IDX] ) WI...more >>

Hi. I am trying to log (Not SQL Transaction log) all deleted rows into a custome log file after performing "cascade delete". I couldn't find any reference for this in SQL manual or News group. Byung Choi ...more >>

Behavior I found: Invalid Column name in select embedded in a IN() clause appears to return a NULL, and fails silently - completing the query giving incorrect results. Behavior I expected: RunTime error, as column didn't exist. ------------------------------------------------------- ...more >>

Hi, Well I'm brand new to frontpage and SQL so I'm probably doing something really stupid.... but here's the problem. I have created a form that saves it's information to an SQL server databse. So I created all the fields on the form and created corresponding columns in a a table in the SQL dat...more >>

Hi, I have 2 MS SQL Server 2000. What I want to do is when Server 1 has database changed, then all the data changed will be reflected to Server 2. What will I have to do in these 2 servers? Any help will be appreciated. Jason ...more >>

Hi all... I've been stumped by this for days. Bit of ASP code: (IIS) Set LocalConn = CreateObject("ADODB.Connection") LocalConn.CursorLocation = adUseClient LocalConn.CommandTimeout = 0 LocalConn.Open sConnStringSO (SQL Server 2000, irrelevant what it is, see below). LocalConn.Errors.Cl...more >>

Hi NG, I'm trying to run the following script as part of a larger scriptfile using isql: USE MYDB GO CREATE VIEW AGENCYSEARCH_mwb AS SELECT Id, SaveId, Checked, SessionId, substring(convert(char(26),InsertedDate,109),1,20) + substring(convert(char(26),InsertedDate,109),25,26)"Insert...more >>

I have two instances of SQL Server running on my Development machine. I am having some performance problems and while investigating the problem I saw with the Process Explorer form Sysinternals that both instances consume each 800 Mbytes of memory! I experimented with sp_configure and by givin...more >>

Is it possible to export the results of a query using SQL Server query manager to an excel spreadsheet file. Thanks in advance. ...more >>

Is it possible to export the results of a query using SQL Server query manager to an excel spreadsheet file. Thanks in advance. ...more >>

How to craet setup msi file or package in .Net VB, it should create database in SQL server and ODBC driver also, is there are scripts for that where can i find it, and how do i do it? Thanks for help in advance Hitendra ...more >>

How to Deploy SQL server Database to another PC, How to create a package that craetes Database as well as ODBC driver for accessing data at enduser PC, using .Net VB ...more >>

Hi everyone. How can I get the unique row from a table which contains multiple rows that have exactly the same values. example: create table test ( c1 as smallint, c2 as smallint, c3 as smallint ) insert into test values (1,2,3) insert into test values (1,2,3) i want to remove whic...more >>

I have a stored procedure that suddenly started performing horribly. The query plan didn't look right to me, so I copy/pasted the code and ran it (it's a single SELECT statement). That ran pretty well and used a query plan that made sense. Now, I know what you're all thinking... stored procedure...more >>

i have a list of ID's (indexed) more than 2500 items. first i have to select these records from one and then Update in another table. when i pass the list to sql with IN() operator in select query it takes too long (about 2 mins) and when i run update query with same criteria it takes too much...more >>

Hi, I'm finishing up a beginning SQL class where we learned on an Oracle database and the transition to working on SQL Server is easy. The next more advanced course will be in PL/SQL, but I know I will be working on SQL Server in the workplace, so my question is if I should take this course...more >>

Is there a way to call a SQL user-defined function in a DTS, perhaps in an ActiveX transformation? I want the destination table to have a value that was modified by a pre-defined function. -Noelle...more >>

Using small stored procs or sp_executesql dramatically reduces the number of recompiles and increases the reuse of execution plans. This is evident from both the usecount in syscacheobjects, perfmon, and profiler. However I'm at a loss to determine what causes a compilation. Under rare circ...more >>

I'm trying to simply change a column definition from Null to Not Null. It's a multi million row table. I've already checked to make sure there are no nulls for any rows and a default has been created for the column. My log is set to autogrow and as the alter column colname char(6) Not Null ...more >>

I am trying to write a utility/query to get a report from a table. Below is the some values in the table: table name: dba_daily_resource_usage_v1 conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_batch ------------------------------------------------------------ 80 |farmds_w|Farm_R|4311 |88 ...more >>

hello people! I am very new to the SQL World. Currently I work on DB2 and ORACLE and this is a new "fruit" for me. I have the task to automate the following scenario: - backup database A - restore database A into database B (overwrite database B) - perform the above every night at 23:00. ...more >>

I'm faced with a situation where I will need to calculate a column for a resultset by calling a component written as a VB6 DLL, passing parameters from the resultset to the component and setting (or updating) a column with the result. I thought that perhaps the best way out would be to create a...more >>

Hello, I have 2 ways of updating data I'm using often 1) via a cursor on TABLE1 update fields in TABLE2 2) via an some of variables ... SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVAR UPDATE TABLE2 SET FLDx = @var1, FLDy = @var2 WHERE ... Now I have a syste...more >>

Need help on the Auto Number or Identity Seed on the Oracle Database I got an Access database that need to be converted to Oracle 9i. Somehow the Trigger we created to simulate the "AUTO NUMBER" on Access could not create the sequence number as soon as the value has been inserted. The seque...more >>

I understand the number of foreign keys allowed is restricted by the DBMS I use. In a general relational schema design perspective how many foreign keys a table shall have? If I have large number of foreign keys what anamolies it will lead to? Is this crucial to identify all the foriegn key rel...more >>

I'm using replace in a stored procedure to eliminate carriage returns a user might have entered in an Access field. The problem is that even though it seems to strip out the carriage return I can't get rid of the "□" character. This is my code: DECLARE @myReturn nvarchar(1) SELECT @...more >>

Hi, I'm trying to select some stored procedures' names who cannot be found into the 'FilterProcedure' field of my table 'Lists': select name from sysobjects where (type = 'p') and (name not in (select FilterProcedure from Lists where FilterProcedure is not null)) ...more >>

I have several databases. In those databases, collectively I have several hundreds objects. All these objects are owned by the dbo. I would like to find: Item (a) a sql script or some automated way of finding the minimum set of permissions so users accessing the database via applications ca...more >>

Hello :-) My question is: If I query a partitioned view, but don't know the values in the "where x in(<expression>)" clause, i.e.: select * from viewA where intVal in(select intVal from tbl1) . Compared to: select * from viewA where intVal in(5,6). Of course "intVal" is partitioning column. ...more >>

I have the following data (very simplified version) TransactionId Agent_Code ------------- ---------- 191462 95328C 205427 000024C 205427 75547C Agent Code 75547C is a corporate agent. The others are not. I have a list of corporate codes so I can query against it, BUT...more >>

I'm trying to write a query which allows that multiple values from one column are placed in one record. ex: table Nr Letters 1 A 2 A 2 B 2 C 3 A 3 B 3 C 3 D 3 E 4 A The result I want to get from an select: Nr All Letters 1 A 2 A, B, C 3 A, B, C, D, E 4 A Olivier...more >>

Lets say I have a table PortDates with 3 columns like this: PORT ARRIVALDATE SAILINGDATE NewYork 12-15-2004 Rio 10-12-2004 Hamburg 10-14-2004 etc. I want to sort the ports on dates from both date columns SELECT Port FROM PortDates ...more >>

Hi, I've got 2 table variables inside of an SQL 2000 function: @tmpBigList(BItemID, BRank) @tmpSmallList (ItemID, Rank) The following UPDATE statement can run for a long time if @TmpTable1 has 500 rows and @TmpTable2 has 35,000 rows. UDPATE @tmpBigList SET BRank = t.Rank FROM @tmpBigList...more >>

Hello, How do I get SQL server 2000 to continue the sequence of identity column values after I perform an insert into the table with the set identity_insert <table name> on statement? Eg if I insert values into the identity column with values 1-999, how do I get the next number to be 1000? Ca...more >>

Hi, -- ddl /* create table #tmp (col1 int); insert into #tmp values(1); insert into #tmp values(2); insert into #tmp values(3); create table #tmpChild (col1 int, fkCol int, Num int); insert into #tmpChild values(1,1,3); insert into #tmpChild values(2,1,2); insert into #tmpChild values(...more >>

Sorry for my english I have a table that contains data of career about the person (staff) like this ... EMATR EANID EMEID EGIID ecc. .. ecc. .. ecc. .. ecc. .. 1 1999 12 31 2002 12 31 1 1 2003 1 1 0 0 0 3 2 1999 12 31 2002 12 31 1 2 2003 1 1 0 0 0 3 4 1999 12 31 2000 7 31 1 5 1999 12 31 2...more >>

I need to automatically generate via SQL, export to Excel and e-mailed to other people montly. what should I setup in the sql server?...more >>

Hello, I need to change collation in my database (more databases acctualy). Therefore, I wanted to make a script, which will do it at one more time. I already have a cursor, updating collation on all tables (fields) in database. The problem is, before I will to update the collations, I nee...more >>

Hi, Is it normal to have @100 process id's in process info folder? I noticed that some process don't get deleted once they are finished. Please advise. Thanks, ...more >>

Could I make a Replication DB between DB2 and msSQL server? Any aids? ...more >>

In sql server 2000, how do you manually go and truncate the logs? Does this only occur when you create a backup? all I want to do is manually shrink the log files. I have backups, so I don't need them. I actually backup the db once a week and back up the logs every day, yet the logs are gettin...more >>

There are several day_timestamp for each index_id. Anyone can help me to write a sql to generate the most recent day_timestamp of index_ids which has not accessed into the system in 90 days from today's date. So, I need to get the most recent date and time for each index_id in 90 days from to...more >>

I have a stored procedure that queries a database using a Select statement with some inner joins and conditions. With over 9 million records it takes 1 min 36 sec to complete. This is too slow for my requirements. Is there any way I can optimize this query. I have thought about using an in...more >>

Hi all, No matter how hard I've tried and researched, I just cannot figure out how to do the following. I kindda think there must be an easy way. I want to join two tables. Table 1: C1 C2 C3 A X1 Y A X2 Z A X3 Y Table2: C1 C2 C3 A N Y A M Z A N Y So I do: select i1.*...more >>

How can i create database level triggers in MS SQL? i m not talking about table trigger or view trigger. Khurram....more >>