Thursday, June 20, 2013

[MS SQL Server] Why SQLCMD?

[MS SQL Server] Why SQLCMD?

Why SQLCMD?

Posted: 19 Jun 2013 05:59 AM PDT

Days by day i am learning something new in SQL Server. Before learning anything i want to understand why do we need this feature. This is the same case when i am learning SQLCMD.I would like to know what are the thing that can be done through SQLCMD which are not possible through SSMS.Or are there any advantages that while doing a certain set of task through SQLCMD.Thanks in advance.

Difference between ALTER TABLE REBUILD and rebuilding clustered index on that table

Posted: 20 Jun 2013 12:57 AM PDT

Hi all,After reading [url=http://rusanu.com/2011/10/20/sql-server-table-columns-under-the-hood/]this article[/url], I was wondering if there's a difference between using ALTER TABLE with the REBUILD option and rebuilding the clustered index on that table. BOL is not very extensive on the subject.Furthermore, I was wondering if it is necessary to check (and how? Counting and comparing columns from the sys.system_internals- and the sys.columns?) if you need to rebuild a table, because as I understand, a lot of space can get lost after performing DDL statements.Thanks in advance!- Alex

Upgradation of sql server

Posted: 19 Jun 2013 11:30 PM PDT

is there any issues to upgrade sql server from 2000 to 2008 ?and what is the better way to upgrade from 2000 to 2008 ?and can we restore system databases of sql server 2000 to 2008 and system databse of sql 2005 to 2008 ?

Automating the SQL Profiler

Posted: 19 Jun 2013 07:43 PM PDT

Hi All,I'm facing an issue while trying to automate the SQL Profiler run via scripts:Requirement:Need to audit few events on 24 hour basis /365 days for auditing purposeWhat I did:1) Started SQL Profiler and selected events as per my choice and stopped the trace...2) Scripted the profiler output and saved it as a stored procedure3) Called the SP through SQL Agent Job abd scheduled to run every 5 mins..Results:Except the first job run, rest all run fails...Can you please help me...Thanks..

MSSQLSERVER Services not start?

Posted: 19 Jun 2013 10:21 PM PDT

Hi,win - 2008 enterprise edtion SQL - SQL Server 2008 SQL SP -service pack 1windows application log found as below error messages, pl. suggestion me how to resolve this issues[code="other"]SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.TDSSNIClient initialization failed with error 0x57, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The parameter is incorrect.TDSSNIClient initialization failed with error 0x57, status code 0x50. Reason: Unable to initialize the Named Pipes listener. The parameter is incorrect.Server named pipe provider failed to listen on [ \\.pipe\sql\query ]. Error: 0x57[/code]

Named Pipes vs TCP - Connection Errors

Posted: 19 Jun 2013 09:52 PM PDT

Hello,A bit of a strange one....and i swear this is a networking issue somewhere....but according to the networks team they cannot find anything.Wanted to know if anyone here can think of something to check and why the temporary workaround works!!---------------------We have a satellite office, connected by a high-speed dedicated link (not VPN) back to our corporate network. In this office are a couple of user PC's which use Crystal Reports to access a SQL database held in the datacentre. They have been experiencing issues with both Crystal Reports and using SSMS, and have been getting the error:[b]Error Number:121 Error Message:TCP Provider: The semaphore time-out period has expired.[/b]This will happen the second time they run the report or associated select query from SSMS....the first time it always works. If i log off and back on again it will also work....but any subsequent attempt fails.Through some investigation we found that this is the only occurence of this error so cannot be related to the database server itself. Moving the users PC into the main office (different subnet) resolves the issue. Moving an already working PC from the main office to the satellite office causes the issue to appear on that machine also.The workaround i found was to change the connection string to use Named Pipes. This allows the query and Crystal Reports to run successfully every time. I know it misses out the network stack (TCP) by using named pipes but for the life of me i cannot figure out exactly why this would work every time if there was a genuine network issue.All the forums on the internet and here just point people to the Microsoft networking investigation article. I have checked a number of the settings (TCP offload, TCP Chimney etc) and they are all disabled on the SQL Server. They are active on the user desktop though....but then again they are active on my desktop and i experience no issues.Without something definitive to help me explain to the networks team why the named pipes connection always works and why it is an intermittent issue....they will not help me!Many thanks

Dbcc checkdb command

Posted: 19 Jun 2013 06:23 PM PDT

Dear Sir,Here i am having a doubt When we run DBCC CHECKDB command if we found any error we can fix them by using repair_fat,Repair_rebuild,Repair_allow_data_loss.If application team not ready for data loss we will restore the backup.if they are not ready to restore the backup also is there any another method to fix this issue.

SQL server agent SSIS error

Posted: 19 Jun 2013 12:00 PM PDT

I get the following error when I execute my package as a SQL server agent job.It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.I don't even know how to begin fixing this error.Where should I check first?Date a value of timeLog Job History (MyJob)Step ID 1Server PCTSQL004Job Name MyJobStep Name Job_1Duration 00:00:00Sql Severity 0Sql Message ID 0Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user: CS\DmcSysManager. The process could not be created for step 1 of job 0x63BB5A86DB23F947866D2A806BE4CC6B (reason: A required privilege is not held by the client). The step failed.

Clone users

Posted: 19 Jun 2013 09:45 AM PDT

I am using below query to get all the permissions of Old user. here my question is i am getting only database query in result set. i need script for all the databases.SET NOCOUNT ONDECLARE @OldUser sysname, @NewUser sysnameSET @OldUser = 'Old'SET @NewUser = 'New'SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)SELECT 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') FROM sys.database_role_members AS rmWHERE USER_NAME(rm.member_principal_id) = @OldUserORDER BY rm.role_principal_id ASCSELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_idWHERE usr.name = @OldUserORDER BY perm.permission_name ASC, perm.state_desc ASCSELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'FROM sys.database_permissions AS perm INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_idWHERE usr.name = @OldUserAND perm.major_id = 0ORDER BY perm.permission_name ASC, perm.state_desc ASCthank you in advance.

how to manage memory for an sql server

Posted: 19 Jun 2013 08:27 AM PDT

I'd like now how to better understand the memory utilize by sql server. I have done some continues statistics and using monitoring tools and it seems that in some of my servers I see a constant high memory usage for both the OS and the SQL Server memory. In some instances the sql server memory reaches 95% and stays to that under for several hours.Any ideas where I can start reading and understanding what it all means and how to figure out what's causing the high memory usage?Thank you.

SQL server service pack for browser

Posted: 19 Jun 2013 08:16 AM PDT

In windows control-panel , uninstall and change a program, I see SQl server 2008 service pack 2 is installed, but also see there is a separate SQL server 2008 service pack 2 browser.I am going to apply service pack 3 to fix a SQL browser issue, I was told the SP3 will fix that.My question is by running SP3 will also upgrade SQl browser, correct?Thanks much
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog