All, Is there any law enforced by regulatory bodies to encrypt 1.Credit Card info 2. SSN I need some document on it, which I can use for my presentation to my management. Any US Bill's text can also be usefull. TIA ...more >>

is there a way to determine the size of the resultset returned from a query in bytes? thanks ...more >>

How to write Select..From with declared variable? I want to do something like this.. declare @table varchar(50) set @table = 'Users' Select name From @table Hrcko ...more >>

I have a View in an Access adp (connected to SQL Server 2000 db) that counts instances of a certain field in one table and aggregates based on date and part number (the date is what's actually counted). This works fine, but I need to add another count of a different field, only this one would...more >>

In a table I have users registering for a mailing. The mailing should be limited to one per household, so I would like to exclude all but 1 from each unique home address. There is a unique id for each registrant, but its possible another family member could have registered more than once with ...more >>

Hello: I need to import an Excel file to SQL Server. The .xls file has the column names which contains dot inside, like AAA.BBB. When I import this file in SQL using DTS Import/Export tool, it creates a table with column names like AAA#BBB. So, during import process the dots substitutes with #...more >>

In SQL 2000, SQL saved DTS packages in the DTS section with Ent. Manager. I saved a SSIS package in SQL 2005 in SQL format, but can't find where SQL 2005 saved it. Does SQL 2005 Management Studio have a SSIS section from which to execute a SSIS package similiar to the old DTS way? ...more >>

When I do the following sql statement, I got the error "Invalid column name 'buy'." SELECT Buy = case when [col1]='BUY' then 1 else 2 end, Ddif = case when [buy]=0 then 1 else 2 end --> error here FROM tbl1 How can I refer to Buy in the same sql statement ? Thanks. ...more >>

Hi, I've two files : ora8i_Data.MDF, ora8i_Log.LDF, from a database 'ora8i' from another PC (other SQL server). How can i restore this database to (my PC) my SQL server ? Thanks in advance, Hatziyannis Apostolis ...more >>

Does anybody know how to set the priority to high? I tried the code snippet below with the sp_send_cdosysmail without any success. Thanks $$$ -- Priority EXEC @hr = sp_OASetProperty @iMsg, 'fields("cdoImportanceValues").Value','2' EXEC @hr = sp_OASetProperty @iMsg, 'fields("CdoPrior...more >>

I've had users reporting timeout errors running large reports. I've been increasing the Timeout property blindly assuming it would actually work (silly me). Today I sat down to test it properly and hopefully resolve the problem for good. What I've found is the CommandTimeout property doesn't ...more >>

I'm trying to do something like the following: SELECT * FROM leads WITH (NOLOCK) WHERE CASE WHEN @lead_id_lookup = 0 THEN lead_id >= @lead_id WHEN @lead_id_lookup > 0 THEN lead_id = @lead_id...more >>

Hi, I'm trying to do something really simple but am not coming up with the right answer. I have master table A on which I am trying to outer join (separately) tables B and C. So it should look like this: Table A LEFT OUTER JOIN Table B Table A LEFT OUTER JOIN Table C I tried doing: selec...more >>

I am trying to restore a database and change the database logical name. I the following error: --------------------------- Microsoft SQL-DMO (ODBC SQLState: 42000) --------------------------- Logical file 'TestOAISys' is not part of database 'TestOAISys'. Use RESTORE FILELISTONLY to list...more >>

After I do a left join of two tables, I get result below. Now I need to replace a NULL value of C_ID with the corrected value. PLease help, Culam. CREATE TABLE T1 ( c_id int, acct_nbr int) INSERT T1 VALUES (232663,17) INSERT T1 VALUES (NULL,17) INSERT T1 VALUES (86714,34) INSERT T1 VALUES...more >>

I'm a SQL beginner and sould use some help with a query. If you had these two tables: TableA order weight ---- ------ 100 5.00 100 6.00 TableB ordernum ttlweight -------- --------- 100 11.00 How would you write the query to display: order sum(weig...more >>

Hello, I have big database on SQL 2k. This database was created a couple years ago, but until today a lot of changes had place. So, I have some procedures in few versions, for example: getClient getClient_ver_1 getClient_ver_2 getClient_ver_7 getClient_ver_11 and the last one is the mos...more >>

If I run the following I was hoping for the same result from both final queries. The code below isn't what I am actually doing, what I'm actually doing is putting values into a 'min' column of a data analysis table, and I want to be able to put more than just numbers (for example, minimum d...more >>

Hi folks, Say I have a sproc that does an insert. One of the pararmeters is an int. If the parameter value exceeds the ints maximum value can i trap this in the procedure and replace the value with the maximum allowed value. I suppose a more general question is trapping errors raised when ...more >>

i searched but could not find please help what is the maximum characters allowed for a column name? ...more >>

Our original application connected to a SQL 6.5 database. This was upgraded to V7 sometime ago and all our triggers use suser_name() to get the user for the current connection. We are now upgrading to SQL 2000 and suser_name does not work anymore. It just returns a NULL. Is there anyway around th...more >>

When you design a table you may specify the max width to say 30. I dont want my fields to store the extra spaces, how can I do this ? ...more >>

Hello, Please pardon my ignorance. I am doing some testing on my local machine (and then I need to setup a server). I have a mail profile, but when I try to configure SQL Mail the profile isn't listed. Should it be? When I setup the server do I need to have Outlook installed? It will be SQL...more >>

We've gained recent visibility into some of the kinds of errors generated by our application, and one of the issues that we're seeing (rarely) are deadlocks. We seem to get a little less than one deadlock per day (and one day has easily millions of transactions going on, so the ratio really isn'...more >>

Hi, I am writing a stored procedure in SQL Server 2000 the will require multiple commits(?) during its operation. The first group of actions that it must perform is to modify a table by adding some columns and altering another. The next step reads data out of the table into a temporary (in ...more >>

I am trying to programatically determine the directory of the log files in SQL Server. I am using SELECT Filename FROM master.dbo.sysdatabases to get the data directory, now I need to find the log files. Thanks, RSH ...more >>

Hi, I'm trying to write a sql query that will sort and filter the output of DBCC SHOWCONTIG. In short, I want to do something like this: SELECT TOP 10 * FROM (DBCC SHOWCONTIG WITH TABLERESULTS ) ORDER BY SCANDENSITY DESC Is there a way to use the table output of DBCC SHOWCONTIG as the ta...more >>

For instance, if I have the following values in a table Id ParentId 1 null 2 1 3 1 4 2 5 4 I understand how to traverse this relationship tree using CTE but how would I build a map where I say Id 1 is r...more >>

Hi all Is it possible to enable/disable triggers without dropping them?? I want to disbale them sometimes to prevent firing them thanks ...more >>

Hi all, I need to store a "lot" of data in text format into a field. I see that i cant store much in var/nvarchar. So i guess i have to use binary fields. But is it possible to search for strings in binary fields with "like" command or any other? Thanks in advance, J. ...more >>

I'm using BCP to copy data from a fixed length delimited file into a SQL table, i'm using a format file, here is the format file: 8.0 14 1 SQLINT 0 4 "" 1 numero_llamada "" 2 SQLNCHAR 0 1 "" 2 operario SQL_Latin1_General_CP1_CI_AS...more >>

Hello, I'm attempting to use a query to add up the charges in a record from a table and two corresponding records in another table and then return a result if there is a difference between the known total and these calculations. My query below works fine for adding up charges from a record i...more >>

Hi, I'm trying to retrieve data from the last updated date. For example, when job runs after long holiday, it will look for the last updated date and retrieve the data. how would I achieve this? I tried Max(invoicedate), the query is always timed out. Please help, Thanks, Sarah ...more >>

Can anyone pleae advise how to speed up the exceution on the following procedure : SELECT DISTINCT TOP 100 PERCENT cat2.Category, cat2.SubCategory, CONVERT (varchar(4), cat2.Y) + '/' + case when len( CONVERT (varchar(2), cat2.M))=1 then '0' + CONVERT (varchar(2), cat2....more >>

Can anyone advise how to improve the execution plan of the following stored procedure ? SELECT DISTINCT TOP 100 PERCENT cat2.Category, cat2.SubCategory, CONVERT (varchar(4), cat2.Y) + '/' + case when len( CONVERT (varchar(2), cat2.M))=1 then '0' + CONVERT (varchar(2), ...more >>

Both return the same records in the same order, with the same values, and I think I'm right, but I just wanted to get some other eyeballs on this so I can win the argument we're having here, because mine (the second one) is more than twice as fast. -- Uses a WHERE...IN(...) and subquery on ...more >>

Hi I am having a problem with my t-sql code basically it is not passing the @Open parameter to my code, which should return different results. I am generating a report in SQL Reporting Services which has a parameter in the form of a drop menu Called "Open" which has 3 values to choose from (Both...more >>

Sybase has an aggregate function called list that concatenates the results into a scalar. sybase eg: select dept,list(description) from parts group by dept This query would return one row for each department. Each row would contain all the description text for that department. Is th...more >>

Hi there, I have created a sp and function that returns amongst other things a comma seperated string of values via a one to many relationship, the code works perfectly but i am not sure how to test its performance.. Is this an efficient way to achieve my solution.. If not any suggestions ...more >>

Hi: This should be pretty simple, but I'm struggling with it. I've got a table with two columns, Site and dtm_Date. I start with one record for site that represents the minimum date for that site, and I want to fill up the table with rows for every date up to the day before the query is run. ...more >>

Those of you who use xp_smtp_sendmail, here is a question for you. II create one html string in a stored procedure that needs to be a part of an outgoing email. I am currently using xp_smtp_sendmail to send this email using smtp address. The problem is when xp_smtp sends this email out, the ...more >>

Hi, I have a SP that uses a temporary table and cursor to give all employee IDs for a passwed managerid at all level of a hierarchy using a structuretable that has parentId and childId. The SP runs successfully, but when I am trying to use the output cursor I get the following error: Ca...more >>

Hi Guys: Which SQL Monitoring Tool would you recommend that can be used to monitor daily activity on SQL Servers which has the capability of sending emails as well. I am looking to resolve some tuning issues and want to install some software that has the capability to trace long running sto...more >>

When returning a result as XML using FOR XML SQL Server 2005 is returning serveral rows when the result is greater than 2036 in length. Each row is breaking on this length. I would like FOR XML to return all the xml in a singe row single column so that I can use a select scalar for the resul...more >>

I am working with stored procedures and functions that query a SQL Server database schema for the text of the check constraints. I am trying to modify them so that they will work on both SQL Server 2000 and 2005. Routines that work in SQL Server 2000 accessing INFORMATION_SCHEMA views retur...more >>

Hi I need to dynamically produce an ordered list of values from a table for presentation to a user with some predefined values to always appear at the top (which are to be excluded from the order) e.g. (All) Val1 Val2 Val3 .... Below is some sample code that attempts to demonstrate w...more >>

Hello, Is it possible to call a SP from within the SELECT part of a SQL query? For example, it is possible to put a sub-query within the SELECT: SELECT a, b, id, (SELECT Tbl2.id from Tbl2 where Tbl2.fk = id) as secondaryID FROM Tbl1 So therefore, shoul...more >>

I want to do a query on a db's system tables to determine what column(s) are part of the primary key. Example. Code: SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND sysobjects.name='tablename' Yields...more >>

Hi, From a Recordset (which keeps growing - approx 1 million), I want to generate a Flat File with specific formatting. Each record from the Recordset written in the flat file needs to be updated in the code. Summary: 1) Create Recordset (this will have 50,000 + records) 2) While loopi...more >>

Hi All, I hope someone can help, i have taken on a project from someone and have been given their db to work from, one thing they have done which i know is bad design is seperate a lit of locations into one field with commas, i.e 1,5,8,9. I now want to create a proper relationship by adding a...more >>

Hi Am I correct by saying that if I call xp_cmdshell in my stored proc and cursor that the user needs SA privilages to envoke xp_cmdshell? please see below sample: Declare @Command varchar(3000) Declare @Request varchar(4000) Declare @Site varchar(20) Declare @BookingRef varchar (20) ...more >>

Hello, I have table for example: date accid value 1/1/2005 1 30 1/2/2005 1 20 1/3/2005 1 10 1/4/2005 2 40 1/5/2005 2 50 1/6/2005 3 20 1/7/2005 ...more >>

Hi, 1. To my knowledge from sql 2k, there is an option "On delete cascade" and "On update cascade". But in almost all databases which I have seen till date, user defined SP's have been created for deleting table content instead of checking this "On delete cascade" option. Is there any speci...more >>

Hi there, I'm experiencing some very strange effects when I trying to include a subquery. I shall demonstrate with a simple example... create table test (ind int, seq int, message varchar(255)) insert into test (ind,seq, message) values (1,1,'date=01/06/2006') insert into test (ind,seq...more >>

Hi, What I would like to be able to do in simple SQL is to group on a term that has come from a longer string. As below. I begin with data in this format. PARTNO DESC 12345-01 Capacitor C1 requires resoldering 12345-01 Capcitor broken, replace...more >>

Hi Guys, I have a table like this Groupid Salary B01 1000 B01 2000 B01 3000 B02 2000 B02 2000 B02 1000 I wish to show like this Groupid Salary ...more >>

Hi, I have to write many insert scripts on very wide tables. Some more than 100 fields. One of the fiels is of data type timestamp. Is there an easy way of doing an insert into...select and excluding some fields other than to specify the fields you are inserting. Something like insert into cu...more >>

I've got a stored procedure wrapping an INSERT on a table and would like for the caller of the procedure to be able to specify whether to use the DEFAULT value associated with a column (or columns) at the time of the call. I've determined that I can cause an INSERT to use a DEFAULT in two ways...more >>