Hello, Continuing with my "Offset" project, I came upon a table with constraints, namely, a foreign key constraint. I would like to move just the ID by an offset instead of all the data in the interest of simplicity. Will the following work? ALTER TABLE foo DROP CONSTRAINT FKfoo [...]...more >>

Hello, I need to tackle a big migration process from an old database schema to a completely new one. As part of the migration requirements we need to divide this process in two sub-processes: 1) To copy and translate the old data into a new temporary database´s submodel 2) To select the d...more >>

I'm working with SQL 2000 server on Windows 2000 server. I'm searching how to get the data directly from the system. In my actual configuration the data running in SQL is not the same than tha data of the system (I have tried to change the system data but in SQL I got another one). Any ideas ?...more >>

Is it possible to give create or alter permissions to someone who you do not want to be a dbo? I have a group of users who I want to allow to create and alter their own procedures, but I do not want to give them dbo. thanks. ...more >>

I am building a form builder that allows you to make dynamic forms. I am trying to make pre-selected questions. One of them is: Choose the Store nearest you. So I add the question in the first insert and get the identity to add in the FormCreator_QuestionOptions in the second table. Here is the ...more >>

I have a database with names in their original language. The collate settings is SQL_Latin1_General_CP1_CI_AS. I would like to be able to read the names such as "Renee" with the correct characters, e.g. an "e" with an accent, however in the results window only shows then these as weired charac...more >>

How do I set a field to be Integer (32-bit) field. ? A ...more >>

I have a working query that displays a list of names and emails. I'm trying to move the email from the old table to the new one. Does anyone have any ideas on how I can do this? I tried changing it to a nested query, but i couldn't get it to work. I've also had trouble finding examples of how peo...more >>

Within a store procedure I would like to pump up the isolation level to serializable just for one particular transaction and restore it back to the previous level when it's done even if there is a fatal error, would someone know how to do that? Thank you very much Here is what I thought the ...more >>

when utilizing ADO.NET in vb.net or c# ....... is SQLDataReader.GetBoolean() the best way to retrieve a SQL-Server database column of datatype bit ? i want to assign it the result to a vb.net Boolean variable i seem to be getting strange results what is the recommended method ? ...more >>

I'd like SQL Server to email me on job failures. I've setup a profile through the mail icon in control panel and tested it successfully. When I go to the Operators area under SQL Server Agent and click the "Test" button for "E-Mail name:", I get this error: Error 22022: SQLServerAgent Err...more >>

Hello I was wondering how to do an insert like this Have this: Rank Foo 1 A 2 B 3 C 4 D Result: Foo1 Foo2 A D B C Foo1 = SELECT TOP 2 Foo........ ORDER BY Rank Foo2 = SELECT TOP 2 Foo........ ORDER BY Rank DESC...more >>

Hi, I have a nvarchar field, in there I store an 8 digit code. I need to bring up only the 4th character in that code, how can I select it ? Table Name = Table Field = Code Select * from Table That will bring me the code field and display all characters, I only need character numbe...more >>

Dear all, I want to monitor which user to modify a record.So when a user login to system,I record his userID.If I set the monitoring code in TRIGER,I must pass the userID to TRIGER.at actually, Triger can not accept this value. Which method can solve my question exclude using SP? (I al...more >>

When I view job history in EM, there is a column titled Run Duration. It is in the format of 00:00:00. What exactly is this format? hh:mm:ss or mm:ss:?? For example, what do the following mean? - 00:00:01 - 00:01:00 Thanks, Brett ...more >>

Yesterday, I created a stored procedure that adds to or updates different tables depending on a flag. It's not any more complicated than other stored procedures I've created, but in this case the changes are being rolled-back. When debugging the procedure under Query Analyzer, I can see the ...more >>

Hi all, I've just discovered the following article on MSKB saying that SQL Server 2000 doesn't support calling .NET stored procedures via SP_OA* methods. http://support.microsoft.com/?kbid=322884 I'm a little puzzled. I've been using this method for serveral months with no problems. Basi...more >>

Hi, I need to randomize the record selection. I am thinking of using NEWID function and do ORDER BY NEWID(). Is there any performance hit.=20 I am afraid NEWID using artificial delay to let timer tick and using the = timer function to come up with unique new id for every record. Thanks ...more >>

Whenever a new DB is created a script with several UDFs is run and the UDFs are added. All is done from inside my application. This has worked in a few hundred installations but I have run into a problem in a couple of cases. When the script is run I get the error "Must declare the variable @...more >>

Hi, It is a long time that I have been using my box and have some .NET apps running on it which use a connection string with Server=localhost and I have not had any problem yet. Today I have updated my box Windows with recent HotFixes (well I am not 100% sure it is the reason of this probl...more >>

Hi, I've written a set of SP to build a log shipping pair without having the server linked (also, can't use the built-in functions, I'm using SQL std-Edition) My question is : Where should I store the stored Procedure knowing that I really want to follow the better possible practices. Is...more >>

Hi, I am working on a SQL Server 2000 DB on a shared server. I need to make some changes and want to make sure users are not performing any operations. The client is a web application based on ASP/ADO. Normally I would detach the DB. But, I don't have permissions. Any other ideas? I was...more >>

Making my first attempt at querying a SQL field which contains Chinese characters... in initial testing, I found that I could do a simple wild card query, just as I might for standard Arabic characters: SELECT * FROM tableName WHERE fieldName LIKE '%荷商荷%' I'm hoping to use a stored...more >>

Hi, I have a very complex dynamic SP that is executed by sp_executesql. I been trying to find how I can view the executed SQL in SQL Profiler but to no avail. Any hints on which event and column to choose? Thanks Christian ...more >>

Are there any examples or related articles? ...more >>

Hello, Have created a stored procedure that will allow a user to enter a full or partial customer name (for example: for the Company ACME Inc., the user can search for ACME and it'll find this customer). I am using the LIKE Command but I am not getting any results, the Column is of type ...more >>

Hi, This is the finished script from one of my previous posts, but this time I have another problem. When I run the script from query analyser everything is fine. However when I try to run it as a scheduled job it failes with te following error: Executed as user: ALRO\streamsrv. Invalid ...more >>

Hi, I am trying to run a dynamically generated SQL string from within a stored procedure with sp_executesql. I have to run two separate queries both generated dynamically. The first time I use sp_executesql it runs the query fine. The second time I use it sp_executesql does not run the...more >>

We have VB program who queries data (using stored procedure) from a SQL Server 2000 db. The database can grow as much as 10 million records. When the user queries data, the data returned to the user varies between 10,000 records to 500,000 records. The data is returned to the user through a so...more >>

Hi all, Does anybody know of a way to perform UTF-8 conversion as part of returned SQL statement from an nText field? I'm dealing with a non-Unicode client application that needs to retrieve Unicode data and it appears easiest to pull the data out in UTF-8 pass it through the client system ...more >>

I would like to remove an unique index from a table and add a new index to a table using the same column from the first unique index.. Every time I try to add a new column to the existing unique index or try to delete it to make a new index, I get this error: Server: Msg 1907, Level 16, Sta...more >>

I have a column that has data with a /store and /shop in it: Like Westfield Bondi Junction /strore and Silom Complex / Shop Is there any way to remove the /store and /shop in sql before I build a dataset? -- Dave ...more >>

Hi i need to pass as parameter the rows number to return to a storeprocedure, i wrote: CREATE PROCEDURE cev_find_eventi @start datetime='1-1-2000 00:00', @end datetime = '1-1-2000 00:00', @num integer = 500 AS SET NOCOUNT ON SELECT TOP @num * FROM eventi WHERE data>=@start AND data...more >>

Dear All we have windows2k server and windows2k pro client, we can able to access the network on both the system but not able to share the printer, now the printer installed on win2k pro client PC the same we want to share with win2k server please give some idea how we can solve this issue. ...more >>

Hi, SET @cmd = 'USE ' + @names EXEC (@cmd) This doesn't work, but is doesn't give an error either. How can I change databases in a job? (See script below) USE master CREATE TABLE #DATABASES ( DATABASE_NAME SYSNAME, DATABASE_SIZE INTEGER, REMARKS VARCHAR(254) ) CREATE TA...more >>

I have an application in VB.Net that uses the SQL DMO object. When I try to get the list of available servers, I get the error "QueryInterface for interface SQLDMO.Namelist failed". I researched this issue and the problem seems to be with the SQL Server version (requires SP2 or higher for this...more >>

I have table of data (tblReport) and a lookup table (xTblReportTitles) that link through tblReport.titleID = xTblReportTitles.xReportID. From tblReport I need to get sums based on specialtyID; both the specialtyIDs and sums t may change at the time the report is run, but I need sums for each di...more >>

Hi All, It seems very simple and I have spent too much time on it to figure out. I have a small table (for phone #). I want to select all records where the extension is not null and the location is NC. However for one extension number I have more than one records. here is DDL: CREATE ...more >>

I'm wondering if there are any other programmers/DBA's out there that have lots of databases that they need to routinely monitor its file growth over time. I'm looking for any VB code or scripts that accomplish this. We have about 500 SQL Server databases on one of our servers and they exten...more >>

Is there a way to check when the index was created? SYSINDEXES table does not hold a create date....more >>

Using a sql server connection to text file transfer, I want to end each row with a Pipe CHR(124), carriage return CHR(13), and a line feed CHR(10). How do I do that? I'm building the package with the GUI, not code. ...more >>

A very simple question, but I don't find the answer. Maybe someone else ? Is it possible to use boolean functions in sql ? E.g. select F_BOOLEAN('userid','subsidiary') from dual with F_BOOLEAN a function like ...(in_userid IN string, in_as IN string) RETURN BOOLEAN. TOAD 8.0 and Business Obj...more >>

Here is my current query: Use WinPayment GO SELECT pos_condition_code, convert(char(11), retrieval_reference_number) RR, message_type, authorization_identification, convert(char(8), card_acceptor_identification) SN, convert(char(25), transaction_name) Tran...more >>

Hi all; Is anyone know if SQL Server 2005 supports VDI (Virtual device interface)? SQL server 2000 supports it, so the main question is a matter of backward compatability. Regards; Dror...more >>

i made new user (test ) with the database role public on ONLY Northwind db when i login with this user and make a select statement to tables in the Pubs db , it's done ok eventhough when i check for the users with the pubs db , he is not there , it's only reside in the users of the Northwind...more >>

Hi to all! at first sorry for my bad english :) i have one very big problem, all searches over internet don't give any results. there is a piece of code from stored proc: exec @retcode = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @local OUT,1 if @retcode<>0 BEGIN EXEC sp_OAGetErrorInf...more >>