Hello, I have a column that has this type of information: Address 123 se main st and or 456 s riverfront apt 12 so the new layout needs to be this way street number, street direction street number street type optional apt optional apt number optional How do I parse over ...more >>

Is it possible from within a SQL Server Stored Procedure to query the NT Groups that the current SYSTEM_USER() is a member of? ...more >>

Hi How can I convert Julian date to Calendar date in SQL 2000? for e.g julian date =2006227 is equal to '2006-08-15' is calendar date.. Any idea ? Thanks -Kris ...more >>

Hi How can I convert Julian date to Calendar date ? for e.g julian date =2006227 is equal to '2006-08-15' is calendar date.. Any idea ? Thanks -Kris ...more >>

Hello group, In my table I have column type of varchar . I need to find text between two last brackets. If there aren't brackets or is one bracket in the row, the function should return NULL. How to create according function. I.E.: MyCol: ---> Result abc(def) gh (i...more >>

Dear experts, Again, sorry to bother you again with such a seemingly dumb question, but I'm having some really mysterious results here. ie. Create procedure the_test As Begin Select CustomerID >From dbo.customers -----------------------------*/ Server: Msg 170, Level 15, Sta...more >>

I am wondering what is the purpose of DB Snapshot in SS 2005? Since it creates pointers pointing to the source database / keep the copy of any modified data, that means all the query might still need to go through the source database. I don't see how this can improve the performance... Than...more >>

Hi everyone, I created a stored procedure with the following select: Select t1.name, t2.value From db1.dbo.tbl1 t1, db2.dbo.tbl2 t2 where t1.number = t2.number When I run the stored procedure a receive the following message: Msg 229, Level 14, State 5, Procedu...more >>

Dear Experts, Ok, I hate to ask such a seemingly dumb question, but I've already spent far too much time on this. More that I would care to admit. In Sql server, how do I simply change a character into a number?????? In Oracle, it is: select to_number(20.55) from dual TO_NUMBER(20...more >>

How do I do the following : columns : address1 , address2 , address3 to be returned as : "ADDRESS1, ADDRESS2, ADDRESS3" But where address columns are empty string then I dont want to append the "," ie not : "ADDRESS1, ," ...more >>

Hello I am using SQL Server 2005 on a Server 2003 platform. I use a stored procedure that takes a parameter (phonenumber) and then gets correct customer information. I parse the telephone number within the stored procedure. This then uses nested select statements to look up the number. ...more >>

Is it possible to send an email right from a stored proc.? Thanks ...more >>

I posted awhile back asking for best practices to manage database scripts ongoing in a project. Many said to have a script for each object (e.g. tblEmployee). But then while I had a CreateTable_tblEmployee.sql, I later had a need to modify the table so then had an AlterTable_tblEmployee.sql....more >>

Gurus? What sample of code inside of a Job/step would conditionally force the Job/Step to fail. In other words if a certain condition exists then we want the step to fail. An instance of this would be if there is a "1" in a #temp table We then can set the failure or success to the correct ne...more >>

Can you disable triggers in 2000? If so, could you send me sample syntax (as I'd like to script this)?...more >>

If the there are three records found in table1, how can a query insert three records into table two...but , I only want one field from table1 to go into table2. The other field will be a parameter that was sent into the stored procedure and the third field to be inserted will be a GUID (create...more >>

I am trying to create a query that lists users from a transaction log table. There is multiple entries for that user in the table but I only want to list the user once if the entries are spaced within 5 minutes of first time they logged in. For example, there is 4 records for Amy. Amy logs i...more >>

Hi, I have a stored procedure on a linked server, and I want to retrieve the result from the stored procedure. I know that I can do the following to retrieve the data: 1. create a temp table #temp 2.insert into #temp exec linkedserver.sp_storedProcedure My question: Is there another w...more >>

I am trying to the daily sales number from a given week (parameterized by year/week: @year and @week) as well as the corresponding daily sales number from the same week last year within the same query. This is for use with a SRS 2005 matrix report. I have two dataset queries which return the...more >>

Hi, I have a several servers where a stored procedure is run individually to collect some stats in a temp table and then that result-set is outputted at the end. Instead of outputting the result-set to display, I'd like to have that result-set inputted (in an append mode) to one of my ta...more >>

Hi All, When I run the bcp in query analyzer, I got following error msg: 'bcp' is not recognized as an internal or external command, operable program or batch file. The bcp.exe is in C:\Program Files\Microsoft SQL Server\80\Tools\Binn folder. What's problem it is? Thanks Tom...more >>

CREATE TABLE #Tiers ( StartingAssets DECIMAL (18,0), EndingAssets DECIMAL (18,0), Fee DECIMAL (7,6) ) INSERT INTO #Tiers (StartingAssets,EndingAssets,Fee) VALUES ( 0,50,.005) INSERT INTO #Tiers (StartingAssets,EndingAssets,Fee) VALUES ( 50,100,.004) INSERT INTO #Tiers (StartingAssets,Endin...more >>

I need to move/copy all objects (tables, data, triggers, SPROCS) in database 1 and 2 to a new database 3. There are no duplicate objects names that can cause a conflict. Since this would be part of an automated update, I'm looking for a programmatic way to accomplish this for SQL 2000 and SQL ...more >>

Using SS2000 SP4. I created a new sp from an existing one and I'm trying to add a new input variable. The sp is below. I added @FastStart. If I run the sp with this syntax uspReportBillingSummary1 '2006-5-3', '2006-8-1','', 0 it runs ok. But if I put a 1 instead of 0 like this uspReportBillin...more >>

Hi everyone! this is my first post, and most of you will probably find my question VERY easy to answer...but I'm having trouble with this. I'm trying to take part of the contents of a cell and either move it to another cell or put it at the beginning of the first cell. The part to be parced ...more >>

Hello, I am not sure how I should do this in a stored procedure. I need to create a case number that looks like this: FJ062930009-A5Z The case number uses FJ then current year (06) then day of year (293) then the total number of cases entered for the current year and month + 1 (0009) - ...more >>

I'm using syscomments to get procs into a new table. I'm then doing some re-formatting on the procs to clean things up. A couple thousand of them. From my new table and after sweeping it up: 1. I'm doing a select into a variable @sql which is nvarchar(4000). 2. I'm then running exec sp_ex...more >>

I need to produce a single XML from two queries. The queries are not in anyway related so I really want to concatante two 'for xml' queries and have a <root> tag around the whol ething. So I end up with: <root> <query1> ..xml from my first query </query1> <query2> ..xml from my secon...more >>

Hello, I am tringt o design a query like so Select columnA from tableX Where columnA (is not in columnA in TableY) Just trying to get the syntax right....more >>

Hello, I've run across an issue that has had me banging my head on my desk for the past few days. My situation is that I have a very complex query that selects several values and uses a group by to select by which hour the data is in. So basically everything between 3 pm and 3:59 pm is going ...more >>

I could have sworn I read somewhere or was in a conference that they said SQL2005 allowed the user of the dot (.) notation or some other special character in name conventions IE: History.Insert.CaseRecord History.Delete.CaseRecord Cases.Select.CaseRecord Cases.Select.HistoryRecordByID ...more >>

hi guys, i wrote this code for searching with many parameters that may be null some of them! when i fill al parameters it works properly but if some of them be empty, it dosn't work! what is reason? any solution is helpful...immidiately CREATE PROCEDURE StrProcMemSearch @married_id tinyint...more >>

Hi, I just did a Transaction Log backup but the size of the log file has not yet shrunk. Any idea why? How can reduce the log file size? In the backup options, the "Truncate the transaction log" option WAS selected. BTW, I'm using SQL Server 2005 Std. on Windows Server 2003 Std. Thanks for ...more >>

Hi All experts, im doing one project something like searching, i knew Soundex, Celko, NYSIIS, Double_metaphone and etc for those sound detection dll, but after filthering still give a lot of not matching data (maybe the pronouse same but word is different), i would like know who know...more >>

Hi everyone I have the following procedure returning twice the result, doubling it, whatever. I think it's the way I'm using the join, but I have no idea how to solve my problem. I commented the last select/join as that was doubling the result too. Thanks for your patience. BTW, this is ...more >>

Excuse the dumb question, but i just can't get a clear answer from the doc's I've checked. If I create a UDF where I pass parameters: MUST I specify the maximum of nvarchar? Is there no data type that is totally variable? -- Mav...more >>

Is it possible to pass into the T-SQL colum name as variable? I badly need to use PIVOT command for creating columns from rows but I dont know, which column names will be selected. .... and... another question is... what to use as function after PIVOT command, in case I want only show the ...more >>

Hi, I am trying to formulate a SELECT statement to query the following table: CREATE TABLE dbo.PART_TABLE( PART_ID VARCHAR(12) NOT NULL , JOB_NUMBER VARCHAR(12) NOT NULL , ENTRY_DATE DATETIME NOT NULL DEFAULT GETDATE() , TYPE_CODE VARCHAR(12) NOT NULL , PRIMARY KEY (PART_ID, JOB_NUMB...more >>

Hi everyone, I'm using the following query to create and insert data into a new table... SELECT identity(int,1,1) as 'id', c.id as 'coupling', p.short as 'product', '2003' as 'year', EXP( CASE WHEN l2.totdem > 0 THEN LOG(l2.totdem) ELSE 0 END + CASE WHEN l1.[output] > 0 THEN LOG(l1.[output]...more >>

Hi, please help me with SQL Query. I need to select only last 30 rows of the table,but I don't know how. Can somebody help me? Thanks...more >>

Hello I'm having problems with a restore that went wrong using the SQL 2005 manager. It frooze during restore, and now I cant delete the database or do anything to redo it all again. I get this error: Database 'mydb' cannot be opened. It is in the middle of a restore. When I check proc...more >>

Hi there Im in the process of converting a Ntext variable held in a table to NVarchar(max). At the moment the variable, a notes field is holding on average 6000- 8000 characters. No problem on converting this to NVarchar(max) but I wanted to know what is the maximum or upper limit on...more >>

Hi all I have the Following: SELECT Dimensionvaluename00,Dimensionvaluename01,Dimensionvaluename02,Dimensionvalu ename03, SUM(CASE reportyear WHEN 2006 THEN value ELSE 0 END) AS [2006], SUM(CASE reportyear WHEN 2007 THEN value ELSE 0 END) AS [2007] FROM view_Pivot group by Dimensionvaluenam...more >>