I have a flaggable enumeration as a column A: 1 B: 2 C: 4 Since it is for flags, I can have any values from 0-7. I wanted this bitwise combination to be a parameter in my SP but I don't know the correct way to create the query for the filter. My idea was to have them using the IN clause li...more >>

An hour and 15 minutes after I hit send, this still has not even appeared in the newsgroup, so I am trying again, in case it went into a black hole: I have these three columns that I want to JOIN from TableA in this order: Vendor NVARCHAR(255) NULL, MachineType NVARCHAR(255) NULL, Model NV...more >>

I have a situation where deadlocks are occuring intermittently within a stored procedure. The stored procedure has a single Select statement and is not run within a transaction. I have yet to determine the other party involved in the deadlock, but how is it possible for a Select statement to b...more >>

Hello, I´ve got a table which only has one column varchar(256) and it serves a repository for one of my interfaces to external clients. The issue here is that when I execute tht DTS to transfer this table´s content into the txt file, it truncates the 256th character. What could it be wrong?...more >>

How to convert universal coordinated time to local time I am puzzled and was not able to figure out this issue Please help. My third party vendor is storing the time as 1116012062 in the datetime field. Thanks Tanweer ...more >>

Hi all! I have these two tables: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CLIENTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[CLIENTS] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SHIPINFO]') and OBJECTP...more >>

I downloaded 'xpsmtp70.dll' from www.sqldev.net, copied it in the BINN directory under C:\MSSQL7 & lastly registered it as an extended stored procedure using Query Analyzer by executing the following: ------------------------------=AD---------------- EXEC master.dbo.sp_addextendedproc 'xp_smtp...more >>

Hi, I imported an Excel table to SQL and selected some columns from that table into a new table. One of the columns I want to select into the new table needs to be converted to int. I used the following syntax that returned the error message "No column was specified for column 4 of 'tbl_Markl...more >>

Is it possible to create a trigger on a table that can understand the command text of the query that is executed on it? For example if someone on my [TAB_1] I execute "select * from [TAB_1]" I would like to receive an email. If someone on my [TAB_1] execute "select <field> from [TAB_1] whe...more >>

Everyone, I have recently finished an application that generates XML and 100% of the logic is T-SQL and SQLAgent. I am having a slight problem where every once in a while a mal formed xml document is being created. this seems to be from my use of bcp and its output buffer chopping at 256, ...more >>

I am using the OLEDB Provider for Active Directory to make comparisons against existing SQL data, but cannot pull back more than 1,000 records. I am unable to find the additional syntax in the portion of the SQL documentation for OLEDB for AD provider. Is there a way to increase page size re...more >>

I want to display a numeric value like 123.45 in query analyzer. If tbl1 contains fld1, a decimal field, and contains 2 rows with the values 123.45 and 123.65, the first row displays as 123 and the second rows as 124. If I make fld1 a float field, then it displays 123.45000000 and 123.65000...more >>

I this possible? More precisely, I would like to write a stored procedure that reads from a table, do something with the data, and writes output to a file. Thanks, charia ...more >>

Newbie... I'm copying data from Table 1 to Table 2. Each has it's own structure and I must perform Lookups, Case Logic, If Then, etc. using Table 1 data to generate the proper input into Table 2. I"ve tried the Following 3 Methods but have problems w/all.. INSERT INTO - Problem 1: Colu...more >>

I'm trying to update a sql server view, but I'm having problems. I get the following message when I try to add or update a record in a view from within my asp.net web form. Any suggestions? I get two errors, one if I try and update and another one if I try and add a new record. Update er...more >>

What is the difference between xtype and type columns in sysobjects system table? If they refer to different things, whats the importance of that?...more >>

In table MYTABLE, I need to update field ROOFAGE according to the value of field MYYEAR: CASE myyear WHEN > 1995 THEN roofage = A WHEN >= 1985 AND <= 1995 THEN roofage = B WHEN < 1985 AND <> 1980 THEN roofage = C WHEN = 1980 THEN roofage = D I can't figure out how to write the UPDAT...more >>

Hi, I have created a Crosstab Query in Microsoft Access and I am trying to Upsize the query to Enterprize Manager. When I try to upsize the query I get an error message saying Overflow!! I have tried to write the query in Enterprise Manager but it doesn't seem to recognise the code. ...more >>

Hi, I've found a script with an sql operator : *= I have not found any reference to such operator on the Net, does someone have ? What does it mean ? thank you mathieu st.pierre ...more >>

Hello all, In my SQL server 2000 database, I have about 100 users. I want to know each user has access to what database but I don't know how to list them out. Doing one by one by opening the properties of each users and under the "database Access" tab, drives me crazy. Is there any sp availab...more >>

Hi all, I am currently trying to solve a problem. What I need is a view returning the projectid with the highest CustomerActivity value for each addressID. I tried different things like the following: SELECT dbo.Projects.AddressID, dbo.Projects.ProjectID FROM dbo.Projects R...more >>

Hi Guys, I am running a script in the Query Analyzer. I have a cursor that reads from severl hundered K records and inserts several hundred k records into a NEW table based on a range of data in each record in the cursor. It should generate nearly 1 million records. It is stopping at 327...more >>

I'm having some issues when I try to direct the output of a stored procedure to a text file. This stored procedure generates SP_ADDARTICLE and SP_DROPARTICLE statements for our replication environment. When I execute the stored procedures in QA, the out put is fine and the statements run gr...more >>

Hello Everybody, I have a table like following... Id CName Amount 1 Meal 100 1 Meal 50 1 Air 100 1 Books 90 .. .. .. How can i write a query which will return following result.. Basically i want to group by ...more >>

Hi I think I'm misunderstanding the way grouped stored procedures work. I have this : drop proc JM create proc JM;1 As Begin print 'JM1' End go create proc JM;2 as begin print 'JM2' end go exec JM The output I was expecting was JM1 JM2 As I execute the proced...more >>

I am trying to write a routine that will allow two dates to be passed in to a UDF and a description to be returned based on the period between the days. This is to allow user reports to be more readable. e.g. Period (in days) Description Returned 4 4 Da...more >>

Is there a way to generate a list of accounts with update access? I don't care about read-only accounts and I need to separate them from accounts that can modify data or database settings. Thank you in advance....more >>

Hi, I am new to sqlserver. I am interested in creating a simple query to ascertain the tables names and sizes in rows of each table in a database. Can anyone tell me the sys tables to query for this info. in oracle: select col,col, from tabs did the trick! Thanks in advance for an...more >>

Rollback does not rollback the value of Identity create table testings(i int identity, n varchar(20))Begin Transactioninsert into testings values('name1')Rollback Transactioninsert into testings values('name1')Select * from testingsDrop table testings The result is 2 name1 But I expecte...more >>

Hello, I get a 1222 error "Lock request timeout" when running this query : select * from lignes_c (NOLOCK) where 1=0 The table lignes_c is engaged in a transaction by another user. (only appending new records) I thought the NOLOCK hint would allow a dirty read in this situation. Is it no...more >>

I have recently used Indexed Views to solve performance problems for aggregations for a large database. Views were created with unique clustered indexes. These views where then used in Stored procs which generate dynamic sql using sp_executesql etc to produce excellent performance. Everything ...more >>

hi why is answer E apt? i would have chosen D. would i be wrong? .. (5) You are a database developer for your company's SQL Server 2000 database. You update several stored procedures in the database that create new end-of-month reports for the sales department. The stored procedures contain ...more >>

Is this possible? Thanks, Michael ...more >>