I am working on a database that is going to store contact information. I am trying to normalize this area quite a bit because I want multiple object types to be able to have the same address. I have built a Company Entity and a Employee Entity. I also have ElectronicAddress and PhysicalAddr...more >>

hi, can we store the images in the database without using streams in vb.net? is there any way that i store the image without using stream. i want to store different image formats tiff, bmp, jpg etc many other types. thanks in advance...more >>

Hi, I'm using ADO to connect to a SQL Server. I want to execute a query which reads sensistive information and ensure that the results of the query cannot be sniffed on the network Can I enable encryption for a specific connection to the SQL Server without forcing either the client machine or...more >>

Hello anybody! Is this possible? I want to insert into a table using a variable field name? This is my query. Note the variable @txtCellNo in my Insert statement. Thanks so much in advance. ...... DECLARE @txtCellNo char(10) IF @intStatus_C1 = 1 Set @txtCellNo = 'txtCell_1' IF @intStatu...more >>

Assume we have two tables: Table 1 ----------------------------------- T1Id Name Value ----------------------------------- 1 Name 1 0 2 Name 2 0 3 Name 3 0 .... n Name 0 Table 2 ----------------------------------- T2Id T1Id Value ----------...more >>

Hi, I would like to ask for help for how to drop constraints within a table. For example, i have a table called A, i want to drop the constraints within the table while the table is not been dropped. Could anyone know how to write the syntax for dropping constraints by using stored procedure?...more >>

declare @i char(10) declare @j char(10) set @i='@j' exec ('select ' + @i) ...more >>

Is it possible to load, run a function from and then unload an external DLL from within a trigger. If it is - how do I do it? Carsten ...more >>

I need a query that will return the UserID of each user who belongs to RoleID 1 AND ONLY to RoleID 1 CREATE TABLE [dbo].[UsersXRoles] ( [UserXRoleID] [int] IDENTITY (1, 1) NOT NULL , [UserID] [int] NOT NULL , [RoleID] [int] NOT NULL , ) ON [PRIMARY] GO FOR EXAMPLE, with this data: ...more >>

Hi, Am a little confused as to which datatype to use. I have a field called UnitPrice which is used to store, as the name implies, the Unit Price of a particular item. I would be naturally drawn to use the Money or SmallMoney datatypes - in this case, SmallMoney as the money values we are...more >>

I am not sure if this is the best place to post this, but since I see the world through Sql Server and not access, I thought I would start here. I have clients running an Access 2003 application that holds shared locks in the database until the Access application exits. These held shared l...more >>

Hi All, We have a table in our db schema that we store email addresses (e.g. mitra@xyz.com). I need to query this table to return only the domain part of the email addresses, i.e. xyz.com, and get a count of each unique domain. I looked at the string functions and none seem to do the w...more >>

Hello All, I have a database with quite a few tables which depends on each other. I have defined relationships between the tables in the database. Now my problem is when i use a dataset (c#) to access these tables, i dont get any relationships/constraints in the table object. Does anyone ...more >>

I have 2 tables one called Agent, and AgentLocatordata. How can i import the data from AgentLocatordata into Agent? Is there a stored proc I need to convert the data if so can i get a sample. CREATE TABLE [Agent] ( [Agent] [varchar] (10) NOT NULL , [BusinessType] [varchar] (6) NULL ) ...more >>

I am trying to allow connection to my ASP page using Sql Server 2000. I have set up a login name "web" and password "web" on my development machine (not my normal Sql System). In Sql Enterprise Manager, it shows "web". In my default database, I have it set up as public,db_datareader,db_dataw...more >>

Hi, I hope we can find a solution to this. Here is the table and content sql code. create table ShoeSales ( orderid int, orderdate datetime ) go insert into ShoeSales values (1, '2004-10-04 12:33') insert into ShoeSales values (2, '2004-10-04 13:33') insert into ShoeSales values (...more >>

Ok, I've got a performance issue that I'd like some help on. All relevant DDL in attached txt file. The scenario is this. I have a table of Clients. Clients are assigned to sales people and an office. Clients have joborders. I'm looking to generate the number of distinct active clients each...more >>

Can someone please tell me what I am doing wrong? I would like to take data from a binary field and convert it into data. I have done this (Thanks to Mike). See code below. SELECT RIGHT(CONVERT(varbinary(8000), dbo.WebParts.tp_AllUsersProperties),1000) AS Data FROM WebParts ORDER...more >>

I have been asked to capture each and every sql statement submitted to the server! I have been told by someone who shouldn't know that it can be done by come kind of logging. I have not found anything that will do this, but perhaps it can. If it can I bet someone in this forum knows how! A...more >>

Hi, We have to test our database against data that changes once a month (i.e. interest payments, etc.). Our database uses "getdate()" to get the system time when doing a time calculation. For testing, I'm guessing the best thing to do would be to set the system clock to be a certain date, ...more >>

Hi, I have this "ShoeSales" table and I would like to count the amount of order per day. The result should be order by the "OrderDate" descending. This is the content of the table that I created. OrderID OrderDate 1 2004-10-04 12:33:00.000 2 ...more >>

Can someone give me a website to read up on learning how to write stored procedures? Thanks! mgm...more >>

I am new at creating querys, I would like to read one file to use as input to another file. If a match is made I would like to pull certain info and place it into a table. Is this possible?...more >>

Hi, just trying to produce more or less elegant way how to filter following ( see bellow ) but cannot. May be somebody fresh look on a problem can help me. Thanks. For the simplicity reasons let's assume a table, in reallity it is a join. CREATE TABLE ( [in] int PRI...more >>

Is Windows CE the correct choice for developing an application which will be used on a laptop type of device while disconnected from the main database (SQL Server 2000 Ent. Ed.) but when connection is available will connect and synchronize with the master database (bi- directional upload/do...more >>

I have a query that looks like this: SELECT distinct project, site, building, dept, division, user_id FROM OracleImportFile The data looks something like this: project site building dept division user_id 15654 NULL NULL 6281 NULL 18328 15655 NULL NULL NULL 37 NULL 15655 NULL 249...more >>

I have a table that has customer info, in this table there is field city and I am trying get total customer by city. Its very easy to get the result. But the city are spelled wrong for ex. HOUSTON, HOUASTON, DALLAS, DALLLAS. select count(*),city from customer group by city So, when I ...more >>

Is it possible to find the names and datatypes of columns returned from a stored procedure without having to execute it? Matt ...more >>

I need to edit an existing stored procedure so that it will find the correct records. This is the current SP that is being used by the facility for reporting. CREATE PROCEDURE spTKMemo @Status varchar(15) AS SELECT TOP 100 PERCENT E.EmpID, E.EmpFName, E.EmpMName, E.EmpLName, EP.Emp...more >>

I am creating a new table and want to add a hyperlink data type. What type of data type do I pick and how to make this work so when I user click on a value (http://www.microsoft.com) the hyper link open IE? Thanks......more >>

Hi, friends, I created a stored procedure like the following: CREATE PROCEDURE dbo.sp_GetUsersInfo @userIDs varchar(255) AS BEGIN SELECT u.userID, u.loginName, u.fName + ' ' + u.lName AS fullName, u.phoneNum, FROM users AS u (NOLOCK) WHERE u.userID IN (@userIDs) EN...more >>

hi, I have a dts package on sql server 2000 server, that populate an excel file and send it out to users as attachment. lately, users were able to received the file through email, and when I try to modified the user list in 'send mail task', I receive this error: 'error source: micr...more >>

Dear Experts, Usually in QA we would create a table Person as: Create table Person (Firstname varchar(30),LastName varchar(30)) But if I'd want to create table with Column Name from a variable such as: Declare @firstname varchar(10),@lastname varchar(10) Set @first = 'Firstname' Set @las...more >>

Hi, I am trying to order the BirthDate in Northwind database. My select statement is based on grouping the birthdate and counting how many of the employees are born the same date. use Northwind select convert(varchar(50),[BirthDate],101) as [BirthDate], count(*) as [Count] from employees ...more >>

From Access I am calling a sp to insert a record, but I get an "The precision is invalid." error. If I call the sp from query manager with the same values it works. I've compared the data types but don't see what is causing this. My sp is defined as: (@SR_NUMBER [int] , @ESTIMATE_TY...more >>

Hello, I'm converting some old informix stored procedures to SQL server and I'm stuck with this : Is there anyone that can help ? thx! create procedure p_opname @pat_nr char(10), @dos_nr char(8), @brief_type char(2), @datum datetime as @huisarts_nr char(6) @wa...more >>

when I save output or direct output to a file of any type (.rpt, .txt, .csv are the ones I tested) from within sql server management studio, the output is always unicode. how can I change it to ansi text instead? Thanks in advance! .. ...more >>

I wrote C++ dll with 5 extended stored procedures: xp_AnalyticalIntraday,xp_openAnalytic,xp_closeAnalytic,xp_progressReport,Xp_setAnalyticStatus. I also wrote sql batch file for this procedures debugging with sp_addextendedproc for all procedures,typical calls to all stored procedures,sp_drop...more >>

Hi, I am new to stored procedures and I am trying to get a feel on the capabilities of SP's. I have a table that has a description for each item in the table. I am wondering if I could design a SP that will take the keys it is passed and loop through the results and produce a string of the d...more >>

Hello all, I have a database with 600mb, but the database log be with 2,5gb. I need to diminish the size this database log. I try BACKUP LOG MYDATABASE TRUNCATE_ONLY, but the physical size don't diminish. Somebody know to make this task? Thanks Dexter ...more >>

we have client who executes SP on our server; but the client sets SET TRANSACTION ISOLATION LEVEL SERIALIZABLE before the client execute the SP on our server. The problem is that, it locks other users. How can I tell SQL Server to ignor the isolation the client sets. Thanks in advance. S...more >>

This expression is returning 1, I was expecting a 0. Does anybody know if this a bug? select isnumeric('32D-3255')...more >>

Hi, I want to ignore any errors when I execute some queries with sp_executesql. I'm using sp_executesql in a storedprocedure. I'm using this to manage some indexes, but if my index allready exists, sp_executesql failed and my entire storedprocedure failed! I CAN'T create some code to test i...more >>

Is it possible to export a table to a MS Excel file using a stored procedure? Thanks, helen ...more >>

We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Code = 800a0c93 Source =...more >>

I have table user: userID,userName,countryID I would like to select all users from the same country as @userID, which I insert into SP: SELECT * FROM user WHERE countryID=(SELECT u.countyID FROM user u WHERE u.userID=@userID) This works but I think that the same could be done with some...more >>

I'm stumped on a piece of modeling and am hoping someone can help out. Some folks came to me with a _spreadsheet _ that lists the operating hours of their locations . It looks like this: LocationID, SundayOpen, SundayClose, MondayOpen, MondayClose, etc. 0987, 11 AM, 5 PM, 8 AM, 10 PM, etc. 0...more >>

If I print the value of @everything(sql statments are below), the output is correct. If I copy and paste the output of the Print @everything, everything runs successfully. If I try exec @everthing, I get the error below, it appears to be adding a singe quote to the beginning of my @every...more >>

Hello there, Could anyone please help me find any alternatives to track this: I have a stored procedure on a SQL 2000 SP3a running on a Windows 2000 Server SP4 with a query which access several tables located on a SQL 7.0 (with the appropriate service pack) running Windows 2000 Server SP4...more >>

hi , while executing this SP below create proc hellotest @ret varchar(255) output as select @ret = 'hello' return @ret I am getting error like ********************* Msg 245, Level 16, State 1, Server BLR-SPRAKASH, Procedure hellotest, Line 1 Syntax error converting the varchar value '...more >>

Does anyone know if it is possible to use the "For XML" option of SQL Server queries inside of a UDF? If yes, can you send me an example? I tried the following and got an error message (Line 10: Incorrect syntax near 'XML'.) It works if I remove the "FOR XML AUTO" line. And the select quer...more >>

Hi, My stored procedure was working fine but when i made added 2 fields to my table and then made subsequent changes to my sp it give me the following error: "Could not complete cursor operation because the table schema changed after the cursor was declared" it works fine from back end (...more >>

We are converting from Access to SQL Server. One of the function that Access query uses is the following: Format(colA,'#.00###') For ex: -if colA = 0.7, the result will be 0.70 -if colA = 123, the result will be 123.00 -if colA = 6.945, the result will be 6.945 I can do the same thing in...more >>

I'm trying to sort out a way round the problem with distributed transactions and loopback linked servers. Can anyone tell me if having my linked server to a second instance of SQL Server 2000 installed on the same machine will work? All the stuff I've read defines a loopback linked server as one ...more >>

Hi I was wondering if anyone knows why this is happening. * I detach or take a database offline * Make a copy of that database files and copy to a different directory on the same server * Attach the original database * Rename the copy and attach - when doing this it still points to the or...more >>

I have a problem and a solution that works. My question is whether there is a better solution. Problem #1 I have a bunch of stored procedures that are used for reporting. All of these stored procedures use the exact same filter, which consists of a list of about ten parameters and a where c...more >>

Is there a way to prompt user for input in sql?...more >>

Hi , I want to return a string (varchar) fro am SP . The SP is like create proc hellotest @ret varchar(255) output as select @ret = 'hello' return @ret while executing it i am getting the following error . ******************************** Msg 245, Level 16, State 1, Server BLR-SPRAKASH,...more >>

I am using this procedure from net for getting the restore script. It lists the latest full backup file name and logs after that point. Is there any way to modify this script to take either date or time as parameter and give the files for restore? I need this if I need to restore the data up to...more >>

I have a problem with a view, my view is used to rerieve data from another db. when i run this query: SELECT a,b, c FROM myView IT will get the data successfully, third column is nullable but still i have populated it. But i run this query: SELECT a,b, c FROM myView WHERE c LIKE ...more >>

hello i have 1 table which have an image column Table1 (id1 int, imCol1 image) i want to update the contents of a record with the ones of another record this is what i do : EXEC sp_dboption 'dbName, 'select into/bulkcopy', 'true' DECLARE @ptrval binary(16),@ptrvalTarget binary(16) SELE...more >>

I woule like to write syntax in store procedure for the following request: 1) check whether the Alliance table had been drop 2) if the Alliance table had been dropped, the Bonus table is required to be re-created Can anyone help me out for how to write the syntax for it? Thanks...more >>

Hi, Is there any way to delete duplicate rows from a table making one remain in the table. Tell me if its possible through SQL Queries rather than Stored Procedures. Thanks & Regards Jiju Joseph...more >>

Can anyone help me to write out the syntax (in stored procedure) for the following request? I want to perform "duplicate" checking for rd_awards table before dropping the existing rewards_catalog_product_cs table in web database If gift code "duplicate" exists, rewards_catalog_product_cs table...more >>