Hi All, I want to know the best practise (oops in the first place a solution then the best practise :)) for error handling in nested transactions. That is, let me explain with an example: 1. From Stored Procedure 1 i would be calling SP2 2. From SP2 ... SP3 would be called. I want all t...more >>

Hallo Friends, I Want to test one function,, Which is very complecated... So i want to see the flow of it from start to end,it's written in SQL SERVER 2005... I tried from query analyser ,but i cant...Can u plz give me some help advice Thx in advance Regards Jitendra ...more >>

I need to write some parameterized views(function that returns a table) for our analytics department. problem is the views will be written against third party software which creates a new database for each new client. We also can not store any sql we design in their database. I will be creating t...more >>

hi all, how can i remove primary key with the help of query ...more >>

Is there any way to feed an array of search terms to SQL Server and have it output an array response? For example, I want to quickly know which of 100 words in a paragraph are found in a particular table. It's very inefficient to do 100 queries sequentially against the table. It is effici...more >>

Hello, after migration from SS 2000 to SS 2005 I have some legacy DTS packages which required SSAS 2005 cube processing. Old OLAP 2000 cube processing task does not work with SSAS 2005 cubes. Is there any possible way to process SSAS 2005 cube from legacy DTS package? Thanks, GB ...more >>

Hi I've inherited a very old app written in VB which pointed to Access. It needs to be working off SQL server 2000. The future I will rewrite it using ADO and SPs. That is a v.big job. For the time being I've changed the code to point to SQL Server and in general is working well With mini...more >>

I am an Oracle database administrator who has been pulled into the SQL Server world and am having some difficulty with what should be a simple task. I have a SQL query process that is stored in a directory on the Windows server. I would like to schedule this to run as a job from within Enter...more >>

Hi, Botton line is I want to be able to see TextData column with RPC:Completed event. from SQL 2005 Profiler 1. connect to SQL server 2. Add Stored Procedures - RPC:Completed event 3. Add TextData column and SPID column 4. Run 5. Wait for few secs, Stop the trace You see the RPC:Co...more >>

In sql server 2000. I have a temp table in a stored procedure, I want to read through the temp table and examine each column in each of the rows of that table. How would I do that. I also need to determine if the table is empty or not before I start reading it. Thanks in Advance, Laurence...more >>

hi guys I am having problems running a stored procedure where i am using two input parameters my stored procedure is as follows ALTER procedure [dbo].[enterdhbnameDhbService] ( @dhb_service char, @dhbname char ) as SELECT dbo.DHBMappingTable.[DHB Name], dbo.Agency.DHB_serv...more >>

How can I combine the following two queries into one answer? SELECT COUNT(*) AS count, SUM(total) AS total, SUM(dbcr_amt) AS dbcr, SUM(dealer_paid) AS dlrpaid FROM ALLTRANS_BASE WHERE (dealer_id = 16228) AND (post_period = 200610) AND (adjtype = 'NEWBUS' OR ...more >>

I have a component that builds and caches SqlCommands and parameters for sprocs using commandbuilder. However, when I try to use this against a SQLCLR sproc, it says it can't find the procedure. If I script an execute for the sproc, it gives a really interesting command in management studio wi...more >>

I have a query that determines a day number (e.g. day #32 is February 1) and two-digit year number from a product serial number string. It can always be assumed that the year is in the 21st century. I need to convert the day number and year number into a mm/dd/yyyy format for display. For...more >>

Hello, I think that I've seen a function to do this but I can't remember where. Is there a built in t-sql function that converts boolean values true/false to there respective yes/no. Thanks, Chieko...more >>

Hi I've two servers which I'm told are built using identical build instructions, both running SQL2005 under Win2003 Server, both with seemingly identical databases installed. So for all intents and purposes they should be mirrors of each other, but built individually. In both server databas...more >>

I'm using VB 2005 with SQL Server 2005. I use a SqlCommand along with the ExecuteNonQuery command to backup my database. When I back up the DB to a file in the Program Files/Microsoft SQL Server/ folder, as shown below Dim backupSQL As New SqlCommand("BACKUP DATABASE [Companies DB] TO DIS...more >>

We have a table(Employees) with a unique clustered index on EmpID column. We also have many other tables which are refering(foreign key) to EmpID column on Employees table. Now we want to remove the clustered index from the Employees table EmpID column and add it back to Name column on the s...more >>

Hi. thanks for your time you took to look at my question and any advice. SQL 2005 server. I have an update trigger that has certain logic built in it. It does 6 = joins like this to detect and act on status change (sttID). Normally, = it's one row effected but less often it can be few dozen...more >>

I would really appreciate if you guys can help me on this. Here is a simplified view of my problem. I have 2 tables: Activities and Hours defined as below Table Hours ActivityID int Hours_Monday float Hours_Tuesday float Hours_Wednesday float Hours_Thursday float Hours_Friday float Hours...more >>

Hi All, I keep getting this error message? 'ALTER' is not recognized as an internal or external command, operable program or batch file. NULL I'm running this: DECLARE @Db_Name nvarchar(25) SET @Db_Name = 'MYDATABASE' DECLARE @SQL VARCHAR (100) SET @SQL = 'ALTER DATABASE ' + @Db_N...more >>

My documents are stored in numerous formats. I thus have a many-many relationship between documents and extensions. I store this using 3 tables, 'Documents', 'Extensions', and a linking table 'Docs_Exts'. Now say I want to have a default extension. Which is the better design, and why: -...more >>

I want to create scripts to import data from about 50 text files. I do not want to use the SSIS, I never trusted DTS and my boss has told me that I MUST create scripts to do this. So, I have a linked server set up on a directory of .txt files. The tables are already created. My query looks ...more >>

What is the correct way to delete a series of records in one transaction? Should I use version 1 or version 2? ALTER procedure [dbo].[Member_Login_Delete] (@LoginID int) -- version 1 AS BEGIN IF @@TRANCOUNT=0 BEGIN TRANSACTION DELETE FROM DealFlow_Redirect WHERE LoginID = @LoginI...more >>

Hi, I'm doing some tests on an offline version of our SQL Server 2000 production database. Our backups were allowing the log file to increase instead of removing inactive entries so I'm planning to flush out and shrink the log file before re-establishing a full backup routine which removes inac...more >>

On May 27, 2:42 am, Erland Sommarskog <esq...@sommarskog.se> wrote: > Your choice is between: > > SELECT @sql = 'SELECT .... FROM ' + @server+ 'catalog.schema.tbl' > > and > > SELECT @sql = 'SELECT ... FROMOPENQUERY(' + @server+ ', ' + > '''SELECT ... FROM catalog.sche...more >>

sql server 2005 Is there a query I can run that will tell me if xp_cmdshell is enabled on my sql server 2005 server? ...more >>

I am trying to manually "replicate" a row that for some reason will not merge properly. I disabled the insert identity protection and try to run the insert query. The problem comes in here. When I do a comparison of all fields in that row between table1's column and table2's column it is tell...more >>

Hi, I am trying to run bcp in store procedure but it generate error. My code is: CREATE PROCEDURE test @comp int, AS Exec Master..xp_Cmdshell 'bcp "SELECT companyid, cconnectid,cctimestamp, custitem FROM connect.dbo.[connect] WHERE cconnectid =''+@comp+'' ORDER BY invoicedate" queryo...more >>

I understand that this does not work, and the only way to do it is to make the entire statement dynamic SQL. Sucks, but you do what you have to do. Now, what I'd like someone to shed some light on is _why_ we have to do that? select * from openquery(@ServerName,@SQL) Msg 170, Level 15,...more >>

Hi All, I am trying to check federated solution on MSSQL 2005 farm. For benchmark purpose we have two servers each with single MSSQL2005 instance. On every server there is same database with same schema and same table with different check constraint. The problem that on simple performance che...more >>

Hi, I am trying to run following syntax within SQL Server Exec sp_rename 'TEST ', 'TEST_OLD' Print @@Error Even when the sp_rename statetment failes @@Error prints 0 I have tried to get the error message into a table- but that is unsuccessful also. create table Temp1 (LogInfo...more >>

Hi, I am new at SQL-SERVER. I wonder if there is a similar way to implement the following cursor with parameter in SQL-SERVER? Is the dynamically built query the only approach in SQL-SERVER to implement it? ------------------------ Cursor with parameter for Oracle -- declare cursor ...more >>

I have an insert button on my page, which inserts a row in a table with 6 columns (6 formfields) everytime I press it. What I want to know is that everytime I insert a row, is there a way I could make sure that the formfields have ordered bookmarks? In the 6th column formfield I need to do a c...more >>

Hi, I wanto to restore a file bak of my server1 to my server2. How can I do this with an automation? Help? I know how schedule the backup in my server1 but to restore on my server2? Thanks ...more >>

I've worked on and off for a few days on this problem and before I give up, I wanted to post it here and see if I could get help or suggestion of how to best go about this. I'm working on a query in Query Analyzer that will be included in a Crystal Report to display a table and bar chart. Mos...more >>

Does anyone have a SQL Server 2005 function that strips all numbers from the left of an address field. I tried using an InStr function looking for the first space, but I get an error saying InStr is not a valid function. I'm using SQL Server 2005 Express. Thanks...more >>

Hi, I want to extract data from SQL Server200 to Excel using T-SQL (query). There are 1000's of records. Performance is main thing. I am using ASP at front end to get reports in excel format. Can anyone please guide which is best way to get data from SQLServer200 to Excel. Sample code will be v...more >>

Hello - I am very new to the SQL world and wondered if someone could point me in the right direction. I have a SQL table called SALES; the output data looks like this: STORE UPC SOLD A 12345 100 B 12345 50 C 12345 75 A 34567 50 B 34567 40 A 67890 25 C 98765 35 Can I recreate a ne...more >>

Hi, Is it possible to run the DELETE action on a table without logging? Something like "DELETE FROM Table WITH NO_LOG" I'm currently working with a nightly process which deletes all records in several very large tables, and the transaction log is filling up a lot more quickly than it ne...more >>

Does anyone have a 'TESTED' ISO (UK) WeekNumber( Date ) function ? I have tried this one but it does not work properly. For example, it returns the 8th Jane 2006 as week 2, but it is week 1 ( Sunday) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp ...more >>

Hello, I created one calculated index which is : Alpha_code+cast(Num_code as varchar(10)) When I created it I had to put in the script before the CREATE INDEX the option SET ARITHABORT ON. But the problem is that, when I try to make an an insert I have an error message telling me that th...more >>

Hi, I have a database that its data file is almost 15GB. When I go to shrink file page (EM), the page displays that the minimum file size can be 8GB but after shrink, the size doesn't change at all! I tried both EM and DBCC SHRINKFILE and the result is the same(the process takes 10 minutes)! ...more >>

With T-SQL, I would like to drop an index of which I do not know its name, but I know the column(s) over which it is defined. Up to now I did not find out any method to do so. I would prefer having a solution with information schema views (with a stored procedure I would not know how to "captu...more >>

hello, i have a application that was initially using ms-access for data storage but later we decided to switch to sql 2005. now there i do have a query that i want in sql 2005 too. i did try but failed to do so. the query looks like, SELECT AP_mstTarget.szTargetID, AP_mstTarget.szT...more >>