Hi, Am new to Stored Procedures and am lost how to achieve the following. I have this table:- CREATE TABLE [dbo].[docs] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [ParentID] [int] NULL , [Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [Link] [varchar] (100) COLLATE Latin1_Genera...more >>

Hi, I have managed to create a second copy of my "live" database, for software testing purposes. Inspecting the properties of the new database, everything seems in order. The logical file name is the same, which I believe is fine, and the physical database (and log file name) is different....more >>

I have the following table CREATE TABLE [tbl_Items] ( [item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__item_id PRIMARY KEY, [parent_id] int DEFAULT(NULL) CONSTRAINT FK_tbl_Items__item_id__parent_id REFERENCES [tbl_Items]( [item_id] ) ON DELETE NO ACTION ON UPDATE NO ACTION ...more >>

Hi All, For hardware upgratdation we have to move our database server(MSSQL 2000) from a P3(Window 2003) machine to a p4(Windows 2003) machine. We have replicated the data from P3 machine to P4 machine. the replication was success full. After this we have changed the name of the p4 machin...more >>

AA Guyz i want to calculate class position of students from a table. Sample data is as follows... Roll # - Name - Marks 1 - ABC - 60 2 - DEF - 60 3 - GHI - 57 4 - JKL - 55 5 -MNO - 50 6 -PQR - 53 The query ...more >>

Below is the script of a Job called "eFIMS_SendEmail" that I wish to run. The intention is that every day of the week the job will execute a SPROC at timed intervals. For example, the SundayRun schedule will run once every 1 hours from 00:30:00 to 23:59:59 However, the clients have stated th...more >>

What is the maximum number of DB that I can create? Infinite? Thanks...more >>

I have two SPs, call them Daily and Weekly. Weekly will always call Daily, but Daily can run on its own. I currently use a global temp table because certain things I do with it won't work with a local temp table or table variable. I have been trying to get code so that if the table already e...more >>

I have data that comes from a legacy system. I can obtain the data in an ASCII format. Currently I have created scripts in ACCESS to import the data into tables. What I would like to do is create an automated import function in SQL. I am new to SQL, can anyone point me in the direction I s...more >>

I am running 2 versions of a correlated subquery. The two version differ slightly in design but differ tremendously in performance....if anyone can answer this, you would be awesome. The "bad" query attempts to build a result set using a correlated subquery. The part causing the error is tha...more >>

Is there a way to transfer ntext data from one table to another? I tried this UPDATE [projects] SET [description] = (SELECT [description_ntext] FROM [table] WHERE [id]=1) WHERE [id_project] = 1; and this DECLARE @DESCRIPTION ntext SET @DESCRIPTION = (SELECT [bids].[bid_condit...more >>

Hi, How can I make MSSQL2K5 setup install everything in the install path I have choosen - it keeps installing some files in ¨%SystemRoot% ? Thanks, JM ...more >>

(Appologies if this group isn't the best place for this post) Is it possible to use DAO 3.6 to access binary data (varbinary(max)) in Sql Server 2005? I have images and sound in a Sql 2005 DB that I need to retrieve (and write) with DAO (ADO and ADO.Net are not options as this is legacy code t...more >>

Hi, I am getting the following error: OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server Column 'tags.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. when trying to execute the following query: ...more >>

Hello I had a question reagrding MSDTC usage. I know that MSDTC will enlist each connection transactions as it's own (take ownership) and commit/abort accordingly. However I'm not sure what would happen where I have a stored proc with input parameters and 2 Delete commands on 2 different tab...more >>

I ran "dbcc checkdb(MCMSdb) with no_infomsgs" and I get the following: Server: Msg 8946, Level 16, State 12, Line 2 Table error: Allocation page (1:274992) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page. What cane be done to correct this prob...more >>

I have installed SQL Sever 2000 on my Windows XP Pro machine from the Enterprise Edition setup and it prompts you that only the client tools can be installed. I wanted to have a local database as well So what install should I have used to do so. Any information would greatly be appreciated. ...more >>

Hi! I have a user on my database that has only "select" access (db_datareader). Problem is, I also want him to also be able to create/update extended properties on tables or views, but without modifying the tables' schema. I played around with GRANT but apparently, a member of "db_datareader...more >>

Is there any way in tsql to delete a sql server registration? (I know how to do this from Enterprise Manager). Thanks! Jennifer ...more >>

hi, i've just restored my db and when i try to login with a user (even if the user is "sa"), an error occours. The error is "User sa is currently inactive" How can i solve this problem? Thanks, Massimo ...more >>

hi all, I checked out in google groups but was not able to find the answer... is it possible to limit size table in sql server? how can I do it? TIA ...more >>

Hi, You all may be knowing that Connection.isClosed() does not tells us if the underying DB connection is active or not; it only checks if Connection.close() had been previously called or not. One sure shot way to find out this is by executing some dummy SELECT query and catching it via SQL...more >>

Hi ... I have a weblog database where I want to count the occurences of a table of string values that appear in all the urls viewed. My tblWebLog as a field that contains the url ... tblWebLog.[cs-uri-stem] I have another table ... tblStrings ... that has a field [strSearch] for a string v...more >>

We have a couple of MS SQL Server 2000/2005 databases with a bunch of ..NET clients written in C#, but may want to replace the dbserver with postgres instead. The clients will still run on Windows, hopefully with as few changes as possible. We don't have any stored procedures or triggers, s...more >>

hi dis is kalaivanan, which one of inner join or left join is efficient and in what way. ...more >>

I have a tblTax. It has fields InvoiceNum, TaxAuthority, TaxableTotal, NonTaxableTotal, TaxCollected. Sample data 1,county,10.00,0.00,0.40 1,city,10.00,0.00,0.10 2,state,0.00,15.00,0.15 When totaling invoice 1 should have totals of 10.00,0.00,0.50 because the 10.00 is the total for the ...more >>

Hi all, whenever dbo is prifixed with the create script the owner becomes dbo. If the below script run by the sam the owner becomes dbo create proc dbo.test as print 'hello' Is there any place where SQL server keeps the record of creator? ...more >>

This routine works in most cases, but fails when a bad date is entered such as: 19910631 -- there is no June 31st. Instead of ignoring the bad date the entire DTS job fails. Obviously this is something that should be validated at data entry, but unfortunately the only control I have is when ...more >>

I re-designed a predecessor's database so that it is more properly normalized. Now, I must migrate the data from the legacy system into the new one. The problem is that one of the tables is a CROSSTAB TABLE. Yes, the actual table is laid out in a cross-tabular fashion. What is a good approach...more >>

Help again please, I need to insert rows into a table from another table. The tables are identical column wise except the table im inserting from does not have a primary key value. On insert I need to generate a primary key that is consecutive based on the table im inserting into. Also the tab...more >>

Hi, We have created a SQL server 2000 database. We observe that the transaction log keeps growing over time. We are now about to run out of space. We have been periodically shrinking the database. Nevertheless the size has increased. I would imagine that a transaction log can be eliminated if...more >>

Hello, could you please advice on how to measure replication performance in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ? I've got two servers with databases installed and configured, I prepared set of data using DBGEN from TPC and I already imported them into databases.Als...more >>

Dear MSSQL- experts, I have a strange problem with SQLSERVER 2000. I tried to export a table of about 40000 lines into a text file using the Enterprise manager export assitant. I was astonished to get an exported text file of about 400 MB instead 16 MB which is the normal size of that data. ...more >>

db_TBO db_TBT ----------------------------- -------------------------------------------------------- TBOID | Date TBTID | TBOID | Date ---------------------------- --------------------------------------------------------- rp01 | 01/08/2006 ap01 | rp01 | 02/08/2...more >>

can anyone help me figure out why when i run the following stored procedure i get the error: (1460 row(s) affected) Msg 245, Level 16, State 1, Procedure SP_SALESTRENDS, Line 40 Conversion failed when converting the varchar value 'X' to data type int. SP: --STORED PROCEDURE FOR INVOICE ...more >>

I would like to have two tables. One I call SystemPropertyTypeTable which contains the defaults and the other UserPropertyTypeTable. Each has 3 fields. PropertyType, Description, Status. The idea here is to allow a user to change his/her defaults or to add a new Property Type without mes...more >>

MSSQL Server 2000 SP3 in both houston and memphis I have a database in houston, lets call it RED. Specific tables from database RED are copied to database BLUE. Database BLUE is then backed up, ftp'd to memphis and restored. In memphis there is a single table in this database that will n...more >>

tblOrgSystem OrgSystemID OrgSystem 1 USA 2 CANADA tblOrgSystemNode OrgSystemID OrgNodeID OrgNode 1 3 Manistique 1 4 Houston 1 7 M-Sales 1 8 ...more >>

I have this query SELECT 'bracket' = CASE WHEN income BETWEEN 0 AND 49 THEN '0-49' WHEN income BETWEEN 50 AND 99 THEN '50-99' WHEN income BETWEEN 100 AND 499 THEN '100-499' WHEN income BETWEEN 500 AND 1000 THEN '500-1000' ELSE 'Other' END, count(income) AS number FROM #persons GROUP BY CAS...more >>

I need to reduce de installation pack of sql server express 2005. how can i do that? thanks. ...more >>

ACCESS FRONT END SQL SERVER BACK END My client wants me to implement a system to allow e-mails to be sent to their customers on a timed basis. For example: Day: Monday Start 00:30 Interval 30 mins Day: Sunday Start 00:30 Interval 60 mins In the past I've successfully implement...more >>

Hello, I am reposting this from another group, because I had no responses. I need to get the max row date from the following query. There is a date field in rcompl.date_time. There can be several rows identical except the date_time in which I need only the max of those rows. Thanks select r...more >>

Hello all. I have quite disturbing situation where I am not happy about the way how SQL handles the query. Situation is related to using user function in INNER JOIN select. Although the problem occured in more complex situation the query can be simplified to following example with same resu...more >>

My query returns a table of results, I would like to add a count column that contains the number of each result type returned. i.e. Type Count 1 3 1 3 1 3 2 2 2 2 3 4 3 4 3 4 3 4 4 2 4 ...more >>

Hi Everyone, I really tried to not post this question but I gave up. I tried brackets, parenth...etc but nothing worked. I get this error message: Incorrect syntax near the keyword 'THEN'. Please help, I am learning SQL Server. thanks in advance. Ismail use mis select CLAIM_DETAILS_HCVW....more >>

Would anyone please instruct how to prevent the duplicate record by setting the unique keys on the ms sql server? i've been checking the duplicate record as front-end and i found out if there is an internet delay or some other reasons, it has a chance to store the duplicated data into the databa...more >>

I inherited an application (or two) that run on SQL Server 6.5, which I haven't used in years, and am having a problem. I get the error: ------------------------------------------------------------------------ Can't allocate space for object 'Syslogs' in database 'master' because the 'logsegm...more >>

hi, My problem is I am creating a new database. I need to all the tables and procedures from one databse to another database in sql server 2000, both in same server. Kalaivanan. ...more >>

Hello, I'm trying to follow some sql sentences that my system send to SQL 2005 express and I don't have a deep knowlegde of databases. I know that there's a transactions log that keeps all sentences that go into database motor. Is it correct? in case yes, is there a way to look at this archiv...more >>

Hi, Can anyone please help me with SQL syntax to create a second variable based on the value of another (both numeric)? My effort is below but I get a syntax error. SELECT charA, CASE charA WHEN < -199 THEN 2 WHEN < 31 THEN 3 WHEN < 82 THEN 4 WHEN < 100 THEN 5 WHEN < 105 TH...more >>