Hi friends following query gets me list of assignments and timespent on them so far select fk_assid, 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from worktime group by fk_assid in the...more >>

Is there any simple way (exec sp_ etc) to "generate sql script" For example: I want send parameter (table name) and generate script like this: exec sp_GENERATE_TABLE 'TableName' Result = "CREATE TABLE TableName ....." ...more >>

Hello. I received a detached database/log from my client. I attached the database to my SQLServer 2000 SP3 installation just fine. In the enterprise manager I can query any table I want. However, when I attempt to query a table via the query analyzer, it always fails, even though I am lo...more >>

Hi all. I have a procedure in my database whose code is more or less the following: CREATE PROCEDURE Fnt ( @Name nvarchar(20) ) AS declare @number1 int; declare @number2 int; CREATE TABLE #Table1 ( ValueRet int, ) set @number1=(select SUBSTRING( pol, 1 , 2 ) from table where...more >>

hi i have the following query and 2 problems with it. EventView is a view. 1. if pregnancy.dischargedate is null i want to display EventValue. (Select ISNULL(pregnancy.dischargedate, EventValue) works fine when discharge date is null it takes the value of dischargedate1. but the problem i...more >>

I am trying to write a statement, that will return a username.. based on a few criteria. now before anyone says anything, i know it isnt a good idea to name a field the same name as the table, i will change that, but i want to get this working first. SELECT username FROM Match WHERE (match = '3'...more >>

I like to know if is posibly to manipulate a DataBase of SQL with FoxPro 7.0 i'm very surprised because i can't do that . Please send me an answer to capeiii@hotmail.com ...more >>

Hello, I get my server inventory from SQL 2000 database via SQL XML in IIS. However, I want to have nice looked HTML or CSV file. How ? Thanks, Wensi ...more >>

Hi I have a table with quarters and fillinquarter eg qter fillinqter 2 3 3 3 and another table with quarters and mid month values eg qter mid month 1 2 2 5 3 8 4 11 what i would like is a table giving the mid month for each column eg qter mid month ...more >>

Hi, I have a table stored 138 million records, and now I want to add a column with an integer data type, allow null and no index. When I clicked SAVE button in the Design Table screen, SQL server spent really long time to add the column to my table. I wonder what the server did during the lo...more >>

My SQL Server version: Microsoft SQL Server 2000 - 8.00.780 (Intel X86) Mar 3 2003 10:28:28 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) ON E.M. of SQL 2000, I right-clicked on a procedure , ALL-TASKS-->Display Denp...more >>

Hi all, The statment below returns thousands of records, I need to return the dublicates only? I'm sure this is an easy question for someone. select ercpid,hospno,examdate,nature,nature2,Assessment from tempercp where (nature = 'sphincterotomy perforation' or nature2 = 'sphincterotomy perf...more >>

Is there a way to run an SQL Script for all User Databases? Right now I use the SQL Query Analyzer and I manually choose each database and run the script. But I have lots of databases and takes a while to do this and I might miss one database by mistake. Thanks. Peace in Christ Marco Nap...more >>

What would be the best way of, within a stored procedure, having specific errors write to a specific error log? I don't mean globally, necessarily, although that would be alright, but even explicitly screening in the procedures for errors. Specifically, I want any deletes that fail because of...more >>

I want to loop through the master DB's sysdatabases table to grab the size of each database. I declare @dbname as the name of the database to use. I really need help figuring out how to assign a variable based on a dynamic variable. When assigning @usedsize I get an error syaing "Arit...more >>

I am geeting the following error in dts "Duplicate key was ignored" How to suppress this message in dts? Version : Sql server 2000 service pack 3a Thanks Bala ...more >>

Hi, if i want to use SQL server as back end, Access as front end for data entry, what are the options i have? I tried to create an access project that connects to the server. i can see tables, querys. but the changes i made in access is showing up in the server, which is not what i want. i...more >>

Hi, I'm trying to create some views, but when the table has a datatype of TEXT I am told that I cannot select DISTINCT on datatype TEXT. I am not explicitly selecting DISTINCT but my query does involve a UNION: SELECT * FROM dbo.aTable WHERE (sub_by = ...more >>

I can see how you can turn the visibility of a row on and off, but how can you hide it entirely. HTML style property display offers none and in-line, but I see ne place to add this is. Is there a work around one can use is you with to hide details while displaying summary nformation. I wou...more >>

I have a function called fxtest1 SELECT b.* FROM customer a inner join dbo.fxtest1(a.custid,’YES’) b on a.custid=b.custid Is this possible to do? Jay ...more >>

Hi all, I have installed Oracle client tool on the SQL server and have checked the tns file. Everythings look fine, even from the server I can run SQL Plus and connect to the Oracle database. When I create a link and try to refresh tables under linked serve, I get this error: Error 7399:OLE DB prov...more >>

Hi If I run these queries: A) select * from Calls A3, Subs_AccessUsersTargets A5 where ( A5.TargetNumber = A3.Associate ) I get the results instantaneously 2) select * from Calls A3, Subs_AccessUsersTargets A5 where ( A5.TargetNumber = A3.TargetNumber ) I get the results ...more >>

I have a bunch of numbers in a table, some are whole numbers, some have 2 decimal places, some have more. I want to return them back in a fixed format of 2 decimal places - eg. 2.50, 3.00, 4.02, 5.00, etc. It's important that I have the 2 decimal places even when it's a whole number (eg. 5.00...more >>

I posted the following on a different forum and only got one response. I was hoping somebody could add something to my understanding: I ran sp_spaceused @updateusage='true' on a database and got: Database Size = 421.06 MB Unallocated Space = 36.26 MB Reserved = 387128 KB Data = 169...more >>

Hi We have a COLDFUSION application which is currently connected to a SQL SERVER 2K database. There are certain fields in the application which need to be a lookup to a table which is in an ORACLE database. I'm trying to figure out whether the best approach to architect this, in my mind I ...more >>

Even though I am joining across two indexed criteria on each SQL Server machine, the linked server uses a remote scan and throws it's reults into a nested loop as the outer reference set. If there is no way to force index usage using linked servers in heterogeneous queries (both SQL Server), a...more >>

We are using SQL Server 2000. We have a requirement in one stored procedure to concatenate TEXT column value from multiple rows and return as a single TEXT value. We tried to use varchar type variable in our stored procedure, but it is not returning full TEXT because of 8000 character limit. Can ...more >>

Hoping someone might be able to point me in the right direction. I have a table that stores employee punch in and punch out timestamps that needs to be parsed into a report that alternates the punch in time with the punch out time as separate rows. Can anyone think of a way to do this in a subque...more >>

I hope this is a simple one for you but I've tried various different ways and cannot find a solution: I want to get the count from the string variable to be @oldCount variable. This is a small part of a cursor, which works fine, and I want to compare 2 counts from a NEWtable and the OLDTable b...more >>

Hi, all. Is it possible to create a stored procedure that will search through all the stored procedures and user-defined functions of a db, and replace any reference to object X with object Y? For example, if we used to have a table called JOE, we'd like all the code (not table) references to be ...more >>

Hi everyone! 1) I write a simple program using ADO.(Connect-Disconnect). When, I explored it in Numega Bounds Checker. There was a lot, lot of memory and resource leaks. (up to ~200k) WHY? Thx in adv, Peter ...more >>

First of all thanks in advance for any information you can provide.... Here is what I would like to do: I would like to use sql reporting services and have both a scheduled and data driven reports automatically print to a chosen printer when the report becomes available. Is this possible? ...more >>

i keep questions in a table, each question has a different answer type. eg answer could be a number then there will be limits for the answer or it could be multiple choice question, then choices must be kept or it could be a yes/no question , how should i design the tables in this situation? th...more >>

after several broken client dbs here is the reproduction script: set nocount on go -- use master drop database test create database test on default = 10 go use test go --- drop table aaa create table aaa ( id int not null , cmp as (case when id < 0 then -1 else id end) ...more >>

Hello !! Why "with nowait" does not work property in ADO ??? In this case CREATE proc habibi_ac_test as raiserror('Some message....',10,1) with nowait WAITFOR DELAY '00:00:10' select top 5 * from documents raiserror('End of calculation...',10,1) with nowait GO When I'm calling this p...more >>

Hi I have two tables, TABLE A contains a varchar(1000), TABLE B contains a text field. In TABLE A we may have multiple rows for the same INDNUM, in TABLE B I want one row per INDNUM Anybody got any programming suggestions on how to do this, the TABLE A is very big so ideally I don't want ...more >>

Hi guys. Hope this is the right group to ask this question. I an working for a billing company that debits bank accounts on behalf of clients (Accounts). Each Account has multiple customers. So we debit the customers and then remit that amount to the client (minus our fee). Customers can be ei...more >>

Hello, all. We're trying to use sp_MSdependencies/sp_depends as follows, EXEC sp_MSdependencies 'dbo.tblSubject',null,266751 exec sp_depends 'dbo.tblSubject' but we get zilch, even though the table has at least one known stored proc. that uses it. We've noticed that the table name was change...more >>

We recently moved a database from one server to another. Suppose database A moves from one server to another. An SP in database B on the original server uses the Scope_Identity variable to determine the ID of a row inserted in a table on Database A. The references to database A are changed ...more >>

Table A Col1 Col2 A 1 B 2 C 3 D 5 Table B Col3 Col4 Col4 1 2 3 2 1 2 3 5 3 My end result should be a select statement that will return the following rows 1 B C 2 A B 3 D...more >>

Hi NG I try to use a Storedprocedure to Check if a user LoginID allready exists in my DB <---------------------------------------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO ALTER PROCEDURE [dbo].[CheckLoginId] @LoginID char AS DECLARE @RetVal as bit I...more >>

Hi, Does anybody know of a function that will accept parameters and replace all occurences of data from a text column with another value? We are looking for something like this: Pass: table_name column_name string to replace string to replace it with pk column name pk column val...more >>

I would like to create a stored procedure in which one of the parameters is the name of an existing table. I don't want to code "if @parm_table = 'Fred' select * from Fred ... if @parm_table = 'Joe' select * from Joe"... Does anyone know of a method to make the table name a variabl...more >>

Hi We have several databases running on different sites (ex. sites A,B and C). At some point, we want to import data from sites A and B into site C. Our tables have this format: CREATE TABLE [TableEx] ( [Code] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_G...more >>

Using an Access 2000 database and linked SQL tables... Before running a procedure which will replace data in the local Access db with data from SQL server, I would like to test that a "good" connection exists betweeen each SQL server table and the Access database. (I do not want to replace t...more >>

Hello there. Is there a means to uniquely identify a SQL Server instance? (For example, through a master stored procedure.) We would like to tie our product to a specific instance of SQL Server for licensing purposes. I've tried Googling, but I can't see through the noise of data modelli...more >>

From Access 2000, I do know how to execute a stored procedure on the SQL server... However, how can you send something back to be displayed in a message box as to the progress of the stored procedure..., and whether or not the stored procedure ran successfully ? Thanks ! ...more >>

Hi I've created a table "T1" with login of "L1" which is a member of db_owner of T1 database ,called "DB1". in query analyzer I did the following: create table t1 (f1 char(8)) the result : l1.t1 now I want to have it as dbo.l1 and I know I can drop it and do it as following: create ...more >>

I want my stored procedure to accept a date parameter, but to default to the current date if one is not passed in. I tried the following but get an error message saying "Syntax error converting datetime from character string.". CREATE Procedure procBasicProductivityRates (@Report_Period da...more >>

Does anyone have script to loop through each DB in the master table? I also would like to do this without using sp_MSForEachDB. Thanks...more >>

Is there any way to see text fields when you select * from table. I see the words <Long Text> rather than the contents of the field. Thanks for any help Bernie...more >>

Hi all! I am trying to rewrite some queries that I have determined are the source of some deadlocks I have been experiencing in my application.I'm having trouble with some of them. Given a query like: insert into tableD (fld1) select a.fld1 from tableA a, tableB b where a.fld1=b...more >>

Does anyone have a script that will write the .mdb and .ldf file to a table in SQL 2000? I am looking to loop through each of my databases and store this info for historical data. I would perfer not to grab info from sp_spaceused or sp_helpdb. Thanks...more >>

Hi, I having a hard time trying to figure out a problem I have. I'm getting frustrated because there's no way for me to know if what I want is possible. That's where you people come. Problem: -------------- I want to create a tree list (a hierarchical structure) of users and admins u...more >>

use pubs go select p.au_id, w.wtf, isnull(w.wtf,0) as wtf2 from dbo.authors p with(nolock) left outer join (select au_id, 5 as wtf from dbo.authors with(nolock) where state = 'CA' group by au_id )w on p.au_id = w.au_id I am getting a full set of 5's for wtf2, but a bu...more >>

In a ASP.NET app I am executing a SQL stored procedure. When I execute the procedure directly throught the server explorer the data is correctly displayed. When I execute the SQLDataAdapter.Fill method to fill the dataset in my program, the dataset elements have had any redundant spaces removed from...more >>

I have created an ActiveX script for a Local Package. I am trying to append to a text file, using the FileSystemObject. When I use the code: objFSO.OpenTextFile("c:\text.txt", ForAppending) I get an error. If I do not put the "ForAppending" on the end, I get bad file mode. Am I usi...more >>

Hi, I want to deploy an ASP.NET application that uses MSDE/SQL Server 2000. 1) Which registry key must I check in the VS.NET installer project to decide if SQL Server is installed on the destination computer? 2) Can I legally distribute the MSDE installer files on my installation CD? If yes...more >>

Hi, I'm a visual studiio .NET dev. and not a sql expert. Situation: I have a local (localhost) sql database and stored procedures. Problem: I want to copy them to a connected Webserver (for my website) I don't want to create individual table and copy/paste manual for each table and stored pro...more >>

I have a customer running a service on Windows2000. The connection to SQL Server returns -19703. If he runs the program normally(not as service) it connects just fine. The program uses SQL Authentication with a fixed user name which exists etc. Suggestions very welcome....more >>

I'm doing my level best to adopt the naming conventions outlined in ISO-11179 and expounded by Joe Celko, but moving from theory to practice has me somewhat confused. I was hoping for some feedback on the following SQL2K tables: CREATE TABLE [HR_Personnel] ( [employee_id] [char] (9) COLLATE ...more >>

I have a series of tables of diffrent data type columns.There are situations where there are no values in the column.Where there are null values,the column default to -2. I want to apply a UDF where every -2 and -1 is rendered as "No Data".This UDF will be applied to all selected columns. Any ...more >>

Hi, I am connecting two sql servers together as linked servers, the servers are running on windows 2003. I call a stored procedure defined on the linked server as part of a transaction and it works properly. However, my problem appeared when I tried to stress test my application, the connec...more >>

The problem is caused by an incompatibility between my update trigger and the stored procedure that issues the update statement. My trigger tests for a change in each column separately and executes code inside an IF statement for each column that is updated. The test is the UPDATE() function for...more >>

We are currently in the process of developing a client/server based generic tree application with which we want to be able to model and render any size, shape and depth of tree. Our primary objective is to provide the ability to model large data trees with some 20 million+ nodes whilst still ...more >>

Hi When executing the following SP i get differing result sets. Almost as if not all of the data is being read. The only difference between the two executions is that in the one instance i EXEC oth_QuarterlyRunPolicyTransactionsGPS and in the other instance i run the SQL from DECLARE @Quarter...more >>

Hello everyone Here's the situation: I'm trying to setup some code that will execute an SQL Script (generated by Enterprise manager) in VB.Net. The problem is that I'm running into very wierd scripting errors like the following: Line 2: Incorrect syntax near 'GO'. Could not locate entry in...more >>