Hi, I am developing a module in VB with the following functionality. I have 3 database backup files which must be restored in the sql server 2000 machine. Then I have to create a sql server login and associate it to the databases as user for them with dbo role. I am using ...more >>

Can I freely download a Setup for SQL 2000 Server Client Tools only? ...more >>

Are they compatible? Thanks ...more >>

Can anyone tell me how to create a view in SQL of my table in Oracle using linked server? I have created a linked server to an Oracle database and I want to create a view in my SQL DB that shows a table from Oracle. I can query the Oracle table thru the linked server, but when I try to save...more >>

Hello: I have a table T1: C1 | C2 ------------------- 0.0750 | 0.8 0.0040 | 1 I create a query: SELECT C1,C2, C3 = CASE WHEN C2=1 THEN C1*1.4303899 ELSE C1 from T1 I get the result: C1 | C2 | C3 ----------------------------------------------- 0.07...more >>

Hi to all SQL Gurus, I am checking up the mechanism to be followed for SQL Server 2000 database migration\creation from one location to other. As per me there are two approaches for doing this: 1. Copy the contents of all the sql source (script) files into one single file, and then final...more >>

Hi to all SQL Server gurus, I am working on the common template to be used by all the developers working on a project. What i know is the settings for the following parameters play a important role while creating and invoking a stored procedure\function: 1. SET QUOTED_IDENTIFIER ON|OFF ...more >>

The problem I run into from time to time is when I use floats to store = numbers they are slightly off. For example, I execute this statement: update mytable set myfloat =3D 3.5 where myid =3D 1 Then execute this query: select myfloat from mytable where myid =3D 1 and I get somethi...more >>

Hello: I notice that when I use checksum functionality to determine the checksum value associated with a result set that was created with multiple joins, "0" is returned. For example, the following statement yields "0" even though I have verified that a result set is definitely being return...more >>

I'm trying to join three tables with outer joins and I'm note getting the expected result. In the Northwind database I added a new Employee without Territories (Insert into dbo.Employees (LastName, FirstName) values ('Smith', 'John')). Then, I select all Employees having or not (right join) an a...more >>

Hi guys, I have a table of categories (see below) that has a lot of other tables related to it. Now I need to change this table to contain more categories in other languages. Should I create a new table that contains translations of each language and related to a definition table, or is there ...more >>

My sample code: CREATE PROCEDURE dbo.BulkInsertFile @File varchar(150) AS DELETE from TempFile declare @sql nvarchar(2000) set @sql = "BULK INSERT TempFile FROM " + @File + " WITH (fieldterminator = '\t', rowterminator = '\t\n')" exec sp_executesql @sql GO ===========================...more >>

Necesito saber cuantes concatenaciones puede soportar una cadena string en SQL/Server, me queda claro que un campo char o varchar pueda tener mas de 5000 chars de longuitud.... pero otra cosa sucede si el numero de items concatenados es mayor a 76 y la suma de todos ellos (en longuitud) e...more >>

this is working ok: SELECT CONVERT(SMALLDATETIME, GETDATE()) + CONVERT(INT, 1) this is *not* working: SELECT CONVERT(SMALLDATETIME, GETDATE()) + CONVERT(INT, -1) Server: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expres...more >>

Hi I would like to know if MS SQL SERVER 7.0 & MS SQL SERVER 2000 can run on Windows XP operating systems ? Thanks Kaushik...more >>

Dear Sir Or Mam I would like to know if MS SQL SERVER 7.0 & MS SQL SERVER 2000 can run on Windows XP Operating Systems ? Thanks Kaushik...more >>

Scenario: I have the following table with following data. create table sourcetable ) item varchar(10), size varchar(10), color varchar(10) ) insert into xdel values ('001', 'size1', 'red') insert into xdel values ('001', 'size2', 'red') insert into xdel values ('001', 'size3', 'red')...more >>

We are developing a shrink wrap planning application using SQL Server as our data store and are considering alternate designs to optimize performance. Here are some application details: ==> Conceptually, the product is quite simple; the end user enters/imports data to create a model describing t...more >>

I would like to access tables & views in database "Gas" from database "Station." Can you please give me an example of the tools/sql syntax in doing this? Thanks Bill ...more >>

3 tables -------------------- TableA TableB -- 1 to many --> TableC what is TSQL for following steps ? ================================ For each record in TableA if matching record exists in TableB then remove related records in TableC remove record in TableB copy record fro...more >>

Hello All, My current table looks like the following: Student_ID Course_Title Stu_A VB Stu_A Database Stu_A C Stu_B Marketing Stu_B ...more >>

Hello, I have a table with say 2 columns id and parentId, and when i delete an element from this table with a particular id, i recursively need to delete all the elements for whom parentid is the specified id. create table recur_table ( id integer not null primary key, parentid ...more >>

I have a stored procedure that calls another one using the exec command. I need to return a value from the inner procedure all the way out. How do I do this? -- Jonathan Blitz AnyKey Limited Israel "When things seem bad Don't worry and shout Just count up the times Things have wo...more >>

collation compatible This option should be set only if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server. ------------------------------------------------------ how do I know if two of my servers have same s...more >>

Hey, I have a stored procedure I have written that takes in 2 dates anda calculates the number of working days between them. I would like to use this within another stored procedure. SELECT userID, sp_WorkingDays(employee.HireDate, Employee.TerminationDate) FROM Employee But I can't cal...more >>

Hi, We have an application which is heavily SQL Server based and which should support multiple concurrent users who can add, edit and delete content. We are finding that all our SPs use 100% CPU and seem to run at normal priority on the server, so if 2 users try to perform an operation at the...more >>

Hi! I'm trying to do something like CREATE FUNCTION dbo.test1(@DateParam datetime) RETURNS TABLE AS RETURN ( SELECT 'some select statement here' ) GO CREATE FUNCTION dbo.test2(@DateParam datetime) RETURNS TABLE AS RETURN ( SELECT * FROM dbo.test1(DATEADD(day,1,@DateParam)) test1 ) ...more >>

Hi, I need to monitor the updates that were made on some development databases that are used on the company i'm working for so that we can audit the changes that were made on the databases. I've tried to get this information from the sysobjects and on the syscolumns tables, but i need ...more >>

I tried to run this query: SELECT Start, DATEDIFF(ss, Start, MIN(Timestamp2)) / 3600.0 AS Hours, ProcessorID FROM (SELECT MAX(a.Timestamp) AS Start, b.Timestamp AS Timestamp2, b.ProcessorID AS ProcessorID FROM tblEvents a INNER JOIN tblEvents b ON a.ProcessorID = b.ProcessorID ...more >>

I need to execute xp_cmdshell in a trigger and pass a command line parameter to the .exe program i.e. I have a .exe program c:\program files\savedata.exe In the trigger I have a parameter @Id bigint I need to pass the parameter @Id to savedata.exe In the trigger, I am trying EXEC mas...more >>

I am trying to use a column alias in a select statement. e.g. USE pubs SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies', 'Copies'/2 FROM titles and receive the error message: Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 'Copies' to a colu...more >>

I have a problem with direct recursive triggers. My SQL Server configuration has recursive triggers enabled (sp_configure tels me this): name minimum maximum config_value run_value ----------------------------------- ----------- ----------- -----------...more >>

can anyone point me to references on t sql techniques to avoid writing cursors ? tia doyle...more >>

Hello, View can be linked to external data source, such as system table, system functions, and etc. I was wondering whether the view can be linked to a passed-in parameter or a database table, as I want the user to only retrieve a portion of data to view? Code Snippet: create view vTest a...more >>

Hello, Our system has experienced time out issues with on of the SQL Severs. We have an ASP.NET application that calls a stored proc, which returns a result set. This stored proc all of a sudden started to time out on Friday after noon. I was able to execute the same procedure in Query Ana...more >>

I would like to use a variable with the SQL Select clause IN() This works fine Select * from customer where state in('OH', 'KY','TX') This does not work declare @parms set @parms = 'OH' + ',' + 'KY' + ',' + 'TX' Select * from customer where state in(@parms) Select @parms returns a field that look...more >>

Hi everybody, I'm using SQL Server 2000 Enterprise Edition SP3, and I've found a strange behaviour of equals operator. Reading MS documentation about equal operato (=) you can read the following sentence: "If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, ...more >>

Does anyone know how I can set a variables value in a stored procedure to the result set of a sql statement that was created dynamically? For example - when I hard code the SQL this works: Set @var1 = (SELECT COUNT(DISTINCT recs) FROM table) But, I want to build the table name dynamica...more >>

I have a "Logs" table in my database, whose increases rapidly with daily transactions. There's a datetime column, and some other information column in that table. Sometimes, the application layer programs will query the "logs" table, but mostly for records within 15 days. So it might be a ...more >>

I want to execute SQL script from a file trough my application. Can I do it somehow using some stored procedure or SQL command? ...more >>

Hi, Is it possible to find out when was the last time that somethings get insert/update records in database? I need to know this time_stamp. we have this old database,and I'm not sure if it's obsolete or not. So I want to make sure that no one has touch it for long time. ...more >>

Hello. We rename some objects from a db using sp_rename. We then try to use DTS to copy the db to another database. It is not the first transfer that we do between those 2 db. The objects that we renames lets say from 'objectname' to 'spobjectname', are recreated in the new db as 'objectna...more >>

Hi SQL Gurus, I still have questions about COLLATION, pls help.. : 1) During Installation of SQL Serv2000 Personal Edition on Win98, I don't see any Windows Dialog to give me chance to determine what COLLATION to use as Server default. Where is it ? What about during Std Edition Instalation...more >>

HI All: I want to restore a database (it 's a backup file generated by = EnterpriseManager) to Sql Server. But my assistant doesn't know how to do it.So i want to write program to = do this job.How can i control EnterpriseManager in program or it needs some SDK to do this? thanks for an...more >>

Hi, How to deletes files in a store procedure? Thanks. ...more >>

I have a stored procedure with many parameters used for multiple purposes and I can't change to procedure, it is under someone elses control. Using visual studios .net I would like to update 4 of the 21 parameters and leave the others untouched. I have created the sql command which refers to...more >>

Hi, all: This is probably a simple problem, but, as an SQL newbie, I'm having a little trouble understanding multi-joins and subqueries. I have the following tables and columns: MemberTable ----- MemberID (primary key) MemberName Address Country FoodsTable ------ FoodID (prim...more >>

Hello. We are contracting a devloper to provide a web-based screens for a hosted SQL Server. The hosted site has unlimited licensing. We are looking to incoporate normal secure web-based data entry/data retrieval/data vewing screens through any browswer. The screens will be reached by login from ...more >>

You are right, we just used it something like "ticket_nbr". I described it as [ID] just for convenience here. So what's the easist way to make it consecutive and reliable (not jumping). >-----Original Message----- >>> When I create a identity colunm named as [ID] in one new table, .. <...more >>

Hi! This error recently occured at vb front-end which generates T_SQL statements. I work properly (at least I didn't have problems by now) with quotes. I have them all doubled in string when I create T_SQL statements. However, here I store encrypted string and it has some funny characht...more >>

Hi all, Where would be the best place to create store procedures that are used for administration? Like a sp_who with a diferent display? Why? Of course not in master right?...more >>

For instance: IsNumeric('1000000,1000000') returns 1 instead of 0. IsNumeric('100000,100000') returns 0, as I would expect. Is there a rule here? Burton Roberts ...more >>

Hello I am executing an 'osql' command and that command requires the -S which expects me to provide the server name. I removed the hardcoding of the servername and substituded the @@Servernave variable and then the 'osql' command fails. When I place some print statements in to find out what valu...more >>

Hello! Was wondering how I can create a copy of one database? FYI I'm not interrested in the data, just the fields and triggers, and so on. Which way is the easiest? Do I create a script and run it from there? Is it smart to have it inside a SP? Any idea would be greatly appreciated. I...more >>

I have a table with 1 column. using bulk insert i inserted 2 million rows.I created identity column and created clustered index on it. When i try to access the table though VB Application i get identity column is not defined. Through Query Analyser it takes for ever.Finally i have to stop...more >>

I need to pass parameters back to SQL Server but I need to prevent anyone running SQL Profile from seeing the parameter information. Can anybody help, please?...more >>

Is create clustered index executed immediately or it is executed as background task. appreciate any help Thanks...more >>

Hi, I've generated an delete trigger on a table that is frequently accessed by a number of different applications. My aim is to capture any deleted data & place it into another table for later inspection. I have no problem in capturing the data from within the deleted record, but am unable to c...more >>

Hello: I have two tables ELEM and ELEM_DATA_TY. ELEM references ELEM_DATA_TY. I have some business rules that need to be applied that may change the value of inserted rows. So, I resorted to using an INSTEAD OF INSERT trigger. The problem is, when I try to insert a row into ELEM with ...more >>

I have what I hope is a simple question -- I have a large transactional table that I need to extract data from. In each extraction, I need to only extract the transactions I have not extracted up to that time. The other system requires a unique ID, and wants to be able to specify the "Last T...more >>

Hi, This might be already asked Question. i am trying build string depending on conditions in Stored Procedures. But i have few parameters which are string type when i tried concatinate them and also with single quote problem and string concatination. code: declare @sqlstring varchar(1000) if (@r...more >>

Why dosent this work declare @dbname varchar(40) ,@sql_exec varchar(1000) set @dbname = 'scbdorn' set @sql_exec ='IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name ='+ @dbname + ')' + 'DROP DATABASE [' + @dbname + ']' EXEC (@sql_exec) SET @sql_exec ='CREATE DATAB...more >>

Hello I am attempting to run osql procedure and I am getting a "Not associated with a trusted SQL Server connection.". I have been told that I need to grant login and dbaccess to the user via sp_grantlogin and sp_grantDBAccess, but this user already has a login and access to this particular databas...more >>

Anyone have a sp that creates a db and take in the name. Have tried this dosent work someone? declare @name varchar(40) set @name = 'blabla' IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @name) DROP DATABASE [@name] CREATE DATABASE [@name] ON (NAME = @name +'_Data'',...more >>

Hi, I need to make a stored procedure that would be called from a C++ program. It would have one table name as in parameter. The procedure would have to check if the table passed to it from the C++ program has relation tables. Then if it does it should delete the relation tables from databas...more >>

Hi, Second time i'm typing this question.. Is there a way to get the createdate of a table with Transact SQL? Thx, Hennie...more >>

Hi all, in my application I nedd to access to one main database (database A) and to a single table of a secondary database (database B). Both databases resides under SQL server 7.0. May I include a link to the database B table in the database A as can be done in Access ? thank you for you...more >>