Hi, I need a bit of help figuring out a sql statement. I have one table filled with static data. (TableEquip) Code Desc ----- ------ T37 Tank T42 Heavy Tank Then I have data that is pulled hourly (TableUsers) User Equip ----- --------- 001 02\T37\W 002 ...more >>

We currently setup an environment using the Server Alias from the SQL Server Client Network Utility. We are using these connections (which are different servers than the one we configured the alias on) as link servers. When I attempt to run a Store Procedure with BEGIN TRANS trying to get da...more >>

I have a VB.Net app and a SQL Server 2005 database. Users must login to use the application, and I have an Employee table to store their details. However, I use a common user ID to connect to the database (for reasons I won't go into here). My problem is, some of my triggers need to know wh...more >>

I mentioned in an earlier post that I am seeing a large difference in speed between two closely related queries. I'm also seeing a large difference between the production server and my laptop. In general, while the server is somewhat faster than my laptop, it is not that much faster. I'm sure th...more >>

Hello, If I have data such as: col1 col2 col3 col4(date) 1 2 3 01/11/2005 1 2 3 02/11/2005 1 2 3 03/11/2005 1 1 2 04/11/2005 1 1 2 05/11/2005 1 1 2 06/11/2005 How can I select so that the results are ...more >>

Hi, I have the following scenario. I have the following table CREATE TABLE iTransRow ( idLoc INTEGER NOT NULL, idSeq INTEGER NOT NULL, idLine INTEGER NOT NULL, Item INTEGER NULL, ItemExt ...more >>

How can I get the name of the current database within a TSQL query (SQL 2000)? There doesn't seem to be an @@variable for it. ...more >>

I'm hoping someone here can help as I haven't been able to tackle this problem efficiently. I'll try to break the problem down to it's simplest components. I have a MonthYear table, a Performance table, and a Company table. The Performance table has a CompanyKey and for a given month (MonthYe...more >>

Hi all, I am trying to import data from dbf files into a sql view using DTS. I see that only buck copy allows me to import data into a view. I can't use this because I need to choose which rows and columns I need to import when I am importing them. What is the easiest way to import data into ...more >>

Please forgive my wording of this request for help. I have a stored procedure that passes in one value (taskid). At the time you could have only one task for this application, but now it allows you to have many tasks. I am trying to find out if there is a way to pass a series of input tas...more >>

Hi, In my sp I have a dynamic sql as set @qry = 'select v1, v2 from ' + @table + ' where ...' in this sp how can I user v1? I need to check the value of v1. Thanks...more >>

I cannot get a Trigger to work on a schema table set that is not dbo. For example with AdventureWorks if I used: <Microsoft.SqlServer.Server.SqlTrigger(Name:="utrigPersonas", Target:="Person.Address", Event:="FOR UPDATE")> When I try to deploy this it returns the error: Cannot find th...more >>

Hi, In sp can I select a value into a variable? like "select myValue into @myVar", then later I need to compare this value with something else. If can't, I need to a vlaue from prevoius select statement in sp, how can I do it? Thanks...more >>

Hello, Im outputting a sql table to a text file. One of the columns in this table is an nvarchar(1200) but the result in the flat file is truncated to 254 chars.....how do ensure that it outputs all chars up to 1200? thx ...more >>

Hello, I have a batch file in the operating system, which runs an osql command that create a stored procedure, a view and a function on a database. It is with -E trust connect. It works when I just run it on windows. However, if I run in as a SQL Job by using the CmdExec, it could not ...more >>

Hello folks, I am currently working on a project plan to move from SQL7/NT4 to SQL2005/Win2003 and I am putting together a documentation for our developers regarding new features and deprecated features in 2005. Can you point me to a detailed ducumentation on the programming side? Thanks ...more >>

I'm trying to improve the quality of life of this SP CREATE Procedure p_generatedata @StartDate smalldatetime, @EndDate smalldatetime as Set Nocount on delete ttemp_data Insert into ttemp_data SELECT appeal_codes, appeal_type, appeal_code, SUM(total_yes) AS total_yes, SUM(Pledge_Amou...more >>

Hello, SQL manual generally suggests following code: IF (some_check_within_insert_update_delete_trigger) BEGIN RAISERROR (....) ROLLBACK TRANSACTON END My question is if anyody knows why RAISERROR (not ROLLBACK TRANSACTION) goes first ? Is there a particlular reason for that...more >>

I have a field that I need to query for true and false. The field is a bit. Is True 0 or 1. I can't open Books Online and the download instructions based on my version SQL 2000, are not clear and I don't know what detailed version I have nor where to find it. -- MichaelM...more >>

Hi, I am trying to create a trigger to update a datetime field when a user logs in to their account. Is there a way to create a trigger that updates a field when the table is accessed? The only other possible way I can think of to accomplish this would be to write code that updates a field ...more >>

Hi All, I have been given a task to display the schema of a target db (MS SQL) and display its relationship visually on an ASPX Page. How should I proceed with it Any pointers, tips would be highly appreciated. Thanks a lot ...more >>

Hello I want to output a table to file. In a DTS package I can use the text file (destination), but my problem is the delimiter. I only have a couple of options from the drop down list(no custom option). how do I use a custom delimiter. I want to use a "`". thanks ...more >>

Hello, I have a T-SQL query similar to the following that returns fewer number of rows when I remove columns from the select clause. Any idea why this is? I'm not changing the JOIN clauses when I do this, just the number of columns, for instance I change ------------------------------------...more >>

The company I work for is a managed healthcare organization. One particular metric we use for determining compensation for a medical practice in our network is tracking the number of times a particular practice sees a particular patient (member of the healthcare network). Practices are awarded o...more >>

in sql 2005 sp1, when I try to run a package from an asp.net page the following statement generate error: myPackage = integrationServices.LoadFromDtsServer(packagePath, "myServerName", Nothing) error: EXECUTE permission denied on object 'sp_dts_getpackage', database 'msdb', schema 'd...more >>

MSSQl 2000 When creating a select statement with joins... does it matter where you = place additional where-clause criteria. Considering the two examples below, is it more efficient to place = additional filtering criteria within the join section.? Does it weed out = extra rows before joini...more >>

Hey All, Thought I'd take a break from Scalar Functions for a while and give Omnibuzz a break from answering them. I've searched thru the BOL, and this forum, as well as the help files in SQL2005. I've gotten to the point where I am exporting the data correctly..however I am looking f...more >>

Hi I am trying to insert records from tbl2 into tbl1 in a sorted order but its not working. insert into tbl1 select * from tbl2order by field1 The records are not being added in a sorted order.can anyone please explain? Thanks for your help ...more >>

Hi, I'd like to select a field of my table and also a truncated version of that field: SELECT Field1, Field1_truncated FROM myTable This field is of type nvarchar. Field1 should contain the entire string valule whereas Field1_truncated should only contain the first 10 characters of the o...more >>

Hi, In my table I have historical data, Pls see sample. CustId Start$Amt End$Amt SnapShotDt 1 1000 900 2005-01-01 1 890 800 2005-02-01 Now when I am trying to read data for SnapShotDt ='2005-02-01' , I want to replace Start$Amt=8...more >>

I am using SQL Server 2000 v8 Is there a way to add a counter to SELECT statement without writing to tables Ultimately I am trying to get a dataset from a SQL statement split into blocks of 80000 rows ...more >>

CREATE PROCEDURE usp_Test ( @LoginName VARCHAR(50) = NULL ) AS IF NOT EXISTS (SELECT * FROM tblVendors_EP WHERE Login_Name = @LoginName) RETURN -1 ELSE RETURN -2 GO I have the above usp for the sake of simplicity. When I call EXEC usp_Test @LoginName = NULL (or EXEC usp_Test), ...more >>

Hello - I am trying to set up a job through sql server agent. The job sets the osqluser and osqlpassword, then calls a bunch of batch files. When I run it in dos, it works great, but in the sql job, it does nothing. Does the syntax need to be different or something?? SET uname=Uname SET...more >>

If rumours are true that .adp is likely to be phased out in future MS Access versions, what front-end with similar functionality (forms, reports, coding) would you suggest for development and use with MS SQL Server?...more >>

Hello, I can create a view on a table from a named linked server database. select * from server1.RemoteDB.dbo.Table1 But I am having a problem creating a view on a table from a non-named linked server that is just using the IP address of the server. Example: Select * from [56.19.175...more >>

Hi all, I am trying to convert the string value of '12.01.50' to a propert date value of 12/01/1950, I have tried various things but cant seem to get it into the right format although I can convert it to a date type. Can anyone help? Thanks Phil...more >>

I have a stored procedure which builds a memory table by going through a Deals list, selecting each relevant type. For example, INSERT INTO @temptable SELECT [column_list] FROM Deals WHERE Deal_Type = 1 AND Cust_ID = @Cust_ID It does this about five times, choosing the various different de...more >>

I am trying to create a database with none of the user-defined tables contained in the model database. All I need, essentially, is an empty database. Thanks ...more >>

I would like to dump each of the stored procedures in one database to a separate file. Programmatically would be great (e.g., start with SELECT * FROM sysobjects WHERE xtype='P'), so that I can have greater flexibility. My last resort is to use some tool already written by somebody else tha...more >>

I have a need to change the columns in a table to rows with values /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM ******/ if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TA...more >>

hi i am getting that message to update field value into table Msg 16933, Level 16, State 1, Procedure RmDupValWSWCode, Line 39 The cursor does not include the table being modified or the table is not updatable through the cursor. I have two databases with same tables, in one database it wor...more >>

Hello I have a very severe problem facing me in Microsoft SQL Server 2000. I have a stored procedure, complicated select, but no temp tables, no cursors, only nested selects. The problem is that when running this stored procedure, it may take 30 minutes, sometimes 3 hours. but when taking t...more >>

Hi folks, a little of bit of advice would be greatly appreciated. I've got an MCSD.net with a 70-229 SQL Server elective. I don't really wish to be an MCDBA (it would be great, but would require too much time right now) and I'm reading about the MCTS, this appears to be one exam only. ...more >>

Hallo, Can someone tell me the difference between pcd_mn..nslAfdeling and pcd_mn.dbo.nslAfdeling? I know that in this case pcd_mn references the database on the server and nslAfdeling a table in that database. I also know that dbo references the owner of the database, but who is '..'? Thn...more >>

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FU_SZ_B_B_S_M_W]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[FU_SZ_B_B_S_M_W] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO SELECT ContactName, CompanyName FROM dbo.Suppliers WHERE CONTAINS(Co...more >>

I have partitioned CustomerProfile related table as 20 partitions. These 20 partitions are placed in 20 different file groups. The partitions are designed to support approximately 100M records in the table. The deployment person says that in production environment I will have only 2 drive...more >>

Hi all, Generally i'm helping to answer questions here, but i have strange problem that I haven't seen before, or at least such a difference in performance of a query. Scenario: I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION + SP4. I have a datawarehouse tha...more >>

Hi, How can I get the size of some records from the table ? One option I can think of is copy those some records to a temp. table and then calculate the table size, but this is not feasible for me. Any other way. TIA Prasad ...more >>

Hi there, Can anyone tell me if its possible to perform a join on two tables that are resident in different databases - both databases are running on the same machine and under the same SQL Server 2005 instance. If anyone could tell me roughly how to do this then I'd be very greatful. If...more >>

Im trying to schedule a dts package to import a database table from a csv file. In enterprise manager when i attempt to create the dts process, im not sure what datasource should i be using to connect to my csv file that resides on the web server? where do i specify me server to connect ...more >>