Hi, I'm having problem getting the value from a query using dynamic SQL. Normally you can do the following: set @i_count = (select count(*) from users) or select @i_count = count(*) from users but how do you do it with dynamic sql? if you try: set @i_count = exec('select count(*...more >>

Hello, I have a question regarding stored procedure desing that provides the optimal performance. Let's say we have a table Products that consists of three columns: Name, Status, RegistrationTime. All columns are indexed and users should be able to lookup data by any of the columns. We have...more >>

I have a lookup table that I'd like to query as a table, however I'm not sure how to arrange this as columns and rows. Usually a human compares the Height and the intersection of the weight to find the column header as: PP = Preferred Plus PN = Preferred Non Smoker SP = Standard Plus SN =...more >>

Hello! Microsoft SQL Server offers a possibility to automatically dispense the next integer to be used as the concrete value for an ID column as insertion time. Does utilization of this feature force serialization at insertion time so that this feature should be used with caution, for instance...more >>

Hi, I have a peculiar problem at hand that I am not able to solve. I would really appreciate if anybody of you can show me the way! I have a Works table that stores work done by an employee with Work Start Date and Work End Date. Now, I want to calculate the number of days an employee has w...more >>

Hello! Some time ago Microsoft changed the algorithm for generating GUIDs in it's operating systems. Does it mean that we may no longer be sure that newid() will really generate a unique uniqueidentifier? Best regards, Henrik Dahl ...more >>

SQL 2K Please provide any thoughts on handling DTS error while called from a SQL job. My SQL Job starts a DTS using dtsrun utility and when the DTS fails, I don't have much details. If I setup in DTS , Property -> Logging -> Error handling , will I get all the detailed error information...more >>

The script below does most of what I need except that I'm having a few problems with it. What I'm trying to do is copy a row at a time from an old table into five new tables. The only way I was able to do this was to select the fields I need from the old table and then loop through each row ...more >>

SQL2K Hellow Experts! Is there a stored proc out there to grant a user EXEC permission on all stored proc on a certain database? Maybe something like sp_grantExecPermission <user>,<'usp_move%'> I've been using the enterprise manager so far to do this, but I have more users now I'm thin...more >>

Hello I'm trying to create a sp as below, which creates some records with 2 iterations as can be seen below. It gives the following error in Query Analyser at present. Procedure 'sp_tmrecs' expects parameter '@taskid', which was not supplied. Can you advise please? Soc. CREATE PRO...more >>

Hi, I was hoping SOMEONE would be able to help me. I'm new to SQL (took an intro course) so I'm still learning. Anyway, I have to transpose this table that looks something like this: ID Region Vol Circum A arm 2 3 A leg 3 5 B arm 1 ...more >>

Hello If i work with an integer as a primary key in a table, I can define it as IDENTITY. This makes it possible to use the IDENT_CURRENT()-Funktion to get the last "generated" primary key. Is there something like this when I use a GUID as the primary key (ROWGUID). IDENT_CURRENT() does ...more >>

I would like to insert data into Oracle database using a trigger in SQL Server. How should I proceed? What all things do I need to take care? If you have some sample code, please post it. Thanks in advance. --Software Engineer...more >>

I have one PARENT table called Category CREATE TABLE Category (CategoryID, CategoryName) and one CHILD table called Product CREATE TABLE Product (ProductID, ProductName, CategoryID) I am developing a user defined function which takes one parameter called ProductID, the UDF is supposed to ret...more >>

I have a job that executes several steps. The job runs processes that are scheduled by users. I need to indicate failure somehow when a step fails. The procedure that runs the scheduled processes has an output message param but my code never gets executed when an error is encountered... th...more >>

Can anyone tell if it is possible to change the data and time of your SQL Server or does the SQL server use the date and time from the PC clock? ...more >>

I'm trying to copy data out of one database and into another where the two data types for the columns are different. The problem I'm having doing this is that some of the values in the datatype real are two big to fit into the new datatype decimal (5,2). Does anyone know if I can test the va...more >>

Hi, I would like a script which would find all the duplicate rows in the table and keep 1 of the duplicate rows and delete the others. I am looking for duplicates where, forename, surname and address are the same. Would appreciat the help. Thanks Damon ...more >>

We want to convert NULL columns returned in a query to value of 0 when using it w/ FOR XML AUTO. We used CASE statement to select NULL values and convert them to 0. This worked fine until the query actually encountered actual value. Now all the ones that were NULLs get converted to 0 but the o...more >>

I'm looking for the best way to calculate a status duration. For instance, in this example, the duration of "Released" was 43 days: Status----Date Open------06/01/04 Approved--07/05/04 Released--08/01/04 Revised---08/15/04 Released--09/02/04 Closed----10/01/04 My initial thought was the...more >>

I've just installed Visual Studio 2005 and SQL Server 2005 Express beta 2 (all on my local PC). I try to connect to the SQL server from studio using "Tools/Connect to database" selecting my local pc in the "Select or enter a server name" combo box. I can select my pc's name but when I try to expa...more >>

Hello. I have a stored procedure in which I'd like to perform an HTTP Get or HTTP Post. I know that I can write VBScript in a DTS Package and execute the package via the stored proc, but would prefer not to have DTS in the solution if possible. Are there tsql command that will perform http f...more >>

CREATE TABLE PhoneNumber ( PhoneNumberKey INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, CountryKey INT, AreaCode NVARCHAR(10), LocalNumber NVARCHAR(20), Extension NVARCHAR(10) ) We treat a phone number as a singular composite value, company wide. But some doofus thought i...more >>

I believe I have found a bug in SQL Server 2000's handling of the ISNULL function. Following is a description of the problem. I have an Orders table with a OrderID and some data. Associated with this is an OrderStatus table which MAY have records for an order in Orders. I created a view ...more >>

Hi. I want to select the following date ranges from a datetime column: between 10/1/03 and 11/15/03 and between 12/1/03 and 1/15/03 and between 2/1/03 and 3/10/03 *ALL IN ONE RECORDSET (SQL STMT) I couldnt seem to make the BETWEEN operator work on dates... Thank you!...more >>

My application will create a database for a user. The SQL server is on a remote machine. Currently the ldf and mdf files are being copied at SQLDataRoot path where SQL is isntalled but now my users want the files in the default directory. OK, I can use SQL-DMO and get the SQLDataRoot to figure o...more >>

We are returning data as XML doc. Some of the fields columns have NULL as value and when we use FOR XML AUTO these are omitted. Is there a way to substitute NULL for a concrete value before XML creation? So far we have tried this (but it doesn't work) SELECT Column1, CASE WHEN Column2 ...more >>

Hi, When I run this: osql -Usa -P -Q "Update myTable Set myXMLfield='<xml><tag1><tag1>1</tag1><tag2>2</tag2><tag3>3</tag3><tag4>4</ta g4><tag5>5</tag5><tag5>5</tag5><tag6>6</tag6><tag7>7</tag7><tag8>8</tag8><ta g9>9</tag9><tag10>10</tag10><tag11>11</tag11><tag12>12</tag12><tag13>13</tag 13><ta...more >>

Hi, I just tried to "Truncate" a table I've set up relationships on. = Although the data is in a state where this could be performed, sql = server complains: "Cannot truncate table 'myTable' because it is being = referenced by a FOREIGN KEY constraint." I can use "Delete From" in this case,...more >>

I created a script that queries a database does some checking and sends a mail. It worked fine when I tested it on my own email address, but when I run it programmatically to emails that come from the query, for some reason it went 5 times!! I'm certain that didn't make the other people very...more >>

I'm looking under the bonnet of our 3rd-party manufacturing system, and I've noticed that developers choice of design strikes me as a little unusual. If you take the Customers table, the design is as follows: CREATE TABLE [dbo].[Customers] ( [CustomerID] [nvarchar] (15) COLLATE SQL_Latin1_G...more >>

Hi, I'm designing a custom solution to export some data from one SQL Server to another. I'm just wonderiing if there is any methodology for resolving the "Primary Key" conflicts while importing. Table1of Server 1 might have some primary key value which is already present in the Table1 of Serv...more >>

I guess I should have read the small print in Books Online first..... After noticing the huge performance difference between ANSI standard UPDATE syntax and proprietary UPDATE FROM syntax, I decided to use the latter for a time critical system. After writing 323 procedures, totaling 7169 lin...more >>

I am checking three varchar columns to see if they are dates. If they are dates then I need to CAST them as datetime. How can I accomplish this? Thanks in advance Aaron Neunz ...more >>

Hi, I constantly run into problems when writing SP:s where I end up using dynamic sql. Here are two cases : Case 1 : I want to filter a numeric column using a commaseparated list, hence I want to write : select * from TransactionView where EventTypeID in (@filter) but that doesn't w...more >>

I was just wondering, couldn't the joins have been written as just JOIN instead of INNER JOIN and LEFT JOIN instead of LEFT OUTER JOIN? Also, does anyone know what the words INNER and OUTER signify? Thanks, Michael Culley ...more >>

What is the syntax for creating a user defined read-only role that grants select only priveleges to users in that role? TIA...more >>

Hi, I have a database that contains about 5 years of very conveluted data, and I wish to prune this back to about 2 years worth, ie -- get rid of about 3 years of data. However I am going to have to do this in stage as the transaction log keeps ballooning in size especially if I delete a...more >>

I would like to create an insert trigger which will export the newly inserted record to a file in C:\Temp. Is there a way I can export data from within a trigger? Thanks....more >>

I read text files in ASP on server side, then try to enter data into a database in SQL Server. The files are in ibm852 (1250) coding - at least that's the code page with which they are shown properly when putting the lines read on the output. However, when I enter them into the SQL Server (cur...more >>

I'm thought that I saw or read something that showed how I could return a text column or a concanted column depending on the value of another field in a select statement. See the following statement. SELECT ac.ServerID, ac.PolicyID, ac.Status, ac.CurrentValue, pi.PolicyText AS PIText...more >>

This is a little confusing. I have a table which has all the employee details like Name, Salary and blah blah. This table is supposed to be accessed only by the HR department. Of course, I gave permissions only to the HR department but there are many domain administrators (including me) who ca...more >>

Hello all, I have a specific sql statement and I am looking to return the result set a lot faster. Currently the setup is like so: myTable schema: Counter decimal 9 (pk) Machine varchar 60 LogEntry varchar 1000 Active varchar 50 SysInfo varchar 255 Idle varchar 50 IP varchar...more >>

Okay, I used XPSMTP along with the Web assistant to remind people of their birthdays. I first had the Web Assistant place a HTM file in a local directory and used XSMTP to attach the file and send it appropriately.Under the SQL Query Analyzer, this worked perfectly; but when I scheduled both ste...more >>

Hi, In my stored procedure, I want to check the existance of index in a table and if not, index has to be created. Can we use 'If Exists' statement? If yes, can you help me with example? Thanks in advance. Rajah V. ...more >>

Hi, how can I catch errors from EXEC(@strSQL)? I have following codes: ************************************ declare @strSQL varchar(8000) set @strSQL = 'some complicated query' EXEC (@strSQL) IF @@Error <> 0 GOTO ErrorHandler ************************************* I found @@Error is...more >>

Good day to you. We are currently experiencing a serious problem with a production system relating to SQL Server and ADO - This is a batch processing system running against SQL Server using ADO/OLEDB MDAC 2.8 to communicate. In essence the batch processing system is a COM+ component that is...more >>

Hi I need a very efficient solution to a problem that I am sure must be a fairly common one. I have a web application that is database driven. The behaviour of the web site is goverened by various database settings, for example font-type, font-size as well as access to functionality (this...more >>

I would like to create a SQL script that will disable all jobs that corresponding to a database. Next script I like to enable the jobs back on the correponding database. Please help me with this script. Thanks, Mark ...more >>

I had read on many sites, including microsoft, that using a table variable instead of temp table for smaller record sets would yield better performance. I preached this to all my developers, who decided to implement it. Such a replacement for a 5,000 record call, and it causes a timeout ...more >>

Hi, I am not sure if MYSQL is related to SQL... If I create an application in .net, can I use mysql instead of sql? my work wants me to use mysql instead of sql but I have never used it before. can someone reply with some links? thanks, jacked!...more >>

Hi Im want to do this in a stored procedure. Problem is, I don't know how to make a loop. Here's my code. Or is it possible to make it in another way? [code] sql="select del_id from Texter" set rs=conn.execute(sql) do until rs.eof sql="UPDATE texter SET Antal = (SELECT COUNT(namn) FROM ...more >>

Hi, I'm wondering if there is some way for me to see via the Profiler what the actual Transact-SQL is when the code is embedded in the application. The app is using jdbc and all that I see is exec sp_execute 'param', 'param', 'param'. I'd like to see what is being executed. Can I? Ple...more >>

Hi all, I have a database A. I take a complete backup of this database called a.bak. I have another database called B. Can I restore A's backup (a.bak) to restore database B without changing its name. So that I have two database A & B with same contents. I referred books online. ...more >>

I have a column of varchar datatype. It holds an projectID that companies can register for building projects. Since different companies have different rules for how their IDs are represented, it has to be a varchar. Many companies will use numbers anyway like 123456. Some will use 1234/7, so...more >>