Dear all, I'm using SQL Server 2005 and would like to generate a script that would display for me the column names that are primary keys; For this target i checked the system views and found the view sys.key_constraints which display all primary key constraints including the constraint name, ob...more >>

Hi All, The following case is true both in SQL SERVER2000 and SQLSERVER2005. Have a table TA. Columns are say COLA1,COLA2,COLA3...... TB is a different table.Columns are say COLB1,COLB2..... Write a query: select COLA1 from TB -- This generates an error " Invalid Column Name 'COLA1'. ...more >>

Trying to go to the bottom of a table using Enterprise Manager, at record 369120 the following message showed up: "[Microsoft][ODBC SQL Server Driver][SQL Server]I/O error (torn page] detected during read at offset 0x0000026a400000 in file 'G:\...\data\ABC_Data.MDF'." I can't restore the data...more >>

I read the difference between sp_executesql and Exec() in www.sommarskog.se site. The point which I didn't understand is how to see the cached query plan OR how visually we can realize the sp_executesql is better than Exec()? They talk abt parameterised queries. How to see what parameterised ...more >>

How do we work around rowsize limitations in UNICODE database? I am using SQL Server 2000 SP3. We need to keep the database as UNICODE. I am looking to see if there is some settings that I can turn on to negate this behaviour....more >>

Howdy, I have one customer on SQL 7 and the rest on SQL 2000. How does one go about shrinking a database and its logs? I have a database that is taking up 6GB with 1.2GB free space and its logs are taking up 4.5GB with 4GB free space. That's a lot of space. How do I get that space back??? ...more >>

This is very interesting error. To get the error; -- Create new database, db1 -- run this query to create a table, create table test(testid int) -- Create new linked server for db1 -- Create another database, db2 -- Run these queries to create one temp table and one sp, creat...more >>

I am sorry if people have solved this kind of problem before, I wasn't able to find a quick solution to it other than have a subquery to do the work. Currently my data looks like this: provider_number provider_name site_location stat_count routine_count ---------...more >>

Could someone guide me as to how I can delete data from multiple tables in a database. Basically I have a Database which has 500 tables and I need to delete data from all 500 tables I dont want to type in delete from 'each table' 500 times. Would some one be able to help me with a script ...more >>

So I have a pretty extensive query that I run, which calls on several subqueries. The main query displays more records than what it should. I'm trying to include only 1 value '11' which can only reside in one of 20 columns RPTCTR01 - RPTCTR20. Then...I basically want any records that match the i...more >>

We have a stored procedure which dynamically creates a view. The variable which stores the Query is a varchar but due to the limitation on the size if the query is bigger than 8K we have an issue. Is there any other data type we can use or is there some other way we could do this? Below is how...more >>

I have a table, let's say it has 2 columns, Some_ID and LineNumber and has the following values: Some_ID LineNumber 1 0 1 0 2 0 2 0 2 0 I want to do an update, so the LineNumbers will be sequential per Some_...more >>

I was trying to create a function that used a cursor and was getting this error. Turns out that since I was borrowing code from a function written in SQL 2000, it did not like the fact that I used INSENSITIVE. Once I removed this albeit old key word, the function compiled successfully. DECL...more >>

I am trying to create a trigger to capture rows that have been deleted out of a table and who deleted the rows. I have been successfull in capturing rows that have been deleted but I am unable to capture who has deleted the row. For example if you are not the person who created the record the ...more >>

I am trying to create a trigger to capture rows that have been deleted out of a table and who deleted the rows. I have been successfull in capturing rows that have been deleted but I am unable to capture who has deleted the row. For example if you are not the person who created the record the ...more >>

I cannot understand the error from the following: DECLARE @pfx varchar(100) SET @pfx='[pow_old].mailhouse.dbo' SELECT @pfx INSERT INTO [MailHouse2].[dbo].ClientUseCode (ClientID, CountyFips, UseCode) select 1 as ClientID, u.countyfipsid as CountyFips, u.usecodeid as UseCode ...more >>

I was registered in a class with Learing Tree for SQL Admin "531", but they called and stated that they did not have enough people enrolled to hold the class and would reschedule us in the next class (3 months away). I was able to find another class tought at the same time by a company name...more >>

Perhaps somebody in this news group knows. In SSIS I have a foreach container thats got an filename enumeration collection of files in a directory stored in a variable I call FileName. Inside the foreach container, I have script task (vb.net) that I want to pass that FileName variable t...more >>

We have an app that uses app role security. (user authenticates, if successful, app role is asserted). Since conversion to sql 2k5 from 2k, execution of a particular stored proc seems dependant on the user's permissions, not the app role's permissions. ie, log in as a user with full permi...more >>

I want to run job over a period of time that essentially polls for a file and imports it if it exists. Let's say I run the job between 3:30 PM and 4:30 PM every 10 minutes. If the job runs and it's after 4:25 PM I want the job to take a different action. How do I check the time in an if statem...more >>

Hi everyone, I have the following table: create table Planning (ID int not null, BeginTime smalldatetime not null, EndTime smalldatetime not null, Type TinyInt not null) Now I also have 2 values representing a period: @Start and @End both smalldatetime. When the period is found withi...more >>

When ever I use one of my standard Insert/Update triggers in a table (see below), I can not make updates to the table via Enterprise Manager. I can update the table via Query Analyzer, VB, MS Access. Actually, it is not all the time. I currently have two databases, I can modify tables with th...more >>

Hi All I have a number series that increase by a set amount between 0.0150 and and 0.1000 and would like to sample this series to extract the closest number to an integer value. So for example all values closest to a multiple of 10. The number series has the field format numeric(9,4). The d...more >>

I have a table as follows, which shows the amounts of tickets sole by different sales rep and their department. A ticket can be sold jointly by more than 1 sales rep ticketID,salesRepID,SalesDept 18922115,829,QW9 18922115,821,QW9 18922115,712,QE9 18922116,712,QE9 18922116,615,S71 Where...more >>

hi, hw to convert minutes to days,hours and minutes thank you vanitha...more >>

ok my question wasnt worded great yesterday, what im trying to do is pull latest appointment from a database so if we had ID,Appointment,Name,date 1,Consumer1,REP1,10/12/2006 2,Consumer1,REP1,14/12/2006 3,Consumer1,REP1,13/12/2006 4,Consumer2,REP2,15/12/2006 5,Consumer3,REP3,10/12/20...more >>

Hi people, Im facing problem like when i each time opening the query, the connection dialog asks me the credentials everytime. Is there any option to supress it. Please help me in finding solutions. Regards, NavKrish ...more >>

For some time we have been loading our SPs into their own SP database. The SPs operate on data in a different database. This works OK and has advantages for us: to update the software we change the SP database, and when the data is backed up we don't save all the SPs as well. Both databas...more >>

Hi I have saved a DTS package as a .BAS file made some small amendments to the ordering of Tasks, is it possible to import this back in Enterprise manager? Kind Regards Ricky ...more >>

Hi to all, with the following query select V.CodiceGruppo, V.ContoGestionale, V.SottoContoGestionale from vConsuntivazioneMensileOreGruppoCdC V where V.ANNO =3D 2006 AND V.ContoGestionale like '%100%' order by V.CodiceGruppo, V.ContoGestionale, V.SottoContoGestionale I've the re...more >>

Hi All, I have a problem. We have two databases on same server and we are fetching database from one and inserting another one. To do that, we are connecting to second database via linked server. In stored prodecure, we are following these steps, --Select data from linked server, ins...more >>

Hi, I have a table TableFather, and other table TableA, TableB, ..., TableH. All those tables have a field name Status. The only situation that the Status in TableMother will be Pass is all TableA, ..., TalbleH's Status are Pass, if any Status in TableA ... TableH is Fail, then the Status i...more >>

Hi, In my SQL Server 2000, I would like to write a stored procedure for a particular table TableZ which has 50 columns, and the stored procedure will check if a column in a row is changed, and write down that change to another table TableLog, if we have 3 columns changed, the I insert the 3...more >>