Hi all, I have an urgent requirement. I have a table which looks like ID1 ID2 Value 1 1 a 1 2 b 2 1 c 2 2 d 2 3 e where ID1 and ID2 is the composite primary key. I want this to appear as ID1 ID2 Value 1 1 a,b 2 1 c,d,e Is it possib...more >>

Is it possible to create a empty table from SQL view...like shown below? CREATE TABLE tmpTable AS SELECT * FROM MyView WHERE 1=2 TIA J Justin ...more >>

Hi I have a problem with getting a field value.I have a view like this: CREATE VIEW V1 AS SELECT F1 AS Date,... UNION SELECT F2 AS Date,... and I write a SELECT statement on it.I have at least a record in first select and F1 have value.but in my application using ADODataset I can see my r...more >>

I'm trying to better understand the structuring of data in databases and have considered a scenario that is befuddling me and so would appreciate anyones' thoughts on this as I'm trying to better understand database architectures. If I have the following tables: CREATE TABLE Games (RecID INTE...more >>

hello I'm here for the first time so hello everybody :) I came here to ask one question, because I have a problem with mssql trigger creation: is it possible to create trigger on table A which modifies some records in table B ? I got the following error: "The column prefix 'deleted' doe...more >>

I called my own extended stored procedure 1000 times to make sure there was no memory leak but the sqlservr.exe memory usage went up so i thought there might be a memory leak. I commented out all the code in the extended stored procedure but the call to srv_paraminfo and it seems that srv_paramin...more >>

i would like to prevent my customers from viewing sql statements/stored procs that i create for their use (for example using sql profiler). i basically want to protect my source from reuse. is there a way to prevent the output of the sql being issued to the server? thanks, t...more >>

I am attempting to do this: For each closed Transaction in Transactions (t) table, if CustomerID <> "" then JOIN to customer table (c) on t.CustomerID = c.CustomerID, if t.AccountID <> "" then JOIN to account table (a) on t.AccountID = a.AccountID. Here is my "best" attempt... SELECT * FROM Tr...more >>

anyone know how to enable identity property for an existing column using SQL statement? ALTER TABLE ... only supports it with new columns, yet in enterprise manager it allows you to enable it ...more >>

I have about a year and a half of SQL Admin experience. I write some T-SQL but I cannot figure out how to write a query to pull out the data I need from our database. This is the situation... select * from table_name (NOLOCK) WHERE field_1 = '12345' and field_2 = '11' Field_1 is ...more >>

What is the best way to store a tree structure in DB form. Thanks...more >>

hi! I have big problem with getting identity value for unique sequence. I need unique integer value for key generation. ( key's format is YYYYMMDD + Random Alphbet + seqno ) I want to get unique sequence like oracle's sequence. So, I created one table which has one int column, then set ...more >>

Dears, I've got a problem. It is required to get the list of all the tables in one database. Is there any sql script can realize it? Your answer will be highly appreciated. Kevin G ...more >>

I have a delete statement that when ran from Query Analyzer takes about 45 minutes to complete. When I put that statement into a dataAdapter and execute with an ExecuteNonQuery, it gets a timeout after a few seconds. How can I keep this long running process from getting a timeout? Thanks, G ...more >>

(SQL2K) In the table and data below, I need to find those rows which have the same IT value but have conflicting date ranges. e.g. In the two rows below, both have value of 'AJ' for IT but part of the date range for 'Official Public' 'AJ' falls within the date range for the 'Abstract of Judgme...more >>

every time I run a sp in the debug mode it returns an error message ODBC: Msg 0, Level 19, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 68 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. what's up ...more >>

Hi! How does SQL Server 2000 store null values for int, char(10), varchar(10), float, datetime, text,... etc.? With an extra bit or byte or ...? ...more >>

Hi all, I have an Inventory table with the usual fields : ItemID, Description, UOM etc. I also have a BillsDetail table where I store all the item purchases, with fields like: ItemID, VendorID, DatePurchased, Qty, UOM, LastCost eta. What I'm trying to do is create views with these fields: ...more >>

Hi, I have database with recovery model set to Full. I executed this insert command to increase the size of my log file: insert MyTable(MyCol) select customerid from customers cross join [order details] The log file became almost 100mb. I did not make any backup from my DB and when I issued ...more >>

Hi All I use sp_help_fulltext_columns in 2 databases at the same server but it produce totally 2 different plan. I beleive one plan is correct. How can I change it? It still the same plan everytimes I run in specific database. Thanks you in advance. James Jarupan ...more >>

I've got a column that in my table that is of the binary datatype and contains 0 and 1. I need to display that on a asp.net page as True or False. Any ideas as to how I can do that, or where (sql, asp.net ). Thanks P ...more >>

I'm trying to UPDATE one table by using values in another table. They both have a common field. Does anybody know the structure of that? I've looked online. Doing this is the SQL Query Analyzer: SOmethign like: UPDATE COMPANY_INFO SET COMPANY_INFO.COMPANY_SBTOTSYS = USERS_OLDDATA.TOTALSYSTEMS W...more >>

Hi all.. Actually my question is about database design, but I dont know the suitable newsgroup for this question, so I post in here. I have 3 type of status, that is 'new', 'pending', and 'close'. I can put this seperately in 3 table or one table. I want to know your opinion about this, sho...more >>

Hey folks, I hope you can help. I have a table like below, Dept ID int incrementing DeptName varchar Then I have the following table, Position PosID int DeptID int (from Dept table) PosTitle varchar My boss thinks that the Position table should have the actually Dept ...more >>

Hi, I read this paragraph in BOL about SHRINKDATABASE command: ---- When using data files, DBCC SHRINKDATABASE has the NOTRUNCATE and TRUNCATEONLY options. Both options are ignored if specified for log files ---- As far as I know, we cannot issue SHRINKDATABASE for data file or log file, but...more >>

I have a table with 1/2 million rows and I am deleting about 100,000 rows and it is running around 45 minutes. its a delete from - in... delete from mytable where mykey in (select mykey from mytable where mykey = mycriteria) I did the same thing in a loop in vb.net using a dataadapter a...more >>

I have a job that runs every 5 minutes that executes the SQL statement listed below. How would I create SQL statement to find the maximum Total_physical_io for each 5 minute interval? Thank You, Don INSERT INTO KPC_Sysprocesses (EventTime, spid, Total_physical_io) se...more >>

Weird I know but I could use some help... Here is my problem. I have a table with table names in it like this: TableNames: Key | TableName 1 | Table1 2 | Table2 3 | Table3 In each table it has something like this: Table1: Key | Date 1 | 1/1/...more >>

Hi All, Who knows how the database views work at the lowest level? If we have a view with a very long query and retrieve data from this view does it retrieve data from all tables only when we send a query to this view, or always when the relative data is updated this view should be updated by ...more >>

I have a potential client who has a Access-based DB which they use for recording orders, customer details etc... They want to provide some of this information online, eg. customer account details and stock-levels etc, whilst retaining their existing functionality. They also want to retain the ...more >>

I have query that returns some results including some with certain key columns that have null values. I want to have the results returned in alphanumeric order but with the null values at the end. BOL suggests this can be achieve with the collation property DBPROP_NULLCOLLATION value DBPROPVA...more >>

I have a table called "UI". I need to update the 'ageGroup' field automatically according to the following logic. IF {@uiAge >= 0} && {@uiAge <=5} SET @uiAgeGroup = 1 ELSE IF {@uiAge >= 6} && {@uiAge <= 11} SET @uiAgeGroup = 2 ELSE IF {@uiAge >= 12} && {@uiAge <= 17} SET @uiAgeGroup = 3...more >>

Hi, I just read this: "SQL Server 2005 doesn't let you specify a subquery as an argument to the IN predicate" in an article by Itzik Ben-Gan: http://www.winnetmag.com/SQLServer/Article/ArticleID/43140/43140.html Have I taken this too literally by applying a general context? Needless...more >>

Anyone know how one can read thought an Exchange mail folder and remove the attachments using T-SQL?...more >>

HI i have installed VisualStudio Sp6 after this installation when open my Sql 2000 server Sp3 i can't visualize Diagrams. Any operation on this windows don't work. I reinstalled Sql Sp3 but don't work. Can help me? Tia Pupo ...more >>

Please Help :) How I can send a query output through xp_sendmail but send it as an Excel file instead of csv file. Thanks Tanweer ...more >>

Hello, I am converting to MSSQL SERVER from SYBASE. In our client side application we use callback procedures with CT_LIB to get information back from the server during a lengthy process or an infinite (looping) process controlled by or called within a stored procedure. I can not find any ty...more >>

How can i upgrade sql70 database to sql2k database.? Thanks!!! ...more >>

I have two filegroups and one data file belonging to each filegroup. I am trying to delete the datafile and filegroup that has not been assigned to default. I have called dbcc shrinkfile on the file in the non-default filegroup but it is apparently leaving one extent of data so I can't delete ...more >>

I need to make the EmpID column in my table lowercase instead of uppercase. The EmpID looks like the following, pl381234, which is the first initial of the first name, first initial of last name, 38 (for facility code) and then the last 4 digits of the social security number. Currently EmpID loo...more >>

I am trying to move some data around, and now that I have it moved, I want to find out which records aren't there that should be. I have 2 tables, Employees and EmpPersonalInfo (they are in different databases), If I use these queries, SELECT COUNT(*) AS TotalEmpRecords FROM Employee; SEL...more >>

CODE if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BacsHdrYearly]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[BacsHdrYearly] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BacsTrnYear]') and OBJECTPROPERTY(id, N'IsUserTab...more >>

Has anyone got any handy tips on tracking down which column is the offender when getting the error message "string or binary data will be truncated" ?? ...more >>

Hi all, I need to compare 2 days and pick the lasted date between 2 columns. Any help would greatly appreciate. Please look at the result want below. Thanks again. DROP TABLE #Test GO create table #Test ( Policy_id INT NULL, Policy_nb CHAR(15) NULL, TranProces...more >>

Any one who can help. In the SQL sample apps, in the ADO/XML there is a ASP file located at C:\Program Files\Microsoft SQL Server\80 \Tools\Devtools\Samples\ADO\XML\OpenXML.asp. After modifying the server name and run it, there is error at line 67 (adoCmd.Parameters) The other FORXML.asp w...more >>

I'm trying to implement column-level optimistic concurrency where one user can update one column of a row and another user can update another column or the same row without a violation but if 2 users attempt to update the same column a concurrency violation occurs. The trick is that I don't want to...more >>

Is there a way to retrieve the number of days in a given month? Thanks for the help! Derek Ruesch...more >>

Hi I need to transfer data from an old sql server table to a new one with extra fields. The old table has a identity field (intAbsID) with seed and increment 1. I need to preserve this value in the new table. The new intAbsID needs also to be an identity as above but in order to transfer the ...more >>

How do I list the DTSes on a server? I know I could give myself access to msdb..sysdtspackages but I obviously don't need to do that as how does enterprise manager know what DTSes there are? I want to present a list of DTSes (and preferably the user they're saved under) to the user in my custo...more >>

We have central sql server and a few linked sql servers.How I can detect,if linked server is on line,then not exists problem with LAN, from stored proc?If I can connect to off line server,sql server raise exception, but I don't want it. *** Sent via Developersdex http://www.developersdex.com...more >>

I've a report on a single DB tabel containing 2 grouping levels. The report contains 2 table elements: one for summary info and another for grouped detail info. When I render the report in report manager it look fine. The report contains 193 pages. I can easily browse through all the pages. Export t...more >>

Hello Experts Can I use the COLUMNS_UPDATED() to check if any column of a row has really changed by its prior value, how please? Thanks in advance....more >>

Sorry for such a basic question but I have just come accross a useful example for a formula for a calculated column that I wanted in my database: (case when ([GndID] = 1) then 'Male' else 'Female' end) Great! The trouble is I don't recognise the language. Where can I find a reference for the l...more >>

Hi, First question to the forum. Hope this works... ;) I'm having different query results in two (supposedly identical) execution enviroments. The problem is equivalent to the one described below (for C&P purposes): The schema: ----------- CREATE TABLE [dbo].[A] ( [A_PK_KEY] [numeric](18, ...more >>

I have three tables t1, t2, t3 Table t1 has primary key. T2, T3 don't have any primary key. All three tables have one common column, which is the PK column of table1. Moreover table2 and table3 are child tables of Table1. Sample Data ========== Table1 --------- PK_Col1 Col2 1 ...more >>

For Example: declare @mytable char(10) Use MyDB_A set @mytable = 'MyDB_B.dbo.MyTab' /* I want something implement the function like this */ if exists EXEC( 'select * from ' + @mytable) print 'OK" -- It's wrong, I know Thanks, Peter ...more >>