Answer Questions

• HilliMonster scripting the isql.exe to export date and backup the DB SQL 2000

  We have no certified SQL DBAs. Mainly because 95% of our production data is on Oracle and DB2 databases. One of our Oracle DBAs is trying to write scripts that can be run by the AS400 robot to export data and backup the databases. I (being the only person with any SQL expierance) have been asked to ensure that the scripts she wrote will not only work but not bring down the server. Can anyone point me to the correct place (web, book, ect) to find out how this is to be done In years past my SQL servers have been stand alone dedicated units. With db backups scheduled through the SQL Manager and tape backups handled by the sysadmin. Please help, Cwells The SQL Server documentation should help (Books Online). -Matt ...Show All

• lordti The Cross Join Operator (*) Can be Very Slow

  Has anybody else seen cases where replacing the * operator with the CrossJoin() function will dramatically improves performance In a support case I have open with MS, they asked me to change my syntax in that way and I was very surprised by the improvement. I was also surprised because previously I've heard that CrossJoin() and the * operator " are completely equivalent from the functional and performance standpoint". The kicker is that the time intelligence wizard uses the * operater by default. The part of the sentence that I quoted came from this guy's blog: http://www.sqljunkies.com/WebLog/mosha/archive/2007/02/20/crossjoin_syntax.aspx Also, I'm not sure why a static optimization couldn't be app ...Show All

• Philip W SQL Express with Advanced Services - what Reporting Features does it have?

  I work for an ISV, and some of our customers use SQL Server, others use SQL Server Express. I need to develop some reports, so I was happy to read that SQL Express with Advanced Services would have some (limited ) Reporting Services available. Unfortunately, the sign-up period to beta test appears to be over, and so I am left wondering exactly what functions will be available. Is there anyone out there who has played around with the beta version reporting functions Greg, I have been experiencing the same issue you described. Seems like it might be related to VS2003. I installed/reinstalled several times on my computer with vs2003 installed. I never got a devenv.exe deployed to the IDE directory. ...Show All

• Chimme Making managed code calls inside SQL Server in context of the client

  Dear all, I am very new to the subject of writing CLR code inside SQL Server, so I apologise if my questions seem naive. I have a requirement to populate an asp.net 2.0 GridView control with data columns, some of which are directly from a SQL Server 2005 database, but some of which are calculated by calling CLR methods passing the values from the database columns to those methods. However, the methods I need to call only make sense in the process context of the client web site which is calling the stored procedure which I want to write to return the data columns. In effect, I want to be able to make a remote procedure call from within the SQL Server CLR code to the methods available in the client process. Is this possible If ...Show All

• Laura06 Disconnect All Ussers But Me

  I have setup a small SSIS package that will regularly delete my log files by detaching the database, removing the file and then reattaching it. Problem I'm having is that there is almost always at least one other connection to the database and I have to kill it for this to work. Is there any TSQL command that allows me to shut down all open connections except the one the command is coming from If you aren't worried about he log files, then set the database recovery model to "simple". That will keep the logs much smaller and increase the speed of updates to the tables. The way to shrink the log files is to use a maintenance plan. You can back it up and shrink the logs at night, which is what yo ...Show All

• Machhindra Default SqlServer database creation path

  Hi, How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this. Thanks Dishan it return empty result set... im not set specific path for data/log when installing sql2005. Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example: master . dbo . xp_instance_regread N 'HKEY_LOCAL_MACHINE' , N 'Software\Microsoft\MSSQLServer\MSSQLServer' , N 'DefaultData' , @SmoDefaultFile OUTPUT But fails for master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE ...Show All

• Sergio.Rykov SharePoint Add In Install Problem

  I upgraded my SQL Server Enterprise 2005 instance to SP2 CTP. I also have sharepoint server 2007 b2tr running on same machine. The portal is running fine off port 80, and Reporting Services is running fine off poer 8080. I configured reporting services with in Sharepoint Integrated mode, using the report services config utility. I downloaded sql server 2005 reporting services addin for sharepoint (SharepPointRS.msi dated 11/9/2007). When I try to install the add in I get the following error: "You must first install Sharepoint Server before installing SQL Server 2005 Reporting Services Add In for Sharepoint Technologies" I'm running SQL Server Enterprise 2005 x64 and Share Point Server 2007 B2TR x64. Any thou ...Show All

• DaveSmith Attach MySql database via internet

  Hie ! Please, how to manage (attach,connect to) MySql database located on host server http://www.avanti.si from my home PC with Microsoft SQL Server Management Studio Express This " http://www.avanti.si:8080\MySqlDatabaseName,3306 " fail ! Thenks! ooppss... did not notice/intended MySqlDatabase as a "MySql.. database" thank you Mike Actually, you can't connect directly to MySQL database, Management Studio is designed for use with SQL Server. You can create a linked server to any database that offers an OLEDB provider, which I believe MySQL has. The linked server will allow you to query your data from MySQL, but not manage the database. Regards, Mike hi, you should ask yo ...Show All

• srinivasintouch SQL 2005 Enterprise to 2005 Standard

  We have a situation where we installed the Enterprise version of SQL Server 2005 on a server that is now in production and we had intended to install the standard edition (what we are licensed for). We have enterprise licenses for other servers so we have both types of media and it was a mistake that was made. I was wondering if there is a way to downgrade the version without uninstalling and reinstalling and having to backup and restore all the databases, jobs, SSIS packages, etc. Any suggestions or help would be much appreciated. Thanks, John Nay. Microsoft is quick to tell you how easily you can upgrade from one version to another ... downgrading though (with the exception of license rights) ...Show All

• Gerhard Brueckl Need help in starting out

  Hi Everyone: I am just starting out on SSIS, and have the following business challenge that I would like to solve utilizing SSIS. There are two SQL Server data stores, one would be the source, and other would be the destination. I need to grab some data from store 1, and insert that data into store 2. Here are my questions: 1) Before i move the data to store 2, i have to check some criterias by callin some sps, and then the end result would need to be inserted into the destination database. Can anyone tell me how would i setup the control flow, to accomodate that in SSIS 2) Can someone email me a sample package that does something similar I just need a starting point so i can build the design of my package. Basically i need to ...Show All

• Oskar2 SQL Server Management Studio Summary Page Columns

  Hello, In SQL 2000 EM, one could view the database jobs and sort them by say, job category, or enabled/disabled, or next exection time. Can this be done using SSMS I would assume if columns could be added to a summary page for the jobs, these new columns would appear in the filter page Can columns be added Unless I'm missing something, this method of displaying jobs makes quickly assessing the status of a job a very tedious process. Especially if there are dozens of jobs defined. TIA, Greg Wilkerson This is a commonly requested feature. It has been reported in Microsoft Connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=124809 . Please go there and vote on ...Show All

• Sue Mosher - Outlook MVP App receiving "Options" message from Service Broker

  I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx ) When I run this app against a remote SQL Server, I receive the message "Updated" which I expect. But when I run the same app against the local machine SQL Server, I receive the message "Options". Does anyone know if there are SQL Server options that must be set to certain values I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint. The recommended way of fixing this is to actualy run the SET options from the user connection. Thi ...Show All

• Amos Soma DatePart Function

  Hi, I'm curren't writing a stored procedure for my sql server express database and need to display the year part of a date field as '04' but using the DatePart function it will only display as '2004' Is it possible to get the Year part of the date to display showing the last 2 digits of the Year only I don't require another part of the date field just the Year part. All help is well come. Code been used is shown below: DATEPART ( yy , [Date] ) Thanks Geert it worked a treat!! Hi, The datepart function doesn't have an argument to do this. You can do the following to get the desired result: SELECT RIGHT( DATEPART ( yy , [Date] ), 2 ) Greetz, Ge ...Show All

• Kevin Berry Does SQL 2005 encryption on x64 work?

  Are there any known issues with EncryptByKey/DecryptByKey on x64 machines I have a test script where I create a sample table and encrypt a column and later decrypt it. It works fine on my x86 box. When I run the *exact* same test script on an x64 server I'm getting unprintable characters back on the DecryptByKey. I cannont find anything I'm doing different between the two. Has anyone seen anything like this before We have not seen this problems yet but I will take a look and attempt to repro this. Would it be possible to get a copy of the script you are using (obviously, please remove all private/sensitive info first) Thanks, Sung Nevermind! I figured it out. I was doing an INSERT INTO SELECT FROM... a ...Show All

• Joe Black Is there a load event or run event?

  Is there a load event or report run event for a report that can be accessed I need to have my report jump to a certain url whenever it is run but I cannot use the jump to url because that has to be clicked. I need this to occur automatically. Any suggestions I tried this but got an error. "An error occurred while executing OnInit: The remote server returned an error: (401) Unauthorized. I tried the exact same thing Protected Overrides Sub OnInit() Dim request As System.Net.WebRequest = System.Net.WebRequest.Create("http://www.example.com") Dim response As System.Net. WebResponse = request.GetResponse() End Sub And that did not work, So I tried putting a msgbox in the routine and the msgbox did ...Show All