Hello, I'm writing a bunch of reports that are grouped by day. I've been selecting convert(char(10), sh.timeread, 112) and grouping by that. I also tried MONTH(sh.timeread), DAY (sh.timeread) and grouping accordingly. My question is what is the most efficent way in T-SQL to group by day...more >>

I need to write a sproc to capture which table is on the verge of having run out of identity values depending on the data type.. Theres 2 issues here. For eg: say if table has a col of identity property and a datatype of smallint. So if we start wuth identity(1,1) and the value reaches around ...more >>

Hi All, My Application uses ADO 2.5 to connect to SQL Server 2000 with SP 4. When I Retrive a Large Amount of Data for Report using a Query then My Application Gives thew below ERROR: Error Class: EOleException Error Message: Multiple-step OLE DB operation generated errors. Check each OL...more >>

Hi All, There is some additional info I forget on this same topic I just posted. I have a database design question, pls give me some help.. I want to define tables for salesman's sales target commission . The commission could be given per EITHER sales amount of : Group of Products OR Group...more >>

I'm referencing an article I printed out but unfortunately don't have the URL to: "Unless I absolutely have to, I try to avoid having my application call stored procedures directly. A relational database manager *should* be able to hide the stored procedures behind a view allowing a nice standar...more >>

Hi All, I have A Problem with this Cursor. It takes a LONG Time. I Want to Avoid that. Please help me to Build a SQL Script Instead of this: SET NOCOUNT ON BEGIN TRANSACTION MAIN_TRANS DECLARE @TIMESMAILED int, @PHONEN VARCHAR(15), @DATEPAID DATETIME, @AMOUNT FLOAT, @SOURCEID...more >>

Hi there, I have a text column that needs to be filled with multiple varchar columns scattered around other tables. Is there anyway to make this works? UPDATE tblFullText SET textcol = (SELECT varcharcol + ' ' FROM tblStrings) The ' ' is necessary to make the textcol fulltext-searchable. ...more >>

Does anyone have a recommendation on a grid control for foxpro outside of the Datagrid control that is shipped with VFP 8. Many thanks Shahriar ...more >>

I'm trying to materialize a view (pasted at end). After reading the books on line I ran the following script to make sure these recommended defaults were set.... USE MASTER GO ALTER DATABASE MyDatabase SET ANSI_PADDING ON SET ANSI_NULLS ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONC...more >>

When using SQL 2000 personal desktop (i.e MSDE) on win 98 Machine with our VB6 application we sometimes get this error [Microsoft][ODBC SQL Server Driver][SQL Server]CryptoAPI function 'CryptAcquireContext' failed. Error 0x8009001d: Provider DLL failed to initialize correctly. On Win2000/20...more >>

Does anybody really know is it possible? We don't want to create a linked database, but we need to make some steps on the remote database, gather some data and then send this data to our main server via IP connection, setting the connection every time when the remote server wants to transfer the ...more >>

I have a table named Documents with columns like: ID, CategoryOneID, CategoryTwoID, IssueNr, IssueDate, etc. Each document is assigned to two categories: CategoryOne and CategoryTwo, to which it is linked by a relationship. A document for a certain pair of CategoryOne and CategoryTwo may have se...more >>

Hello, Hope you are all well i have a quick question for you. I have a stored procedure i'd like to supply a list of numbers along with and identifing character code i have read an article by Erland Sommarskog (http://www.sommarskog.se/arrays-in-sql.html) detailing how to pass a character l...more >>

Hi! I'm using SQL Server 2000 with an Access XP ADP. When a user tries to store his changes sometimes he gets the message 'Another user has changed this record'. But the record was not changed on another machine. Now I found out that this problem can occur on SQL Server due to rounding-di...more >>

hello please help because I can not find solution myself I have a few tables and I defined on some foreign keys with ONDELETE CASCADE problem is that I'm getting an error that "it may cause cycles or multiple cascade paths" I understand what it means, but that's what it is, there are a few ta...more >>

I've declared a sproc as follows: CREATE PROCEDURE usp_CreateFolder @FolderGuid UNIQUEIDENTIFIER, @FolderName NVARCHAR(50), @FolderDescription NVARCHAR(100) = NULL, @ParentGuid UNIQUEIDENTIFIER = NULL, @ParentId INT = NULL, @NodeType TINYINT I'm attempting to call this sproc as f...more >>

I have about 5GB back up on Tape. after restoring about 92% there is as error. there are 5 databases on Back Up Tape. how to retrieve Partial back, so that i can get First 4 databases, and leave the last database on which error occurs. Any help is greatly appreciated. thanx...more >>

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 >>