I have an app that has to store historic stock data. Here are the rough requirements: 1) For data less then two weeks old information is stored with a resolution of 5 minutes. 2) For data older then two weeks information is stored with a resolution of 1 day. 3) Up to 10k stock symbols have t...more >>

dear all, I'm looking for a better version of this stuff. I want to obtain the time of a datetime field: DuracionUlt is a datetime field. SELECT DuracionUlt, Ltrim(Rtrim(cast(datepart(hh,duracionult) as char(2)))) + ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult) as char(2)))) + ...more >>

Hi, I have the following problem. In my system, I'm going to have many tables that use drop-down tables. For example, my 'Persons' table has a column called 'FK_HairColors_ID' which is a FK of the table 'HairColors' If I want to add a new person calld 'John' with black hair, I do this: ...more >>

Is there anyway in SQL Server to calculate a soundex value of a column and place it into another computed column? has anyone tried this before? thanks! ...more >>

Hi All, I'm creating an appplication with some stored procedures. The stored procedure needs to return the @@Identity of the table's new record. The function has no errors and compiles without any warnings. The function does work fine some times (returning value). But most of the calls are fa...more >>

I am having the database having more than 50 tables in that I need to rename a column in all the tables. Is there is way that i can rename the columns in all the tables without changing it one by one? I'm using MS SQL 2K Thanks In advance ...more >>

Hi I have an audit routine that records who did what useing "suser_sname". We are connecting to the database using an ADP and Windows authentication. The problem I'm getting is that regardless of who I log on as, suser_sname keeps returning the name of the currently logged on user on the s...more >>

I am designing a table to contain a field to stores a value that must indicate none, one, or many choices. The form will have a group box with checkboxes (multi-choice) You see the records in this table contain partially pre-configured records. The records serve as "master" data that will late...more >>

OK the process seems to be correct but nothing is being populated in the .csv file. All I am doing is: In activex script have this right: ------------------------------------------- DTSGlobalVariables("sSh").Value = "" DTSGlobalVariables("sSh").Value = DTSGlobalVariables("sSh").Value & "('48...more >>

hi I have a user defined function say myIntDate that returns an int that I want to use as default value in my create table script.Can I do that? e.g Create table customers ( field 1... dateJoined int NOT NULL default dbo.MyIntdate(getdate()) field 3.. ) Thanks. I am getting a sy...more >>

When I try to open the Tuning Advisor or when I try to view the property of the system databases or when I try to start the Server Agent I receive the following error: Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlo...more >>

Hi all, I have a situation similar to below User Task Efficiency A 1 100% B 1 99% C 1 98% … Z 1 50% … A 2 98% D 2 96% F 2 94% … Z 2 50% So on for task codes 3, 4 etc (hundreds of different task types). Some users may have not worked on certain tasks. I would like SQL which ...more >>

I get an error when I try to execute the following code using ADO.Net in VB.Net: conn.open sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'" command=new SqlCommand(sqlString) command.connection = conn command.ExecuteNonQuery() The error occurs when executing the ExecuteNonQuery lin...more >>

Does anyone know of a way to predict that the files in a filegroup do not have enough space left to expand? For example, a file has 3753 MB out of a maxsize of 4096, with a file growth of 10%. Adding 10% to 3753 will exceed the max file size, so there is no room to expand even though technic...more >>

Hi I am trying to make a stored procedure which verifies that a record exists in an other table if not creates it then gets the id of the new record to complete its transaction. here is my current code, it seems not to be able to get the record id even if I have inserted it to the new table. ...more >>

I am relatively new to MS SQL 2000. Can I create a global UDF that can be accessed from within any stored procedure from any database on the server? The function will be doing string parsing and manipulation. thanks......more >>

Is there a way/option/switch (in sql server mgt studio 2005) to display = and work with bit fields as 1s and 0s, instead of the boolean True or = False? I really don't like having to type those extra three characters!=20 And I Hate Change!!...more >>

CREATE ASSEMBLy seems to be able to load assembly from physical path only....more >>

I need to run a Sql query to extract the first part of the First Name without any punctutation from the table "Tabble1". Thanks Example: John F. A. Jimmy Danny C. F. Paul Result: John A Danny C ...more >>

Thank you for your reply. A long while ago I did read some article that mentioned that RETURN has a reserved range of -1 to -99. Is that true? Is it true still for SQL 2005? Does it have any "reserved" values Farmer ...more >>

In my table the rows for one person are displayed as id firstName MName LastName SSN grpNum 1.John Coleman Smith 1111 1 2.John Smith Coleman 1111 1 3.Coleman John Smith ...more >>

Here is the situation... I have a table full of appointments, all of which should have one and only one order in the order table associated with it. I wrote a little app to display each instance where the count of orders for a particular appointment is <>1. With that aggregate function added ...more >>

Does SQL Server allow you to pass in a Database name and use it in a stored procedure? I need to do a join on two tables in two different databases. Here is some code example that I'm trying to accomplish //call in C# to add a parameter to the command object cmd.AddInParameter("@dbname",...more >>

I understand that GetDate() cannot be used within the *body* of a user-defined function because it is non-deterministic. I now have a table-valued function with which I want to use DateAdd and GetDate() as *parameters* -- thus: Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()...more >>

Ok. I have GOT to be missing something. I need to script out drop and create statements for the indexes for all the tables in a database. I SQL Server 7.0, this was a piece of cake. In SQL 2k, it took about twice as many clicks, but was still doable. I can't figure out how to do this in SQL ...more >>

I've got the following table : CREATE TABLE M6_CHECK ( ID_QUOTE VARCHAR(20), ID_ITEM VARCHAR(20), BEGIN_DATE DATETIME, END_DATE DATETIME ) This table contains the following data : insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE) VALUES('Q1','I1','01/01/2001','12...more >>

I have a log file that is 13 times the database size (mdf -2.8gig, ldf - 35gig). I would like to bring it back to the normal size. Every night, I have a complete database backup at 9:00pm, but the ldf file is still 35gig. I tried the command: DBCC SHRINKFILE('CRM_Log',0). However the .ldf f...more >>

Got an interesting issue am trying to design around and would appreciate some advice on an apporach. Giving some table definitions: CUSTOMER ID NAME PURPOSE ADDRESSID ADDRESS ID STREET CITY AUDIT ID USER DATETIME NOTE AUDITHISTORY ID AUDITID OL...more >>

I have a feeling that I'm doomed with this request, but I'm hoping that maybe I just haven't been looking in the right places for an answer. I've got two non-synchronized databases with the same structure. Let's call them A and B. Both databases are having inserts, updates, deletes done to t...more >>

Hi all, I have three tables, two with unique integer PK's and a join table for a 1-M join. PARENT JOIN CHILD +------+ +------+-------+ +------+ + 7 + --> + 7 + 45 + --> + 45 + +------+ +------+-------+...more >>

it's about the Maintance plan clean up task...in sql2k, you can just specify all system databases in maintenance plan and purge files older than a specified time (i.e. 2 days old), but in 2k5, there is a seperate maintenance clean up task, you can't specify the database (i.e. all system dbs) or ...more >>

Hello, I have a need to refresh a table called Employees each day from Active Directory. This table will be used in a training database. When employees terminate, they disappear from AD. So I would like to run a query that would select all of today's employees, and selectively append to the...more >>

Hi, If you set criteria for 3 columns in a query, will placing an index on only one of the columns improve performance at all? And if you do index all 3 columns, does it make any difference to the performance if you place all three columns on one index versus place each column on its own...more >>

I run SELECT * FROM sysobjects WHERE name LIKE '%cl_%' AND xtype = 'u' ....and receive the following result set. Why do these come back? They don't appear to have a 'cl_' in their names. What am I missing? name ------------------- vht_lu_vehicle NoOfClaimsFreq claimval...more >>

Hi I have killed a process running from sql server agent. the process id is 55 and when i give sp_who2 it still shows the process as killed /rollback. when i give the command kill 55 it says 100% rollback complete, but still the process exists. Any suggestions to kill this process comp...more >>

I have two columns of data in SQL Server 2000. For arguements sake lets use the example: Column 1: First_Name Column 2: Last_Name I have a third column called Full_Name. I want to do an update and copy the first and last name values to the full name column with a space in between so: ...more >>

Hello All, I am new to stored procedures and I was wondering if there is any way to accomplish this with one stored procedure. The reason I want to do this is, because this way I will only need to create 1 C# function in my asp.net application that passes two variables. All of your help would...more >>

I have a table 'Y' with years (YearID, YearDescription)(Eg., 1, 2000-01; 2, 2001-02; etc) I have data in another table 'X' (x,x, YearID,x,x,x...). I have data populated this table 'X' with data as shown above. Suppose I have a record such as (x,x,1,x,x,...) pertaining to one employee and anoth...more >>

I have something kind of bizarre going on and I'm not sure how to troubleshoot it, I've put print statements all over in my code to try an find out where it's breaking but it doesn't seem to be breaking at any specific point, there is no error message, processing just stops about the time the ...more >>

Hi, I have some tables in my database that, upon modification, write a file to disk with data. I have create the triggers that handle this, using the sp_makewebtask stored procedure. For these triggers to work, I needed to login from my application with integrated security. This is not a pr...more >>

how would i do this in pure t-sql not in a win app i have column A B and C all integers psuedo sql INSERT INTO TABLE1 (C) values (A+B) A+B = C thanks ...more >>

Dear All, Here is the scenario: We have production and We have a DR Site. The database is getting replicated from production sit to DR site using log shipping. Now the issue is: We have unique transaction id for each transaction. For the transactions being made at DR site, we want t...more >>

Hi, I have an statement select a, b, c from table with returns the the contents of the columns a, b, c. I now would like to add a constant to the result. What does a statement look like to return content of a, b, c, 1 with 1 is my constant? Thanks for any advice in advance Pete ...more >>

Hello, I am a beginner when in comes to SQL Server and recently I started working on a Web Service written in ASP.NET that works with a SQL Server 2005 database. When running the web service from Visual Studio in debug mode everything works file using the following connection string: const...more >>

I am having the database having more than 500 tables in that maximum emptyy tables. Is there is way to identify the tables name that are having only data. Thanks In advance Nizham ...more >>

Hi everyone, I need to monitor a folder to and write in a table the filename and datetime of every file when it is added. Is it possible to do this in sql server or I have to write a program to do this? Thanks.. _Mario_ ...more >>

I have a table with 3 columns, propertyid, valuationdate & valuation I want to return the most recent valuation for each property, sounds simple but it's not !! Thanks ...more >>

how to convert date filed in this format : dd-mmm-yyyy eg: 28-Mar-2006 ...more >>

In sql server 2005 I have a table with two columns with primary key contraint but sp_pkeys return me only the first column when, the same call on the same db on sql server 2000 and 7 return me the two columns. That's happen only on two tables on 200 and I haven't find any reason why. Is it a bu...more >>

Hi, How do I program a Stored Procedure to render the result as on line with all data comma separated. Let's assume I have a list of cars: Ferrari Porsche Maseratti I want my Stored Procedure to give me this result: Ferrari, Porsche, Maseratti TIA...more >>

i've run "dbcc shrinkfile ('employ')" The size of the file does not reduced. ...more >>

1) We have a trigger which applies further changes to the inserted/updated records. Is this fundamentally bad or an acceptable practice? 2) We suspect that one version of such a trigger is causing deadlocks. Interestingly this does not seem to happen if we use a cursor. See two versions bel...more >>

I would like to create a query that repeats a record a number of times based on a int value for that record. For example: Room Number Beds Location A01 2 Northern Wing A02 1 ...more >>

Dear all, I've problem with my stored procedure. When I put raiserror in my stored procedure, the error didn't show in Visual basic, although there's an error exception. Here my stored procedure : CREATE PROCEDURE test @IDNO INT, @Info VARCHAR(20) AS BEGIN begin tran INSERT INTO TCo...more >>

Hi there New to SQL 2000 and i have a question regarding scheduling a task. I have a text file that is being downloaded to a web folder from an AS400 system three times a day. I want to then grab this file and insert and replace the exisiting records in a database table. What be the best...more >>