Hi, I have one temporary table(mytmptable) which have four fixed columns.Now i have another table tbl_CostCenterGroupName which contains about 50 cost center group names.Now what i want is to add these 50 cost center group names as columns to temporary table mytmptable.That is 50 cost cent...more >>

Hello, I have a sql Script file name : timetracker-add with extension SQL Script File. how to create a data base from this file. It is urgent. Thanks, junior ...more >>

I am trying to upgrade from SQL 2005 Express to full SQL 2005 using the command line option. Also, install a new full SQL 2005 using the command line. I am using the same command line parameters, to install a new full SQL 2005, that I use to successfully install a new SQL 2005 Express, without ...more >>

I want to execute dbcc shrinkdatabase from a stored procedure, but since the command needs to know what database to shrink, how can I tell, from w/in the SP what database the SP is running in? something like @@database (which doesn't exist), or something like that. Thanks. Darin *** Se...more >>

Hi all SQL 2005 gurus, I have an interesting question: In a loop process, is it possible to rollback one transaction if an error occured, but save the error message for this particular instance in a data table (or any where else)? Here is my scenrior: Open out_cursor Fetch Next From out...more >>

Hi, I have a text file contains: Flag Y null Y Y blank blank I want to insert that text file into my table A: TableA has only 1 field called flag How do I default a "N" to the flag field for null or blank and otherwire the value if there is a value. In this case would be a 'Y' af...more >>

Hi, I'm not sure whether this is the correct newsgroup to post in. If not, please tell me! I have two tables tblTIssue and tblTEvent (ddl's below) and am attempting to do a query for each issue (i.e. one row per issue) and the next event for it. Some issues have more than one event and...more >>

Sometimes I run BCP with the user and password switches (-U and -P) and at other times I use -T (Trusted connection). It won't work if I type in my network user ID after the -U, will it? That switch requires a SQL ID, yes?...more >>

What are some good online resources for learning about sql server 2005? I'm interested to read about problems and how they were solved, general articles on specific areas, what people are finding useful and why, case studies. ...more >>

I've looked in Books Online as well as google and have yet to find what I need. Using SQL Server 2000 image columns, I need to be able to update the columns, actually totally replace the value in a given column. Currently, I am not using "Text in Row". I ask a question about this later in thi...more >>

I understand that there are many advantages of stored procedures over ad hoc queries. One of them being speed. I just recently started using the Execution Plan so that I could improve my queries. I was analyzing an ad hoc query and reduced the subtree cost from ..765 to .126. I was quit...more >>

Is it possible to format a list of integer numbers converted to char(3) 1, 2, 3 so that they display as 001, 002, 003 The t-sql function CONVERT() does not seem to have this capability, or maybe I am missing something. Thanks very much, John...more >>

Hi All, After rename my sql server box using: EXEC sp_dropserver '<old_name>' go EXEC sp_addserver '<new_name>' I start getting some weir problems. I just check the values and I got this. select @@servername ---> MyDomainSQL select SERVERPROPERTY('machinename') ---> MYDOMAINSQL select...more >>

Is it possible to store the output of getdate() in a parameter? Neither of these work: sp_delete_backuphistory getdate() declare @now datetime set @now = getdate() sp_delete_backuphistory @now Thanks ...more >>

I want to do a bitwise OR, AND, etc. for two binary fields. It gives an error: Msg 402, Level 16, State 1, Line 1 The data types binary and binary are incompatible in the boolean OR operator. If I convert any one of the operands to BigInt it works OK -- that will be fine for up to binary...more >>

I am having problems retreiving text using syscomments with SQL Server 2000.( Everything works fine with 2005.) Below is a sample procedure that gives problem: CREATE PROCEDURE CustomerDistribution @cCurrency varchar(20) AS SELECT risk.limit * dbo.ccv(trancurrency.accrate, trancurrency.b...more >>

We have 1 process that is adding new records to a table (Signal table) every few seconds, 24 hrs a day. To contain the size of the table, we run an archiving stored procedure (from another process) every 5 minutes. This procedure removes the oldest records (usually several months old) from ...more >>

I am finishing work on a schema change that involves adding in some indexed views and changing the join structure of several tables. Everything works as expected when I test things inside SSMS. However, there are a few Procs that when I try to execute them via ADO.NET, return the error: INS...more >>

Hi, I have the following database tables: Booking (BookingId, CustomerName) Agent(AgentId, AgentName) BookingAgent(BookingAgentId, BookingId, AgentId) Sample data is as follow: Booking (1, 'Robert') Agent (1,'Agent A') (2, 'Agent B') BookingAgent (1,1,1) (2,1,2) I need to create a...more >>

I want to assign a default value if the results come back with nothing. For example: SELECT ISNULL(sku,0) AS SKU, ISNULL( weight,0) AS Weight FROM dbo.sku_info WHERE part_let = 'a' and model = 'SAN2300Green' I am obviously not doing something right because it isnt working. Suggestions? ...more >>

Hi All, I have a table like this (it is sorted by StartTime): Action StartTime ------------------------------------------------------------ Running DateTime1 stopped DateTime2 Running ...more >>

I am a newbie and I would like to know the difference between the two: select count(*) from tableA select count(1) from tableA select count(10) from tableA Thanks....more >>

Background: I want to load a table from a grouped select on another table with insert/select. The destination has several bit fields that correspond to boolean expressions relating to data in the source table. Example 1: destination field 'Special' is a bit that corresponds to source field '...more >>

Hello, I have a stocked procedure in my db "master" called "sp_send_cdosysmail" (witch I found in the microsoft KB). I have another db (aido) and I want this one to use the stocked procedure after a trigger fired. here's the idea : db : aido table : error trigger : for in...more >>

I would like to write a tool to read log file. Can anyone help me with the format? I know some software can do this. That means some body must know the format. Appreciate any suggestion. Lewis ...more >>

Hi All, Is there any way to incorporate a user input variable into a Stored Procedure? For example, I would like to create a delete statement for a specific date, which should be entered by a user in a some kind of a message box at the time of execution. Thanks, Pasha...more >>

I have to a update a table. Query is below: update table1 set field1 = 1 where table1.userid = (select userid from table3 where table3 .field1 = 'a') I know this query is wrong, but how can I repeat this 350 times for all 350 users? What is the best way to do something like this? ...more >>

Hello I am using SQL Server 2005. I have Dettached my database . I have lost my LDF File. Can any Please give ur valuable suggestion to attach my database . Thanks in Advance. -- With regards, Rama ...more >>

Hi all, I've looked everywhere to find a solution and wanted to know if it at all possible declare @strSQL varchar(100) set @strSQL = 'select count(*) as ClientCount from clients where ID = ' + str(ID) exec (@strSQL) how can I access the ClientCount within the stored procedure? or how...more >>

I have a query that some very kind souls helped me get working. They query returns the correct results, however the results are not formatting the way I want them to. The query is below, and it is displaying individual results for every user like this(sorry the columns don't line up): Uname...more >>

I'm trying to build up a running total column of Status (StatusTotal in the example below) however I cannot figure out how to do this. I have tried a (SELECT SUM(Status) FROM @SalesStats) as a subquery within the select however this returns null Any help would be greatly apprecaited tha...more >>

I am using ADO with Delphi to access a large SQL Server Desktop (for testing) table. READING the table is very fast at all times; however, the first (and only the first) UPDATE takes up to ten minutes! All subsequent UPDATEs are nearly instantaneous. I found another message thread that ment...more >>

Hi, I am using SQL Server 2000. I have trigger which sends out email (uses SQL EMAIL) when a record is inserted and certain columns are updated. It has stopped working since last Friday, I have recreated MAPI profile and tested it succesfully. I can run the exec master.dbo. xp_sendmail...more >>

I have 2 Tables (tblcontacs and tbladdress joined by Addressid) tblcontact has some Null addressID's. I want to insert Address record in the tbladdress tables With a valid(Identity) addressid and everything else NULL and then update the corresponding tblcontact with the new Addressid from tbla...more >>

I want to fetch first 'n' characters from a table. The condition which I am stuck is the words should not get broken. i.e., "This is a sample string". Here if I want to extract 6 characters alone ... then it would be "This i". This doesn't make any sense to me. So if we find that a word is ...more >>

I have few questions 1- whats ways we can monitor SQL performance ? is there any scripts or tools like profiler...more >>

Did this syntax: SELECT RTRIM(substring(Description,1,charindex('^',description)- 1)) from Descriptions got this error: Invalid length parameter passed to SUBSTRING function. If I remove " -1 " it works but I need to remove the ^ from the query. Any ideas?...more >>

Hi, We are currently being audited for SOX compliance. The auditor wants us to create log that captures all the activities from the DBA in the SA role. Is there an easy way to generate logs for this audit that can be written to the system event logs or flat file? We can cause any performance...more >>

Hi, I am trying to write a detailed report of all my users and roles permission for a database. For example, the result would like this..(it would be nice if the code works on SQL 2K5 too) -- in here, I am looking for the user and its permission to tables,views,etc.. ObjectName UserNam...more >>

I have 2 related tables, tbl_pipe_deals and tbl_pipe_rentroll. tbl_pipe_deal contains the deal_id primary column and tbl_pipe_rentroll contains the following columns: deal_id (foreign key, one to many relationship) row_desc text adjusted float Their are multiple rent roll records for each ...more >>

I'm using VS 2005 since more than one year and have Business Intelligence installed and running. Recently I created my first Integration Services Project and got this error, an "X" red button saying: Microsoft Visual Studio is unable to load this document - Reference set to an object not se...more >>

I am trying to build a property website and i set up the table already. But I dont know how to insert and read the image from the table. I have 2 table, tblPicture and tblProperty. I link my PropertyID to PictureID. Its 1-N I am using Sql server 2005 and ASP.Net 2.0. What is the simples...more >>

Hi, When sorting a numeric field in SQL 2005, I am finding that it is sorting it differently than it was in 2000. i.e. 1, 10, 1000, 1001, 1002, 2, 20, 2000, 2002. Is there a reason for this? Appreciate the help. Damon ...more >>

Is it possible to view the NT Event Logs using a SQL Query? I don't see query running in Profiler when I select NT Event Logs from the Log file viewer. Thanks Andrew...more >>

Hi, We have a simple web application, classic ASP (VBScript) based, which uses XML-explicit stored procedures for most of its data transfer functions. The application has been running a-OK for 2 years+ on a Windows 2000- based web server and SQL Server 2000 database. The app has recently...more >>

Looking for the short keys in SQL 2005 Management Studio. In SQL 2000 I used: Ctrl+U for the Database window and Ctrl+W for the Window Selector all the time. Is there an equivalent in SQL 2005? Thanks - Hutch...more >>

Hi, I'm trying to query across two different servers but get a syntax error at '-'. Thanks for any help. Line 2: Incorrect syntax near '-'. SELECT shipname, lastname, firstname FROM mysrv-s-d..Northwind.dbo.Orders d JOIN mysrv2-s-p..Northwind.dbo.Employees p ON d.employeeid = p.emplo...more >>

Hello, Could someone explain the strange result of the query ? Thx. JN. declare @nb as bigint -- Max = 2^63 - 1 = 9223372036854775807 set @nb = 5 print power(@nb, 24) -- 59604644775390624 print power(@nb, 23)*@nb -- 59604644775390630 ...more >>

Hi all. Im am in a need of a 0/1 field. It is important that this is indexed the best way since it is very usefull in some of my queries. I wonder if Bit fields are just as effektive as other fields regarding the use of indexes. I don't care if some other fields takes up more space in the ...more >>

Hi all. Using SQL2005 I want to right click the table in Management Studio and script the table to the a Query window. It scripts the table, but not the indexes. Is there anyway I can get this out all at once? -- Thanks all Regards Geir...more >>

I have 3 tables. Doc : store the document records, include creation date, last modify date, document name, document type and so on. D759246 : store the document's field value. Alias : store the document's directory id and access control There are 750,000 records in Doc table. There are 750,0...more >>

How can I observe or demonstrate the behaviour of TABLOCKX and HOLDLOCK in a transaction? eg if I were doign a table-wide UDPATE, would it help to acqure this table lock at the start of the transaction, or is the optimizer going to do that for me anyway? Thanks wBob...more >>

I am trying to get the following code to work in a Stored Proc. It works fine in QA when separate by GO statements -- but when executed within an SP, it errors out because the mytable_ID column is not created. GO is only used in certain apps like QA to separate batches. Why doesn't the SP ...more >>

Hi all I have created a stored procedure which is displaying the sum of amount of 2 fiscal years. and displays it . the where clause has 6 crietria. all of them uses in clauses which has month which is been compared. The sp takes 5 minutes to execute. i want to optimise this query how can i ...more >>

Hi, I need some information regarding sql server 2000. Recently we found that one of our sql servers 2000 was crashed. We opened the sql server enterprise manager and tried to open the database, but it was displaying “Suspended mode” What is the route cause for the database to be ...more >>