I am doing a test on migrating an Oracle 8i database to SQL 2000. I've never done this before and I would like to find out if there are any complications or side effects if I try doing the transfer of data using DTS? If not DTS then what would you suggest I transfer data with? Thank you ...more >>

Hi I have a fairly complex access frontend/backend app which I need to upsize to SQL Server. At this stage I would prefer to use access as frontend. I have the following questions; 1. Should I keep using the mdb as frontend or switch to an adp (access project) instead? Any reasons? 2. I ...more >>

Hi again, I am able to schedule a backup and default it to the server. However, is there an option or a way to locate the backup somewhere else on the network? regards, gilbert ...more >>

-- Business Rule, first name, middle name and last name can all be null -- ddl create table #cat (catID char(8) primary key, first_name varchar(15) null, middle_name varchar(2) null, last_name varchar(15) null) -- dml, populate sample data insert into #cat values ('Black123','ghost','','bi...more >>

CREATE TABLE Sales1 ( varchar (10) CustID, varchar (10) TransID, datetime SaleDate, money S1, money S2, money S3, money S4, numeric V1 ) CREATE NONCLUSTERED INDEX Sales1_CustID ON Sales1 (CustID) CREATE NONCLUSTERED INDEX Sales1_SaleDate ON Sales1 (SaleDate) "m...more >>

Greetings, I am adding foreign keys to a database and saving the generated scripts. What I do not understand is that all script begin with empty transactions. Why ? Example follows : /* vendredi 29 juillet 2005 10:54:36 User: Server: (LOCAL) Database: NewsPape...more >>

I'm chasing after a documetn that was available on one of the Microsoft websites that was titled somethign like "MS SQL Server Best Practices" and detailed a nyumber of best practices about securing the server. Included in this was revoking public access to the system table objects. Can som...more >>

i need to import data to excel from an sql db. the sql db is on a server, excel will be on a remote computer. I'm quite happy to use access as an intermediary if needed. i would also like to be able to write from excel to sql is poss. although it this if tricky then i might well concentrate on i...more >>

Hi, Is it possible to get metadata (i.e. descriptions of tables etc.) in sql-server? In Oracle you can retrieve this information with tables like all_objects, user_tables, user_views etc. For example, this query selects the owner of the table 'ret_ods_test' (in Oracle!): select owner fro...more >>

What would be the correct way of writing a sql select state with where clause while also using IF ELSE. I am using T-SQL and I cannot get it to work. I probably have the syntax wrong. I want to be able to have different where/and/or clauses in the sql dependant on what value was passed into th...more >>

Hey all, sorry for asking such a grunt question, and thanks in advance for any help... My hosting ISP offers access to their MS-SQL database, and my understanding is that it's generally simplest to use some sort of client app to do stuff like adding tables and whatnot. SQL Server includes E...more >>

Hi! I need to recreate the system tables (sysobjects, syscolumns, sysindexes, sysforeignkeys, sysconstraints, sysreferences, sysindexkeys at least) in another SQL server. You may say "Thats easy! Backup and restore the database!" and I would answer "I can't, the database size is above 50GB and ...more >>

This is the result I get in Word as I handle a document that merges data from the database. The field «Date_Fin_Membre» returns me this result even if the query stipulates WHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10), dbo.Membre.Date_Fin_Membre, 101)). I have also tried other ...more >>

I am attempting to execute the Stored Procedure at the foot of this message. The Stored Procedure runs correctly about 1550 times, but receive the following error three times: Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours, Line 68 Subquery returned more than 1 value. Thi...more >>

Please help! Getting this error intermittently: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.  at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBeha...more >>

I am trying to create a query which should return 4 columns Facility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicants I would be glad to even have just 3 columns since, the Total can be computed in the display table (ColdFusion interface) Facility | NoOfActiveApplicants | ...more >>

We have a table that has an identity field along with 5 other domain fields. The identity field is not declared as a primary key. The table has 3.5 million records. A consultant was hired recently to provide insight. His major recommendation: modify the table to make the identity field a pr...more >>

I wish to run this script daily. Can this be scheduled? backup log shipmateDB with truncate_only G ...more >>

I am no expert in sql, but I keep stubbling on this problem: I have a table t1 with 2 columns (a,b) I have a table t2 with 2 columns (c,d) I need to delete all records from t1 which have the same value (a,b) than the value of (c,d) in all records in the t2 table. I oracle, this is simple:...more >>

I am trying to query data in an XML variable and I am having trouble getting the data out in the format that I would like. Given the following XML: declare @myDoc xml set @myDoc = '<Ownership TeamGuid="23EA393A-3926-4A55-8329-FE747593379D" TeamName=".NET Framework"> <Roles> <Role Guid="...more >>

Hi all, I have two tables: workgroups (wg_id, wg_name) workgroups_keywords (wgk_wg_id, wgk_keyword) Each workgroup has an associated list of one or more keywords. What I want do to at first was given a particular list of keywords bring back a list of workgroups that have at least one m...more >>

I have one field organization_operating_name that is on two tables vendor and vendor_loc I want to update the vendor name to the vendor_loc name I tried this but get errors... update vendor_loc set organization_operating_name = vendor.organization_operating_name where organization_op...more >>

I tried all the INFORMATION_SCHEMA on SQL 2000 and I see that the system tables hold pretty much everything I am interested in: Objects names (columns, functions, stored procedures, ...) stored procedure statements in syscomments table. My questions are: If you script your whole database ev...more >>

I have a scheduled job contain 2 steps. Step 1 is execute DTS Package using CmdExec 'DTSRun', step 2 using TSQL to execute just simple query. This job execute recurely every day, every 6 hours. The problem is, sometimes this job was successfull, but sometimes failed with error as below : .......more >>

This is what one of the tables looks like - they are all similar and there are about 60 different tables: CREATE TABLE SalesData1( smalldatetime TimeStamp, varchar(8) CustomerID, numeric S1, numeric S2, numeric S3, numeric S4, numeric X ) I'm not sure on the data types, S1 thru S4 are...more >>

I'm using a Unicode sql script imported using OSQL. One of the values we are attempting to insert is a Registry Multi-String value by passing a string to a stored procedure. These Multi-String values appear to be delimited by a Hex 06 (^F) character. When I import this character, embedded in ...more >>

I have 2 tables: - Users, with fields id and fullName - Accounts, with accntID, userID, accntName, Active I need to write a query which lists all the users, along with the number of accounts they have. I am using the following query - SELECT U.id, U.fullName, Count(A.accntID) AS CountO...more >>

Hi, I tried to Develop one Report in SQL Reporting Service. I have installed SQL Reporting Serivice in Windows 2003 machine. SQL Server Database is in another machine, running on Windows 2000. I developed and deployed the report and all went fine. When I tried to see the report through ...more >>

My table looks like this: char(150) HTTP_REF, char(250) HTTP_USER, char(150) REMOTE_ADDR, char(150) REMOTE_HOST, char(150) URL, smalldatetime TIME_STAMP There are no indexes on this table and there are only 293,658 records total. When I do a select like this it takes forever: SELECT C...more >>

Hey, all... Some time ago, I used a tool which I believe was available from Sun. It was a java applet, as I recall, which sat between a SQL client and a SQL server. It allowed the client to connect to it at any port, and would in turn connect to the server at the standard TCP port (or whateve...more >>

Hi, I'm trying to create a Stored Procedure that returns a recordset, but I want to be able to choose the ORDER BY clause in mijn parameter list of the Stored Procedure. Since CASE .. WHEN can only be used in the SELECT clause, I came up with the following: -- BEGIN SCRIPT -- DECLARE @bla...more >>

Is Set RowCount @RowCount More efficient than simply using TOP? Thanks for any input. ...more >>

hi, I am planning to upgrade my SQL Service Pack from 3 to 4. Can any one guide me what are the points to be considered before doing this. One more point I want to do this in a Clustered Enviornment. I am using SQL Server 2000(Enterprise Edition),3GB of RAM. Thanks in advance T...more >>

When I declare a cursor,I use a variable to replace the sql statement: DECLARE rs CURSOR LOCAL FAST_FORWARD FOR @sqlPlan But it is not true.Who can correct for me. Another question is : How to execute a sql statement state by a variable "@sqlPlan" and insert the result to a table "@FeatReque...more >>

I've used ADO before and now SQL-DMO to execute SQL statements on a SQL Server database. I was just thinking if SQL-DMO has any capabilities that ADO.NET does not have when it comes to having a programming interface to SQL Server? Would you know if I should not waste my time learning SQL-D...more >>

Hi everybody, Is there way to check what were the last modifys to specified database?? I mean the last insertion, deletion ........... .. and in which tables. Thanks for advance -- "Imagination is more important than knolwege" (Albert Einshtein) Message posted via SQLMonster...more >>

Hello, another question from a newbie to mssql. Is there a way of allowing access to database only by providing username and password (disabling trusted connection and preventing administrator to access database through enterprise manager or otherwise without supplying username and password)? ...more >>

I'm a developer, not a DB admin. I'm writing a .NET app that uses crystal reports. The table I need to output is built inside a stored procedure. No choice, it makes use of some temporary tables. (Believe me I've tried to get around that.) Crystal reports seems to only know about tables ...more >>

Hi, I am new to database development and am writing a database as part of a university course I have created a table as below called CableWire - the table is created ok. CREATE TABLE CableWire (CableWireID CHAR(7), BSstandard CHAR(16), Colour VARCHAR(16), Material VARCHAR(16), Metr...more >>

I have a problem with my TEMPDB Database.It gets extra size through importing data into one of my databases in same server. How can I shrink my TempDB Databse? Thanks ...more >>

I want to loop through a table (tblrelmanager) and merge the results with a > word doc. The trouble is I am not used to SQL coding and I cant get my > WHERE > statement to work. > Can anyone tell me where I am going wrong > > Thanks in advance > Dave > > > > > >...more >>

I am trying to setup a Performance Condition Alert and on this one SQL 2000 Server the 'Type:' drop down does not give me the option to choose "SQL Server performance condition alert". "SQL Server event alert" is the only item listed in the drop down. Is there some setting on the Server that I...more >>

Using SQL 2000. I need to select rows based on a date range. Both the beginning date and ending date of the range will be entered in the mm/dd/yy format. There are 2 columns in the table called MonthOfEval and YearOfEval, both varchar(9) and both with data like "August" and "2005-2006". I th...more >>

In Access, if I want to update one table with information from another, all I need to do is to create an Update query with the two tables, link the primary keys and reference the source table(s)/column(s) with the destination table(s)/column(s). How do I achieve the same thing in SQL? Rega...more >>

I need to connect SQL Server 2000 to DB2 on z/OS through DB2 Connect 8. I can successfully connect and query data through a System DSN, but trying to link the server using this DSN and MSDASQL fails (can't seem to find the DB2 Connect server). I've also tried IBM's OLE/DB driver (IBMDADB2). ...more >>

I work in a University and we are looking at installing a MSSQL 2000 database for students to be able to use for their final year projects. Previously we gave them mysql and access. I am quite new to MSSQL and was wondering if any of the stored proceedures which get created with accounts could l...more >>

I have a flat file table that describes crash data in SQL Server. It contains vehicle information. I would like to know if anyone knows a SQL statement that could go from this table= events CRASHID | VEH1_TYPE | VEH2_TYPE | VEH_3TYPE ------------------------------------------- 555555 ...more >>

Hi, We are using Month-year tables to keep the history of long transaction of our application. For example: We capture the details of a certain action in table "TransDtls<CurrMonth><CurrYear>" (this month: TransDtls072005). This way tables keep growing. every month a new table gets created....more >>

Is there a way to tell who or what process deleted a record in a SQL table. I know you can setup triggers or Profiler - but is there a way to see how a record was deleted if you DO NOT have a trigger or Profiler already running? This is SQL 2000 Standard, sp3a with default options and configura...more >>

Our database server has started acting weird and at this point I'm either too sleep deprived or close to the problem to adequately diagnose the issue. Basically to put it simply... when I look at the read disk queue length, the disks queues are astronomical. normally we're seeing a disk ...more >>