Hi After executinq a query. The results are in de grid dsiplay with the columnlabels above Then I copy all the line to paste them in Excel How can I make that when I copy the results grid (normally I do it by clicking in the left corner of the grid) I also have the cloumnlabes copied in excel ...more >>

Hello, What is the best way to handle a query where I want all the data values from a row in a table that has the most recent order_date? If my table has order_date, order_num, order_address for instance. It is made more difficult by the fact that there can be more than on row that has the ...more >>

Hi All, I have one doubt in SQL Server, Regarding how DELETE Works? What is the Actual Thing that SQL Server Do when we delete any record from the Database Table? Does it maintain any Falg Internally or actually Delete the Record from Database? (Like In FoxPro It store some flag internally...more >>

Has anyone discovered the algorithm and the appropriate system tables/columns to use to compute the actual amount of space used/remaining within a database datafile? I'm trying to hit out to the values displayed in the "Taskpad" view in Enterprise Manager and want to write a routine around thos...more >>

What is the difference between: INNER JOIN LEFT OUTER JOIN RIGHT JOIN CROSS I'm a new to SQL, please help. Thanks ...more >>

How do I trap a BULK INSERT error - say attempting to import data that is wider than the actual field The code - a script, a sproc, a sproc called from another sproc - just dies at the BULK INSERT statement. Is there no way to trap for such errors?...more >>

My son set up a database using sql for our leagues hockey season, and without much 'techie know how' we (I mean 'I') need to know how to select the team with the most rows in the winners table. CREATE TABLE winners ( name VARCHAR2(32) NOT NULL, year DATE, description VARCHAR2(128), c...more >>

I have 3 Tables... Transaction, Customer, Item For each Item I need to find out the latest Transaction and display the Name of the Customer. Can any one help? ...more >>

Hello, Here are some of my records: ;Other;Sales;Admin; ;Sales;All Users;SalesAdmin; ;Other;Sales; ;Sales;; It are rolenames with a ";" as delimiter. Now I want to select all there records and filter out all rolnames to one string. This must be the result: ;Other;Sales;Admin;SalesAdm...more >>

CREATE TABLE [dbo].[Customer] ( [AccountNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N...more >>

I've a Stored Procedure which retrieve some data into a temp table. It works fine except that when attempt to output the rows from the temp table into an output file, it fails. bcp "SELECT CoName, Email FROM tempdb.dbo.#BC2" queryout e:\project\sicc\ftp\TEST.CSV -c -t \, -r \n - However, if I use...more >>

Hi all, Can xp_sendmail send html type content or just text? I asked this because i need to bold some of the fonts in the content. Please advise. Thank you....more >>

I've got a problem on a column VARCHAR(30) that has a trailing tab after every entry. I have tried to use UPDATE myTable SET mycolumn = RTRIM(mycolumn) but this doesn't seem to have done the job. I am having to delete manually. The data came in from a tab delimited CSV . Am I missing ...more >>

I need to build a sql statement that converts a field to a double SELECT ApplicationVersion, 'DEMO' as Datamodel FROM DBCompatibility where DataModelVersion IN ('7_8','7_9') I need to be able to retrieve all data that is between 7.8 and 7.9, mainly the datamodelversion field. Thanks ...more >>

Hi, Question 1: If use INSERT #TEMP_TABLE EXEC p_StoredProcedure, does it lock the tempdb system tables during the stored procedure execution on SQL Server 2000? If yes, does it mean other uses cannot create temporary tables? Question 2: In the context of Question 1, If the CREATE #TEMP_TABL...more >>

Hello: In a stored procedure in SQL, I want to create a parameterized SELECT query where the table name for the query can be supplied as a parameter SELECT * FROM "tablename" where "tablename" is supplied as an argument just before the query runs. Can someone help me with this syntax ...more >>

Hi All, Any help appreciated. I have an absolute load of views that I need to create in multiple databases. Some of these views reference other views and so on. I'm unsure of the order that I need to create them in. Question: - Is there any way in TSQL to remove the checking of constitu...more >>

Running SQL2k Would like to determine customers that haven't submitted anything in the past year. To do that, I have determined that it involve 3 tables. Basically I want to select Name, Address, City, State, etc. - [Schedule Forms].[Date Created] needs to be at least 1 year ago - [Schedul...more >>

Given this simple table: CREATE TABLE [dbo].[SomeTable] ( [SomeInt] [int] NOT NULL ) What is a SQL statement that would DELETE all/only duplicate rows ? Starting table content like this... [SomeInt] 123 75 456 75 345 75 would end up being this... [SomeInt] 123 75 456 345 ...more >>

For each record in Table 1, I need to find the next available record in Table 2, where Table1.X1 = Table2.X2 and each match must be unique. The records in Table2 can appear only once Table1 Table2 X1 J2 Description J1 X2 Descr ...more >>

I am trying to get all records with a date field of today. I tried the following but it gave me no records, even though there are several with todays date. SELECT * FROM RepairOrder WHERE (ExpectedDate = GETDATE()) The ExpectedDate is a smalldatetime field and some have both a date and time...more >>

Hi I have the following tables CREATE TABLE ( id INT IDENTITY (1,1) NOT NULL Desc1 nvarchar(400) NULL CREATE TABLE (id INT NOT NUL Desc2 nvarchar(200) NULL InsertDate DATETIME NOT NULL InsertDate has a default of GETDATE() defined I then define a view vw_ALL CREATE vw_AL SELECT A.id...more >>

HI all, We are collecting quite a few SPs and a few views. When a table schema changes, though, I don't find a problem in the SPs or Views until I use them. I would like to find a way to validate (syntax check) all of them programmatically. I looked into sp_recompile, but it will only mark...more >>

Hi Freinds, SQL 2000 Which statement if more efficent? if (select count(*) from timecardexportsbcode where lactive = 1 and csbcode = @sb_code ) > 0 if @sb_code in (select csbcode from timecardexportsbcode where lactive = 1) if exists(select csbcode from timecardexportsbcode where lactive =...more >>

Hello Everybody, here is my problem: I have a backup of database from customer. When I restore it on SRV01 (Windows 2000 Server, MS SQL Server 2000) the stored procedure PRC01 runs quite fast (1 sec). But if I restore the same backup on my machine CLI01 (Windows 2000 Professional, MS SQL Serve...more >>

Good day! --- sp_tables null, null, null, "'TABLE'" --- why must we enclose it with " ?! -- Best Regards Yanick Lefebvre ...more >>

Hello: I have some code in a asp.net function in C# like this: DateTime datetime = DateTime.Now; SqlCommand CommandEvent = new SqlCommand("spAddNewEvents", Connection); CommandEvent.Transaction = Trans; CommandEvent.CommandType = CommandType.StoredProcedure; *I try First : CommandEvent...more >>

I need to return every day in a date range. For example, if I pass in the start date and end date as parameter, I want to get every calendar day in that date range. How can I do it? Thanks....more >>

If I have two tables, Account(parent) and Contact(child) , and want an indicator that a certain contact is a primary contact for the account, which of the following two approaches are better, given the initial schema: Account ====== AccountID AccountName Contact ====== ContactID Accoun...more >>

Hi, I have a table like this. CREATE TABLE AssignedIDNumbers ( AssignedIDNumber varchar(90) PRIMARY, CarrierID INT ); Now, each assigned id number can have different formats. for example 002435243-34 000023423-12 02343324-233 pretty much its a string with a hyphen ...more >>

Hi, I'm trying to use a CASE statement in a where clause. 1. are you allowed to use it in a where clause? 2. If #1 is yes, what's wrong with this statement SELECT ISNULL(id_creditCard, '') 'id_creditcard', ISNULL(id_merchant, '') 'id_merchant', ISNULL(bill_name, '') 'bill_name'...more >>

Where can I check the IP-addresse of a running SQL server? Is there a difference if it is the full version or if it is the MSDE that is running? Can someone explain how this works... Zeke ...more >>

DECLARE @BeginofPrevMonth DATETIME, @EndofPrevMonth DATETIME SET @BeginofPrevMonth = DATEADD(MONTH, DATEDIFF (MONTH, '1900', CURRENT_TIMESTAMP ) - 1, '1900') -- Previous month SET @EndofPrevMonth = DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE() ), 0)) -- Last date o...more >>

Hi , I've inherited problems with a database which has a start time and end time being stored as varchar. For example some of the data is stored like 9:00 AM and some as 9:00 is there any way I can convert this to the correct format in order to query these columns properly i.e. select * from myt...more >>

Hello I woul'd like a script that tells me all columns with the datatype char or varchar, i have look in the sysobjects and syscolumns table with the following script "select c.name from syscolumns c INNER JOIN sysobjects o ON o.id=c.id WHERE o.xtype='U' and category =0 AND (c.xusertype=17...more >>

Hi I am mystified, probably stupidly, by this. I can write a simple insert sproc that returns an identity value CREATE PROCEDURE [procmyTableInsert] @TSCtlNo [varchar](10 A SET NOCOUNT ON DECLARE @TranID IN INSERT [myTable] [TSCtlNo ) VALUES( @TSCtlN IF ...more >>

I have a reflexive table Employee i.e. I can have a CEO with many VP's, each VP can have many Managers etc... So basically I have an n-ary tree in one table. Each node has PK and Parent columns. Parnet is null if the node is a root element. Ok, so what I want is a Formula field that will alwa...more >>

I've been able to find out how to do the date without separators, but is there a way to take: getdate() and extract time from it, without the separators? Like: 09:45:30 would be 094530 and 08:29:00 p.m. would be 202900? SC ...more >>

Hello! I have one index question ... I have a large table with fields like: id, start, duration, service, Now, id is not really relevant, but it's there because I needed it for repliaction for one unique value. Start is a datetime; duration is bigint and service is a alpha-numeric varchar...more >>

I have to output a couple columns in a text file from 2 sql tables. The first field is a date, second is a time field. I have the following code: rtrim(cast(datepart(yyyy,getdate()) as char)) + rtrim(cast(datepart(mm,getdate()) as char)) + rtrim(cast(datepart(dd,getdate()) as char)) ...more >>

I was asked to write a stored procedure that will return the column/field names for any given table in a DB. I'm still new to T-SQL and don't know how to do this without returning * from a table then getting the fields names. I'd be passing the table name as a parameter to the SP. What's the easi...more >>

Hi all! :O) I'm trying to generate the sql script for a given database. For some reason the script file isn't created on the disk.. I can see the beginning of the script on the first row of the [Grids] tab though (QA)... any idea? --- declare @DatabaseCollection int declare @Server int de...more >>

Hi, I have some students with notes. For each student I would like to give some points according to their average. If they are on the first quarter, 4 points second quarter 3 points third quarter 2 points fourth quarter 1 point how to do that ? I order my table by AVERAGE NAME AVE...more >>

For administrative purpose I NEED whether a job is enabled ,sheduled and the history of the jobs .Which are all the tables linked to it so that i can do it ...more >>

Please help me with complicated query: eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where value in "a" field is dublicate, but leave one of each. in simple sentence I need to delete invert distinct of field "a". Please help me with this query Rows are NOT duplicated, just ...more >>

Hi The table I am using has a date field that is a char type and values in the field are yymmdd. Table name: emp_tmp Create emp_tmp (Name char(30), date char(6) ) Values in the table Name date Joan 011222 Joe 990215 Doe 981119 Sam 00051...more >>

Hello, I would like to build a bitwise table in SQL with a couple of values which are an int and a varchar(50). I would like something like the following: 0 - No preferences 1 - Movies 2 - Sports 4 - Internet 8 - Travel 16 - Sleep 32 - NBA 64 - NFL This way I can store and manage t...more >>

Hi, group Somebody to have experience in using types tables instead temporal tables. I need some of code I would to price some examples. Best regard Mario ...more >>

I would like to cast a SID into a basic string and I am not sure how to go about it. From what I can tell, SID's are VarBinary(85) at least as SharePoint stores them. I have tried a simple Cast(my.SID As VarChar(50)) without any luck. Any suggestions? Ultimately I am tr...more >>

Hi All, I selected "Back up the transaction log of the database as part of the maintenance plan" in the "Database Maintenance Plan" wizard. However there was no "Remove inactive entries from transaction log" option. Will the transaction log be truncated during the backup? TIA ...more >>

I have seen 'N in queries can any one explain the significance of it thanks...more >>

What are the factors to consider on whether to use surrogate foreign/primary keys in fact/dim tables? Is it a requirement to use surrogate keys in a data warehouse environment? It does complicate things more in the ETL process and maintenance wise by using surrogate keys, agree or disagr...more >>

Hi all, What is the simplest way to return a resultset of a range of numbers - for example, calling myproc(startnum, endnum ) returns a result set of consecutive numbers starting with startnum and ending with endnum I remember reading a TSQL Blackbelt solution sometime ago... Regards and t...more >>

Dear Group, While auditing a SQL Server Database I have found duplicate index (MessageID) on a Heavily Used ( Select, Insert, Delete, Sorting Opeartions)Table. Basically this table is interfaced with a asp page which displays emails for users. Now I am confused which index can be dropped ...more >>

I'm using SS2000. Table tblHL_leadsales is a summary table. For every row in tblLeads_Branch where resp_flag is a 1,4, or 5, I want to increment pest_sales_count and term_sales_count in tblHL_leadsales. If there is only one row in tblLeads_Branch for a promo_code, lead_flag, branch combination i...more >>

is there anyway to pack a database in sql server to get rid of any unneeded data such as deleted records? thanks ...more >>

Hi, I know the manual says that the scope of the object pointer returned from sp_OACreate is local to the calling procedure, and that the object is destroyed on procedure termination, but it would be really cool to be able to pass an object reference between stored procedures, maybe somehow pl...more >>

Hi I have stored procedure that calls another procedure. The first procedure looks like this create procedure [dbo].[SDETraktKatSelTrakt @rg char(2) ='11 A SET NOCOUNT O exec ARCSDE.tSDBTraktplan.dbo.TraktkatSelTrakt @r What happens is that my VB-app is calling the procedure: SDETrakt...more >>

Can anyone tell me differences between Mydb..MyTable and MyDb.dbo.MyTable Thanks...more >>

I want to search our medical database for duplicates. I plan to group by last name and Date of Birth. My query will look like the following: SELECT PLNAME, DOB, count(Company) FROM CLMASTER WHERE CLMASTER.COMPANY='MAIN' group by PLNAME, DOB order by PLNAME, DOB I want to select record...more >>

I am upgrading to SBS2003 with SQL2000 from SBS4.5 and SQL7. I am using Access as the front-end to my SQL database. I cannot figure out why the code below will work a machine running Access 2003 connected to SQL7 but will not work on a system running Access 2003 connected to the new SQL2...more >>

Hi, i would like create an text file(or CSV file) from Tsql storedprocedure.. How do it ? Thx in advance and sorry for my poor english :) N....more >>

If i ran an update statement on a table (that has a update trigger) that update more than one record ,Does the trigger fires n times (Depending upon the number of rows affected by the update statement)...more >>

I am working on the following problem... Need to display membership levels per month for each location... I have put the following query together to get the cross tab for month & location, but I am having trouble getting the counts into the query SELECT DISTINCT Location.[NAME] as Location, ...more >>

Someone posted this solution to an inquiry to find the names of all employees whose salary is 2nd (or 3rd or 4th...) greatest in the whole company (see below). I have now stared at this query for quite a while and can not get my mind to figure it out. Are there any online resources that I can use to...more >>

I'm trying to create some client side code to connect to a SQL server database from javascript but it just will not play. There must be something missing that I've not had to do before, because connection strings work from every other language - C#, C++, VB6, VBScript... you name it. Javascript just...more >>

Yesterday I had a problem with one of my websites in that lots of Querys kept timing out when called from a web page I ran SQL profiler and found that (for example) 1 of the stored procedures was running for around 31500 miliseconds then timing out The exact same stored proc only took 2 second...more >>

ciao a tutti... ci risono... devo migliorare delle performance di accesso ad una tabella... la tabella non ha indice primario, ne altri indici... sulla tabella ci accedo con select di questo tipo.. select @ExistInOAG = count(*) FROM caprs05dev.dbo.OAG WHERE Air_Ca...more >>

PK user period amount --- ---- ------ ------ 1 001 D 10 2 001 D 11 3 001 N 20 4 002 D 30 5 003 N 40 How could I UPDATE the table such that the reuslt will become: PK user period amount --- ---- ------ ------ 1 001 D 0 2 001 ...more >>

I'm trying to make a script that loops through all tables in a database and change the owner on every (user) table, but I can't seem to execute stored procedures within a variable. It says: "Could not find stored procedure 'sp_changeobjectowner" Any solutions? /Lars ---- DECLARE @SQ...more >>

The table: user period amount ---- ------ ------ 001 D 10 001 D 11 001 N 12 002 D 30 002 D 31 003 N 40 A summary table is to be created for each user+period, with one extra requirement. For each user with reocrds in both period "D" and "N", ...more >>

Hello, Inside a column I have this result: Record 1: Sales;Admins Record 2: ;Sales;Admins Record 3: Sales; Record 4: Admins; You can see the delimiter ";", it can be everywhere. Now I want to delete "Sales". Therefor I have to search where the "Sales" is. (records) After that I want t...more >>