Hey I want to learn more on how to schedule tasks in Sql Server 2005. Lets say I have a table containing some records. At a certain time I want this records to be copied over to another table. The time when this data trasfer should execute is stored in another table in the same database. ...more >>

Hi all, can work this out doing it a long way by making individual tables and then inner joining them all, but the code gets real messy. I'm not really sure how to use case statements but I'm sure they would be easily applied in this situation. Ok the problem: Select 1 select pro_job Shipm...more >>

I was researching a problem in my atmosphere SQL, I am using an application that needs that the routine sp_executeSQL is executed. For the log of the application, it is exhibited the message that that routine was not found. Does know how I can solve this problem? ...more >>

Just got back to the hotel from a restaurant where, on the menu, it said, "SELECT Beef..." I instinctively asked the girl, "FROM where?" She didn't get it. Oh well. -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "Veritas e aequitas; in nominae Patri,...more >>

hey sql server 2005 select convert(datetime, '27-08-06', 110) gives this result: Msg 241, Level 16, State 1, Level 1 Conversion failed when converting datetime from character string I want to convert this text to datetime, this isn't much but I'm just learning how this convert method...more >>

Hi All Please find the stored procedure written using SQL Server 2005. This returns a new account number everytime new information is enetered. But I want to make sure that only unique combination of CompanyName, ShippingAddress1, ShippingCity and ShippingState gets into the database. What i...more >>

Hi All Can someone please help me to buy good books on SQL Server 2005, both beginner and expert levels. TIA, Ami. ...more >>

I want all of the records that have either Sold Sales from History, that will include 90% Probability pending sales. I want to include the associated primary contact company name and record id for a given StaffName and its UserID ** I'm not sure how to use my joins so that I get a record i...more >>

Hello! I have developed a software which uses DBLib to access SQLServer 2000. I tried if the same software will work with 32 bit version of SQLServer 2005 - there was no problem. But now I have a customer who has a 64 bit version of SQLServer 2005 and I urgently need to find solution for us...more >>

This is probably very easy but how would I do these two things inside a Stored Proc: 1. Do a "Select Count(*).." and dump the resultant rowcount into a variable instead of returning the value from the SP? 2. Do something like "Select Top 1 LastName from SomeTable..." into a variable in a...more >>

Hi, I have a two part problem and I was hoping someone could point me in the right direction. I am storing URL's that point to HTML pages or files in SQL Server. I am storing the URL's as varchar. I am trying to append the google analytics javascript code after the url. Here is that code...more >>

hey sql server 2005 I'm trying to select a field which consist of 2 possible values. Either 'G' or 'J'. In the resultset I don't want this value to appear as 'G or 'J'' but instead I want to display some other meaningful text. For example if it's 'G' then display "Golf", if 'J' then disp...more >>

Hi I need a function converting a string to "first letter of each word to capital letter". Example: initial string: sQl ENTERprisE manAGer\ result string: Sql Enterprise Manager There is a function called INITCAP which does this thing. Maybe someone has got ready to use user function? ...more >>

Hello I'm looking for a ready to use user function which takes a string as a argument and returns string containing fist letter of each word as capital. example: initail: sUN iS ShINIng result: Sun Is Shining The similar role in Oracle has function called INITCAP Best Regards Da...more >>

How can I do a SELECT statement inside a Stored Procedure without necessarily RETURNING the results? Say I want to select some data but then only return that data to the calling code under certain circumstances which I test inside the logic of the SP. Can I do this? Alex...more >>

I have two tables that contain codes and some corresponding data for each code. Table A is the codes from last year, and the Table B is the the same data for the current year. Each year, codes are added and deleted. I need to determine the codes that have been deleted since last year. In oth...more >>

We recently converted our database into SQL Server 2000 from non-relational database. We have approx.30 millions record. We are having some consistency problem and want to cleanup old record. here is the example. we have 2 tables. Table A(Parent table) and a Table B(child table) . The records ...more >>

Using SS2000 SP4. I have a table with many instances of each company id. I want to get only one row based on a date. I tried this: select distinct T.numCompanyId, (select top 1 tblCompanyFranchise.numProgramId ProgramId, tblCompanyFranchise.numCompanyStatusId StatusId, numTaskId, dtTaskCr...more >>

I need to create a second unique column. field1 already a primay key, How can I make field2 unique as well? I cannot use forieng key because no relationship with other table. Thanks...more >>

Just wondering what the concensus is on naming tables - as singular or plural (e.g., a table named Person vs. People; or Order vs. Orders). I have seen the plural form generally used. But in reviewing the AdventureWorks sample database it appears that the singular form is used. Are we bein...more >>

I have the following query (table T10M has 10M rows, T25M has 25M rows and T3M has 3M rows, all tables have proper cluster/non-cluster index cover all join keys) select T10M.C1, t24M.C1, t24M.C2, t24M.C3, .... into #tmp1 from db1..T10M inner join db1..T3M on T10M.K1 = t3M.K1 in...more >>

Hello All, What's downside of having nvarchar field with equal or more than 1000 characters length? Thanks, Walter ...more >>

I have a question regarding the size of my SQL server in relation with my SharePoint databases. I am a real SQL newbie and not sure how to deal with the size of my databases. I have a DB maintenance setup to run everynight but my space available seems to be dwindling down when I look into Ente...more >>

Hi, I keep getting the following error when trying to deploy an assembly to an instance of SQL Express: Deploying file: AppLogic.dll, Path: C:\Documents and Settings\cbilson\My Documents\Visual Studio 2005\Projects\PtgShell\AppInDbPOC\AppLogic\obj\Debug\AppLogic.dll ... Error: Failed t...more >>

Hi All I need your help. I have a table called teletb and defined as: create table teletb (Type nvarchar(10) Null, Phone varchar (50) Null, Cnt int Not Null, [id] int Not Null) Go It stores some fax numbers and would like to create the e-faxes with '1' for local numbers and '011' for ...more >>

Hello! I'm newbie (yet) in MS SQL2k and I have a big problem, so I would like to please for help: There are 9 big databases with a _lot_ of user tables. I had to insert 2 new fields into _every_ user tables (at the end). It succeeded, but noticed that I made a mistake: set a Default value...more >>

What is diffence between the below two. I get length as 30 for first query and 32 for second with two space. Why isn't rtrim removing the space? SELECT LEN(RTRIM('4 MONTH WAIT THEN 1ST OF MONTH ')) SELECT LEN(RTRIM(checkperiod)) FROM dbo.testdb WHERE EmpId = '582' Any help would be app...more >>

Is there a function or some way to get the name of the database (sans extension) of the current database (the database in/on which the query is executing)? Short explanation: legacy system Long explanation: Master database, lots of different child database. From within a child databas...more >>

Greetings, I want to SELECT TOP 20 PERCENT resultset, then SELECT BETWEEN TOP 20 AND TOP 40, then SELECT BETWEEN TOP 40 AND TOP 60...all the way up to 100 PERCENT...possible? SQL 2000 Platform Thanks in Advance, Don *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hello All, I have a database table that stores the history of a data readings taken from hardware devices. The hardware device is queried by software once a minute (or more) and the value stored in the database for trending an analysis. The table structure is: CREATE TABLE DeviceData ( ...more >>

Hi I need to find the best way to store mp3's for download by our users, as they will have to pay to download the mp3's I need to find a way to secure them from users simply trying different links to get to the downloads. Normally if I was doing a system to allow users to download files I ...more >>

Hi champs How do I disable absolutely all constraints on a table? I just want to empty all my tables quick. /Many thanks...more >>

I have a numeric column called Price that stores the pricing info. There is a req. to display * in that column on certain conditions. I was trying to use CAST to convert varchar to Numeric, but i get this error: "Error converting data type varchar to numeric." Any suggestions on how this c...more >>

I've got a fairly large database, about 250GB and am adding some new non-clustered indexes. I'm thinking about separating these into a separate files and/or filegroups, but know on the live server, all file(group)s will be on the same physical disk. Is there much point in doing this? We ...more >>

Hello, ******************************************************************************************************* set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description:...more >>

Dear ALL, I am running sql 2000 (sp3 a) on a cluster (compaq) windows 2000 advanced server sp4 with rollup update with database located in a raid 10 array . it was woking properly since 2002 but recently i getting lot of index corruption around 5 corruptions in this month and all rectified b...more >>

I have two SQL databases, which both contain several tables. A table from database A, should be copied every 1 hour to database B. It should be an exact copy. Of course an update on this table would be fine as well. To make it even more clear: those are really separate databases....more >>

CREATE TABLE [dbo].[tblJobs] ( [jobID] [int] IDENTITY (10000, 1) NOT NULL , [jobNo] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [openDate] [datetime] NOT NULL ) CREATE TABLE [dbo].[tblJobOrders] ( [joID] [int] IDENTITY (10000, 1) NOT NULL , [jobID] [int] NOT NULL , [orderI...more >>

I have a quick question, but I've been unable to find the answer, so I'm posting it here. I'm working on a stored procedure (running on SQL 2000) that creates a temp table in memory (saved as a variable in memory called @TempTable and defined as a TABLE), and that table has a column that ...more >>

When running the Database Engine Tuning Advisor, I often get recommended to create statistics. I've tried to figure out the benefits and drawback on this, The recommendation could be something like: CREATE STATISTICS [_dta_stat_718781768_4_8] ON [dbo].[mytable]([myfield1], [myfield2]) Shou...more >>

Hi, I take it there's no guarantee that running a select on a table that uses a clustered index on an identity column will return the rows in ascending order of the identity? Cheers! ...more >>

Hi, in a table i inserted in a field type varchar with the maximum character possible (8000 correct me if im wrong..) When my client inserts a very big text in the backoffice...its not all visible in the website...maybe is because of the maximum of character excedeed? Can anyone give me a poss...more >>

All our power went and the UPS for one of our Sql Server's did not work. When checking the Sql server Logs the Memory had been changed from 2147483647 to 16 during the reboot "Configuration option 'max server memory (MB)' changed from 2147483647 to 16. Run the RECONFIGURE statement to install...more >>

I want to add "ON UPDATE CASCADE" to my foreign key constraint. For this, I first drop the constraint, and then add it anew with ALTER TABLE dbo.TBL_Sales_Import_Monthly WITH NOCHECK ADD CONSTRAINT [FK_TBL_Sales_Import_Monthly_TBL_Stores] FOREIGN KEY ([store]) REFERENCES [TBL_Stores]([storen...more >>

Good Morning. I have a nested stored procedure executed 5 times from the calling procedure. Within the nested sProc I have a count_variable that is set from a count on a table. The 1st time the program is called the variable is set to 22, the second time 40. Is there a way I can manage to ...more >>

Hello, I am trying to do the following: INSERT INTO LogTable(eptid, userId, status) VALUES (@eptId, @userId, 'APPROVED') SELECT @@IDENTITY UPDATE AnswerTable SET log_id = @@identity WHERE userId = @userId Is this correct? I simly want to update the log_id field of the AnswerTable with the ...more >>

Is it possible to generate XML documents from the SQL Server (2000). I know that it is possible to return XML structure as a result of a query, but is it possible to generate a file with the extension XML saved to a given place at the server? TIRislaa ...more >>

Hi all. When selecting some prosedures in enterprice manager and then scripting the objects you typically get this "GO" between then statements. This seems to work ok when you run the script for Query analyser, but not if you want to run the script from with another prosedure or from a VB.NET...more >>