I have full backup of database at 13:00 and another full backup at 17:00. I've made backup of transaction log at 17:05 When I try to restore database to state at 15:10 (point in time) , the dialogue in Enterprise Manager says that only time after 17:05 is valid. It seems to me that I'v...more >>

Hi, I executed the following query in Northwind: select c.* from customers c join orders o on c.customerid=o.customerid There are original indexes available on these two tables: 1) Clustered (Customers.CustomerID) 2) Clustered (Orders.OrderID) 3) nonClustered (Orders.CustomerID) The...more >>

Is there a more readable alternative (syntax) for this below? (@sPass and @sPassword are nvarchar(20)) IF @sPassword COLLATE Latin1_General_BIN <> @sPass COLLATE Latin1_General_BIN SET @success = 0; ...more >>

Hello, Currently I'm trying to make PERMANENT changes in a sqlserver database INSIDE a tranaction. Via the site: http://www.sqlteam.com/item.asp?itemid=2290 a have made a simple test which works fine. Now I wonder how to retrieve a value after the sql statement has been executed. For examp...more >>

Dear all, How can I get a "sequence no." column for each row in select result? The following is what I want, [Original] Name Birthday ------- --------- ABC 11/06 DEF 08/18 GHI 12/25 [Desired] Seq. No. Name Birthday --------- ------- ---------...more >>

Is SQLDMO.dll redistributable? Also, is it included with MDACTYP.exe? ...more >>

Sorry if someone has already answered this, but I cant simply find the answer in today's postings, so here is my question: I am trying to combine two sql statements to get just one result, right now I have: SELECT Count (ActivityId) AS ActTotNum FROM dbo.Activities WHERE CaseId = MMColPar...more >>

Hi, I would like to create a trigger that fires when i delete an item from a table. This trigger must that the said records and place them into another table. Please help Thanks MP ...more >>

Hola a todos, me gustaria saber si alguno sabe como obtener mediante SQL los nombres de las columnas de una tabla Es decir me saque un reporte como el siguiente: select nombrecolumna(tabla.*) as columna from tabla y el resultado sea asi columna --------- clave nombre apellido saldo ...more >>

I have a opened date, and a term field. I would like to calculated the number of terms that has passed for a particular accounct. For example: opendate = '02/15/2004' and Term = 2 months Result should be 4. For example: opendate = '02/15/2004' and Term = 4 months Result = 2 Thank...more >>

Is it possible to authenticate user using a SQL database, containing users and passwords? What I want to achive is: I have as SQL database containig data for my app. This database also contains usernames, passwords and rights which are specific for my app. Also I have a middle tier WebService...more >>

I am trying to combine two sql statements to get just one result, right now I have: SELECT Count (ActivityId) AS ActTotNum FROM dbo.Activities WHERE CaseId = MMColParam and ActType ='HISTORY' Which gives result: ActTotNum = 14 SELECT Count (ActivityId) AS ActCompleted FROM dbo.Activiti...more >>

I am writing an ASP application and was wondering what the best method would be to connect to an SQL2000 server? What would a good connect streing look like? Thanks! Jon ...more >>

Wheather I change a column datatype, other properties, or a collection item, I have to pute everything into BeginAlter, DoAlter. How can I see the ALTER TABLE script before applying those changes? Thanks, Costi ...more >>

I have two statements, each one gives me a number: SELECT Count (ActivityId) AS ActTotNum FROM dbo.Activities WHERE CaseId = 24 and ActType ='HISTORY' SELECT Count (ActivityId) AS Actotcompleted FROM dbo.Activities WHERE ActType ='HISTORY' AND (Lastmodified IS NOT NULL AND Dateinitiated ...more >>

Dear colleagues, You may here find again the question about hierarchical data. I have already read posts by Mr. Celko, Mr. Kass among many as well as I visited appropriate sites (e.g. http://vyaskn.tripod.com/ - "Working with hierarchical data in SQL Server databases"). Unfortunately, I could...more >>

I would like to update the items of order WHERE value of order is lower than 1000. So, I created derived table: UPDATE T2 SET T2.tsAd_ID=dbo.vrniReklamoPostavke(@datum,T2.cas_prejema_klica,T2.ID_MEDIJA,T2.ID_IZDELKA) (SELECT T1.* FROM (SELECT p.tsAd_ID,n.cas_prejema_klica,v.ID_MEDIJA,p....more >>

hi, is that possible i can do something like select type from Exec sp_depends 'Customers' or Insert Into Table (Type) values (Exec sp_depends) since i only want the column Type to be selected or inserted Thanks Ed...more >>

Any ideas as to why a simple query (SELECT COUNT(*) FROM Table) on a table with 1,900,000 rows would take 3 minutes to run in Query Analyzer and more than 10 minutes to run from a VB 6 app using ADO? ...more >>

I ve got an Oracle 9. server running on AIx. and One SQL Server 2000 Sp 3on w2k and SQL server 7 on Winnt 4.0 i did link this Oracle server on both SQL serevrs. When run an simple SQL query i got different results. Num of rows are always different In SQL server 7.0 i got 49430 rows( w...more >>

I have different SQL Scripts that I have written with the SQL Query Analyzer, and I need to run each script for all of the User Datbases. The only way I can see to do this is to choose each database with the Combo Box then run the script and reapeat. Is there an easier way to do this? Th...more >>

I recall reading a while back that using Decimal() for money types was the ANSI standard. Is there a defined precision that they recommend? Zach...more >>

Hello, I have the following two tables: Table-1 has one column: [dates] Table-2 has two columns: [dates], [data] I would like a join query to have the following two-column output: Dates Data 01-01 some data 01-02 01-03 some more data 01-04 ...more >>

Hi, This is showing up as .00. How can I get the decimals on this select cast(5 / 240) as decimal(10,2)) Thanks...more >>

I have a temp table which stores the temp values. I need to run a Query as Delete from #TEMP_DATA WHERE sName <> 'Samay' or Paymentplan <> -1 This Query returns me only the User with samay which has a paymentplan as -1 It does not diplay me the result which has either of the conditions true...more >>

Hi, I am trying to write a query in ESQL which involves columns of type DateTime. How does one uses hostvariables for coulmns of type DateTime. Also, i am trying to use GetDate() function in the sql which errors out. E.g select count(*) from msdb..sysjobs where date_created <= getdat...more >>

SQL Server 2000 I have a table with a UserID column and three date columns CREATE TABLE [T1] ( [UserID] [int] NOT NULL , [Date1] [datetime] NULL , [Date2] [datetime] NULL , [Date3] [datetime] NULL ) ON [PRIMARY] GO INSERT INTO T1 values (1, '1 Jan 2003', '1 Jan 2004', '1 Jan 2005'...more >>

Hi, How can I know how many instances are running on my server and what are the names of those instances ? Thanks in advance, Pat ...more >>

Hi, I'm wondering what the best practice is for accessing a remote XML document in TSQL. Currently I am able to access it using the Microsoft.XMLDOM object and the sp_OACreate statement. However, I am running into roadblocks in that the size of the XML text returned is too big for an nvarchar...more >>

Hi! I am trying to create a stored proc that runs openrowset and I am trying to give a variable as an argument for the openrowset select statement. It won't work like this! It gives "Incorrect syntax near '@strTemp'." Does openrowset not accept any variables at all???? set @strTemp = 'SELEC...more >>

Hi I am trying to get full text indexing to work with views in SQL2000. Is this possible or is it tables only? If it is possible how do I set this up? -Mac ...more >>

HI, I try to use the following T-SQL to write all the dependent of each table into a table called "Depends" But I can only insert two fields "Name", "Type" I would like to do something like Insert Into Depends values (@ObjectName, Exec sp_depends @ObjectName) but it fails Thanks Ed D...more >>

I have an application that needs to load data in bulk quantites. I use a single stored procedure to perform all the validation. Records which pass validation are loaded into the database, records that fail are returned with error messages explaining why they failed. The procedure can easily ru...more >>

Hello Using sql2k. When I add a nvarchar column to a table, lenght exceed maximum lenght prompted, but it saved and opreation of inserting, deleting is OK. But, i can never modify the table with enterprice manager or query analyzer. The table is dead-lock, what a pain. Any idea ? P...more >>

I have a table as below: mid pid rsid sid tdate rno m1 p1 r1 s1 1/10/03 null (1) m1 p1 r2 s1 1/11/03 null (2) m1 p1 r3 s1 1/12/03 null (3) m2 p1 r7 s2 1/10/03 null...more >>

Hello there I've by mistake drop critical table Is there a way to revive it? any help would be useful ...more >>

Hi, I know I could use EM to show the Dependences of each objects(like Table, Views, Stored Procedures). Can anyone tell me how i am able to do it in T-SQL since I have 100 tables,100 views, 80 SP and I don't want to look at them in EM one by one. Thanks Ed...more >>

Hi Freinds, I need to know all users ( NT Aut. and SQL aut.) and theire access to databases and access levels. I need a script to figure this out. I know how to review through Ent.Mng but need to run script on SQL. Thanks in advance, Pat ...more >>

We are in the process of making a big schema change. Removing many tables, changing column names, removing columns, etc. I would like to be able to check the syntax on all the stored procedures to see the ones in error. Also, is there anyway to disable Deferred Name Resolution and Compilation...more >>

I have replication running, transferring tables to a database. Is there a way I could replicate the data, however I would like the destination server to have 'slightly differnt' data. Eg. Row on server1 '1,1, test' Row on server2 '1,1, test2' Can this be done? thanks, ...more >>

Hi all, I have some question,I have to do multiple insert statements into SQL database, and it's really many. So I thought may be there is way to create some macro to do this stuff. I have: insert(projectid,date,category,amount,as_at) select(projectid,'date...','category',amount,'date.....more >>

Hi, I’m trying to create a check constrain that goes check in a table if the value exists. Something like: Natur_Jur IN ( SELECT ID_Natur_Jur FROM tblXML_V_NaturezaJuridica ) When I try to save the table I get one error message that I cannot have subqueries in check constrain, what I ...more >>

Hi, I have tried all I know. This is my query select store,name,quantity1,prevquan,modif, (sum(case deli when 0 then quantity + over - deliv - short - damg else 0 end)) - (sum(case deli when 1 then quantity + short + deliv + damg - over else 0 end)) from openquery(server,'select cust...more >>

I have a table that has about 50 fields. I would like to select all the fields except for one. Is there a way to not have to say select field1,field2,.... for 49 fields? Thanks ...more >>

I need to get AS400 data into SQL Server 2k, I have tried link server using both OlDB and Odbc but I dont really how to set it up...more >>

Anyone know of a function or method in which you can determine before you assign numeric data to a field if it will cause an overflow. We receive data files from vendors and on occasion a field which normally fits inside an int comes in as a numeric(18,0) (well that is the field size it would ...more >>

HI, Given the following tables: create table Product (ProductID int primary key, ProductName varchar(50) ) insert Product values(1, 'Car') insert Product values(2, 'Bike') insert Product values(3, 'Van') create table Type (TypeID int primary key, TypeDesc varchar(50) ) inse...more >>

HI, Given the following tables: create table Product (ProductID int primary key, ProductName varchar(50) ) insert Product values(1, 'Car') insert Product values(2, 'Bike') insert Product values(3, 'Van') create table Type (TypeID int primary key, TypeDesc varchar(50) ) inse...more >>

Newbie question: We have a table with 1.1 billion recs. Queries are slow. Our DBA says it's only an index problem. I'm curious. Is there a way to determine how many index levels/hits have to be transversed for a query... clustered and nonclustered? Is it true that with the right indexing, t...more >>

Hi experts, I’ve got so far with this problem but now I’m stuck. Here is the code to create 3 test tables with data. CREATE TABLE [tblEmployee] ( [strLogonName] [varchar] (30) NOT NULL , [strEmployeeFullFirstName] [varchar] (50) NOT NULL , [strLogonManagerName] [varchar] (30) NULL , ...more >>

I've heard a lot of .NET 2.0 features integrating directly into SQL server - what are these features, and can you use them in express editions? ...more >>

I have two tables for which the application is issuing a query such as this to get an empty resultset of the correct structure: Set FMTONLY On Select col1, col2 from table1 Set FMTONLY Off Both queries specify the column names (no asterisks). Both tables have about 1 million rows. One of...more >>

Hi, I am looking for advice on best practice with SQL 2000. I have created a fairly simple database - about 12 tables. I am unsure about the best way to build the front end - it is going to be web based. Do I use asp.net code to create, edit and delete records or do I use stored procedur...more >>

Hi NG, I am executing an SP from a database in SQL Server 2000. My problem is that I would like to pass table-data selected along the way in this SP to a #temp-table in another db (on the same server). After this has been done I would like to continue working in this db (to avoid renaming e...more >>

I need to develop a db to store large files; each files comes with a certain number of attribute (name, lenght ...). The main action is to search the attributes show results, select a record and download the file. The question is: it's better to use a single table which contains attributes and...more >>

i need a function that return the smallest number between some number in set like LEAST in oracle example: least(34,3.4,2,343) = 2...more >>

Hi everybody I'm writing a trigger which must respond to any update to the table, UNLESS the update affected only a specific column. As the table has more than 40 columns, checking those column values from the "deleted" and "inserted" tables requires a lot of coding and will be vulnerable t...more >>

Hello Folks, Just one small question. It is possible to know in Sql Server when someone deleted a table ? I mean, i first thought about using Sql Profiler tool, add the required events, filter and so on,and trace for any drop statement occured on the server. But i thought it would be much bet...more >>