Hello Everyone, I am writing a view and I have some negative values coming out of some fields in one of my tables. They should be there, but when i retrive data i need them to be non negative. I want to know if there is a function or if there is a way to get rid of negaive sign. Thank you al...more >>

Hi All, Is it possible to store the XML stream object into SQL Server image field? The reason that I need to store in such way because this field may store the PDf or the XML. I have also pass the XML into the stream object as text type. and use "WriteText" function to write it into the dat...more >>

Problem is, when I create View, and some fields are calculated and other = fields I will return through *,=20 I will get result, where head of resultset is in correct order, but = "body" (I mean data) is in mixed - bad order. For example i will try to explain it graphically: Table1: Col1 ...more >>

SS2K allows you to create, use, and list stored procedures with dots in their names. sp_helptext, however, fails to get the contents of one of these beauties because object_id() fails. This problem is not remedied by fully qualilfying the stored procedure name. While I can already hear some pe...more >>

Hi guys, Could anyone point out my error? I am trying to create a local variable that should hold a table name, and then use it in a query as such: Declare @table_name varchar(20); Set @table_name = 'test'; Select * from @table_name; I get an error along those lines: Server: Msg 137, le...more >>

Is there a stored procedure that allows me to get the definition of a stored view in the database (the querytext) ...more >>

Hi I have written this query for generating a report. select * from shrcertmaster left outer join shrsharemaster on shmacno = ctmacno This returns the output given below. CtmCoy CtmAcNo CtmCertNo CtmBenf CtmBatch CtmShare UB 1234567905 123453 123476 0001 1000 UB 123456790...more >>

I am an absolute new beginner at this... hope you can bare my question. Trying to install SQL-server 7.0 on my home PC with Windows XP It will not make contact with the database... because of some login Anyone who can say something about this problem? Where do I specify this login??? ...more >>

Hi, I want to create a temporary table and store the logdetails from a.logdetail column. select a.logdetail , b.shmacno case when b.shmacno is null then select cast(substring(a.logdetail,1,charindex('·',a.logdetail)-1) as char(2)) as ShmCoy, cast(substring(a.logdetail,chari...more >>

Hi SQL Gurus, This is a re-post, since there is a mistake in first post. I have a batch process using DML to : (below just the simplified code to explain the process). The question is : from performance point of view : Is it better to copy the data to @table_var first then process the data fro...more >>

Hi I am using Windows 2000, Delphi 7 Enterprise, ADO and SQL Server 2000. I have implemented a locking mechanism using sp_getapplock and sp_releaseapplock where the @LockOwner is Session. The Idea being that all locks remain active until lock is released or session is terminated. All works ...more >>

I need to find the max length of all the column names in my table; basically a select max(len(column_name)) from table_name but I want to do this for all the column names. Is there a way to do this without typing all the column names, perhaps with the syscolumns table? ...more >>

This statement errors out and the culprit is the red text. SELECT SUM(CASE WHEN SOP30200.SOPTYPE =3D 4 THEN (SOP30200.DOCAMNT * = - 1)=20 - SOP30200.TAXAMNT ELSE SOP30200.DOCAMNT - = SOP30200.TAXAMNT END) AS Total, (SELECT SOP30200.DOCDATE FROM SOP30200 = WHERE SOP30...more >>

Hallo all, I have one table that have many records, in particular one column is datetime GETDATE(). How can I do to count records grouped by day? I tried ========= select cast(datecolumn as smalldatetime), count(*) from t1 group by cast(datecolumn as smalldatetime...more >>

Hi, I am writing a stored procedure which contains the following WHERE clause: WHERE property_name Like Case When @cboPropertyName IS NULL then '%' else @cboPropertyName End It works fine, until there is a Null value in the property_name field. Is there a wildcard character different tha...more >>

Hi Everyone, I'm hoping someone can help on this one. I am doing some performance monitoring using the Windows NT performance monitor. I want to see the % Processor Time of the SQL Server Instance . I have two Instances running on this box. In the performance monitor there are two entries th...more >>

Hi, I was wondering whether there is a way to format numbers in sql as below. Meaning I was to convert 1 to 0001 (with maximum of 4 as the length) So that 100 = 0100, 0999, 0022, 1222, etc... Does anyone know how to do this? Thanks Dhruv...more >>

I've created a table contains a column with varchar(3) data type. When I query "select * from test where msg like '%[%'", it doesn't work. Can anyone help me? Thanks a lot. ...more >>

Hi All, I'm trying to do something real easy but I am getting confused and would appreciate some direction. I have a User Defined Function that I want to build a strnig in by looping through records. I get "Incorrect Syntax near the work Begin" Example: CREATE FUNCTION [dbo].usf_GET_ISDN...more >>

Hi, CREATE TABLE [dbo].[MyTable] ( [Start_Date] [datetime] NOT NULL , [FromS] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ToS] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ToS] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Status] [char] (...more >>

Hey have a question!! if I have a select returning a empty dataset, in this select is a inner select.. will this inner select be executed?? I have a very large select, with 15 inner selects, it takes 18 secunds to return a empty dataset.. this is a example.. select *, (select id from t...more >>

Hi SQL Gurus, I have a batch process using DML to : (below just the simplified code to explain the process) The question is : from performance point of view ? Is it better to copy the data to @table_var first then process the data from there ? OR Should I just use Inline View three times wi...more >>

I am trying to figure out how to select a record with the least nulls in two fields and/or an ordered "rating" of which to select.... col A col B col C ----- ----- ----- 1 null null 1 null value 1 value null 1 value...more >>

Hi, I need to merge two DateTime values into one where one value holds the date and the other holds the time. How would I do that? Thanks, AEgir...more >>

Hello all, Can any show me how I to create a RNADOM function in sqlserver ? I need to sort my records in random. Im planning to create a column function and sort by? Thank you in advance, - John *** Sent via Developersdex http://www.developersdex.com *** Don't ...more >>

Given that I install DNF on a SQL Server box, can I effectively write a managed assembly in .NET and use this as an extended stored procedure on the aforementioned SQL Server box, or is this just totally absurd? Thanks in advance, Jeff Cheung ...more >>

I am trying to call a method in an ActiveX DLL I have created in VB. I am able to get the sp_OACreate and sp_OASetProperty calls to work fine, but when I try to use the sp_OAMethod call I get an error. The call I am using is: DECLARE @queueEmpty as bit DECLARE @param as bit EXEC @hr = sp_O...more >>

Hi, I am working on importing some records from a source that includes duplicate records... the subquery - select p1.Part_Number, p1.Type, p2.[Description], p2.Primary_Vendor, p2.Manufacturer from (select distinct Part_Number, Type from HWEquipment.dbo.Parts) p1 inner j...more >>

I have a problem with SQL-Server, which I am connecting to using ADO.NET. Basically I am using a DataAdapter that has a Select Command that is a stored procedure which loads a single record from a single table. I am ensuring that the connection to SQL-Server is closed before results are sent t...more >>

We have an application that runs on both JET and SQL Server. The JET version has a unique index on a particular field. The SQL Server version didn't have one and this was reported as a bug which I have been asked to fix. The problem is that SQL Servers unique index considers nulls to be the same ...more >>

Wassup Guys... So... I need to install just the Query Analyzer in a client machine to access a database on the server... How I can do this... Exists another IDE like Query Analyzer????? ...more >>

Hi there. there are 2 servers that started giving this message when we tried to connect to them (no matter how we connect .. EM, OSQL, QA ) Timeout expired there is no ( slammer )on any of the machines both machines slow down more than 50 times when starting SQL server and go back to normal ...more >>

Hi, How to Prevent Concurrent Locking in SQL Server. I am using .Net & SQL Server. I am using the following method to prevent Concurrent Updation Each table I have one TimeStamp Column, whenever I am updating the row I am updating the TimeStamp. When I am selecting the particular row ...more >>

I wondered why it is only possible to update a view when your update only references one base table. Surely you have resolved any ambiguity when creating the view. Why is this a hard problem for the DBMS? "Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE...more >>

Hello Can anyone tell me replacing temporary tables with table variables can improve performance of a procedure. From help it seems to be very clear that table variable might be better over temp tables, but when i ran my procedures (old one with temp tables and the new one with table variab...more >>

When I create a table USER, and try to execute any SQL statement like: select * from USER; The SQL Server Enterprise Manager will pop up an error dialog. However, if I do this: select * from [USER]; Then it's fine. I tried other table names, and don't need to use []. I want to know i...more >>

I have 2 tables: User Table: [Name, ID] TimeCard Table: [ID, Date, TimeIn, TimeOut] I want to display all records by joining 2 tables, i.e. I want the output with columns: [ID, Name, Date, TimeIn, TimeOut] I tried the following approaches: Approach #1) SELECT * FROM USER ...more >>

I want to change the collation from all varchar fields in a table to "SQL_Latin1_General_CP1_CI_AS" (+100 fields) Can I do this with a script? If so How? Thanks for your answer ...more >>

Hi there, I would like how do I know how much a stored procedure (or a trigger, or a query) is consuming my processor. I saw some postings here discussing query performance ( some querys went from 0.22% to 0.02% of processor consumption). Thanks a lot Daniel ...more >>

Hi, think i have found a resolution to a problem which has been ocurring intermittantly on new sql server installations for some time. The symptoms are being unable to see or connect to a named instance of sql server when there is a default instance running also. I have spotted this behavio...more >>

Hi, What is the easiest way to find if string has all LETTERS of another string? E.g. string 'CDPSRB' has all letters of 'CD' and 'CS', but not of string 'CA', nor 'GH'. I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4 Thanks in advance Nikola Milic ...more >>

Has anyone ever tried to call a .NET component froma stored procedure? If there are any examples of this then please point me to them! Thanks much! Jeff Tolman jeff.tolman@enm.com ...more >>

I want to check a record to see if a value is 0. if it is, I want to = update a second field in that same record --=20 ______________________ David Fetrow HelixPoint LLC. http://www.helixpoint.com davef@helixpoint.com Interested in Affordable Email Marketing? Check out the HelixMail...more >>

Hello I'm trying to create a Global Temporary table in SQL Server 7.0 from VB 6.0, wierd thing is, it gets created sometimes and sometimes not, and I get a Invalid object name error when trying to reference the temp table. Given below is the code I use. Could someone tell me why it would crea...more >>

This is a follow-up to a question from yesterday, but different enough to start a new thread. I recently was given advice to roll back an identity field in a table to 1000 by running this code: DBCC CHECKIDENT('Tablename', RESEED, 1000) Then, poking around in Enterprise Manager, I went in...more >>

Using MS SQL2k I have to ftp download a csv file from an external site and ftp another csv file back. Any ideas how to acomplish this or a pointer to a resource that explains it. Regards Don ...more >>

Using MSQL2k & ASP application. I need to do the following and have never done this before. I have 2 tables Orders & Details and need to export this as one row in a csv file every 10 minutes AUTOMATICALLY, It will not be many rows about 20 and 15 cols. I also have to import another csv Au...more >>

Hi, I have a simple select statement in a stored procedure that returns all rows from a local temp table. 'select * from #rsReport'. Works fine, returning entire table. But when using the stored procedure as a record source for an Access report, a msg pops up 'Invalid Object Name '#rsRep...more >>

I want to return 1 row for each EvidenceID (EvidID). The row should could contain the EvidenceID and TicketID. Twist: I want the TicketID that has the max TicketDate for each evidenceID Table structures: CREATE TABLE [dbo].[TicketDetails] ( [TicketID] [int] NOT NULL , [EvidenceID] [in...more >>

Hi, How can I reference a local temp table to drop it? I have an application that creates local temp tables, I need to delete and create these tables 'as and when' during the lifetime of one connection dependant on user input. I'm passing a SQL string using ADO: if exists (select *...more >>

Hi everyone I've been trying to create a stored procedure to satisfy my needs but I'm stuck right now. What do I want to do? I have a stored procedure which has to run according to values people enter in 10 fields. Some values need to match exactly and some need to be between numbers. Sometimes...more >>

Hi I am importing a text file in a table and obviously all the data is seen as Varchar. Although the values match the field types, the server throws an error saying i have to explicitely call the CAST. Is there any way that i can let him do this job? Smthing like mySQL does? Regards ...more >>

Hi, This might be an easy one, but i am stuck... in a stored procedure i wanna get the lenght of a column CREATE PROC sp_XXX AS BEGIN declare @iWidth as integer EXEC sp_columns MyTable, NULL, NULL, MyField -- how do i get in variable @iWidth the length of the MyField ...more >>

In the query file, first I created a new database in 'master'. how can I change to the database I created and continue to create the tables? 3X. ...more >>