Hello, I'm trying to set up transactional replication on two SQL2k Servers. Unfortunately I stuck on the following problem: I use 'Configure Publishing and Distribution Wizard' , I make one of the servers (publisher) its own distributor, then I set all the paths .After pressing 'Finish' but...more >>

Hello all- I have an issue with a SQL statement. I have a table defined as: CREATE TABLE [dbo].[History] ( [Tag] [varchar] (255) NULL , [DateTimeStamp] [float] NULL , [HistoryValue] [varchar] (255) NULL ) ON [PRIMARY] GO and I try, for every single tag, to find the maximum of Histo...more >>

I'd like to know if anyone can give me a detailed explanation as to what happens to a SQL 2000 database during a restore. The restore backup set will be a complete database restore for the purposes of this thread. Are indexes rebuilt? Are statistics rebuilt? Or is the db in the same state as...more >>

Is there any way to allow a user to use the xp_cmdshell extended stored procedure without giving that used execute permissions to that stored procedure? Let me clarify. Lets say I (as the dbo) create a stored procedure called sp_send_err. This stored procedure executes the xp_cmdshell extend...more >>

I am in the process of cleaning up an existing database application. I know for sure that there are many unused tables and procedures contained in this application. I wanted to use the sysdepends table in SQL Server to find out what tables are used, but as many of you already know, the depende...more >>

I urgently require the source code of xp_gettable_dblib, which is a sample program under the path of SQL installation. When I was going to locate it, I discovered that I haven't installed it during the installation of my sql server. I am out of town now and my CD is in my home so I am asking for...more >>

Hi Fellows, I am trying to write a User Defined Function in SQL 2000 to accept a Parameter of type Text Field and then Do a replace function to manipulate the some strings inside that text data. (Scenario: I have an email body and i have to replace some tags like <^ContactEmail^> and others t...more >>

When a workstation losts connection to server, it can leave an uncomplete transaction. Then SQL Server removes the transaction. Could anyone guide me how to set the delay before SQL Server do it ? Thanks in advance John S. *** Sent via Developersdex http://www.developersdex.com *** D...more >>

The following code: DECLARE @tsql varchar(1000) CREATE TABLE #Test (...) SET @tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)' INSERT INTO #Test EXEC(@tsql) generates the following error message at the insert statement: Server: Msg 8501, Level 16, State 3, Line 4 MSDTC on server 'MyC...more >>

We needed to replace our Motherboard on a server running win 2K Adv. Server with SQL server 2000. After replacing, we could not bring the system back with the previous OS. So, we have decided to migrate to Windows 2003 server and install SQL svr on it. On the old server, the SQL DB is resi...more >>

I have the following tables on a non-SQL database that I can access via ODBC: Updated OrderHeader OrderDetails InvoiceHeader InvoiceDetails When an order or an invoice is added/updated a record with the order number is added to the Updated table. I need to loop through the Updated table...more >>

Hello All, Short Version: How large can a MS SQL Server INT become? Long Version: I have this SQL statement... CREATE TABLE admin_tokens ( user_id varchar(27) NOT NULL default '', elt varchar(16) NOT NULL default '', token_index int(11) NOT NULL default '0' CONSTRAINT...more >>

I have created the following trigger: CREATE TRIGGER [CreateFile] ON OutputTable FOR INSERT AS Declare @filename nvarchar(35) Declare @filecontents nvarchar(2000) Declare @strcmdshell varchar(150) SELECT @filecontents = OutputText FROM INSERTED SELECT @filename = 'c:\' + OutputFi...more >>

I am attempting to connect to a remote default instance of SQL Server 2k on Win2k using Enterprise Manager from my local host but receive a "SQL Server registration failed, timeout expired" error message each time. I am able to connect using Query Analyzer when specifying the IP address of th...more >>

I'm trying to pass parameters to an extended stored procedure, to no avail. I would like to pass two integers to the dll and I have the following three snippets: 1. The C++ portion of the dll: .... declspec(dllexport) int myAddNumbers(int m, int n) .... 2. The creation of the extended ...more >>

I have two columns in a table: StartDate DateTime and StartTime DateTime. The StartDate column holds a value such as 07/16/2004 The StartTime column holds a value such as 3:00:00 PM I want to be able to add them in a stored procedure. When I use StartDate + StartTime I get a date two days ear...more >>

Attempting to register (from a Win XP Pro machine) to my SQL Server 2000 machine (Win 2K Server), via Internet (http), error says "Errors occurred while connecting to 'mysqlserver'. Cannot open connection to Analysis server 'mysqlserver'. Network error 'mysqlserver' Do you still want to regis...more >>

Hi, Does anyone know of a script that will give "weighted job duration"? I want to use it, to identify which jobs are hogging the CPU. That is for a given server, list the sql agent jobs ordered by: (avg job duration in minutes) times (avg num of times job runs in a given day)....more >>

Hi, How to replicate data from SQL Server CE (Pocket PC) to MSDE using C# (data size = from 2 to 10 MB)? -- *Best regards,* Klaudiusz Bryja ...more >>

Using SQL2000. I want to return the # of columns with non-null values. Here's my query so far: select case when Dx1 is not null then 0 else 1 end + case when Dx2 is not null then 0 else 1 end + case when Dx3 is not null then 0 else 1 end + case when Dx4 is not nul...more >>

Hi all, I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need to implement a logic to receive an adress build out of various user definable fields from various user defined tables. The function is already implemented in the Client software and as UDF-compliant in MySQL and Or...more >>

I have some software (written in Delphi 5) which has been working for several months without a problem. I have been given a copy of the database on our development server (SQL 7) and have pointed the software to this. So, nothing has changed with the software. In theory the servers should be t...more >>

I had an error crop up with a DTS package which I need some help with. Error Message : The application, , generated an application error The error occurred on 07/20/2004 @ 06:12:28.203 The exception generated was c0000005 at address 41504E2C (<nosymbols>) This happened this morning, but t...more >>

Hello, I have a linked server to oracle 7.1 from SQL 2000. when I try to execute simple select statement which returns one row of data using openquery is not fetching the data. After 30 minutes SQL Query analyser is hanging. This is happening on the production server and it is working from...more >>

hi, i had been removed the row counts and the column spaces... but i failed to remove the extra tabs between rows of data return from the osql output. how to detect the row delimiter? i noticed i can use bcp to have a more decent output file, but i don't want to do too much of file read-write...more >>

I'm using msde. On the little icon in task tray, you have under current service, a choice of mssqlserver, and another choice is msdtc. What is msdtc? And what is sqlserver agent? ...more >>

I have a master table that has all my accounts in it. In a 2nd table I have update notes per each account, so there are multiple notes entries for each single ID account in teh first table. How can I get the OLDEST dated entry in teh second table, for every account that's had a not entered...more >>

I have the following error: Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coor...more >>

Dear list, I am trying to get the names of the tables and the column names from the code below for a database but it is not working. When I run the query below the column titles are delivered but there is no data. I think this might be a premissions issue. Has anyone run into this before?...more >>

Hello all, Newbie here. SQL 2000, Windows 2000 I'm trying to alter tables in my SQL DB using statements like the following: /* AD_GROUPS */ alter table AD_GROUPS alter column AD_GROUP_NAME nvarchar(64)not null go /* ARTICLES */ alter table ARTICLES add column CONTENTTYPE_REF int nu...more >>

Hello, I've got 2 databases (both on MS-SQL Sever2k). One of them (remote) contains table which is often updated. For various reasons I'd like to maintain a copy on local MSSQLServer. Is there any built-in mechanism which allows to do such things?! I can do this manually, by examining the r...more >>

Hello! We are running MS SQLServer 7.0 with ASP on Win 2k Server. Today suddenly the Web server couldn't connect to the DB Server because the password for sa was wrong. We found out that the password was changed. Is it some kind of hacking thing or just normal password expiration? The same...more >>

I apologize if this has been asked before- I searched google but could not find a concrete answer. I recently inherited a database whose t-sql code is written in a format that I find difficult to read (versus the format I have used for years). I have tested the queries below using the SQL P...more >>

I received this error message in the application event logs. Can any one tell me what this is pertaining to? Error: 7102, Severity: 20, State: 99 SQL Server Internal Error. Text manager cannot continue with current statement. Thanks Barb...more >>

Hi. Does anyone know any web site with common database models presented and explained. Database models like calendar, adressbook, storehouse... Thnx in advance ...more >>

SQL2K, sp3 Active Server Pages front-end I am having a blocking problem with a spid executing a stored procedure that does no updates. When I look at the locks the blocking spid has, some of them have lock mode "IS". Looking in BOL, I see "S" is shared, but I don't see "IS". Can anyone tel...more >>

Can someone please help me ... the transaction Log on my database is getting ridiculously large and I'm at a loss as to what to do?? The Db in question is in SQLServer 2000 and is on a web server. The data in the database is sent new data from access on a 15 minute basis from our company (the ...more >>

I've been trying to get the scripts posted earlier to the group by Clay Beatty to work properly. Using the three components he posted, I have managed to get a new SP generated. However, when I paste this scrip into a new copy of the original database (different name, same structure as a test), I...more >>

Hello all, Thinking about building a new database in the enterprise addition of sql server and using some horizontal parititioning techniques in order to accomaodat what will eventually be a monster huge database. Can you share some hard earned experience, gotchas, etc...with me? We will be s...more >>

hi there anyone had an idea to join following 2 queries to 1???? ----- QUERY 1 --------------------------------------------- SELECT TMS_CaseF_2.Name AS TCDomain_0, TMS_CaseF_3.Name AS TCDomain_1, TMS.CaseF.Name AS TCFolder_2, TMS_CaseF_1.Name AS TCFolder_3, TMS.TestCase.Name A...more >>

Hi, guys. A very simple question for all of you: how can I get the amount of bytes exchanged during a Merge replication between two Microsoft SQL 2000 servers? Thank you. Bye, Angelo.-...more >>

I have a db that has a table x in it called data1 I have a program that does to things, updates values in the data1 table and also inserts new rows into this table. The update existing values works great. Then when the insert loop runs, I get this error on the following line. insert into...more >>

hi i am trying to write a insted of insert trigger to create a unique id when i insert a record in my database. can anyone give me an example with out using identity. thanks...more >>

The business rule is, the sales manager is commissioned on the avg. number of appointments set up per salesrep per day during the month. I have 2 tables: The UserLog table records only 1 entry per day per user (salesrep). This will log how many salesreps worked a particular day. The second ...more >>

Coming more from Oracle background, I want to do a point in time recovery test on SQl Server. Let's say database PUBS backed up every night and Trans log backup 3 time every 6 hours between 6 a.m. and 6 p.m. , now we want to create a database PUBS2 and recover data upto 2:00 p.m yesterday , i.e....more >>

I would like to pass an unique identifier (UserID) to a Crystal Report from a SQL stored procedure. I found an article from Business Objects about this issue, but I couldn't get my head around it's hard coded example. Does anyone have an example of this? Would I need to also create a paramete...more >>

I'm building a database server that's going to be running MS SQL 2000 server and .net application. Should I go with SATA RAID or is U320 SCSI RAID recommended? The latter is much more expensive, but is the performance worth it? Also, should I go with dual Xeon or should I go with AMD's Opter...more >>

Hello, I've got installed Win 2003 SBS Premium with the SQL Server 2000 on a server machine. It works almost fine, except the application which uses the SQL Server. The main part of the application runs (since the last update) fine, but other tools of that application (database import and the ...more >>

hello to all, there are two databases named A and B. One database contains employee id as primary I have another project database which includes that employee id as foreign key. How can I reflect my updations,deletions thank you in advance, vishnu...more >>

I have a situation where I want to take data from a local sql server 2000 db and update a remote database. I have the sql all set, was wondering if this can be done in a timed interval with a stored procedure on the local db. Thanks in advance for your time Dean-O...more >>

Hello Specialists ! Please help me - i need advice in importing textual data to SQL Server. I am using DTS with a simple process : Text(source)->Connection. I want to increase speed of importing because i have to import 4GB (1000 char lines lenght) of data. Do you have any tips for me ? ...more >>

I have two table both say A and B. If i insert a record in A that record should be inserted in B. If i delete a record in A that record should be deleted from B. Is that possible.If yes please tell me. Thankyou in advance, vishnu...more >>

I'm using an Access2K front end on a SQL Server2K backend. I use Scope_Identity() in a lot of stored procedures to pass the newwly inserted record's unique ID to the next select statement in the same stored procedure. What I'm wondering is how I can pass the Scope_Identity back to the calling a...more >>

I have created an Access2K front end application that connects to a SQLServer2K backend. I use this vba code to create the connection from the Access app: Dim strConnect As String 'make sure all previous connections are closed: CurrentProject.OpenConnection "Provider=" 'create new connecti...more >>

Is there anybody out there with a MS SQL 2K Security Baseline or Security Checklist. Where can I get one???? Thanks in advance David ...more >>

Hi everyone, I tried to upload my database to my web host. Nevertheless as they do not give me enough permission to perform some DTS thus I have to generate script to do it myself. The problem is MSSQL script generation operation can only generate the definitions of the objects, no data inser...more >>

EXEC sp_attach_single_file_db @dbname = 'mydatabase', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\'mydatabase'.mdf' Server: Msg 5180, Level 22, State 1, Line 1 Could not open FCB for invalid file ID 0 in database 'mydatabase'. Connection Broken What can I do with suc...more >>

I wasn't able to figure out the vocab to search for this on usenet. I'm sure it's an easy solution, but I have no experience with SQL Server: Situation: tbl_CompanyData is a 1735 x 20 table with a CompanyID key in the first column tbl_MergedCompanyData is a 1735 x 20 table imported fr...more >>

I am getting quite a few deadlock errors where both sessions are trying to execute sp_execsql according to the the trace information in the error log (see below). The database is being asscessed by an application written in .NET, as well as a few people using Query Analyzer. This seems to be h...more >>

Hi folks, I've a sql query problem I was wondering if you all had a quick and dirty solution for. I've a query: Select code, value from table_a where date in (2004) and a_code in ('1000','2000') and b_code in ('01000','02000') This returns a table that looks like: A_CODE B_CODE ...more >>

Does anyone have any idea how to syncronise two SQL identical tables across a network, between servers? We want to run our website from a SQL tables on the same server, but set up so that if the main SQL tables across the network change, or the tables on the web server change, the two seperate...more >>

Hi All I know an SQL Server timestamp seems to be as useful as rocking horse for show jumping, but I'm hoping you know a 'fudge' to get me round a problem or at least confirm that it isn't possible. I have 2 tables, one called ACCOUNTS and one called STOCK. These tables have the usual int...more >>

Is it ill-advised to have columns whose values pull from scalar functions using other fields in the record as parameters? For example, if I have create table a(iID int primary key) create table b( iID int ,iDetail int, CONSTRAINT PK PRIMARY KEY(iID,iDetail), CONSTRAINT FK FOREIGN KEY ...more >>

Hi, I am having a problem with a replication over a Modem-Connection, which works fine over LAN. Has anyone experienced this problem before? Settings are: 2 SQL Servers 2000, SP3 on Windows 2000 Publischer Database ist about 3GB, Subscriber DB about 1,5GB Publisher and Subscriber are conne...more >>

hi im fairly new to sql more rusty than new but anyway here is my prob i need to create a stored procedure to create a primary key in a table this key should be 1st position Current yeary 2004 i use the the 4th number "4" 2nd position is 0-9 or A-Z ascii 48-57 and 65-90 3rd thru 8position P...more >>

I have a DELETE statement that deletes duplicate data from a table. It takes a long time to execute, so I thought I'd seek advice here. The structure of the table is little funny. The following is NOT the table, but the representation of the data in the table: +-----------+ | a | b ...more >>

I am extremely new at SQL Server2000 and t-sql and I'm looking to create a simple trigger. For explanation sake, let's say I have 3 columns in one table ... Col_1, Col_2 and Col_3. The data type for Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on Col_2 to compare Col_1 ...more >>

John wrote: > Hi, > > I am designing a table for classrooms with many features. Following is > the detail of the table (let's say the name of table is CRoom): > > 1. Room Name (key) > 2. Building name (foreign key) > 3. Capacity > 4. Chairs > 5. T-arm chairs > 6. Tables > 7. Desks > 8....more >>

Hi, I am designing a table for classrooms with many features. Following is the detail of the table (let's say the name of table is CRoom): 1. Room Name (key) 2. Building name (foreign key) 3. Capacity 4. Chairs 5. T-arm chairs 6. Tables 7. Desks 8. Lectern 9. 35 mm slide projector 10...more >>

I am trying to transform data inside one of my tables. However, I only want to change part of the data in a column because the last part is basically a UniqueID to a file. Is it possible to only change part of it? Here is what the column looks like. I have thousands of these. D:\oldpath\Con...more >>

Is there any way to set up a User in SQL Server so they DO NOT have to qualify a table name with the owner name in a Select statement?...more >>

I have simply query: SELECT time_start-time_end FROM table1 WHERE [.......] When I run this query, then I see: 1900-01-01 00:38:00.000 1900-01-01 03:43:00.000 1900-01-01 03:02:00.000 1900-01-01 04:31:00.000 I want to get sum of all this dates and times. How Can I do this? Best re...more >>

I have a windows batch file that executes a SQL Server bcp command. I would like to obtain a return code if the bcp command fails. However, I cannot seem to find the return code (if any) for bcp. For example, if the bcp command is improperly formatted, or has a bad password, I want the batch fil...more >>

I used Enterprise Manager to change the data type of one table column from one thing to another. When I attempted to save the change, I was warned that "one or more existing columns have ANSI_PADDING 'off' and will be re-created with ANSI_PADDING 'on'". I didn't expect to see this message. I ...more >>

It is my understanding that Views cannot have parameters. Also that stored procedures can not be queried. My problem is this: I want to select the rows that match a certain parameter. From that I want to select the most current 20 rows (there is a date field). From that I want to select...more >>

Hi, I'm trying to work around a bug that our helpdesk software has. When a new issue is created, it cannot automatically default 2 fields to the value of No like we need it to. I have a field called "Audited" and one called "Billed to Client". When a new issue is openned, it just leaves t...more >>

The following is a create that I was trying to run in SQL/2000 SQL Analyser: CREATE TABLE tblxyz as (Select * from tblPlayers); tblPlayers has 2 rows in it. I continue to get the error Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'AS'. I have tried i...more >>

After reading Erland Sommarskog's most enlightening articles on SQL Server 2000's error handling capabilities (http://www.sommarskog.se/error-handling-I.html and http://www.sommarskog.se/error-handling-II.html) I have a question regarding batch cancellation caused by a client. Because a batch...more >>

Hi, i want to search record in a table where a varchar column is equal to a word or this word with the letter 's' or this word with the letter 'e'. Today, i've this query : select * From Table Where Column = 'Word' or Column like 'Word[es]' I want to remove the 'or' of this query to have o...more >>

I have 3 tables: 1. tblSports which has an ID and Sport columns (basketball, baseball, football, and volleyball) 2. tblTeams with an ID and Team columns (TeamsA to D) 3. tblJoin which has two foreign key columns (ID for Sports, ID for Teams) This is where teams are joined to whatever Spor...more >>

C:\Program Files\Microsoft SQL Server\MSSQL\Data\" contains .mdb and ..ldb files that are huge, some reach 21GB! while backup files don't exceed 8MB. I guess it's a kind of transaction log, but I'm not sure. Can I erase them all and lose no information? Where I configure it to avoid it to ...more >>

Hello - I have over 100 MSSQL Databases on my SQL SERVER. How do I restrict all the MSSQL databases and its transaction logs to 100 MB. Can someone help me with any script which will do that. Thanks, Rubal Jain www.Rubal.net...more >>

I used Enterpise manager to make a backup of a SQL Database. When I restored it on another machine, I did not have any of my indexes. I checked the backup wizard and I could not find any reference to including or not indexes. Are my indexes there, but not built? Maybe a stored procedure I need ...more >>

Hi there, I've only recently started a project using sql server 2000 for the first time. One of the considerations we have to take is that we need to continuously age/purge data from a couple of large tables (100 million rows+) - to keep the tablesize growth to a minimum. Coming from an Oracl...more >>

We are converting a legacy visual foxpro system to use a SQL back-end. A number of (existing DBF) tables currently have a zero-filled primary key eg. '000255' which is just an auto-incrementing key - but always stored as a char field with leading zeros. For backward compatibility we are cons...more >>