goal 1 : to perform a check that multiple varchar cols are all non-null and all non-whitespace .... goal 2 : efficiency any ideas ? my initial attempt at some sql : ( NOTE: Col1, etc. are all VARCHAR(n) ) DECLARE @RequiredColsPresent BIT SELECT @RequiredColsPresent = (LEN(RTRIM(LTRIM...more >>

will have what in the next version of SQLServer the inclusion of VBA could generate forms and reports? ...more >>

Hi guys, I'm using ip-to-country (http://ip-to-country.webhosting.info) to guess the user's country based on his ip. I've imported the csv into sql table, but now I'm stuck at trying to compose a function to convert IP addresss of the form A.B.C.D to an IP number (bigint). Can someone as...more >>

Hello, I would like to be able to pass a SQL statement to a sproc that uses SP_EXECUTESQL. However, the maximum size of an NVARCHAR is 4000 bytes. Since the internal sproc requires and NVARCHAR type, does that mean you can only have a SQL string of no larger than 4000 bytes? Thanks...Paul ...more >>

hi I used the following script to test an empty string and a string with space set ansi_padding of g declare @a varchar(10 set @a=' if @a = ' print 'empty els print 'not empty but set @a with ''(0 space) and ' ' (with 3 spaces) give me the same result 'empty' Any idea Tk Fre ...more >>

In a select statement I need to create a string of exactly 4 number like so. Fir example, if the author_id value is 1 I need to return 0001, if it's 15 I need to return 0015. How can this be done easily? Thanks a lot. ...more >>

I have a Stored procedure which includes several Table variables which have joins to other table with in the database. What is my best strategy to aid in the stored proc's speed. Thank you ...more >>

Hi all, I have a legacy VB6 application using adodb.recordset using a system DSN and I am seeing very strange behavior with the cursors. I'd love to get rid of them, and that will happen when we convert to C# :-) I have a single table select statement of the following form: SELECT * FROM t...more >>

Hi All, I have two SQL 2000 servers and need to run a query from server one to import data from server two but using a SQL query built from tables held on both servers. I got some help on this last week but I am now struggling to pass my username and password to the remote server, could som...more >>

I need to select a max(id) from a table in application. After I select it, I will check the id selected is more than 0, if it is null, I 'll default it to 0. Then the new_id = id + 1. The new_id is going to be used as a id when inserting a record in that table. How can I ensure the max(id) for the t...more >>

I'm building an e-commerce app and am keeping the client facing front end on a live web server and the admin section of the app on a local intranet server. All data is stored in an SQL DB on the live web server. I will be having in the region of 500 product images in the app and am looking for...more >>

I need to preserve an account number that will never be allowed to change once it is Updated from <Null> Please Help...more >>

Is there a quick method to audit all transactions (edit, update and deletes) and store the audit trail in a separate table (prod_table, prod_table_audit) against all tables w/i a database? Thanks. ...more >>

Hi, Is there a way to restore the backup files of a database which has only one file and one filegroup (primay) into a database which has multiple files and filegroups? and can the reverse to be done (multiple files into one file)? e.g.: db1, db1_data on primary restore db1 backup f...more >>

Hi ! My problem seems very simple, but I just can't get around it: I would like to notify my users in GUI if certain jobs are currently running or not. Using DMO I can succesfully get list of all jobs with their current running statuses. Unfortunately there's no event (or is there ?) in D...more >>

I want to be able to have the where be dynamic and condition on different fields.... I'm a friend of sp_ExecuteSQL, however I thought there was a down-and-dirty way to do this.... -- errors..... declare @num int set @num = 5 select top 5 * from contact1 where case when @num = 5 then...more >>

Hi all, I have Users table with columns ID, UserName, and IsActive. IsActive is a bit column and UserName is nvarchar. I want to create a constraint so that there are no two users with the same UserName and IsActive=1. In other words, there _can_ be two or more inactive users with the same ...more >>

Hi, I would like to do the following I have a stored procedure which does something like select * from table where key = 1234 union select * from table t1 inner join linktable on blah inner join table t2 on blah where t2.key=1234 only more complex With a stored procedure I can...more >>

Hi to ALL, If I want to get a set of records from Database in readonly mode (Ex. For Report or to Display in a Grid) Which of the Folowing is FASTER? 1) Use of SQL Statement in ADO Objects in READONLY Mode from The Application Program. 2) Create a VIEW in Database Server for that SQL Query ...more >>

I stumbled across something and wonder if it is a bug or a feature. I have a table with two fields in SQL 2000. One is a unique index and the other is a character field. If I run two queries: SELECT COUNT(*) FROM Tbl WHERE col2 = 'X'; SELECT COUNT(*) FROM Tbl WHERE col2 <> 'X'; I...more >>

This same issue seems to keep presenting itself. Is there an off-the-shelf, or easy way to do it? To write it myself I can envison a VB.NET GUI application that uses SQLDMO to show the servers, and drill down to a database, then let you select one or more tables. What it would do is to writ...more >>

Does anybody know an easy way how to split 1 existing field value into multiple fields based on delimeter, i.e. I need to split '{1,2}{6,2}{2,4,5}' into 3 fields: 1,2 6,2 2,4,5 Something like Split function in VB? may be somehow to use PATINDEX('%{_}%',field1)? Is there any T-SQL command I ...more >>

Thanks in advance for taking the time to read my question. I have a problem with deadlocks. This is the statement update table1 set field1 = 1, field2 = 2 where fID = 1 if @@error <> 0 Begin --Error action End however, if for any reason the transaction is c...more >>

Hi All, On my START menu, Enterprise Manager shortcut is missing. It was there before. But somehow got deleted. Please let me know, how to put the shortcut back there. Now I don't know any other way of opening Enterprise Manager. Thanks, Anil ...more >>

I have an addresses table containing an ID field, various address fields, and an EntryDate field. So for each ID, there could be several addresses, each with a different EntryDate. How do I write an SQL statement which will return a list of all ID's and their LATEST Address (Determined by t...more >>

Hello guys Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the ...more >>

Hi (Anit) I have the foll pro CREATE PROC testtt @numb varchar(10) @returnVal VARCHAR(50) OUTPUT A DECLARE @sql NVARCHAR( 2000 ) @SQLx NVARCHAR( 2000 SET @sql = N'SELECT cover-dat FROM issu where number = ' + dbo.quotestring(...more >>

Is there a way to programmatically retrieve the rules text from a rule besides using sp_helptext 'rulename' ...more >>

Yes, I have several stored procedures for a particular database (sql server2k). Is it possible to create folders or something like folders to group the sp's? Is there a way to organize individual sp's other than giving them categorical names so as to reduce one long list of sp's? TIA...more >>

If i try to alter a column with data type text i get the following error ALTER TABLE TESTALTER COLUMN TEST_MEMO[ntext] Cannot alter column 'TEXT_MEMO' because it is 'text' ...more >>

Hi All, Table structure : Table1 Field1(PK) Field2 Field3 Table2 Field1(PK) Field2 Field Mapping : Table1.Field2 can be null or it should refer to table2.field1 Query 1: select table1.*, table2....more >>

I have returnded varchar and int, but can a UDF return a whole set of data? --Stephen Russell S.R. & Associates Memphis TN 901.246-0159 Steve says get rid of the notat_ to send him a reply! ...more >>

Hi, I am using a using a stored procedure for displaying analysis data. But when i run this procedure this will block many process or Lock other process. DB Query is very large and complex and it takes much time to execute (in minutes). please advice. thanks n regds., AVNISH KUMAR ...more >>

In user database i created function: create function marioTest() returns varchar(20) as begin return 'Blablabla' end WHen I do: select marioTest() I get: 'marioTest' is not a recognized function name. I could bite my legs out that I did the same thing back at home, and ...more >>

Hello: I am trying to convert a humungous Crystal Reports record selection formula into an sql WHERE clause. I'm trying to do this via text editor search and replace (as much as possible anyway). In Crystal there is a "time" function that is like this: time(datetime) Is there a way to ...more >>

I'm trying to update some rows in a table, and send the count of updated rows to a text file, that I will then email of to our app support team. Any ideas on how to accomplish this?...more >>

Is there anyway just to select the date portion of a datetime field? Select HireDate from Employees I just would like hireDate and not the time with it. Thanks...more >>

Hi I have the fol declare @test varchar(50 set @test = TOP 1 pos_issue_year, pos_issue_numbe FROM pos_data_fil WHERE substring(pos_upc_number,7,len(pos_upc_number)-7)= 0051 ORDER BY pos_issue_year DESC, pos_issue_number DES select @tes I am getting this error Server: Msg 156, Level ...more >>

Hi everyone, Is there a fast and reliable way to determinate if a table has an identity field? I'm working in a transfer process between servers and from time to time I have to transfer records between tables with identity fields. The process uses dynamic sql widely because is driven by...more >>

How can I get a list of all user stored procedures in a database? I tried SELECT * FROM INFORMATION_SCHEMA.ROUTINES SELECT * FROM sysobjects WHERE type='P' However, both return also system stored procedures. olig ...more >>

What's wrong with this delete statment in a stored procedure CREATE PROCEDURE dbo.Delete_Min_Vehicle_Type @Min_NumVa AS DELET FROM MIN_Vehicle_Typ Where Min_Num = @Min_NumVa When I perform a syntax check I get the following message Error 156: Incorrect syntax near the keyword 'DELETE Must ...more >>

Hi there, I have a sql table with a datetime field. I am having real difficulty querying to that table using a data. The format of the date in the field is in the format '2004-03-29 15:32:00.000 When I try this qurey I get an erro SELECT FROM Action where ActionDate = '2004-03-2...more >>

i want to write user info to a table when row deleted on delete trigger.how can i get user info ?this user is using query analyzer and connect server via remote desktop connection. how can i get this user machine name or ip...more >>

I am running the following query... select * from sysobjects (nolock) where xtype = 'PK' and name not like 'PK_%' There are records in that table with PK776 or PK764. Most records have PK_ in them. But I am getting 0 records returned from my query. I would think that my query "and name...more >>

I know how to retreive data from SQL Server into .NET in a Dataset. Unfortunatly I have had to change my SP to modify the data that I retreive so that it includes references to FootNotes. So my part# could now be changed to include a (2) or (P)(3) after it. This is all done within a cursor c...more >>

I had a VB Application that use SQL server authentication throught ADO. I am wondering how to create a VB aplication to login in by Windows authentication throught ADO, does anyone has an idea how to do it?...more >>

Dear All I'm beginner in using stored procedures on SQL server 2000, so i have a question at all i wrote one(this) CREATE PROCEDURE GET_I @NEWID DECIMAL(8,0) OUTPU A BEGI DECLARE @FID DECIMAL(8,0 DECLARE @AID DECIMAL(8,0 SELECT @FID = (MAX(FID) + 1) FROM ADR_ SELECT @AID = (MAX(AID)...more >>

Hello Experts, I run the following code to return the dependencies of my DB views. The Errors that I get are totaly strange: declare @Obj as nvarchar(100) set nocount on declare d cursor keyset for Select top 10 TABLE_NAME from INFORMATION_SCHEMA.TABLES Where TABLE_TYPE in ('VIEW')...more >>

Hello I have a table with that stores several occurances of the same ID. I have year field and I want to get the latest year only. I tried to use the Max Function, but I keep getting duplicates. How do I get just the latest year Thank you Brandon...more >>

Hi How I can use OpenRowSet command in T-Sql with trusted connection ? Thank you, Eric...more >>

H Thanks in advance for any help and please note I am relatively new to SQL I am supporting an update script running in the Analyzer. I am having issues with an IF statement When an IF statement references a missing column within, it produces an "Invalid Column Name". But, surely this should...more >>

Hello SQL Gurus, I found out that query optimizer works incorrectly with the distributed partition views having fields of type smalldatetime in the tables. With the fields of type datetime all works fine. There is the description of this problem: I tried to distribute one table to th...more >>

Hi All I am to attempting to perform a group by to select the distinct ProductID and Description from the table below and display the results as illustrated below. Basically what I am attempting to do is show the information about all products where the ProductID and description are di...more >>