hi all, got a question, what's the different of .NET transaction ( using connection transaction ) and the transaction inside a store procedure? which is faster? does .net transaction use DTC? or both use DTC? thx Kevin ...more >>

Can anyone offer some advise. When a table holds a defined number of fields (determined from a count query) and a predefined time period has elapsed since the last email was sent I want to send an email to various recipients and set a date flag to state that an email was sent. Can this b...more >>

I want to setup VS 2005 and sql server 2005 on a dual boot system and it will be using the d drive. does anyone know of any issues regarding this type of setup? Thanks. -- moondaddy@nospam.nospam ...more >>

*I have a Query like this: SELECT CARTERA_PROV.cCAJA, CAJA.sCAJA, CARTERA_PROV.fEMISION, CARTERA_PROV.fVENCIMIENTO FROM CARTERA_PROV, ANEXO, TIPO_DOCUMENTO, CAJA, CONCEPTO_GASTO, MOVIMIENTO_CAJA WHERE case CARTERA_PROV.cCONCEPTO_CARTERA when...more >>

I'm having some issues on a proc I'm creating. What it does is allow a user to pass in parameters for a .net search page. I have this but it does not return any data: select FirstName from customer where FirstName LIKE '% @FirstName %' is this correct? am i missing something? also anot...more >>

Hi, Are there any new feature for debugging non-CLR SPs in Management Studio? I couldn't find anything in BOL. Thanks in advance, Leila ...more >>

I am getting a primary key violation with an update. My update statement is like update e set e.EID = neid.New_EID from EmpTable e inner join NewEID neid on neid.Tech_SSN = e.SSN and neid.New_EID not in (Select EID from EmpTable) The problem is that the final (Select EID from EmpTable...more >>

I would like to create a trigger that would call a stored procedure in SQL Server 2000. This procedure would retrieve a users existing e-mail address from a table, give the user a new e-mail address and update the address information in a new table and send the user an e-mail confirmation. I w...more >>

Hi, Is it master dbid alwasys 1? please help. Thanks. ...more >>

hi all got a question on the using select max(somecolumn) + 1 to get the unique id for a new record. since the select doesn't really have a lock on the table. so it's not thread safe. correct? this way, two users can get the same id return from the select statement. thx Kevin ...more >>

Hi, I am trying to update data that contains special characters. table: sqlCollection (this contains sql queries) column: sql I want to replace Test[select member='yes' where isnull({member_cat},'') !='catA' ] with Test[select member='yes' where isnull({member_cat},'') !='catA' and ...more >>

Hello, I am taking over some projects frrom a real sharp young guy who is/has moved on the bigger and better things - of course, ZERO documentation :). Below is an Instead Of Update Trigger on a table (I am pretty sure this is an Instead Of trigger). I believe there is no reason this can...more >>

Hello. I have a pretty simple INSERT statement that's pulling data from 3 files. The one table Table3 (SM) has 1000's of rows all with the same Effective and Termination dates. My main table Table1 (IDS1) has 5 rows. I need to pick up the Effective and Termination dates from Table3 but s...more >>

Hello everyone, Put simply we have recently been the victims of a malicious hacker because we were not aware of the dangers of SQL injection. Now, I am adding addition validation to my forms and also GET variables (we are using PHP). Does anyone have any good techniques for the kind of valida...more >>

Hi guys! I get this message when trying to insert a new record into a datatabel which is part of a dataset connected within VS 2005 (VB) from a datagridview control via a DataTableBindingSource and TableAdapter to an SQL Server. In the SQL server the field is declared as "do now allow null"...more >>

Hi I know this is off-topic, but I'm looking for some knowledgeable people... The company where I work have an application which uses VB6 for the front end and SQL Server for the back end. The front end uses the (published) stored procedure API to access the database. The business ...more >>

I already have a solution to this, but I have a feeling that there is a better way to do it... I have a table containing information on Serial Numbers; in the past, when the information is edited, the original row is left alone, and the data is copied to a new row with an incremented Sequen...more >>

Hello, I'm trying to use a pass through query in which I want to recreate something that I can do in the query analyzer. I'm using Access 2K as front end and MS SQL Server 2000 as back end. I want to create a view, use a select statement using this view linked to a table and then I want to dro...more >>

I am trying to find a way to check if a user belongs to a program specific role. Previously we had been using: // Not Exact but I think it can be followed hr = m_pDmoSrvr->GetTrueLogin(&login); LPSQLDMOUSER pUser = 0; hr = m_pDmoDb->GetUserByName(login, &pUser); hr = pUser->IsM...more >>

I have 2 tables: TBL_Client and TBL_ClientCategory TBL_Client ============ ClientID = key FirstName LastName TBL_ClientCategory ============ CompanyCategoryID = key CategoryID ClientID Why does this SQL not work?: SELECT * FROM TBL_Client INNER JOIN TBL_ClientCategory ON TBL_Clie...more >>

SQL 2000. Let's say column MyDate is a datetime type. Is this comparison syntax OK as is? ..... where MyDate <= getdate() Or is some formatting of the column value and/or of the function's return value required for the comparison to work? Thanks Liam...more >>

How do you call an external VBS script from within a T-SQL Job? At the end of a job I want a VBS to fire off in the last step. Not sure how to do this. Looked everywhere ... :)...more >>

Hi, I have a user table that is connected to a report table by the userid. user table fields: userid user name report table fields: userid (short int) report number (values being 1, 2, 3,4, or 5) report location (values being a, b, c or e) hasaccess (bit) the relationship is one(us...more >>

I want to create a SQL server view that combines 2 tables on 2 different servers. Both of the servers are in house and I have permission to view both of them. What is the easiest way to go about doing this? I am able to create a view with multiple tables on one server but have never had to cr...more >>

I have a temp table I'm filling from two different data sources. I then do an ALTER TABLE statement to add an identity column. So far so good. But when try to select the first x number of records using the identity column to put into a cursor I get a 'Invalid Column Name' error. Anyone give me...more >>

I want to use a stored procedure and a calendar table to determine: 1) Is it the first business day of the month?; and 2) What was the last business day of the previous month? CREATE PROCEDURE procTest AS --psuedo code Is today the first business day of the month? If yes then determi...more >>

Hello, When I originally wrote the SQL below, I didn't think the last two fields could ever be zero, but I was wrong. How can I rewrite this so if NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero? SELECT (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE + (dbo.TDT_CUT_BLOCK...more >>

Hi, I have a database containing student historical data about subscriptions to departments in a school. Example data for 1 student: studID: 0001 deptID: 1 dateIN: 01/01/2004 dateOUT: 01/01/2005 studID: 0001 deptID: 1 dateIN: 01/01/2005 dateOUT: 01/01/2006 studID: 0001 deptID...more >>

Hi, I have a simple query, select emp_group,min(salary) from employees group by emp_group. now I get the results. Is there a way to get the ID(Primary key Column) value for which the query returned minimum salary. Thanks Kiran...more >>

I am trying to execute a batch file in my query using the xp_cmdshell command. I would like to add this to my stored procedure, but am currenly just using SQL query analyzer. Here is my code. declare @OrderNumber varchar(50) Set @OrderNumber = '2006-0160016' Update dtbl_labelInfo ...more >>

Hi, I wonder wether a software or a device (or script) allowing to migrate data from an existant database to a new database by using correspondant ODBC drivers existed. Thanks for your help. ...more >>

I am trying to do the following: Objective: Check to see if a store has recorded 'Actual' sales at least once for each three month period starting 1.1.00 through 3.1.05. At least once for Jan, Feb, Mar of 2000. Then at least once for Feb, Mar, Apr. Then Mar, Apr, May, and so on through to t...more >>

Hi guys, I wonder if I may ask for your help here. I have two tables RoleNames and Roles where RoleNames are the available roles and Roles are the Roles which the users 'may' have. RoleName ID int RoleName char(10) Roles RoleID int user_name NVCHAR I need to find our what roles a...more >>

I am not an expert in SQL I am faced with the task of copying certain attributes froma table in a database to create a table in another database to have a sort of refined backup database.I have to do it through a script to run in Sqlserver query analyser I have created a query of sort -> sel...more >>

Hi, I am trying the following on SQL Server 2005. I want to find out the permissions for different roles on database and database objects. So, selected all the records from the "sys.database_permissions" catalog view and joined it with the "sys.all_objects" catalog view. But I found some...more >>

I am getting a syntax error reported on line 9 (the last move or perhaps the one before it) If i remove the last line then it seems to be ok but I need to move the transaction log as well. Thanks BACKUP DATABASE Internal TO DISK = 'C:\Work Folder\Sql Server\testing.bak' RESTORE FILELIS...more >>

Hello, I am trying to run/test an SP in Query Analyzer. The SP takes an input param and outputs a value. How do I set this up in QA? Here is the SP CREATE proc sp_Company_Workshop_Exists @RecordID int, @WorkshopExists bit output as if exists ( select * from Workshop a inner joi...more >>

I'm coming across a problem with SQL2k. One of my SP which involved inserting and updating of records on a huge table (hundreds of millions records) is returning me errors without a proper error description. I'm suspecting that the maximum size of SQL2k table has been reached for this table...more >>

Someone else setup the table and I cannot change it, so I am stuck with what I have. I have an INT field called hourminute. The data is 3-4 digits, and is always based on hour/minute in 24-hour format. I need the output to be hh:mm. In the field, there is not a COLON and that is what I n...more >>

Is it possible to issue the CREATE TABLE command and specify a multiple-column primary key? If so, what is the syntax? I've checked BOL and as far as I can tell, you may only select a single column as the primary key *within the CREATE TABLE command*; ALTER TABLE must be used for multiple-column ...more >>

Hey guys, I'm trying to get the column names from a table to which I'm connected through OBDC. I don't want to use linked server. I get my data with Openrowset. I found the SQLDescribeCol and SQLColumns ODBC functions. Can anybody tell me how to use those functions in a SQL Server stored proce...more >>

Hi everyone, I'm hoping someone could give me some advice on how to achieve the following. I'm making a system that stores information on holiday homes. In particular I need to store information on their bookings. When is a home occupied and for how long. I've considered making a Bookin...more >>

I want to check a colum in a table to see if any rows contain a comma. How can I do this, is there a function in sql server to check for existance of a certain character in a field? thanks...more >>

I am attempting to perform a contains of one variable string in another. here is a simple example of what I am attempting to do, this should return true, but I am not sure if this is a limitation of sql server, that it will now allow a contains on two datatypes. Any ideas? declare @t1 varc...more >>

This query returns 1579 rows: SELECT DISTINCT CUSTOMER_NAME, CUSTOMER_ID FROM TRANSACTIONS INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERID = TRANSACTIONS.CUSTOMER_ID INNER JOIN ADMIN ON ADMIN.USER_NAME = TRANSACTIONS.USER_NAME WHERE ADMIN.COMPANY_ID NOT IN ...more >>

I am trying to replace a cursor with a CTE. Is it possible to scroll through the records returned using the CTE? i.e. If I get more than one row returned check the values in each row? A bit like a result set? I know there is some recursive stuff you can do but I don't think it will work ...more >>

I am faced with the problem of pulling out XML from a SQL Server database (using option explicit) when the level of nesting of some of the elements is unknown. The table structure is similar to the following: msgTable ------------- msgName msgID ------------- msgOne 10001 segTable ...more >>

I wanted to know what are the advantages/disadvantages of passing null values in the sp as oppose to setting them as default in the tables. I am using SQL 2005, "set ANSI_NULLS ON". Thanks ...more >>

SELECT CASE WHEN ISNUMERIC(t.clnum)=1 THEN (SELECT CASE WHEN PropertyAddressOptionCode = 'F' THEN 1 WHEN PropertyAddressOptionCo...more >>

I'd like to find the previous and next record of a table based on the numeric value of an identity column. For example, consider the following sample data: RecID | theValue --------------- 1 | first 2 | second 4 | third 6 | fourth 7 | fifth If the value '4' is pass...more >>

We are wanting to model standards for the equipment. But for each equipment, there may be a completely different set of characteristics that make up its standard. There's more to it than just this, but let's keep it simple here. What would be clear and obvious to anyone who's done even a basic A...more >>

Apologies if this is too basic. We have just changed from using MSS Microsoft SQL Server 6.x DBMS to ueing ODBC and the following no longer works: SELECT cffe_use_fld_nm.use_fld_id , cffe_use_fld_nm.use_fld_nm FROM cffe_use_fld_nm WHERE ( cffe_use_fld_nm.use_fld_id not in...more >>

Hi guys and gals, I need to pick your brains for a date/time series question. I'm trying to write a query that will displays accounts, with their different account types that overlap using the start and end dates. So say I have the following records:- AccountId - AccountType - Start ...more >>

Apologies for cross posting but I can't figure where to post. Please feel free to move my message elsewhere (notify mat<at>oldhallsoftware<.>com) cheers I have an ASP.net app that mobile service engineers use to exchange info with our back office systems thru GPRS. Works great but my com...more >>