Answer Questions

• hye_heena SQL Job Management

  I have a customer with a complex application that they are moving from SQL 2000 to SQL 2005. In the process, we are moving to a consolidated and shared SQL environment. In the past, the customer's application support team has had sysadmin on the database server in order to perform a number of management tasks, including creating/modifying/deleting jobs and job schedules. I am uncomfortable with this solution in an evironment where carelessness on their part could potentially impact other applications. I have been able to address all of their requirements except the the job management aspect. The existing fixed DB roles for msdb for SQL Agent fit the bill, except for the ownership restriction. They way I read the documentation, all of t ...Show All

• Quirk how to find out minimum size

  hello i have one doubt.can anybody clarigy it. every time i am doing my database shrinking manually. what i am asking i find out every time when i shrink my database that sql is showing minimum size of database to shrink.how can its getting minimum size but i need to find out how to get minimum size of particular database programatically. thanx Kiran - see the answer I posted in the getting started forum ... where you posted rod colledge can u send me that url excuse my poor english "every time i am doing my database shrinking manually." Bad planning. Disk is cheap. You probably don’t backup your database acording to the recovery model of it. Shrinking database files driv ...Show All

• QWERTYtech Reusability & SSIS - issues they do not write in books about

  I have a simple requirement: Each package needs to have Error Handling - which needs to Execute a SQL statement. It's the same Stored Proc - where each package passes in its ID. Ok - I get that part about creating a custom task and so on for reusabilty. But - lets say that after deploying this task in 20 packages - I need to change the name of the Stored Proc. What is going to happen in that case Correct me if I'm wrong - but after deploying the new version of the custom task - do I need to go to each package and update the reference to new version Not quite - configuration cannot handle when the processing logic changes. Lets say for instance in addition to ID input of the Stored Proc - the Custom task / Error handler --> you n ...Show All

• Henry_Yang Conversation Group

  I have not been successfull in getting conversation group to work. My understanding is that I can specify a 'guid' for a conversation group id in the create dialog and when I send a message on this conversation it will have that specific guid for its conversation group. When I do this it does not appear this way in the "target" queue. I am looking for an example to help me understand how to use a conversation group. The MSDN has not really provided that run able example that I can run and verify and tweak. The idea that I would like to try is that the initiator must send 5 different XML messages to a target. These 5 messages are all related and must exist together. What I assume is that if I want the target to get all 5 ...Show All

• SaloS sql 2005 downgrade to sql 7.0

  Hello people, I'm experiencing problem here that we need to downgrade our sql 2005 back to version 7.0. Apparently, all the mdf files from 2005 are recognized as "corrupted" by the 7.0 server. All my dbs are created within 2005 and i'd like to move these to the older 7.0 server. I wonder whether there are any procedure to convert 2005 mdf files so that is is compatible with sql server 7.0 or what is the most common way of solving this Any pointer would be appreciated. thx! No, you will have to export / import the data to / from the SQL Servers. There are internal changes within the database that cam’t be read by SQL Server 7.0 HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All

• John Sobernheim SQL problem !

  I dont know weather this is the right place to post this issue. If not please suggest me , I did not find anything right then this one. Ok now the problem. I've got a table TABLE_A --------- name -- vharchar(50) age -- integer DOB -- date I need to select records from this table depending on a selection criterion. thats easy , but i want to know that how many columns matched in the searching because i need to store the rating for how many columns matched for a particular search. Ex. if name , age and DOB matched rating is A3 if name , age matches rating is A2 and so on. This table is a huge one having 20 Million records, so I want a solution that is optimized too. How you are searc ...Show All

• bharathi_tunes Automatic failover failure

  Hello, we're running a mirrored database with High Availability for Automatic failover including a Witness instance for a web application. When doing a manual failover on the database in Management studio, the roles are switched correctly and the database is in "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. The web application has no problems switching servers by using client failover with the jdbc driver. There is no problem accessing the database with Management Studio. However, if we stop the SQL service on the Principal server the role is automatically failed over to the Mirror server by the Witness. The database is then in the mode "Principal, Disconnected" which should be f ...Show All

• cosminb Identifier too long

  hi there, i have following code SET QUOTED_IDENTIFIER ON DECLARE @sTableDiff varchar(1000) SET @sTableDiff= " ""C:\Program Files\Microsoft SQL Server\90\COM\tablediff"" -sourceserver ""VSNET1"" -sourceuser ""sa"" -sourcepassword ""sa2345"" -sourcedatabase ""PMSTEMP"" -sourcetable ""DEMAT"" -destinationserver ""VSNET1"" -destinationuser ""sa"" -destinationpassword ""sa2345"" -destinationdatabase ""PMSRESTORE"" -destinationtable ""DEMAT"" -f ""c:\Diff""" PRINT @sTableDiff EXEC XP_CMDSHELL @sTa ...Show All

• Mile Petrov Populating levels and members

  The code given below helps in retrieval of dimension levels and members cubenamev stores the cubename dimyv stores the name of the dimension Dim dbConn As New ADODB.Connection dbConn.Open(strconn) Dim dtCatalog As New ADOMD.Catalog dtCatalog.ActiveConnection = CType (dbConn, Object ) 'code to populate levels and members Dim cubes As ADOMD.CubeDefs = dtCatalog.CubeDefs Dim cube As ADOMD.CubeDef Dim cubedefn As ADOMD.CubeDef cubedefn = cubes(cubenamev) Dim dimery As ADOMD.Dimension dimery = cubedefn.Dimensions(dimyv) Dim leveler = dimery.Hierarchies(0).Levels Dim level As ADOMD.Level Dim ...Show All

• Daniel Shevchenko Cannot transfer/copy database in one step like I could with 2000.

  Hi, I would like to know if there will every be a clean way to transfer a database from a remote server to a local server (and back again). I've tried several different approaches, but they all currently have bugs/problems. I've looked into database publishing wizard - but this creates a huge script that takes 10x longer to run than the old DTS transfer. I've tried copying data, and objects separate, but this requires multiple steps, using DTS this was all done in one step. I've tried using SSIS Transfer SQL Objects, but the defaults are not created, using DTS, the ENTIRE database was transfered, which is what I want. I've tried using SSIS Transfer Database, but even when I select "Online" mode, I get an error aski ...Show All

• Cyber Junkie First and Last record in a set

  Anyone have an effecient ideas on how to do the following: For example lets say the data is Temperature over time.For example lets say the data is Temperature over time. DataSet (from a file) comes in sequentially (I've even added a "RowNumber" column). I then drop some temperature records that I determine to be bad.. so the RowNumber sequence would typically look like 5,6,7,8,10,11 etc.. (often the first few readings are bad). What I need out of the set is a bunch of aggregates, High, Low, Average, Count of Readings etc... all those are easy with the Aggregate object in SSIS... however, I struggle to get the starting temp and ending temp. What I have done is multicast the data into the following streams 1. Goes thro ...Show All

• adurling Script Task Variables

  script task: there should be another way to select variables than the comma seperated list One has to type in a whole list of variables, hoping not to make any mistakes IntelliSense for example But hey, I'm not complaining... greets   There is! You can do it in code. Writing to a variable from a script task ( http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx ) Still no intellisense though!! I highly recommend you use the code option because this minimises the risk of variable locking. I plan to blog about this soon and have written about it in an upcoming article in SQL Server Standard. -Jamie ok, I'll do it your way thanks Dear J ...Show All

• Josh Petrie Failed to update database because the database is read-only.

  I am building a Windows Forms application in VS 2005, using C# and SQL Server 2005 Express as the backend. When I try to accessd data from the db, it works with no problems. When I try to insert/update/delete, I get the following error message Failed to update database "DBNAME" because the database is read-only. I am using a connection string with the following syntax: "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\PATH_TO_DB\db.mdf;Database=MyDB;Integrated Security=False;User Instance=False;User ID=USR;Password=PWD;" I have made sure that the permissions on the DB are set (in Windows) so that all users can modify. The user that is logging in to the DB has status set to DBOWNER. I have ...Show All

• nhaas Sql Query to update a Column when the subquery returns more than one row

  Hi People, I am having a table which has some 10 cols, only one column had all Nulls. DB-SQL2K5 I am now writing a query like Update Test1 set Id = (Select t2.Id from Test2 t2, Test1 t1 where t2.Name = t1.Name) as likely this query is faling as the sub query is retuning more than a row. What is the best method to achive my requirement Thanks Use this please Update Test1 Set Id=T2.ID From Test1 T1,Test2 T2 Where T1.Name=T2.Name try this..   Update Test1 set Id = t2.Id from Test2 t2 where t2.Name = Test1.Name You modify the subqery a little bit: Update Test1 set Id = (Select top 1 t2.Id from Test2 t2, Test1 t1 where ...Show All

• LiveGadgets I can't connect to http://localhost/Reports - rsinternal error

  After a new installation I can't connect to http://localhost/Reports - rsinternal error The logfiles give me the following: (haven't been able to find an answer to this issue) <Header> <Product>Microsoft SQL Server Reporting Services Version 8.00.1038.00</Product> <Locale>en-US</Locale> <TimeZone>W. Europe Daylight Time</TimeZone> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles\ReportServer__07_28_2006_11_27_56.log</Path> <SystemName>BUIS8907-6</SystemName> <OSName>Microsoft Windows NT 5.1.2600.0</OSName> <OSVersion>5.1.2600.0</OSVersion> </Header> aspnet_wp!webserver!b0c!7/28/2006-1 ...Show All