I need to monitor database performance , how do I do it , can anyone help me....more >>

Hi Friends, In a single query, referening to view is faster or referening to base tabel is faster. Consider I've base table X in which i've column - ColA and i've one view vwX which built upon table X and many more table and that view has also column - ColA which is coming from table X. Now...more >>

Many DBAs are competely against having any type of web server on the database server box. Web server admins say, "what is the big deal?, We can tighten the web server down so hard, it'll be able to cut glass". In a practical since, let's say your company wants to use SQL Server Reporting Servic...more >>

Hello, How can I convert datetime value : 2005-10-11 00:00:00.000 to date value: 2005-10-11 ? Thanks, GB ...more >>

I'm using sql 2005. One thing I could do in sql 2k which I don't see in sql 05 is when I script objects in EM it would check to see if the object exists first, and if so, it would drop it before creating it. I don't see such an option in sql 05 SSMS. Its a much better scripting wizard but s...more >>

Dear friends: There is some confusion here about the choice of which index should be clustered. The choices are generally: - the surrogate identity column. - one or more columns that make up the natural key. My contention has been that the latter is the obvious choice. This is the ...more >>

I need to connect to the Sql Server 2005 on the local machine using C/C++ and am trying to find the fastest way of doing so. I'm dealing with up to a million records and usually lots of small selects. I also can't use joins/stored procedures at this point due to accomodating older software. ...more >>

Hello, I have created a small query that will output a list of email addresses that are separated by commas. I plan to copy and paste the list into a email invite field (send to: field for an email app). But what is happening is that only one address appears in the field when I paste because o...more >>

Hello I was given the task to "filter away" duplicate rows in a 10 million row table with about 30 columns :S And the table has no primary key, no constrains, nothing, and I need to find a way to clear that mess up, in a way... sigh You guys must think, alright, this is easy, just group by,...more >>

Hi, I was wondering if someone had a method to rebuild indexes automatically, based on the results of 'dbcc showcontig'. I want to automate index rebuilding by using the results of 'dbcc showcontig with tableresults' into a table. Then i want to query the results which indexes have a logi...more >>

Hi I'm trying to create a isBooked statement What I have is a db with: Date, StartTime,EndTime I currently run two SQL statements: 1) "SELECT Count(PupilID) FROM TblLessons WHERE " _ & "Date =@GetDate and Time between @GetTime and @GetTimeEnd" 2) Dim StrSqlEndTime As String = "SELECT Cou...more >>

I'm about to install VS.NET 2005 and SQL Server 2005 on a new/clean development machine (XP Pro/SP2 etc). Is the order of installation important (i.e., which product should I install first)? Thanks! ...more >>

Need some guru help with a monster T-SQL query. Trying to make it more efficient, possibly less code, and anything else I can do to it to make it run faster. This is an SP and Tables created by the person I replaced, but I need to deal with it....for now. I have a Status table that has 50...more >>

The sp below appeared to work fine in SQL 2000, i.e. to start a job, and then return a value on completion. Running this in SQL 2005 appears to just return a value indicating the job has started, but not necessarily completed. I was hoping for some feedback on this, as I don't know any other ...more >>

Hi Folks, I need to get a report of all my views and their dependencies. I know I can check this by right clicking and chosing Delete, then Check Dependencies, but I dont want to use this procedure as its dangerous and I want the whole output in one go. Regards ...more >>

In a Sql query I need to convert a given date to fiscal year. Fiscal year start from 1-Apr to 31-Mar each year and I need the fiscal year in the format like 2005-06 etc. Thanks ...more >>

I have an application where we are replacing a subsystem including portions of the database. In order to minimize the code impact on the existing application, we have decided to create a few "compatibility views" - i.e. database Views that produce the same result and with the same names as the ...more >>

Ok,,im stuck. I can access a remote 2000 sql server from sql 2005. I connect directly to the 2000 database ok. I can run queries on it all day. I can do the same thing with another remote ODBC server with SQL2005. THe question is how do you login to SQL2000 from sql 2005 and yet still be ...more >>

I am struggling with some syntax. I have created a couple of derived tables and now want to LEFT OUTER JOIN to them. Can someone help me??? Thanks in advance. Here's the SQL... SELECT DERIVE1A.column_1, DERIVE1A.column_2, DERIVE1A.column_3, DERIVE1A.column_4, DERIVE1A.column_5, ...more >>

I have created a full-text catalog in pubs and also include the lname of employee table in the index. However, I got empty result set with the SQL when execute in the Query Analyser : SELECT * FROM employee WHERE contains(lname, 'Perente') ...more >>

I have a table with a full-text index on a given column. If I run the following: declare @s varchar(100) select @s = 'fast' select * from tblHannahRES where contains(RES_SER_TI, @s) select * from tblHannahRES where contains(RES_SER_TI, 'fast') on my SQL2000/SP4 box I get substantially dif...more >>

public void mySqlProg { public mySqlProg() {} public String setNullYoTS() { String quot = "\'"; String nullifyMgrTS = "UPDATE REQUEST_TBL SET MGR_APPROVAL_TS="+" "+"WHERE TRANSACTION_GUID = "+quot+tranid+quot;; return nullifyMgrTS; } } when i execute the above statement, ...more >>

Is there any flag to enable ansi-92 joins syntax for sql server 2005? -Nuno...more >>

I have a query which join 5 tables say my result is Warehouse ProductName Features Price Manf I just need to make some analysis and need to select any 5 features from Features column for each productname. There can be 10 - 50 features mentioned in the table with features. ...more >>

Hi Guys, In Sql Server 2005 can I send a list of ids as a parameter to Stored Proc and then use it with the IN part of my SELECT statement. I know you can't with 2000 but was hoping that this would have changed with the new version. Cheers, Pete ...more >>

All - I have two assemblies (lets say AsmA and AsmB) loaded into SQL Server (Create Assembly) Both these assemblies have EXTERNAL_ACCESS permissions. When the code inside AsmA is executed (within SQLServer context), it tries to load a type in AsmB "dynamically" (using CreateInstanceAndUn...more >>

Hi, SQL 2000 sp3a How do I change the phisical location of the transaction log file? Thanks, Yan ...more >>

Hi, I am a relative novice when it comes to SQL Queries and converting them to ASP pages. I thought that I would be able to cut and paste from the SQL Query Analyzer and get most of the way there. My problem is a syntax error in the From part of the query. The query gives me the desired resul...more >>

I need to set up a trigger that updates a field in a record directly after it is inserted. I have been burning some serious cycles on this and can't figure it out. Any help would be apreciated. Here is what I have so far: CREATE TRIGGER DateMod ON tablename AFTER INSERT AS DECLARE @RECO...more >>

I am having problems with removing the update files from a folder. When a do a new backup, I want it to overwrite the old backup files with the new one. Apparently the code I am using is wrong. Any suggestions??? ...more >>

Are there a way to pass a expression as a parameter of a stored procedure? I am writing a stored procedure, There is a Select statement something like: Select Name, Street, City, State from CustomerAddress where state in ('MN','CA','TN') Are there a way passing "('MN','CA','TN')" as pa...more >>

What would be the best way to force SQL to fire a SPROC at 6 am each day? I'd appreciate any advice on different and best methods. ...more >>

I have two rows in table1 and these two rows are then duplicated in table2 making four rows. I then try and use the following query to select the four rows from table2. select * from Product P where P.ProductVersionID in (select ProductVersionID from PartSet where SetID = ???) Running the ...more >>

I am trying to determine if a server is available from a trigger in MSDE 2000. We found an example online in which SQL -DMO was embedded in a stored procedure (which I converted to a function) that attempts a connection to the instance in question and then returns success or failure. I have incl...more >>

sql server express won't install latest upgrade.. says it can't login as sa. my sa login has a password but uprade installation nevers asks for it. any suggestions on how to install upgrade ?? ...more >>

I am using the following script to insert a huge table. What's the right value for set rowcount? Should I execute checkpoint in loop? Any better approach? set rowcount 10000 -- wha'ts the right value WHILE @@rowcount > 0 BEGIN -- checkpoint insert into des select .... ...more >>

Does anyone know if Microsoft has released an installation package for SMO? The reason is as follows. In a .NET app we have, we reference SMO files. The server this app is running on does not have SQL 2005 installed, and we don't want to install it just yet. All I want to install are the file...more >>

Hi, I would like to count the number of orders for each month for each one of my customers over the last 24 months. The information will then be used into a Crystal Report Bar Chart. The problem I'm having is that Crystal will only display information that exists. Some of my customers do n...more >>

Hello, I have this Oracle query that does a hierarchical traverse : -- 1. attr_value_list_admin.pl -- Identify collection name of child container SELECT container_name FROM logical_container WHERE parent_id = 1 START WITH container_id = 338058 CONNECT BY PRIOR parent_id = container_...more >>

Hi i heard that in sql server 2005 they introduced MAX to replace text data type like strtemp varchar(MAX)... i have sql server 2005 and i am trying to use it but does seem like working .... i guess the only reason is.... my database server still on sql server 2000 but on client i have sql ser...more >>

I have an arbitrary number of tables in a SQL2000 db with names matching 'MyTable_%'. Is there a simple way of dropping these with a SQL command? LMcPhee...more >>

in my understanding of fixed numeric types and datatype precision rules, dividing two decimals with identical precision/scale should result in a decimal outcome with the same precision/scale. however, i run the following in query analyzer: declare @price decimal(38,10) declare @mult decima...more >>

I have a couple stored procedures in which I am trying to determine all of the "Default" constraints that are on a given table. I know that this information is available in the sysobjects table, but I'm trying to avoid directly querying the system tables whenever possible in favor of using th...more >>

is their any way to get result from EXEC function like if i send a query suppos select then i want to check is it return 0 record or more than 0 without using cursor thanks like count=exec @sql ...more >>

is their any way to get result from EXEC function like if i send a query suppos select then i want to check is it return 0 record or more than 0 without using cursor thanks like count=exec @sql...more >>

I have a program that creates and manages apartment buildings for construction management software. Previously, I had a stored procedure that, when handed a building name with floor count and units per floor integer, created a flat table with 5 columns for each room (one will hold the rooms "nam...more >>

Hi, I have a table that includes two Datetime columns. The first column holds the date. e.g 2006/5/10 and the second column holds time e,g 08:15:25. I am having a problems specifing a query that will list the rows in the table between a date time range. i.e return all events between 2...more >>

Hi! I need some help to calculate the time difference between two columns, Start_time and End_time. The column values represent the employees' start/end working hours and are stored as varchar(10) in the format hh:mm. Therefore i would like to subtract the start time from the end time to find ou...more >>

Hi there, I have this stored procedure I am trying to optimize: CREATE PROCEDURE uspLeaderboardStateResults @StateID int AS BEGIN CREATE TABLE #ResultsOutput ( rowID int not null identity(1,1), Rank int, MemberID int, MemberCode varchar(50), State varchar(5), ...more >>

Hi i have query which i need to join it like i am doing some thing like Select [a].[dbo].[tbla].* From [a].[dbo].[tbla] INNER INNER JOIN [B].[dbo].[tblB ON [a].[dbo].[tbla].[ID] = [b].[dbo].[tbla].[ID] and i am running that query in database A but the only problem ...more >>

I am looking to find records that share the same Policy_Desc but have different Policy_ids. I know there are records that fit this criteria but for some reason this query returns zero rows. SELECT Policy_Desc, Policy_id FROM Policy_Table AS Outside WHERE EXISTS ( SELECT 1 FRO...more >>

I have two tables Well status Date,wellid,gas 1/5,A,10 1/5,B,20 2/5,A,11 2/5,B,19 1/5,A,10 2/5,B,21 Well test Date,wellid,gas 20/4,A,10 2/5,A,10 19/4,B,20 the output I want is Date,Wellid,gas,LastweltestDate,GasfromLastwelltest 1/5,A,10, 20/4,10 1/5,B,20, 19/4,20 ...more >>

Dear all, I've got an issue which to encompass both sql and vb and I can't work out. I'm trying to retrieve data from an ASP page by ADO 2.6 and appears the following error: " Microsoft OLE DB Provider for SQL Server error '80040e37' Invalid object name 'GEN_VentDesp'. /mtotgen/prueba...more >>

hi, i've problems representing nested tree in sql server strucutre ... my scenario is : several process (p1,p2,p3,etc..) makes several operations (op1,op2,op3,...) i need to store the combination process,operation,time and this ismy table structure (processid,opid,dateop) now i need to show a...more >>

Hi all, I thought that ROW_NUMBER is designed to handle paging on large tables. However, when I test it on a table with 1,000,000 records, a simple select with ROW_NUMBER hangs for more than 10 minutes. Any idea what can be done? My table is: Events (ID, Date, Desc) My query is: ...more >>

Good morning all, I would like to use the sqldiag in order to diagnostic SQL server, so I am doing that in SQL query: xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL$MICROSOFTBCM\Binn\sqldiag.exe"' and I have this errors: 'C:\Program' is not recognized as an internal or exter...more >>

Dear all, I’ve made a SSIS package which take a sql statement and carry on to the .xls file but when I launch that package appears these errors: Error at Data Flow Task [Excel Destination [31]]: Column "descripcion" cannot convert between unicode and non-unicode string data types. (Mic...more >>