Hi I am having a simple question related to the cursor, as I am not too much familiar with cursor In my stored proc, I am using cursor for self-insertion and self-updations. Cursor is based on one basetable where I am making insertion and same time updations in one of the columns of the row bas...more >>

Hi, I want a to write a function to tell me if there are any rows in a table that belong to a certain customer. Like this? CREATE FUNCTION myFunction ( @cust_no int) RETURNS int AS BEGIN DELARE @recCount int SELECT COUNT(*) FROM customers WHERE cust_no = @cust_no S...more >>

Hi, how do I get a list of all tables (User) and list of views when I am having database name with me Please let me know Kishor ...more >>

Consider this: CREATE TABLE ProductCategory ( ProductCategoryID int IDENTITY(1,1) NOT NULL PRIMARY KEY , Title varchar(25) NOT NULL, CategoryParent int FOREIGN KEY REFERENCES ProductCategory(ProductCategoryID) NULL ) GO INSERT INTO ProductCategory(Title) VALUES ('Books') INSERT...more >>

Using SQLServer 2k Hi I am trying to find duplicate records by grouping by certain columns in a table. The query below gets me close to what I need but there is an additional condition I need to check for but am at a loss on how I to do it. Based on the DDL below, Everything with matching ...more >>

Using MS Access 2k with SQL 2k. I've got a handle on how to display errors to users when the error is generated in an SP or trigger using RAISERROR But if I have a column set to not allow nulls in the backend and a user tries to create a record where one of the values is null, the message th...more >>

Hello! I have a table CREATE TABLE [dbo].[test2] ( [id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [kentta] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO With the data like this: id kentta 1 gfdsgdfg fd 1 1 2 testia gf hgfhia2 3 ...more >>

Hi All, I tried to detach database using the following code: SQLDMO.SQLServer2Class oDMO = new SQLDMO.SQLServer2Class(); oDMO._SQLServer_LoginTimeout=30; if (Environment.OSVersion.Version.ToString().Substring(0,4) == "4.10") { oDMO._SQLServer_LoginSecure=false; } else { o...more >>

Hi, Following is the query to list the id and sum from titles table for each pub_id. USE pubs SELECT pub_id, total = SUM(ytd_sales) FROM titles GROUP BY pub_id Result: pub_id total ------ ----------- 0736 28286 0877 44219 1389 24941 How can I modify the query to return...more >>

What is the appropriate way to set up this scenario: I have a table called incident used to record incidents. An incident can span across several cities and counties, and includes other info like date, name, etc. Cities and counties need to be able to be recorded as multiple items. How should ...more >>

In Access you can perform INSERT on an updatable joins. How do you do the equivalent in SQL Server 7.0? I implemented what I wanted in Access XP and then used the upsizing wizard with the 'Use triggers' option. But I got an error when trying to do an insert on the view representing the join. I'd...more >>

Hi, I'm trying to create a sproc that will run inside a job. If the sproc for some reason fails, I want to use xp_sendmail to send me a mail message. However, in the mail message (body preferably), I'd like to be able to include the error that the sproc generated. Is this possible? I looked...more >>

All, How can I force a cursor_close to occur right away? I have a user with a statement coming from an ASP page. It is a SELECT statement. Using profiler I can see a cursor_open and a cursor_fetch statement, but I cannot see the cursor_close statement for some time. The user has the records...more >>

Hi, I created a table and inserted some sample data using the following statements: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TimeClockLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TimeClockLog] GO CREATE TABLE [dbo].[TimeClockLog] ( ...more >>

Hi all. I have a series of linked stored procedures that export data from my database at specified intervals; which I then save as tab-delimited files. Those stored procedures function smoothly. It turns out that some of the tables have embedded tabs. I need to export the data in a tab-d...more >>

Hi I apologise if I am posting it twice but in the earlier email I forgot to include the subject and I am not sure whether it will be accepted by the forum. I have the a table whose data is as follows: 2 aaaaa 2 yyyyy 2 zzzzz 2 xxxxx I need to combine the text data of all the four a...more >>

My Original Post which no-one wanted to help me with :-( Hi all, Using sql server 2000. Debugging used to work! We think that after SP3 was installed it stopped working. Does anyone know how to enable debugging again? We really miss it.... ian -------*FYI the answer is*------ ...more >>

Hi all, I have the following question: How do i generate Rows IDs manually? I have an existing table that i use to prepare some batchs, each batch has an unique section ID to identify the user that is preparing the batch: something like this: Create table UsersBatchs (fSection...more >>

MSSQL 2K sp3 W2K sp4 System event log (application) and SQL server error log is showing too many [Login failed for user 'sa'] source logon I could not find, which application is trying to connect to SQL server and for what? Can any body guide me to get out of this problem. Thanks in advan...more >>

in this select select 1300000.0000/1315000.0000 the result is ..9885931558935361 but inside another select where the values are inside of tables, the result is just .9885000 how can it display more values after the 5? tia, dk ...more >>

I am joining two tables, see below. How do I select the rows where a-pk = b-a-fk with the greatest b-mod_date? In other words, I am only interested in getting b-data where (a-pk = b-a-fk) and (b-mod_date = max (b-mod_date)). Thanks TABLE a-pk (integer, primary key a-data (string TABLE b...more >>

I have very strange thing here: My colleague and me run the same queries from our pcs. It is Select count(*) from TABLE. I get response for 1 sec, and he gets it for 45 secs. Table has 5 million rows. It has clustered index on decimal column, which is used in execution plan. We're on the same ne...more >>

why do I get this? declare @div char(2) set @div = '01' exec myProcedure @code = @div + '400000', @year = 2004, @amount = 15000000 I get: Incorrect syntax near '+'. If i try cast or conver like cast(@div + '400000' as char(8)) I get: Incorrect syntax near '@div'. any idea wh...more >>

Hello, I have a database with 2 filegroups D: has the data.mdf (500GB of data) primary filegroup E: has the second data2.ndf (100GB of data) secondary filegroup F: has the log.ldf The D drive is out of space, so I created a new drive G: with 1 TeraByte space. How can I move the data.mdf...more >>

Hello, What is the easiest and fastest way to take data from TableA in DatabaseA to TableB in DatabaseB periodically or if possible once something has changed in TableA. Thanks, Jim. ...more >>

Hi, I'm not going to state the table layouts because I'm not exactly sure what form they'll take yet, however I have come across a dilemma in my thinking, so wondered what people thought. I have a number of sites, site 1, site 2 etc I have a template which consists of a number of template...more >>

Background: I developed a VB6.0 Application which allows a user, via a RichTextBox, to Paste an image (from MS Paint) and store it as part of a record stored in an MS Access Database - in a Memo Field (rs.Fields("fldImage").Value = rtf.object). This also works in the reverse, when retrieving record...more >>

I have a table which has a password field with the Varbinary(85). I store password of users using the built in PWDENCRYPT function in it. Everything works out well. Until I update the table with a different password. See the code below.. Declare @enc_password as varbinary(85) Set @enc_pa...more >>

hi i have an image field in a table and i was wondering what is the maximum capacity of it? i want to be able to store short movie clips, picture files etc. in it at the moment but at a later stage could go upto 300mb - 700mb. what is the limit at the moment. thnx ...more >>

Hi, I would like to create a job that does the following: Our company uses merge replication with approximately 30 subscribers and one server that is configured as both the publisher and distributor. We have a table that records certain application events, and that table is included in our...more >>

I'm trying to set up a user -defined function that will take an integer (N) and a pipe-delimited string (strMSG) as arguments, find the Nth delimiter in strMSG and return the value of that field. I can't seem to find any Looping functions that would allow me to do this. Is there a way to...more >>

I had an ASP intranet app up for a while, in use by many at my company. I now want to add a search engine to return records from what has been typed in (and therefore submitted into the database) so far. Specifically, I need to be able to capture a phrase that the users typed in and build an SQL...more >>

I need to poll multiple remote sql servers and determine the default database collation order of several databases on each remote laptop accross a world wide sales forse. I can get files on and off the machines but nneed a way through oSQL.exe to poll the databases for there specific default dat...more >>

I keep getting: Error 137: Cannot use empty object or column names. Use a single space if necessary. I have a parameter that I want to be able to use after the GO statement. Example: CREATE procedure usp_LogisticCompanyBrowse @FinanceCompany varchar(6) = null AS ---select statement h...more >>

I have a trigger I was testing, now when I try to drop it, the system just runs and never returns an error or finishes. I have tried from both ent. manager and query anal. (version 7) with the same results. Can I just delete the record for it in the sysobjects table or will this cause more pr...more >>

I'm getting the following error message: "Incorrect syntax near the keyword 'TOP'." I can't figure out why. Here is the T-SQL code: SET @MsgText = ( SELECT tblU10_ADTqueue.colMsg FROM tblU10_ADTqueue ORDER BY colMsgID ASC TOP 1) Any help would be appreciated. TIA, Gordon...more >>

On the commercial market, there are a few configurable tools that automatically create audit history tables for a SQL Server database (OmniAudit, MSSQL LogManager, ApexSQL Audit). I also need a query & reporting end-user web-interface. By "end-user" I mean someone who has relatively limited d...more >>

As the title. Thanks in advance. David ...more >>

Can anyone explain why '%' is used? I have the following: update employment set yearsOfService = datediff(year,serviceDate, current_timestamp)/12, monthsOfService = datediff(month,serviceDate, current_timestamp) %12 I've just never seen it used this way....more >>

Hi, I'd like to do something like use northwind declare @sql varchar(256) declare @result varchar(256) set @sql='select @result = count(*) from categories' exec(@sql) print @result I get an error saying the '@result' is not decl...more >>

Hello Hope you can help. A file is named like this S_ACTIVITY_ATT_1-10DI6_1-3TG.SA The part "1-10DI6" is a row_id of a record in a table. I want to use this part to query for the recor in that table. This means that I have to filter this part out of the file name My first step to do this is...more >>

Hi I have each table for different kinds of transactions like, checking/saving, brokerage, etc. And I create a view transaction_detail to union each table. but when I do the following query against the view (query1) it takes forever. if against a table transaction_detail_bank (this table comtains ...more >>

Hi, I have two check constraints on my table. Suppose user inserts a record that violates both of these constraints, then SQL server returns an error to user(client app). If both of these violations have the same error number, how should I identify that violation of which constraint has occur...more >>

Hi I need two helps, one is the function coding and second one is suggestion/comment. 1) I want to write a characters function like following, run: select CharValue from Characters('1,2,3,4,a,b') output: CharValue ---------- 1 2 3 4 a b 2) what is your comment on this? ...more >>

Hi, I have a Sp that returns a recordset which is used as the datasource for a form in an Access ADP. The query itself is fine, however I want to know if it is possible to format selected fields. For example: SELECT format("yyyymmdd", DateIn) as Date FROM .... The records are being out...more >>

Ok, I am just getting started on learning triggers on MSSQL. I am trying to create a trigger that will take the take the Password field of the changed record and update a record with that same UserName in another table. So: CREATE TRIGGER Update_radcheck ON FS_Users AFTER UPDATE AS UPDATE r...more >>

Hi! This is propably a FAQ. But anyway.... In the example beneath '80' will be returned. declare @test int select @test =0800 select substring(convert(varchar(4), @test),1,2) It seems that 0 is being left out. How do I make sure that '08' is being returned? TIA Klaus...more >>

Does anybody know of any ExportData/ImportData examples? Thanks Yannis ...more >>

Hi, I have a table (Customers) that an existing applications has been using for a while. The field TelNo has customers telephone numbers from diferent countries in their respective format, eg '(818) 456-5678', '011-34 (76) 8900992' Others numbers are in different formats, eg '818-456-5678',...more >>

I have a table that contains categories this table has a relationship to itself in that some categories may be a sub category of a category. CREATE TABLE tmpCategories (CategoryID INT IDENTITY, ParentCategoryID INT, CategoryDesc VarChar(20)) INSERT INTO tmpCategories (ParentCategoryID, Cat...more >>

I have a table with a date field that we query a lot. The query is only ever interested in the date part of the smalldatetime field. Is it possible (and if so can you show me??) to create an index on this field with only the date so that we can increase the efficiency of these queries? Than...more >>

I am tring to make a trigger for all proccess on a table which is for store. CREATE TRIGGER depoStokHareketYapildi ON [dbo].[fos_depoStokHareket] FOR INSERT, UPDATE, DELETE AS begin --declare @toplamCikan bigint DECLARE @newGiren VARCHAR(100) DECLARE @newCikan VARCHAR(100) DECLARE @sonuc ...more >>

I can successfully use the following query on several other database products: select dtid_no from udwrdtid where (drmo_ric,ric_sfx) in (select drmo_ric,ric_sfx from udwrdrdo where drmo_name = 'TEST') However, I have been unable to find the proper syntax for doing the sam...more >>

I have a table like: tblID tblFld ----------- ----------- 10 500 20 300 110 300 102 300 120 400 In my case, the tblID 10 is equal to 110, 20 and 102 are the same. So when I do "select * from myTest", I want to get: tblID tblFld ----...more >>

is it possible to access the propert Properties("Nullable").Valu using ADOX and SQL server, i get the erro "Object or provider is not capable of performing requested operation."...more >>

I have a database with two tables (class and book) that are joined by a link table (class_book) such that a class has many books and the same book can be used in many classes. How might I go about pulling this data into Excel and showing the classes down the left side, the books along the top,...more >>

Hi, I am an SQL newbie, and currently I want to urgently generate a report based on a table as shown below Trust Site OFA F 5A1 5A11 20000 23. 5A1 5A12 34567 45. 5A1 5A13 14000 12. RR8 RR81 21980 16. RR8 RR82 15600 11. RR8 RR83 14000 8. I wish to write a select statement to select Trust, ...more >>

I was hoping someone out there could help me. I'm wondering if there's a way, in a Select statement, to combine two fields into one, even if one of them is Null. For example, I was using SELECT strFName + ' ' + strLName AS strFullName But then I realized that if strLName is NULL or strFN...more >>

We want to enable our developers to be able to backup and restore from some canned databases, even though they will not have the necessary rights. We have a stored procedure that drops a database, creates a new database, and restores it, but it runs with the users rights. We want the stored proced...more >>

I have two tables that are identical. One is a temp table and one is the master table. The user makes changes to the temp table by adding or deleting records. I need to update the master table based on those changes. My first step is two add any records from the temp table that do not exis...more >>

SET XACT_ABORT ON BEGIN TRAN Declare @mysql nvarchar(3000) Set @mysql = "insert into..." Exec sp_executesql @mysql IF (@@ERROR <> 0) BEGIN GOTO tran_failure_block END Set @mysql = "delete from..." Exec sp_executesql @mysql IF (@@ERROR <> 0) BEGIN GOTO tran_failure_block END ...more >>

I would like to use t-sql to pull out all characters in a field upto a space or a 10 character limit, then if their are character's after the space move it to another field. Can I do this in T-sql? For example FirstName Middl Barbara Jo Randy Alle Take and separate for...more >>

Can anyone tell me if there is a way to determine the status of the SQL Server Agent from a query? Thanks in advance, Raul...more >>

We have a database with information, which we would like to change. I have an excel-sheet with 6 columns: onr_old;HO_old;SO_old;Onr_new;HO_new;SO_new. We want to change the combination of onr_old;HO_old;SO_old in Onr_new;HO_new;SO_new. How can I change the query, as shown below to use the e...more >>

Is there a way to send SMTP Email from a SQL Server application?...more >>

I would like to schedule SQL server to run an external application which sends email notices on a daily basis. I currently have scheduled this as a job but I am guessing that there is a better way to do it within the database itself, possibly as a function, etc. Any info would be appreciated. Tha...more >>

I have the following stored procedure which on execution gives the following error message "formal parameter @EmpCreated was defined as Output but actual parameter not declared output This is called from an ADO routine in MS Access Can someone help? THanks so much CREATE PROCEDURE dbo.procPhone...more >>

I need to add a number of working days to a date using SQL - WITHOUT a functions. Here's my single line WorkingDays calculation: DateDiff('d',[Date1],[Date2])-(DateDiff('ww',[Date1],[Date2}*2) I'm looking for something similar. Any help? Thanks Craig...more >>

Dear Friends I have installed a new server in the domain and want to transer some of the databases on the server i have took the complete backup and restore the same. but i see that the users from the security tab are not reflecting. please suggest how i can import or restore all the login in the ...more >>

well im starging programing with vb and php but im only on win98 i was wondering if there is a version of mysql which works with win98 because the one i downloaded didnt work...more >>

Hi, Is anyone done "Data Paging in database". Retrieving only required rows from database depending upon the page number passed. One way i know is using dumping data in temp table with identity and retrieve the data from temp table by filtering the id #. What can be the other way ? ...more >>

Hello Experts, DDL: Select 1 CustID, '02' Type Into #CustomerTypes --In my 1st following query I want to return the Type '02' to Type_Special Field and not to Type_Normal, --what do I do wrong? Select CT1.CustID CustID, CT1.Type Type_Normal, CT2.Type Type_Special from #CustomerType...more >>

Dear group, I am planning to purchase used books for the below titles. Are there any major changes done or can i go ahead to purchase used books? Also I could not find the publishers website to look into the errata, etc. The Guru's Guide to Transact-SQL by Ken Henderson (Author) SQL...more >>

Hey, I would like to know if it is possible with sql server 7 stored procedures to obtain the highest value in a column in a datatable, increment the value with 1 and insert a record with the new value. Self-incrementing unique id's are not an option, because previously created values sh...more >>

Hey I have a table that has amongst other columns, also a version_no column of type rowversion. I have a stored procedure that updates a record in this table and also checks whether the rowversion column has changed. So when it has, it let's the user know that the column has been changed or deleted...more >>

I have set up a linked server to an Access database on another server. The problem is I can't tell if it is set up correctly. When I use sp_addlinkedserver, it tells you the server was set up even if the file is not there (I tried it with a non-existant file, just to test it). I tried the ...more >>