Is there any Workaround for Taking Backup on Remote Tape ? Thanks and Regards Mahesh...more >>

I am new to SQL Server and am having a problem trying to figure out the best way to approach it. I am working with SQL Server 2000. My database has two tables of interest with fields that are 60% identical (that is, most of the fields have the same data type and context, although they ...more >>

I am calling an SP from VB which returns 2 result sets. I use the 1 result set within the SP and use it to populate a TMP table which participates in a join in the second select statement. The sp runs fine from query analyser, but from VB I always get an empty recordset! Any suggestions...more >>

Hi, I am trying to do the following query: Table # 1 : UserId = Integer (Unique ID) UserType = varchar (Could be for example = 'contact') Table # 2: Userloginid = Integer (Unique ID) UserId = Integer (It comes from the ID in table # 1) PermissionGroup = narchar (Could be for example = ...more >>

I want to do something like this. SELECT * FROM dbo.Contact [C] -- If any rows are returned then return to front end. IF @@rowcount > 0 Begin GOTO FinishSuccessModule ...more >>

Is there a way to create a link server that is only valid for a particular instance of a stored procedure? Or, should I just create a linked server which all SPs would use? Basically I want to avoid leaving a connection open indefinately to a remote server as this seems to take up resources...more >>

Hi, I need to use sp_executesql and I know how to use it to get one row, but dont know how to process a result set returned by executing sp_executesql. Anyone with an example would be great. Thanks...more >>

Newbie question: What are the benefits of utilizing a stored procedure to INSERT and UPDATE data versus having your application directly execute a query? I have seen some mention of security issues, but I am wondering if there are any other reasons (db efficiency, simpler to code, etc.) ...more >>

Dear All, I have some trouble handling the apostrophe( ' ) contained in a string. Let say i have a string (boy's game). How can i pass this to a variable @str . I can't pass this using this: set @str = 'boy's game' -> this will produce error. But i do not want to use this: set @str = '...more >>

Hi, What is the quickest and the best way to learn SQL? Regards John ...more >>

Hi all, I'm trying to restore a db BackedUp with SQL-DMO.Backup2 with a MediaPassword specified. "Restore2" works perfectly, but if I try to access methods like ReadBackupHeader or ReadFileList I get this error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Access is denied due to a pa...more >>

Hello all, Is there any problem with a temporary tables participating in a join with a normal table? I wrote the following code and I keep getting the error: ‘Cannot resolve collation conflict for equal to operation.’ Thanks in advance!! --Create a temp table CREATE TABLE #Co...more >>

Hello, In Oracle (only as of v 9 i think) there is a sql statement they have called MERGE. This allows you to either INSERT or UPDATE a table with a given set of data, and a given set of fields to link on, and it will dynamically determine for each row whether to insert it or update it.This...more >>

I 'm using a calendar table with the following structure: CREATE TABLE [dbo].[Calendar] ( [CalDate] [datetime] NOT NULL , [IsWeekday] [bit] NULL , [IsHoliday] [bit] NULL , [IsWeekendDay] [bit] NULL ) ON [PRIMARY] GO When I run the following query I get the current date not yesterday ...more >>

Hello All, I'm trying to run the following script and I keep receiving the error below. I've tried setting ANSI_NULLS and ANSI_WARNINGS to on in all different points but nothing seems to help. Any help on this would be fantastic! -Anubis CREATE TABLE #TempEventTable ( RecordI...more >>

I have a SP that returns 3 sets of results. I would like to name them so when they hit my ADO.NET DataSet, each result DataTable can be called by name instead of an ordinal. Is that possible? Thanks. ...more >>

hi All, Could anyone confirm that I only need Visual Studio .Net Professional and *NOT* Enterprise Architect to allow me to edit/create Stored Procedures on a SQL Server 2000 database? Cheers! Kyle ...more >>

Hello, I have a table called AgentLoc and I have a table called Agent same schema. I need a query that will look at both tables and if any matching Agents(pk) are found found delete them from Agent table and add Agentloc. Example some agents are in the agent and agentloc table and some are ...more >>

Hi Can someone tell me the best way to get a column list from a table to construct a SELECT statement. I can get a list of columns: DECLARE @sql varchar(4000), @TableName varchar(100), @ColList varchar(1000) SET @TableName = 'BulkInsTemp' SELECT column_name from INFORM...more >>

I want to expand a current application in VB.Net that uses an XML file to house the names of stored procedures to run and their associated Crystal reports to execute after the stored procedure has completed. I want the expansion to provide for a select of the names of the stored procedure...more >>

I want to access SQL Server 2000 database via VB.Net to get the list of stored procedures and their associated parameter names and parameter types (date, string, etc.). I am trying to expand a current XML file and application that provides maintenance of SP's to run for related Crystal re...more >>

Is there a way to enter null as a value in the table rows grid? All I can find in BOL is that NULL is treated as a string. Lloyd Sheen ...more >>

Good afternoon everyone. I have a very frustrating problem that I've been trying to solve all day with no luck. I have a DTS Package that performs a message queue task... simply dumps two global variables to a message queue. If I run the DTS Package through Enterprise manager it works. F...more >>

Has anybody had any incorrect results with xp_fileexist? I run: declare @exists int exec master..xp_fileexist '\\server317\Marketing\0323023096.jpg', @exists output select @exists And it returns 1 (meaning it should be there.) But the file is not in the folder. I could have swore ...more >>

Okay, I want to start by saying that the tables I'm working with are in our production database, and I can't change the tables. The database is SQL 2000. I use an Access Data Project just for some user side interface. Okay, for demonstration purposes I have 1 Order table and 3 SubjectInfo ...more >>

Hi all, I have a table in which permissions are set on each column depending on the role/ group etc., Now my question is that how can I find the columns that the user has access so that i can only use those columns in the select for retriving data.. your help is greatly appreciated. R...more >>

Hi, I change the filegroup of my table's BLOB using EM. How can I do that without EM (using TSQL). Many thanks, Leila ...more >>

Hi all, can anybody please how to reset passwords for an DTS? I need to edit and run it...but the former DBA left without giving us the passwords... Thanks, Tudor ...more >>

Below, ssp.CompanyPhoneNumber is NVARCHAR(20) NULL, and its contents are a freshly bulk inserted data file, so some of its values are null. The three columns in the PhoneNumber table are what I'm trying to focus on for this example. out of 1.5 million records, only 600,000 have area codes an...more >>

Hi, I posted yesterday with questions about transposing a table from columns to rows. This is the SQL query I came up with, but it keeps giving me a syntax error near "CASE". Does anyone have any clue why? I'm very new to SQL, so any help is appreciated! This is what the table roughly ...more >>

How do I determine when a stored procedure was last modified using ALTER PROCEDURE? If I use drop/create, the create date shows up in sysobjects, but this doesn't change when using alter proc. Thanks, Wayne ...more >>

Hi, Please find the SQL script below... Very simple IP management database that has 3 tables: tblHardware is where I keep all my network devices' information.. Fields are HardwareID and IPStatusID tblIPStatus has 3 records in it: Static IP, Dynamic IP and "Does not use IP"... Fields a...more >>

Hi, I'm trying to use a function that returns a table by passing in a column name to the function. All the examples I see pass in a hard-coded value. I get the following error: Incorrect syntax near '.' Tables: create table tblOne ( theID int, Col1 varchar(10) ) create table tbl...more >>

in asp page im getting this error how can i solve it.. yesterday the codes work but i setup visual studio 6.0 now it doesnt works.. ...more >>

I'm trying to 'pivot' a table thus: To simplify, the table has a title, quantitya, quantityb, quantityc. So the table may have 3 rows: games 11 0 26 houses 20 9 42 cars 0 8 3 I want to end up with a new table with 9 rows: games 11 games 0 games 26 hou...more >>

Hi, we have very simple task to do but the query performing that task looks a bit complex. We have following table CREATE TABLE [VerdictSample] ( [VerdictId] [int] NOT NULL , [SampleId] [int] NOT NULL , CONSTRAINT [IX_VerdictSample_SampleId] UNIQUE NONCLUSTERED ([Sampl...more >>

How can I delete this data Select * from Agent full outer join Agent_phoenix on Agent.Agent = Agent_phoenix.agent where Agent.State = 'OK'...more >>

Hi, I have transactional replication with queued updating. When I'm running this function on subscriber: INSERT INTO matchup_edge (fk_game_id, offense, defense, coaching, misc, intangibles, overall, evaluator) VALUES (1292, - 1, - 1, - 1, - 1, - 1, - 1, 'Covers Team') SELECT ...more >>

Hello... I have a table of artist names like: Beyoncé Knowles Whitney Houston Tom Jones When I create an SQL query in an ASP page, I don't know how to query the database to get the names with the accents, like Beyoncé Knowles. "SELECT * FROM Artists WHERE Name LIKE 'Beyonce'" How d...more >>

how can i update only the minutes in date/time value like: Time 12:09 PM 11:18 AM 1:32 PM to 12:00 PM 11:00 AM 1:30 PM...more >>

Greetings. A coworker brought up an interesting problem and solved it via a cursor. Since cursors are evil I said "I bet there is a SET solution to this". But I can't figure it out. This is more an interesting challenge and learning experience rather then immediate need. DLL posted below....more >>

Does anyone know what this error message means? Our database keeps kicking into Suspect mode with severity 21. We use SQL Server 2000, running on Windows 2000. The error message that appears is 'Operating system error 997 (Overlapped I/O operation is in progress.) on device 'e:\logfiles\...more >>

I have a table Movements with 2 fields : Date and amount. The goal is to transform my table in 1. to result in point 2. 1. Current Situation for the account (Extract) TABLE Movements Date Amount ---------------------- 2004-08-01 1000 2004-08-05 500 2004-08-10 750 2004-08-15 -200 200...more >>

Hi All, I want to return a recordset (cursor) from a stored procedure. The sp is: ----------------------- CREATE PROCEDURE MyProcedure @MyValue CURSOR VARYING OUTPUT AS SET @MyValue = CURSOR FOR SELECT * FROM MyTable open @MyValue GO ----------------------- I think this is correct...more >>

I have a SQL 2000 server. I use enterprise manager to restore databases. I'm able to restore databases from SQL backup files when the backup file is small. However when I try to restore large databases (I have 3 databases with files size above 1 Gb) I get this "Timeout Expired" msg. The msg w...more >>

Hi All, I have a text string that looks something like this TerracedMewsFlat All I want to do is break up the word based on the Capital letters so it looks something like Terraced, Mews, Flat Does anyone know a easy way of doing this. Any help would be great. Thanks Phil...more >>

Is this possible? I did a backup script like this: backup database dbCustomers to disk = '\\bert.xxxx.com\sqlbackups\SQLSERVER\USER_DB\ServerSwitch\dbCustomers.bak' and then a restore like this: restore database dbCustomers from disk = '\\bert.xxxx.com\sqlbackups\SQLSERVER\USER_DB\Serve...more >>

Hi , I have a TSQL script that when run as SQL Server Agent job sometimes generates the error that is not handled properly . The same script when being run through SQL Query analyzer handles the same error as it is supposed to handle. Why am I seeing different behaviour ? Thanks very ...more >>

I am trying to set up a regular SQL login which is NOT a sysadmin with the ability to run xp_cmdshell. It's not the best idea, but I have no choice. I granted it execute permissions on the proc and used xp_sqlagent_proxy_account to point to a login which is a local admin on the server. ...more >>

Hi, I want to delete indexes in a table thru stored procedure. I user the statement 'Drop index 'XR_Vend.IX_XR_Vend_MatchId2' and getting following error 'Cannot drop the index 'XR_Vend.IX_XR_Vend_MatchId2', because it does not exist in the system catalog. Any advice would very much a...more >>

Hi Folks, This is the query I am trying to write. I have a table which can have multiple rows for one column say Jobid but different values for another column say ticket Id. Table looks like this. Job Id Ticket Id 1 100 1 99 2 20 2 ...more >>

Hi, please tell me best way to import into SQL table from this flat file. Thanks DATA FILE HAS 01111111111115414 hollyhbrook Lane Suite 3 None denver NC48110-1111 0212345678912345200 02333333335555554590 FILE SPECS: Record Type 2 ...more >>

Here is my trigger CREATE TRIGGER trCalculateBalance ON [dbo].[tblEntitlement] FOR INSERT, UPDATE, DELETE AS IF @@ROWCOUNT=0 RETURN UPDATE tblEntitlement SET decBalance = 28 + decPreviousRollOver - decPreviousBorrowed + decExtraDaysTotal + decNextBorrowed - decYearDurationTotal, C...more >>

This may seem like a trivial question....... When a client views the server logs in the Management folder of the Enterprise manager, does this disallow the server to write to the log? Would this cause the server to crash? Thank you...more >>

All: I have a very basic question. Let's suppose I have table T with one column QUANTITY. Let's also suppose T has one row, with the value 100 in the QUANTITY column. Now, what I want to do is.... 1. Read the value of QUANTITY 2. If the value of QUANTITY > 0, decrement QUANTITY Th...more >>

Dear All, I'm a little bit confused about the RESTORE Command, let me explain. We backup our db on a different server, every hour for the db, and every 10 minutes for the log file So if at 12.55 our server crashes then we need to resore the db and 5 log files. restore database Pari...more >>

I am using the following query to return a list of creditors from three tables: SELECT CreditorName,Addr,City,StateAbbrev,Zip FROM SClaims,States WHERE Pet = #SESSION.UID# AND SClaims.State = States.StateID UNION SELECT CreditorName,Addr,City,StateAbbrev,Zip FROM UClaims,States WHERE Pet ...more >>

Hi, I am trying to use the GO statement in my recordset.source string and i get an error "Incorrect Syntax Near GO". I use vbcrlf & " GO " & vbcrlf so that go appears on a line by itself or else sqlserver will not run the query. Is it possible to return multiple recordsets within one records...more >>

I have the following code in a form of my VB application: "... strCnn = "Provider=sqloledb;" & _ "Network Library=DBMSSOCN;" & _ "Data Source=" & ctlServidor.Text & ";" & _ "Initial Catalog=" & ctlBD.Text & ";" & _ "Tr...more >>

Hello Experts, Which Option do I have to turn on when creating a stored procedure that has unicode characters inside? Thanks in advance....more >>

I have an SQL statement that I am programmatically populating, depending on user input. The string is... SELECT * FROM Tickets WHERE (TicketDateStarted > #" & Date1 & "#) AND (TicketDateStarted < #" & Date2 & "#) ORDER BY TicketDateResolved I have checked all of my fields and they are a...more >>

Hello, Can anyone tell me why this keeps reporting as incorrect syntax? DECLARE @ORS [nvarchar](4000), @ORS2 [nvarchar](4000), @ApplicationPath [nvarchar](100), @ProcessingFile [nvarchar](50) SET @ApplicationPath = N'D:\WebFiles\Live' SET @ProcessingFile = N'File-029239.csv' SET @OR...more >>

Hi all, Is there any downside to running sp_recompile on all objects in my data base once a week? Should I run this against system objects as well? TIA, Joe...more >>

Hi I am trying to create a program (does not matter with what) that will investigate a store procedure and display the data to the end user in a different manner. If the SP receives parameters and returns an OUTPUt, there are quite a few system SPs that do this. My problem is when there ...more >>

No sure if this is possible but here goes. I've created a quite complex view based on various tables and other views. The final value that is calculated with the view needs to be entered into another table record. This final value in the view can be altered by changing any of upto 4 base ...more >>

Hi all, I am having a situation please tell me which is faster.. i have a table say customer master, and i am having field called customer type which is a list of values like "A","B" or "AB",etc list count some what like between 10-15 i am using this table in many joins.. i have millions ...more >>

I have to write a .Net application which can compare SQL Databases including things like: - DB structure, PK's, FK's, indexes and types of indexes i.e. should be able to detect if the same index has cascade set on one db and not on another, or unique on one and not the other, Constraints, ...more >>

Hi I am using 8 tables in a query. In my select query I am using all the 8 tables. Is it good? My friend is suggesting that to achieve good performance, populate the non-driving table data in temporary table and join the other tables with that temporary table to get the better performa...more >>

currently i would like to send sql mail to few users when i found that got duplicate data in the table.The following is my coding in stored procedure : if not exists(select catalog_code, count(catalog_code) from rd_awards group by catalog_code having count(catalog_code) > 1) ...more >>

I have the following table (abbreviated for clarity): player_stats[ id, number_value, stat_field_id, game_id, player_id ] players [ id, lastname ] And I'm able to get the top <n> totals for a particular stat_field_id from player_stats with no problem: SELECT TOP 5 SUM(number_value) AS...more >>

Hello, I'm having a difficult time finding the right sql syntax to perform an update. Here is the situation: I have two tables, each with an orderid field and a removal_date field. There is a one-to-many relationship between table A and table B, with B having multiple records to each one in...more >>

Hello All, I have a question on Locks. The following script should also help : CREATE TABLE [dbo].[nolock1] ( [a] [int] NULL ) GO a --- 1 1 2 From Terminal One BEGIN TRANSACTION INSERT Nolock1(a) Values (3) From Terminal Two ==> before a commit is issued....more >>

Here is the code to create and populate 3 test tables. CREATE TABLE [tblAbsence] ( [intAbsID] [int] NOT NULL , [strLogonName] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL , [intYear] [int] NOT NULL , [strAbsRsnCode] [varchar] (12) COLLATE Latin1_General_CI_AS NOT NULL , [dtmAb...more >>