Hello, I'm trying to implement a ROT13 procedure for more mangling. I'm oh-so- close: CREATE PROCEDURE ROT13 @str VARCHAR(255), @ret VARCHAR(255) OUTPUT AS DECLARE @i INT, @len INT, @adj INT SET @i = 1 SET @len = LEN(@str) SET @adj = 13 SET @ret = '' WHILE (@i <...more >>

Hi: I have a script below, when I run it on its own, it is great, but when I run it as an "Execute SQL" task as part of a DTS package, the step fails with a "division by zero" error. Shouldn't it ignore this error due to setting ANSI_WARNINGS and ARITHABORT both to OFF? I get the error in Quer...more >>

I have a situation where I'm deleting rows from one table and I have a FK (one to one) to another table where I also need to delete the records. For reasons best left unsaid, I can't use a cascade delete. I can find out which records need to be deleted by doing something like this: SELEC...more >>

Hi, Someone left me with the unfinished script below. The problem is that the if conditions does not seem to work as should, because the @@error is always 0 Maybe there are linitations with the nesting of IF.. ? maybe something else I dont know? I am not very good in SQL script, so i would...more >>

Hi I have specific question which I am unable to comprehend. 1. Sql server should be running with Domain a/c not local. But Should it be the same as the UserId of the mail. Say I started sql server with username : aaa, configured it ti profile whose username is bbb. will it work normally...more >>

Hello, I have an application that requires a download of a CSV file. The CSV file contains over a hundred fields. Three of these fields could potentially hold product information. In order to work on the vendor's system, all three products need to be listed in a single row(record) of the ...more >>

Hi, I'm working with a complex .net app with a sql back end. I'm trying to enforce a naming convention for sprocs and udfs. For compatability reasons the old sprocs will still have to exist until all the code that refers to them is pointed to the new name. I don't want to have to maintain 2...more >>

Hi, I have a situation. We are developing 3 tier applications. I got one SQL server, and .net application. The front will call the SPs tru a component. Each call to the sps thru the component will be enclosed within a BEGIN TRAN .. COMMIT TRAN( coded in .Net ). Now the problem i am facing is ...more >>

Hi, The commercial department of my company is saying that day 03/01/2005 (dd/mm/yyyy) is in the first week of the year and the SQL Server, through the DatePart(week function, '20050103') it says that this day is of the second week. Somebody knows that agency determines the calculation form...more >>

Hi, Is possible to Generate a script of the database using the SQL Analyzer or SP? Could anybody post any examples? I am insterested in specifying some properties as well, but I don't know if all this is possible. I haven't been able to find an example yet. Thanks a lot. ...more >>

Hi, Hopefully someone can help... I am writing a large query to return results for a report. It uses many subselect queries to return data and I am getting some incredibly spurious results. I need my counts at 'campaignID' level, and so I initially have a query to return the unique camp...more >>

create table t1 (c1 int, c2 int, c3 nvarchar(5)) insert t1 values(1,0,'A1') insert t1 values(2,0,'A1') insert t1 values(3,0,'A1') insert t1 values(4,0,'A2') insert t1 values(5,0,'A2') insert t1 values(6,0,'A2') create table t2 (c2 int, c3 nvarchar(5)) insert t2 values(7,'A1') insert t...more >>

Hi guys, Following my thread "Parallelism algorithm" dated Jan. 2nd.=20 As I promised, I have conducted a "mini test" regarding the CPU = utilization distribution as follows: Dedicated test server quad XEON / 4G RAM. I used a "real world" CPU intensive query that uses all CPUs to > 90% = ...more >>

I would like to access our SQL Server from outside our LAN. Basically, access the SQL just like most ISP's do. Can this be done if the SQL Server is behind a firewall? I was hoping to be able to open the sql port number on the firewall and accomplish this task. Do I need to do anything wit...more >>

Hi, I've deleted and re-added a user to the db. But now this user doesn't have any rights anymore. Can someone please tell me if there is a stored procedure or something to re-gain these rights easily? This is what I did: USE master EXEC myDB..sp_revokedbaccess MyAdmin EXEC sp_d...more >>

Hi All Is it possible to connect to SQL server 2005 from ASP 3.0? And if so, how? ta...more >>

I see some examples in TSQL like below that have a N before the value. What does N mean? @myvariable= N'myvalue' ...more >>

Hi I have a table Employees, with fields like: EmpNumber, LastName, FirstName, Age, Sex, ..... The EmpNumber is an auto inc value (my key value), so it's a unique value, not for the LastName and FirstName. I don't want the LastName and FirstName fields to be unique because they could corres...more >>

Has anyone created PDF from SQL by throwing SQL select data into a PDF document and then showing that PDF to the end user in the PDF format. I am wondering if SQL Server provides any API for this or do we need to go to Third Party tools for this. Thanks in advance, -Nitin ...more >>

Has anyone has any exposure of creating PDF document on the fly with a SQL Query Resultset data? Are there any APIs available that can be integrated with SQL Server to utilize such a functionality? Thanks in advance. -Nitin ...more >>

Hi What causes the Stored procedures to have longer compilation times??(Time at first Execution of a stored procedure) I am aware that at first time the Stored procedure has to compile and next subsequent executions will make use of procedure cache,my questions is some times the differn...more >>

Hi, I'm getting the following error when trying to update. I know why. I want to Update the records that don't get the error. Server: Msg 2627, Level 14, State 2, Line 5 Violation of UNIQUE KEY constraint 'IX_patientclinical'. Cannot insert duplicate key in object 'patientclinical'. The ...more >>

Hello, all. We have an EDI TS-130 file (academic transcript) that we would like to import into SQL Server 2000. Is this possible to do for free? If so, how? ...more >>

Hello I have a tbl: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPictureStats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblPictureStats] GO CREATE TABLE [dbo].[tblPictureStats] ( [PictureStatID] [int] IDENTITY (1, 1) NOT NULL , [Time...more >>

If I run the following query in QA, the result is sorted alphabetically. I do not want it sorted. I would have thought that a union would just pile one on top of another without reorganizing them. Why is this being sorted? select 'add' union select 'removed' union select 'changed' ...more >>

I would like to run two queries in my SP, both of which will return a count value, and I'd like to combine them to return a single-record recordset with two values. I know this will be a simp1e one, but I just cant get my head round it today...! Eg: Q1: Select Count(*) as Count1 from ...more >>

Hi NG, given are two SPs called "spOuter" and "spInner". spOuter calls spInner. The execution of spInner leads to an conversion error, which I want to catch and execute some further error handling code. Here are the code examples for those SPs: spOuter: CREATE PROCEDURE spOuter AS ...more >>

Hi Sql Gurus, How to see the Isolation level of a particular server What is the command for it ??? If you know any good articles or information please post it Thanks in Advance PVR...more >>

What would be the best practice to follow to keep track of MS SQL server changes... Stroed procs, tables, views, triggers, indexes, DTS and also jobs ect.... I am not quite sure how Source safe works with sql server. Any other way to do this... Even if its manual work, its okey.. I would appr...more >>

Hi I have been working with a complex trigger that takes a long time to process. It is an insert tirgger that is set off by an asp page inserting values into a table. The problem is that I want the asp page to complete loading the moment the insert has gone ahead, however the page waits for...more >>

I am trying to insert a row into my table that works fine, except where there is no row returned from the select clause of the insert statement. The following works fine as long as there is at least one row that meets the criteria. insert into SkillsAnswers(PositionID,QuestionUnique,Answer...more >>

Is it possoble to a vbscript running inside a jobstep to reference the job or the jobstep itself, like a "self" or "parent" property ? How can I do this ? ...more >>

Hi all, How would I remove Leading Zeros in a nvarchar Column The data looks like this 000336 000353614 000823699 001346726 001432256 001505994 0054978 006781 008172 009310 009310 009736 thanks Gerry ...more >>

Hi there, I inherited an application which enables different organisations (within the NHS) to upload their telephone data to a central application. The existing method for this is that a user uploads a .csv file with all of their data in. The ASP page then executes a job. Now this i...more >>

Hello, First: Best wishes for 2005. I created a simple download counter and wish to extract this data from the database. On every download this sp is envoked: --- CREATE PROCEDURE stats_dl_add @file_fk numeric, @date datetime -- simple date so record value is increased until new day ...more >>

Hi there: I have a table that has several different fields with time values in seconds (datatype int). Beside every such column is another varchar column that is meant to represent the same number in HHMMSS. I want to avoid a cursor, but doing a straight UPDATE to me doesn't seem possible beca...more >>

I want to write a stored procedure that takes a username or role and returns a table as detailed below: SPName CanView ----------------------------- GetXXX True InsertXXX False UpdateXXX False DeleteXXX False etc... for ALL s...more >>

I am trying the following SQL statement: DECLARE @PN varchar(50) SET @PN = '5501' SELECT OptDescription, SUM(CASE PlanNumber WHEN @PN THEN Price ELSE 0 END) AS @PN FROM EstimProposalBase GROUP BY OptDescription, PlanNumber Which uses the following table: ID PlanNumber Price OptD...more >>

col1 col2 col3 5 5 1/1/1970 0 0 1/1/1972 5 5 1/1/1971 0 0 1/1/1973 Select col1, col2, col3, col1 - col2 As col4 From t1 Order By col1 - col2, col3 In the above case, the order is not what I was expecting. It is as if it is s...more >>

I need to combine the counts of 3 tables into 1 final value. I have a query like below select count(*) as total from table1 union select count(*) as total from table2 union select count(*) as total from table3 How do I get the total from this query? I have tried wrapping the above qu...more >>

I am looking to convert an 'int' number 1-3 digits into a 3 digit 'varchar' with zero preceding 1 and 2 digit numbers in one SQL statement if possible. select (CONVERT(varchar(3),DUR_DAYS)) from tableA returns: 4 6 12 102 I would like it to return: 004 006 012 102 ...more >>

Does anyone know when the client tools will be released for the 64-bit version???...more >>

I could do this with ASP, but I cannot find anything in BOL to let me know how to do this via T-SQL. I created a new field in an existing table, and I'd like to take the data in field1, move it to field2, row by row, and then go back and set field1 to NULL for the same rows. I guess I don't kn...more >>

I have a trigger that was created in SQL 7.0 with 6.5 compatibility turned on. We are upgrading our business software so we are changing our database to SQL 2000 but the following portion of the trigger does not execute with SQL 2000 and the compatibility level turned on to 8.0(we do not want any...more >>

hi all; i am new to sql server; now i use Access 2003 and Sql server 2000. i have serveral question: 1- how do i backup my sql database including my user and other security that i create with my database? 2- i have seen new feature in access 2003 , BACKUP DATABSE. I want to write code to ...more >>

I built the WHERE Clause part of the query into a variable called @LikeString. I am getting a syntax error [Incorrect syntax near the keyword 'ORDER'.] when I use the variable name after the WHERE SELECT FunctionID, AccessRoles FROM SubMenus WHERE @LikeString ORDER BY MenuO...more >>

Does anyone know of a way I can remove commas from data that is in a table? Another programmer imported the data into sql server and left commas in one of the fields. The application does not recognize the comma, so therefore it errors out. There are about 2500 records that would have to be up...more >>

Hello, Does anyone has an example of a trigger where on insert or update checks if the value is 6 numerical characters in another table? Thnx ...more >>

Hi there I have a table “tblJobRefs” which includes the fields LdUserName varchar 35, JobDate smalldatetime, JobAttend varchar 50. Includes the data: Ldusername JobDate JobAttend Aardeman 2005-01-04 00:00:00 Attended Abdulghafar 2004-12-22 00:00:00 ...more >>

Hi Friend, We are fetching data for access 2003 report from linked table. Table are in SQL server. So before opening the report we check whether SQl server is running or not, so that we can show customized error msg when sql is not running. We are able to ascertain whether sql ...more >>

I know that you can use the graphical tools in Enterprise Manager to create a database. If I choose this path and create a db on my test machine, how do I create the same db (i.e. the physical schema) on my production machine at a later date. It seems that you should use a series of DDL co...more >>

In returning a dataset as XML from a stored procedure, the root element is named "NewDataSet". How can I specifiy the dataset name in the stored procedure? Thanks. Tom_B...more >>

I need to run a stored procedure for each row returned from a SELECT statement. Like: SELECT (exec pDOSP(name)) FROM Shippers WHERE sid>400 AND sid<500 So, for each name in shippers where the ID is between 400 and 500, I need to run that procedure. If can't be a function because a funct...more >>

Does anyone know how to simply get just the date element of a datetime value. Eg if the datetime value is '2005-01-05 12:35:12' to get the value '2005-01-05 00:00:00' I can do this by converting to floats and back again but it is messy. Would appreciate if anyone knows about a simple funct...more >>

Hi, I need to generate a string which represent parents, and all the way up, of a child, such as it should return following string for empid = 12: 'Nancy > Janet > Robert' Here is the data plus schema: empid mgrid empname salary 1 Nancy 10000 2 1 Andrew ...more >>

Hello: I am trying to create a report using Reporting Services. I have created Report Project in Visual Studio .NET but I don't know how to create executable file to run my report from command line. Could you please give me a hint ? Thanks, GB ...more >>

I need to create sp with selecting top records from one table into another. I'm new in this stuff. Apparently this is not the right way, because getting an error :-). Here's what I'm trying: CREATE PROC sp_collecttop @toprecords int AS INSERT INTO TopCollector (RecNo, RecDate, RefNo,...more >>

How can i find out the date of the previous friday from any given date ie Given date = 01/07/2004 previous Friday would be = 25/06/2004 Given date = 03/09/2004 previous Friday would be = 27/08/2004 Given date = 05/01/2005 previous Friday would be = 31/12/2004 ta in ...more >>