IF I have two index Index1with column iCutomerID, sFName and Index2 with iCutomerID, sFName, sEName IM I right that I can delete Index1 none is clustred /Kenneth ...more >>

We have a requirement to restore the relational database from a .bak file. This .bak file can contain more than one data file and log file. How do we programatically determine the logical files within this bak file and restore accordingly. I tried using 'Restore Filelistonly', but then here ...more >>

Hi all, I've been trying to register my local sql server in enterprise manager without any success - I can see my remote sql server, but it just wont connect to the local one on the laptop - I think I may have only install "client tools" in the past because I thought I'd only want to connec...more >>

Is there a way to debug SQL Server 2005 stored procedures from within BI Studio? I don't see any way to do it, but may be missing something. I can do it from a VS 2005, but some systems don't have that installed. Thanks, Tom...more >>

Hi, Using SQL Server 2000 Qn 1. Is the record size smaller if a field that of type int is Null for that record? Have I saved 4 bytes? Does it make any difference for any other SQL data type? Qn 2. Can you have FK's on fields that are nullable? If you can, is it considered poor practice...more >>

I've seen in several examples where cursors are used particularly on this board that people use While @@FETCH_STATUS <> -1 BEGIN IF @@FETCH_STATUS <> -2 BEGIN ... END END On most of the Microsoft BOL examples I see that they seem to always use... WHILE @@FETCH_STATUS = 0 BE...more >>

I want to import an excel spreadsheet 2000 into a sql server 2000 database. When I import the columns from the excel spreadsheet, I am not always certain what columns, and how many columns are included in each excel spreadsheet in advance. Thus is there a copy to automatically import all t...more >>

I am working to customize some forum software. So I have a table of topics, and I added a second table (read topics) that tracks whenever a user reads a topic. Currently there is a query that grabs records from the topics table based on a date range, so users can see topics posted since x days...more >>

I find PLENTY of examples of User Defined functions. However, I struggle to see how/where to SAVE this function! I can go under the database, Programmability, Functions, Scalar Functions, and create a scalar function. However, when I click SAVE I get prompted to save the ..sql file to disk. If I...more >>

I find PLENTY of examples of User Defined functions. However, I struggle to see how/where to SAVE this function! I can go under the database, Programmability, Functions, Scalar Functions, and create a scalar function. However, when I click SAVE I get prompted to save the ..sql file to disk. If I...more >>

I want to use a trigger to enforce referential integrity on a table where a FK is not possible (PK of the referencing table is composed and one of its columns is absent in the referenced table). The trigger passes the syntax check but fails to be saved with the following msg: "Error 21001:[SQL...more >>

Is there a way using t-sql to get the ID of an inserted row following the insert? Example: @RowID as int @RowID = INSERT INTO Contacts (Person,Phone_Number) Values('John Smith','123-456-7890') SELECT * FROM Contacts WHERE ID = @RowID Thanks, ROn ...more >>

I nee to print labels for employees. Each lable has Employee ID, Employee Name, Department Name, and Office Code. The label sheet has 4 labels per row. I am using SQL reproting service to generate PDF file and print it on label sheet. Here is dataset I have: EmployeeID EmployeeName D...more >>

Hello everyone, I need help with two SQL statements The first is: I have a table called tbSitters which contains two columns named bsLastName and bsAboutMe. I want to be able to find records where the value stored in bsLastName can be found in bsAboutMe for a single row (not all records) ...more >>

I have looked around and found the equivalent for IIF (Access) to be a SELECT CASE in SQL. I have tried this with no success. I am also looking for the equivalent of MAX and have had no luck. The portion of the string I am trying to SQL'ize is: SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])...more >>

Hi Freinds, SQL 2000 How can I give a user SQL profiler access without making him SA Thanks in advance, Pat ...more >>

I am having a strange problem in a stored procedure using a table variable. Here's a snip of it: DECLARE @tblVar TABLE ( ordnum int, ordDate datetime, ordVal money ) INSERT INTO @tblVar SELECT order_num, creationdate, 0 FROM dbo.Orders WHERE (creationdate > '1/1/2005') ...more >>

Hi, I am working on Time Off schedule. Here is how SQL Server table looks like: ID BeginDate EndDate ======================= 1 01/03/2005 01/05/2005 I have a webpage in VB.NET that employees are using to add vacation dates for themselves, which are then inserted into the above tab...more >>

Hi, I disabled all constraints on a database before using DTS by running the following command: exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' When comparing the source tables and the destination tables after the DTS I found that some DEFAULT constraints were not disabled. ...more >>

Hi, I am new at T-SQL. I need to access a table with the field inv_dt. I need the sum of the invoices where it takes only the sum of the current year invoices. When 2006 arrives I only want the sum of 2006 invoices without having to rewrite the code for each year that rolls around. Any ...more >>

Newbie question: What would be the best way to implement the following: I have a database in production with an amount field as an int. I need to to encrypt the amount using a simple encryption method of varchars and placing that value in another field (varchar(100)) and insert a zero valu...more >>

In FIGURE 2, I'm successfully building a dynamic statement on Northwind. However, in FIGURE 1, I'm trying to add a WHERE subquery that should return FIGURE 2's results but only for the last 10 days. I'm getting a strange error in QA. Can someone help me with my syntax in FIGURE 1 near the ERRO...more >>

Hi I have enabled manual log shipping on a server, Every night the database is backed up on Server A and restored on server B . The restoration happens with a stored procedure created on Server B, executed from server A. the command is exec serverB.master.dbo.restore_db After this com...more >>

Hi! I'm using SQL Server 7.0 and it is a multiuser application. My problem is that sometimes during parallel calls to a stored procedure it produces wrong results. It takes between 5 to 20 sec to execute the stored procedure. To be able to know why the result sometimes is wrong I will log som...more >>

Is this function as well as alter index reorganazi or rebuild backward compatible to sql2k? Thanks, KOni....more >>

I installed sqlserver 2005 developer edition and running vs2005 developer edition. Visual Studio Project templates are not installed by default. How can i get the project templates installed with out going through set up? -- Thanks, <Ram/>...more >>

I have a sequence of 3 operations, and its repeated many times by an aplication (timer loop) 1) Open a transaction 2) Execute 1 (ONE) insert in a "simple" table. This table has only an auto-increment identificator and some fields numbers and chars. 3) Comitt the transaction This proccess...more >>

Hi everyone, My website database (msSQL 2000) has a number of tables of which two are named tbSitters and tbPcodes. Both tables have a common column named bsID. (tbSitters.bsID is a primary key and tbPcodes.bsID is a foreign key) I need to find out which records in the tbSitters.bsID column...more >>

Is there a boolean data type in SQL? I'm trying to bind a number of check boxes and radio buttons in a .net app to some columns in a SQL table and I don't see any boolean data type. What should I do if I want to assign a data type "boolean" to a table's column in SQL? -- TS...more >>

Hello, I do alot of searching by date alone and i currently have a column that will be converted to a date/time field. Is there any advantage to separating the date and time into 2 columns or would this be a waste of resources? Thanks for the input. ben...more >>

We can use sp_setapprole to enable application role. How to disable the app role?...more >>

Hello, I am trying to uninstall Sql Server 2005 Beta in order to upgrade to the Sql Server 2005 Enterprise Edition but without success. Here is what I have already done: I had VS 2005 2.0 Beta and Sql Server 2005 Beta installed. I wanted to upgrade to the Professional Editions so I uninst...more >>

Hi all, I have included All sample data, just copy and paste in Query Analizer I would like to modify the query below to return all that had a prior "pretest" 30 days prior or equal to the "test". so the return result that I would like would look like this: 1 A 1997/12/08 pretest 1 A 19...more >>

Hello everyone, I have a table that is setup to record a page of web form elements. The form elements are dynamically created. Each page contains x number of questions. (x depends on many different things There are 4 pages. Since we never know how many form elements are on the page, the DB ...more >>

I need to combine two fields in a single column in a query like Stock Number and Product in a way it will look like this: Stock NumberA - ProductA Stock NumberB - ProductA Stock NumberC - ProductA .....and so on What I want to achieve here is the position of the "-" seperate the Sto...more >>

I am struggling a bit with Service Broker. Basically I am trying to setup a scenerio where I perform an insert(update, delete)on one server and the insert is sent as XML to a second server where the same insert (update or delete) will take place. All of the samples are pretty simple and ...more >>

Hello, I am using ODBC to work with Microsoft 2005 SQL server express from a c++ program, when trying to run a select sentence I am getting the following error: "Connection is busy with results from another statement" I have searched the net and from what I understand it is not possible ...more >>

Hi All, First the term explanation so you know what I am talking about: 1. HL7 - file that contains patient information; tab delimited. 2. CloverLeaf - interface engine that translates HL7 files into = different formats. For those of you who have some experience in single- and multi- = thr...more >>

Hi, I am trying to build a stored procedure that Querys a table, and returns the resultset as XML so I can send it as a Service Broker Message. This is my non working code: .... DECLARE @QueryResults varchar(max) OUTPUT @QueryResults = SELECT * FROM HumanResources.Employee FOR XML A...more >>

I use to know how to do this in oracle, but cant remember... how do i set up a constraint on a column in a table so it can say only be "START","STOP","INPROG" only as the text that column can have? thanks! ...more >>

I have a view (View_A) that retrieves data from another view (view_B). There are several Union All statements in View_A that retrieves/groups various data elements together; the all use the same FROM View_B with differences in the WHERE When i try to open View_A, i get the following: -[Mi...more >>

can I get someones opinion of full backups? I currently have full backups 3 days a week and t-logs every hour. I was thinking about switching to full bakcups every day. Is there a problem with this strategy?...more >>

Hello, I need a database to model various work shift for different companies. I don't want to set each day, but rather know that company A works 10 hour days 4 days a week and Company B does 7.5 hour days 5 days a week and 1 4 hour day on saturday.... Frankly I am perplexed how to go abou...more >>

I'm wondering the following: If I have a table with records that don't have a timestamp column, is it possible to get to know when they were inserted? Thanks in advance, Stijn Verre^t....more >>

Hello, I am using the following code as part of a routine for an accounting procedure - the problem is that I originally was using a simple Select statement and then setting the variables to those amounts which did not return errors but once I put more than one set of related records in the...more >>

Hi everyone, If anyone can help me with the following, I would be very greatful. I want to create a pretty basic function under sql server 2005's runtime. I've made the assembly and attached it. And when I do something totally simple like return a simple string it works fine. However when ...more >>

I found an "undocumented" stored procedure that returns the size of the tempdb called sp_tempdbspace - this stored procedure queries two different tables to compute the allocated size of tempdb as well as figuring out how much of that size is being used. My question is about the queries u...more >>

Hi, I have a problem with retriving data from one table. Table looks like this: ID CONTACT EMPLOYEE_ID And now, one employee can how more than one contact, like this ID CONTACT EMPLOYEE_ID 15 e-mail 553 16 phone 553 ...and so on. How can I retrive this contacts, for same employ...more >>

hello, i want to return a nr of records depending on the parameter @nrRecords I tryed to execute this in the queryanalyser - but i get a error, what is wrong? Declare @nrRecords int SET @nrRecords=10 SELECT TOP @p companyName FROM myTable the error is Server: Msg 17...more >>

Hi all of you who've had a chance to play with 2005... I have two quick questions with triggers in 2005. 1. Will there be available true "BEFORE" triggers as opposed to the "INSTEAD OF" triggers? 2. Will triggers now be fire-able in a "FOR EACH ROW" style as opposed to bulk triggers? ...more >>

A constraint UNIQUE (col1, col2) _partially_ protects from inserting duplicate values for col1 and col2. It does not detect whether col1 and col2 values been inserting in reverse order. For example, val1/val2 - one record and val2/val1 - another record. Am I right? If yes, how to protect from t...more >>

I have a bit of code that checks through all orders and looks for all orderlines where the status is either closed, completed or cancelled, but where at least one is cancelled, and it sets the status of the order to closed. I also have an equivalent script which sets the order status to com...more >>

Table and sample data is id date name value1 1 15-11-05 a 5 2 15-11-05 b 2 3 16-11-05 a 3 4 17-11-05 a 7 5 18-11-05 a 5 6 18-11-05 b 1 If date parameter ...more >>

Howdy, My company is setting up a new dedicated hosting server and we're debating using the new SQL 2005 verse the MSDE. I'm not familiar with the new version, so I have one big question:: Outside of the connection string, will any syntax in my existing ASP.NET script/stored procs have to chan...more >>

Hi, I'm trying to left outer join two tables onto one. For some reason the table is losing some of the left table data (vw_standard_costing_items). Please can you tell me why, and a fix please? I'm left outer joining vw_standard_costing_budgeted_amount and vw_standard_costing_pur...more >>

Hi. I mean left outer join must return all rows in the left table. Why? SELECT DISTINCT a.cliente, a.Expositor FROM prm_Exp_x_PV a LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv LEFT OUTER JOIN prompaneles d on c.panel = d.codigo WHERE ...more >>

i have a sp in which i am using multiple queries to build a single table. Now i want to use the result of this sp in other queries or as a source of a cursor.. How can i achive that ? will converting this sp to UDF is a good option ? ...more >>