InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:42:52 GMT20http://www.sqlservercentral.com/Forums/Topic1305989-1550-1.aspxHi, I have table 'A' and wants to copy all data to table 'B'. Table 'B' is empty and all the contents from table 'A' will be copied over to table 'B'. Now the data from table 'A' will be copied to table 'B' every day. So for example : table 'A' has 10 rows then those 10 rows will be copied to table 'B'. Now table 'B' has one more column named date_time to track time. So this process will occur every day so the data copies every day and never gets deleted. So after 5 days table 'B' will have 50 rows and continues increasing. Now this needs to run as a job. Can someone instruct me on how to create a job.Thu, 24 May 2012 10:15:58 GMTespanolanthonyhttp://www.sqlservercentral.com/Forums/Topic1305823-1550-1.aspxHi,I have to change the Collation level setting for existing SQL Server 2000.In this server i have around 20 database which all have collation level "SQL_Latin1_General_Pref_CP437_CI_AS" and SQL server 2000 has it's default collation level which is different the current database collation level.I need to change the serevr level collation and make it same as database collation which is "SQL_Latin1_General_Pref_CP437_CI_AS".I came across below article doing search on this issue.http://msdn.microsoft.com/en-us/library/aa197950%28v=sql.80%29.aspxI don't have much experince with SQL 2000 administration, so i am looking for some guidence here.Please give me some guidence or issue i can face during this change.Thanks in advance for your Help.Thu, 24 May 2012 07:28:41 GMTJinu43http://www.sqlservercentral.com/Forums/Topic1305554-1550-1.aspxHi all,Now there is one problem about CN Principal DB and US Mirror DB.The status of principal is Principal/Disconneted and the mirror database(having no status) seems like read-write(but I cannot access the database ,the error message is: database is not accessible) .When I run the following sql select state_desc from sys.databases where name='dbname',it showes that the query result is :"Recovery_Pending".BTW,there are other databases mirrored between the two instances, and they ran normally.Then check the database error log on the mirror instance:Unable to open the physical file "D:\DATA\Tfs_TFS2005Collection\LOG\TfsVersionControl.LDF". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".A file activation error occurred. The physical file name 'D:\DATA\Tfs_TFS2005Collection\LOG\TfsVersionControl.LDF' may be incorrect. Diagnose and correct additional errors, and retry the operation.We have tried the below methods to fix the error one by one,but they could not work.1:alter database dbname set partner resume----on the principal2:alter endpoint endponitname state=stopped -----on principal and mirroralter endpoint endponitname state=started3:ALTER DATABASE dbname SET PARTNER='TCP://Principal:5022'----on mirrorWhen executing the third sql statement, there was one error came out.Msg 945, Level 14, State 2, Line 1 Database 'Tfs_TFS2005Collection' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.Appreciate your help,Thanks in advance.Thu, 24 May 2012 01:09:51 GMTWisonhttp://www.sqlservercentral.com/Forums/Topic1304115-1550-1.aspxOS - Win 2003 Enterprise EditionDatabase - SQL 2008 Enterprise Edition Memory - 4GBplease tell me, how to measure /PAE really using or not after configured as below boot.ini file.boot loader]timeout=5default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS[operating systems]multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAEThanksanandaTue, 22 May 2012 06:54:07 GMTananda.murugesanhttp://www.sqlservercentral.com/Forums/Topic1305491-1550-1.aspxWe know that we can make use Oracle as the publisher and SQLServer as the subscriber.But how about Oracle RAC?Should I create the publication on each node or anyone of the nodes?Wed, 23 May 2012 21:30:35 GMTWisonhttp://www.sqlservercentral.com/Forums/Topic1305422-1550-1.aspxI would like to know when is the upgrade of databases really happen?For example, I am migrating a database from SQL server 2005 to a new server that has SQl server 2008 R2 installed, does the database upgrade process happen when I restore the database?The reason I ask this is there is another option to move the database, that is to Attach databases with the files, -- if we copy over the .mdf and .ldf files to the new server, and attach them, does this process do the upgrade of databases too? Thanks,Wed, 23 May 2012 15:59:06 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1305322-1550-1.aspxAfter migrate databases from SQL server 2005 to a new server with 2008 R2, is it a required steps to run the following:1. check database integrity task2. Update statistics.3. rebuild index.or 3 is not necessary?ThanksWed, 23 May 2012 13:44:28 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1305118-1550-1.aspxI created a maintenance plan for my database that will create Full database backups every week. I noticed that the size of the .BAK file is decreased from previous week's file size? i thought i should always see the file growing, but why is it decreased?can someone tell me why this happens?1st Sunday(4/24)- 5.76 MB(full db backup)2nd Sunday(4/29)- 12.2 MB(full db backup)3rd Sunday(5/6)- 6.83 MB(full db backup)Wed, 23 May 2012 10:22:00 GMTpreetid2http://www.sqlservercentral.com/Forums/Topic1305096-1550-1.aspxI see there are two some windows group, it was created when SQL server was installed.The windows group name are like SQLServerSQLAgentUser$MyComputerName$MSSQLSERVER, SQLServerMSSQLUser$MycomputerName$MSSQLSERVER etc,all these names have the server name in the group name.My question is when we migrate or upgrade server and move to a new server, we usually need to rename server name. We know we need to change physical computer name, and also use sp_dropserver, and sp_addserver. But after renaming server, some footprint of old server name still exist in windows, like the ones above.Do we need to change the name, or leave as they are, if changed, will it affect SQL server?I search in all microsoft site, cannot find an answer for this.Wed, 23 May 2012 10:02:16 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1304180-1550-1.aspxIs it possible to automate an occassional copy of database tables with a datestamp against each row, then perhaps a week later to add any changed rows with a new datestamp against it? So over time a single database with the same tables as the original will also contain dated changes.The idea is to keep a record of changes with as little overheads as possible, and that current reports could use the new database with minimal changes.I have read up on CDC and snapshots but not sure how to achieve a single database with minimal overhead.[Follow up] Sorry to push this up again, but if there is any more information needed please let me know. I'm not looking for a complete solution - just some pointers in the right direction.Tue, 22 May 2012 08:04:46 GMThoggyhttp://www.sqlservercentral.com/Forums/Topic1304619-1550-1.aspxI need to rename a reporting server which is installed SQL server 2008 R2 , both database and reporting service is on the same server.Now after we rename the phyical computer name, then I need to change the sql server name by using,EXEC master.dbo.sp_dropserver ‘[SERVER NAME]‘EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]‘, ‘local’And restart the server,What is the next step for renaming the reporting server, thanks,I have read the http://msdn.microsoft.com/en-us/library/ms345235(v=sql.105).aspxBut it is not clearly said:So in my case, should I do the first one: Renaming a SQL Server Database Engineor should I do the second one too: Renaming a Report Server ComputerIf it is the first one quoted as below Renaming a SQL Server Database Engine--------------------------------------------------------------------------------If you rename the SQL Server Database Engine instance that runs the report server database, do the following:1.Start the Reporting Services Configuration tool and connect to the report server that uses the report server database on the renamed server.--[u]Question: when it says the renamed server above, does it mean after physical server name change, and also after I changed the sql server name by using sp_addserver[/u]2.Open the Database Setup page.3.In Server Name, type or select the SQL Server name, and then click Connect.4.Click Apply.If the report server is using a local Database Engine instance, you can use (local) or (local)\instancename to specify the server. If you use (local) to refer to the server, you can rename the server and the connections will continue to work. If you are using a remote server, or if Reporting Services is configured using the server name, you must update the database connection information whenever the server name is changed.[u]--Question: I tried in report configuration tool to put the (local), it doesn't work[/u]ThanksTue, 22 May 2012 16:49:13 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1302137-1550-1.aspxHi,I have installed sql server 2008 R2 standard edition twice, default and another named, both of the installations failed to install sql server, but the thing is they are capturing the registry as if they are installed, taking the default instance spot with the services shown in the configuration manager,I have tried to uninstall it from control panel, programs and feautures, but its not removing that feature from it,Can someone please tell me the steps to remove it manually from the registry so that i can be able to install default instance once again.ThanksThu, 17 May 2012 14:17:25 GMTALIF-662928http://www.sqlservercentral.com/Forums/Topic1304337-1550-1.aspxGood day,I have two instances of SQL 2008 installed on a single server, and I need to uninstall the one instance.When I run the Uninstall (using the programs add remove feature of windows 2008), I get the error:[b]The CPU architectures of feature(s) you selected to removed are different then Setup. To remove these features, Setup architecture must be the same as the features being installed.[/b]Any ideas?thanksTue, 22 May 2012 11:19:03 GMTWandraghttp://www.sqlservercentral.com/Forums/Topic1301588-1550-1.aspxHi All,I am in situation where I am not sure how to divide / Partition the table, and send the Data to Multipple File Group / Files.So I want to implement the Hash Partition as we do for the Table as we do for the Tables in Oracle.Please need your pointers.Thanks & Regards,Sudhir Nune.Thu, 17 May 2012 03:48:09 GMTsudhirnunehttp://www.sqlservercentral.com/Forums/Topic1304112-1550-1.aspxlast night I had to kill a processes/job that normally takes 1 min to run but for some reason it was hung for about 8+ hours. Nothing was blocking the process before I killed it so I have no idea why it would not complete. After I killed the processe it has been in killed/rollback status for 7 hours+. When I run kill 55 with statusonly I get "SPID 55: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds." the spid is running with program name .Net SqlClient Data Provider (biztalk). . After doing some research i read that by restarting the DTC or sql service might resolve the problem. Does anyone suggest to try anything else? If i restart the DTC in my cluster, can it create any issues with my other transactions?Tue, 22 May 2012 06:52:19 GMTDBA-640728http://www.sqlservercentral.com/Forums/Topic1304447-1550-1.aspxHi everybody,I need to restore a SQL Server database that is 60GB and have 75% free space is set with the Initial Size of 58GB with Autogroth of 2GB. Is there a way to Recover just the used space so the database size would be 10GB instead of 60?Environment: SQL Server 2008 Standard 64bit; Operating System: Windows Server 2008 R2 Enterprise.Simple Recovery model (but can switch it if needed)Any help and cooperation would be greatly appreciated.AlexTue, 22 May 2012 13:16:46 GMTAERhttp://www.sqlservercentral.com/Forums/Topic1304273-1550-1.aspxHello,I'm in the process of planning an upgrade from SQL 2000 and SQL 2005 to SQL 2008 R2. I was wondering if anyone has a good procedure for checking the existing SQL 2000 and SQL 2005 databases for this deprecated feature warning: Not ending Transact-SQL statements with a semicolon. We have tons of SQL code; does this mean we have to find every single statement in those tons of code and add a semicolon after each statement in it? Does that include SET and GO statements as well, in other words do they have to be modified to, say, SET ANSI_NULLS ON; and GO; ? If so, does anyone have any scripts or recommended tools to make this easier?Thanks for any help!-webrunnerTue, 22 May 2012 10:01:21 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic1302822-1550-1.aspxHello All, I wouldl ike to know how to export the data from excel sheet to database. I have already a table structure in the database and now I would like to Export the data from excel sheet. I can do by Import and export wizard.But the problem is with the data types conversion. I am receiving as nvarchar. The other option I found is by copy n paste onto database. But the real problem is I have around 100 tables to work on, I can't keep on copy and paste onto database. Do we have any solution where we can export the data from excel sheet to database, where the data types won't get changed. Please, suggest me some solution. Thank you.Fri, 18 May 2012 14:06:23 GMTDBA_SQLhttp://www.sqlservercentral.com/Forums/Topic1303293-1550-1.aspxHi,I was trying to create a table by importing an excel sheet through the import/export wizard.Im ending up getting the following error.[i]Operation stopped...- Initializing Data Flow Task (Success)- Initializing Connections (Success)- Setting SQL Command (Success)- Setting Source Connection (Success)- Setting Destination Connection (Success)- Validating (Success)- Prepare for Execute (Stopped)- Pre-execute (Stopped)- Executing (Error)MessagesError 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE [dbo].[Sheet1$] ([ID] float,[Name..." failed with the following error: "profile name is not valid". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. (SQL Server Import and Export Wizard) - Copying to [dbo].[Sheet1$] (Stopped)- Post-execute (Stopped)[/i]I guess there is a problem with the db mail i have configured. not sure though. Thanks in advance :-) !Mon, 21 May 2012 05:03:35 GMTsufiyan.sargurohhttp://www.sqlservercentral.com/Forums/Topic1304102-1550-1.aspxI separated a Database into two Databases Staging and Data Warehouse.In doing so I moved started moving the schemas to a new Physical Server, Windows Server 2008 R2 64 bit with SQL Server 2008 R2.The server is fast however I'm accessing an AS400 over a B2B.When I create a Stored Procedure that references the Linked Server via an Open Query it takes several attempts and I get the following error. [code="plain"]OLE DB provider "MSDASQL" for linked server "LinkedServerDef" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".OLE DB provider "MSDASQL" for linked server "LinkedServerDef" returned message "[IBM][System i Access ODBC Driver]Communication link failure. comm rc=10060 - CWBCO1048 - A firewall blockage or time-out occurred trying to connect to the System i".Msg 7303, Level 16, State 1, Procedure usp_merge_into_Unit_Detail_Name_Description, Line 3Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerDef".[/code]I know that this is an IBM Error but is it Cause or Effect.This did not happen on another Server, a Virtual Server with a lot less resources.I started getting this error when running SSIS Packages. I was running tasks contained in Several Containers in Parallel. When I reduced the number of task that ran in parallel and tweaked it I was eventually able to avoid the error.I'm not sure what could be causing this problem and what steps to minimize the probability of this from reoccurring?Tue, 22 May 2012 06:35:15 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1303723-1550-1.aspxHello EveryoneI hope that you had a nice weekend.I am getting ready to break the mirror between two SQL Servers. I know how to do this, but is there anything that I should be on the lookout for before breaking the mirror? Anyone ever had a mirror that would not break?Let me know if you have ever had any hang ups or snags.Andrew SQLDBAMon, 21 May 2012 14:26:34 GMTAndrewSQLDBAhttp://www.sqlservercentral.com/Forums/Topic1303452-1550-1.aspxHi, I'm a beginner DBA. I created 2 seperate maintenance plans (from SSMS -> Task-> Backup wizard)to backup my database- Full and Diff. Differential .BAK files are created daily from Mon thru Sat, and Sunday Full .BAK file is created. If i understand correct, the Differential database backup is the backup of all the changes made in database from last full backup; it is cumulative itself. so the size of next differential database backup is more than the previous differential database backup. The size of Full database .BAK file sizes from previous Sunday to next Sunday is growing, which is good. but the next Sunday db Full .BAK file size doesn't add up to be the size of the last Full db .BAK + most recent Diff db .BAK file size.look below my db file sizes...1st Sunday - 7.53 MB (Full .Bak file size) Mon - 2.34 MB (Diff .Bak file size) Tue - 3.34 MB (Diff .Bak file size) Wed - 3.78 MB (Diff .Bak file size) Thur - 4.22 MB (Diff .Bak file size) Fri - 4.35 MB (Diff .Bak file size) Sat - 4.47 MB (Diff .Bak file size)2nd Sunday - 8.10 MB (Full .BAK file size)shouldn't my 2nd Sunday Full .BAK file size be 12 MB (7.53 MB (1st Sun) + 4.47 MB (Sat) )? but why is it only 8.10 MB (slight change only .57 MB) ? sorry if i'm asking the basic question. i appreciate if someone can answer my question.Thanks!Mon, 21 May 2012 09:17:35 GMTpreetid2http://www.sqlservercentral.com/Forums/Topic1302306-1550-1.aspxHi all,I have successfully created a DB mail profile.However , when i try to send a test-mail, i get a message as follows :The test mail has been queued for processing. Depending on n/w speed bla bla ... But when i check the db mail log , i see that the operation has failed with the below error :[i]The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2012-05-18T12:10:35). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 172.17.24.128:25).[/i]I guess this is because of some mistake in the server adress or the port. The adress i have provided is that of my outlook excahnge server as the email id provided is my outlook id. Port number was the default 25.Also, this is an Exress with advanced features 2008 r2 server installed on the local machine. Thanks in advance :-)Fri, 18 May 2012 01:13:15 GMTsufiyan.sargurohhttp://www.sqlservercentral.com/Forums/Topic1304003-1550-1.aspxHi All,There is a column in sys.sysindexes named 'FIRST', which gives the first or root page. I came across a script which extracted file id from this field in following manner:Column 'FIRST' returns value 0xC54CFB020100 for an object, then script extracts 6th and 5th location characters (in this case 'C' and 4), combines them and string becomes C4, converts it to INT and that becomes file id..... I want to know what relation exists between this field and file id, is there any naming convention of pages which contain file number in between their names?Thanks in advance.Tue, 22 May 2012 03:47:14 GMTMI_DBAhttp://www.sqlservercentral.com/Forums/Topic1253683-1550-1.aspxToday I found TempDb size has increased and disk space is very low. I need to get more free space in disk. And I tried to shrink TempDb but it is not getting any free space in disk. So May i know that what is alternative step to take to get free space by without restarting SQL Server. Because it is in production environment the users are connected. My disk Used Space is 15.6 GB and Free Space is 4.29 GBFri, 17 Feb 2012 02:32:01 GMTmohan.bndrhttp://www.sqlservercentral.com/Forums/Topic1303163-1550-1.aspxHi Guys I have detached the system databases and attached the master database but the models database won’t get attached. Get the following error A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)FYI - Shared memory is enabled - Tried doing this as admin:sqlinstance Appreciate your feedback ASAPCheersJude Sun, 20 May 2012 22:23:37 GMTjude.pierieshttp://www.sqlservercentral.com/Forums/Topic1303808-1550-1.aspxHello everyone,I am working to upgrade SQLServer 2000 to SQLServer 2008R2. For this, I have created a development environment with Windows Server 2008R2 running SQLServer 2008R2. I adopt the following approach.1. Took backup of databases from SQLServer 2000 and restored on SQLServer 2008R2.2. Now on 2008R2, I deleted all the logins from existing databases and left the default logins.3. I created all those logins with exact username and password, because I doubt that those logins were used in the application.I was stuck while trying to transfer the non-system stored procedures of 2000 Master database to 2008 Master database. What I found was, there are 70 stored procedures which are not available on 2008 Master database. Few of them are being created by the development and I reckon few have been created by some services. I can script those 70 stored procedure and create them in new instance but the problem is they are associated with certain dll files. What I am thinking is, copy all those dll files from old instance and copy them into new instance.The hard part is there is no way to test how they behave in new environment until and we got some problem from application. Has anybody got any idea on how can I check the dependencies of those dlls, so that I can make sure that they behave as desired. Or is there any other approach to solve this problem, rather than copying those dlls.Thanking you in advance. Mon, 21 May 2012 20:22:14 GMTbhojrdhakalhttp://www.sqlservercentral.com/Forums/Topic1157477-1550-1.aspxHi All,I opened a session and am executig the below query. use db1go --create table #tmp3--(id int,-- name varchar(100)--)--go begin tran insert into #tmp3 select 101,'alex'go 10000I again opened a second session and executing the below query to see what are queries are currently beig run.My session 1 is running but the below query "st.text" column returns me NULL.I am expecting to see that batch is currently being run in the other session. Basically i wanted to know what all queries submitted to the server. I can use dbcc inputbuffer but i have 255 char output limitation.Can anyone help me on this regard?SELECT spid, blocked, db_name(a.dbid) dbname, program_name, ISNULL(nt_username,' ') nt_username, loginame, st.text as query FROM master..sysprocesses a LEFT JOIN SYS.DM_EXEC_REQUESTS dm on dm.session_id=spid OUTER APPLY SYS.DM_EXEC_SQL_TEXT(dm.sql_handle) AS st WHERE spid > 50 Thanks in advanceWed, 10 Aug 2011 03:29:02 GMTOracle_91http://www.sqlservercentral.com/Forums/Topic1300362-1550-1.aspxMy organization has roughly 30 SQL Servers, and I am trying to come up with a policy to test the backups our enterprise backup is taking. My initial thoughts on what we will need are as follows:1) We'll need a test server - I'm guessing this will need to be roughly a 500 GB box with 4-8 processors (that is comparable to our most intense prod boxes)2) We'll need a SQL Server License - Express will not work as some of our databases go well over 100 GB in size - This cost is my greatest concern. Is there a cheap solution here since I will be the only one using this box to restore databases? (I will delete them when I'm done)All I want to do is restore the databases to this server to verify that our backups are good. Does anyone have any comments on what the best process is to accomplish this task? Any suggestions on my ideas/questions above would be greatly appreciated.Thanks,MichaelTue, 15 May 2012 09:27:11 GMTmreynoldhttp://www.sqlservercentral.com/Forums/Topic1303610-1550-1.aspx1) I've been reading about how compressed backups run faster than non-compressed backups. I tested that over the weekend by setting compression on a 1.3 TB DB. The full backup which had taken 4-5 hours to complete finshed in less than 2 hours with compression on. But the most critical time measurement is how long a emercency restore takes. I wouldn't think a compressed backup would restore faster due to the time to re-write the data files being the same. Would a restore of a compressed backup actually be slower due to the extra procoessor work to uncompress the data?2) Can a compressed backup created on SQL Enterprise edition be restored on versions of SQL Server which can't create compressed backups - like SQL 2008 Enterprise --> SQL 2008 Standard, or R2 Enterprise or Standard --> R2 Express? Mon, 21 May 2012 12:44:31 GMTdan-572483http://www.sqlservercentral.com/Forums/Topic1301004-1550-1.aspxI have a environment where customer base is more approx you can say 500+All our team needs to manage using regular practices. But we found a solution using sharepoint user will do the basic activities like backup, recovery, reporting etc. Did anyone have implemented. Any ideas or suggestions would be appreciated. ---------------------Wed, 16 May 2012 07:14:43 GMTSaga...http://www.sqlservercentral.com/Forums/Topic1300148-1550-1.aspxHi for some reason my install of SP1 crashed and I cant re-run the installation again. it gives me error as A failure was detected for a previous installation, patch, or repair for instance 'INS2' during configuration for features [MPT_AGENT_CORE_CNI,]. In order to apply this patch package (KB2528583), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.A failure was detected for a previous installation, patch, or repair for instance 'INS1' during configuration for features [MPT_AGENT_CORE_CNI,]. In order to apply this patch package (KB2528583), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.Error code 0x84B20001.now I tried to run the repair but that's also not going pass this point -- >Rule "SQL Server Cluster Resource State Check" failednow I got end up having 1 instance with SP1 and other one just a base sql2008 r2.I have rebooted the Server many times but no luck.. its a production Server so i can reinstall.please help!-DTue, 15 May 2012 05:51:47 GMTDamian-167372http://www.sqlservercentral.com/Forums/Topic1302902-1550-1.aspxWe have a new member in our team and he needs some DBA training for SQL 2008. Does anyone have any recommendations for courses on the west coast? I think a traditional course would be best, but are SQL 2008 courses still available?Fri, 18 May 2012 17:13:35 GMTElizabeth.Blockhttp://www.sqlservercentral.com/Forums/Topic1302544-1550-1.aspxHi allI've had a bit of a nightmare configuring this server, as though I've done a lot of SQL "internal" admin and DBA (security, backup policy etc), the network side is something I'm not very strong on.Anyway, this [i]named instance [/i]SQL 2008 installation was set up to listen on a dynamic TCP port. The way this was set up in SQL Configuration (Protocols, TCP/IP) was:- Dynamic TCP ports and TCP Port both set to Blank for IP1-IP[n].- For IPAll, Dynamic set to an explicit value (49944), TCP Port blank.I've had some "exciting adventures" making sure that UDP 1434 was opened in the Firewall so that SQL Browser can provide this info to clients. (AND that TCP 49944 was opened).(a GOTCHA: for some reason, TCPView shows sqlbrowser.exe using UDP port 1434: but doesn't show it as LISTENING. This is misleading. It clearly is listening: I sent a request from a client using PortQry.exe and Browser correctly returned the server machine name, instance name, version and port number, as it should).What I'd really like to know is:[b]1.[/b] What is the point of listening on a dynamic TCP port, rather than on 1433? Especially when the named instance is in fact the only instance on the box? (I'd love to rename the instance to default, but I gather that's a whole world of pain, a re-install and restore would be easier)[b]2.[/b] What happens if the SQL Server service restarts and TCP 49944 is in use? I'm guessing that Browser would correctly report the new port number [X] now in use to clients - but then someone would have to open up port [X] in Firewall, which would be a PITA.thanks for any input!sebFri, 18 May 2012 08:03:18 GMTseb 30496http://www.sqlservercentral.com/Forums/Topic1301057-1550-1.aspxHi Guys,I have recently backed up my data warehouse database, now I’m trying to restore the database in my VM Virtual Box but its giving me error.Please Note that i previously had the same database on my VM and detached it and i deleted the .MDF and LDF files, later i copied over the new .bak database and i can’t restore it.ERROR[quote]TITLE: Microsoft SQL Server Management Studio------------------------------Restore failed for Server 'BGCPC01'. (Microsoft.SqlServer.SmoExtended)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476------------------------------ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BGPhoenix.mdf' is claimed by 'msphxx_index001'(3) and 'msphxx_data001'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&LinkId=20476------------------------------BUTTONS:OK------------------------------[/quote]Wed, 16 May 2012 08:04:12 GMTshani19831http://www.sqlservercentral.com/Forums/Topic1298967-1550-1.aspxthis comes to the differences of creating a database by a script vs SSMS. we are moving servers and when moving to the new server we are making all our file sizes where we want them by shrinking or enlarging them. we came across a database that one of our developers created with:[code="sql"]CREATE DATABASE SomeName( NAME = 'SomeName', FILENAME = N'SomeName.mdf' , SIZE = SomeSizeKB , MAXSIZE = UNLIMITED, FILEGROWTH = SomeSizeKB ) LOG ON ( NAME = 'SomeName_log', FILENAME = N'SomeName_log.ldf' , SIZE = SomeSizeKB , MAXSIZE = SomeSizeGB , FILEGROWTH = SomeSizeKB )GO[/code]of course since we cant shrink below the original size of the log file SQL Server yelled at us. (the DB was set to full when it only needed simple and so the log grew to an ungodly size). the databases created in SSMS did not have this issue. the question i have is what does SSMS do differently from the create database script? I know when SSMS tries to alter a table it copies the data to a temp table then drops the original and creates a new table and transfers every thing back so SSMS must be doing something different when creating the database.EDIT: forgot a couple of ' in my sanitizingFri, 11 May 2012 14:33:40 GMTcapn.hectorhttp://www.sqlservercentral.com/Forums/Topic1302006-1550-1.aspxCan someone let me know how to find out the ACTIVE and INACTIVE users in DB. I am actually moving my database from one server to another server and would like to make a comparision of users who are active in another server. Please, help me with this solution. Thank youThu, 17 May 2012 11:12:08 GMTDBA_SQLhttp://www.sqlservercentral.com/Forums/Topic1301325-1550-1.aspxI would like to create a SQL Job that scripts out all the objects in the database, with no data. Can this be done using SQL code? Or I should ask it this way, is there SQL script code that can script all the objects in the database with no data?The only way that I know is to query each of the system tables.Thank you in advanceAndrew SQLDBAWed, 16 May 2012 13:31:31 GMTAndrewSQLDBAhttp://www.sqlservercentral.com/Forums/Topic1107572-1550-1.aspxHiI created a user 'test' and assigned the db_owner role to the user, under the security tab in sql server management studio.I wated to give the user permission for CREATE PROCEDUREAfter searchin the internet i found these t-sql [code="sql"]GRANT ALTER ON SCHEMA::dbo TO test GO GRANT CREATE PROCEDURE TO test GOGRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO test Go[/code]These statements executed sucessfully...But when i try to CREATE PROCEDURE under the test userthis error is comingMsg 18456, Level 14, State 1, Line 1Login failed for user 'pradeep1'.Pls helpThank you in advancewith regard Dilip dThu, 12 May 2011 04:22:30 GMTdilipd006http://www.sqlservercentral.com/Forums/Topic1301523-1550-1.aspxHi All,We have a Production Set up where Multiple Alerts have been configured.Now All the other people are able to get the Alerts but I am not.Please advise do i need to Mention my Name some where Specifically to start getting the alerts.Please help as this kind of Urgent as their are multiple major alerts that I have missed.Immediate response would be highly AppreciatedThu, 17 May 2012 01:30:19 GMTJai-SQL DBA