I am looking for a plugin or way of detecting an open tran in SSMS that is visible indicator,
I know of DBCC OPENTRAN
Select @@trancount
SSMS know you have an open tran when you attempt to close your SQL query window. Just looking for that to show or alert before that.
The context behind this is you given a script and its run on SQL and for some reason the tran is not committed. or rolled back.
I have installed SQL Server Management Studio 2005 and have beenfinding the equivalent of the Data Transfer Services in SQLServer2000. I think I have worked out that it is in SQL Server IntegrationServices but when I open File-New-Project I do not see the SQL ServerIntegration Services there, though Help assumes that it is availablefor selection when starting a new project..How can I make it visible in the New Project window?I have successfully used the import function so I think this meansthat Inegration Services is there somewhere.I would appreciate some advice,Best wishes, John Morgan
View 3 Replies View RelatedShould BEGIN TRAN ...COMMIT be used in a procedure body if I have only select statements??
CREATE PROCEDURE [dbo].[procname]
@param1int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
[code]....
When I go to open a file in SSMS, the Open File dialog box appears. In the left side of that box, there is a panel containing shortcuts to various locations: Desktop, My Projects, and My Computer. I would like to add some shortcuts to folder that I use in this area, but I haven't been able to figure out how. how to do this?
View 9 Replies View RelatedI have several users with an unusual problem with SSMS 2012. When they attempt to connect to a database using the "Connect to Server" dialog box, the connection just hangs. Sometimes after about 15 minutes the connection will be successful. Other attempts simply spin seemingly endlessly. Users experiencing this issue are both running SSMS 2012 on Win 7 Pro (64 bit). The following troubleshooting steps have been tried:
1. When the user runs SSMS "As Administrator" the connections work almost instantly. (Elevating privileges is not a solution in our environment)
2. Wireshark shows that SSMS does not try to hit the SQL server when the user experiencing this issue clicks connect.
3. I can create a new virgin user on the PC and that login experiences the same problem.
4. A complete rip and re-install of SSMS 2012 does not resolve the issue.
I am running an Execute SQL task that does a Begin Tran, then the next task in the sequence is a data task which imports a XML file into two tables. If i doo a Rollback Tran only one of the two tables is rolled back.
Is it possible to have both tables rolled back from one Begin tran command or do i need to split the datatasl into two and treat each import as a seperate issue ?
The connection is set to retainsameconnection
thanks
App developers are complaining that a blocking report I sent them today does not have enough info to troubleshoot and fix the root cause of the blocking.(I used SQL Sentry to get the report and send to the users, but I think my question is more fundamental than just using one 3rd-party tool.)The reason for the complaints is that the top blocker is a SELECT stmt on TableA that is not the Wait Resource of the blocking (TableB).
I explained that this is likely because the "blocking" SELECT stmt on TableA is part of a larger explicit tran that includes TableB, and that they need to look through their code to identify the SQL stmt that is the root cause of the blocking.They would like that root statement identified in the blocking report, but I don't think it is possible to get to that through DMVs.
I am wondering if I can show the Excel window when the SQL server agent job runs. The SSIS package contains a script task that opens Excel and does some formatting. I just need to monitor the formatting of these Excel files.
View 2 Replies View Related
Hi,
I want to rollback my t-sql if it encounters an error. I wrote this code:
begin tran mytrans;
insert into table1 values (1, 'test');
insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10
commit tran mytrans;
I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?
cherriesh
I installed Red Gate's SQL Search and now I have an Add-ins icon. From the Icon I got to a list of tools, one of which is SSC's script library.
When I downloaded it I got this file "SQLScripts.utmcsr&__utmv=-&__utmk=243779397" which did not trigger an installer when clicking on it. How do I add it.
Granted there are nearly 8000 tables in this particular database. What needs to be installed or what bit needs to be flipped so the user can see all tables in SSMS?
View 4 Replies View RelatedI have an xml document that (for this example) I've simplified to look like this:
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
[code]....
When I try querying the xml document in SQL, I get nothing back, unless I remove the schema information. I'm using this:
declare @x xml
select @x = P
from openrowset (bulk 'E:VehicleOption0514.xml', single_blob) as Products(P)
declare @hdoc int
exec sp_xml_preparedocument @hdoc output, @x
[Code] ....
I am not able to login to the SSMS, while I am logging I got this error, how to troubleshoot..
Cannot connect to LAASYA.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
[URL]
------------------------------
The system cannot find the file specified
------------------------------
BUTTONS:
OK
------------------------------
SMSS installed on my client forgets all the settings I entered under tools / options.maybe it has something to do with installation of system updates.
View 0 Replies View RelatedI get the following error when I try to expand the sql server log in the management studio. My login is in the securityadmin server role.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
Every time I start SSMS 2012 the "Connect to Server" box is partially obscured in that the top of it, the move bar can't be accessed. I've manually moved it using "alt+space" then "M" followed by the arrow keys, but it's getting annoying. Is there any way to change the initial start position of this box?
View 6 Replies View RelatedWindows 7 pro 64.
8gb ram.
Already have SQL2008 installed, and it works fine and great.Needed to install 2012 alongside for another project. Heard it should be fine to have both running. So I did. Installed MDS, DQS, SSIS, Service, and management tools. Launching SSMS 2012, when I click the "new query" button... I get the following error: library not registered. (exception from hresult:0x8002801D (type_e_libnotregistered))
If I connect object explorer to a server (local or otherwise), and try to script an object, it gives me the same error and then crashes the application. If I launch SSMS 2008, I can use it to connect to 2012 instance. So I know the instance and service are running correctly. It looks like its just the SSMS that is a problem.
I liked the default appearance of SSMS in 2005 and 2008. 2012 is lousy by default.
My quesion is whether it can be made to approximate the way it behaved in 2008.
What I liked: Registered Servers and Object Explorer resided on nested vertical tabs on the left-hand side of the screen. Queries stacked up on the right-hand side of the screen.
I managed to get the Registered Servers and Object explorer to display with nested vertical tabs (tabs at the top, rather than the bottom - that's ok). But if there aren't any other vertical tabbed displays, then the tab on top fills the screen. There's no point to that. Both Registered Servers and Object explorer are narrow trees. The rest of the screen is white nothingness.
If a query is opened, it then fills the screen - empty. If I want that in a vertical tab I have to manually make it one (right click, choose New Vertical Tab Group).
s there a way to make the doggone thing behave?
The way I got the Registered Servers and Object explorer to behave this way was to right click on a tab and play with the vertical tabbing options.
What's the most straight forward to determine what the last cumulative update was that was applied to SSMS?
I know that CU8 is out for SQL Server 2012, I just need to confirm which (if any) update was last applied to SSMS I'm currently looking at.
Previous to 2012, grid results from SSMS queries pasted into Excel beautifully and were easy to format.in 2008, The results from this query pasted into a spreadsheet with the query contained in a single Excel cell:
(sample - not the whole script)
select top 300
Avg_CPU_Time
,Total_Physical_Reads
,convert(datetime,Last_execution_time) as Timestamp
,Stored_Procedure
,Query_text
from dbadmin.dbo.History_CPU_IO_ByQueryAndSP
This was wonderful. I could deliver to developers wonderfully tidy reports on query resource usage with timestamps along with which stored procedure the queries came from.
Can't do that in SSMS 2012. Try it. It's a disaster. The Query_text, when pasted, spreads across multiple Excel columns, including the ones designated for other data. The result is totally unreadable.how to make the query_text stay in it's own cell? I've tried converting query_text to varchar and ntext. Same results.
Normally, whenever I've wanted to export data to a file (which is very rare that I do), I have used SSMS. Yes, I'm sure there's a way to do it in T-SQL, but I've just done an export using the SSMS wizard.
But that appears to me to no longer be available in SSMS 2012. So, how is it done from SSMS 2012? Or is it only done using T-SQL/DDL?
While working on a forum posting on this site, I came up with this code:
WITH JoinedTables(UserGroup, UserName) as (
SELECT 'UG1', 'Tom' UNION All
SELECT 'UG1', 'Harry' UNION all
SELECT 'UG2', 'Albert' UNION all
SELECT 'UG3', 'Jim' UNION all
SELECT 'UG3', 'Sam'
)
SELECT UserGroup "@name"
, (SELECT UserName "User" FROM JoinedTables T2
Where t2.UserGroup = t1.UserGroup
FOR XML PATH(''), TYPE)
FROM (SELECT DISTINCT UserGroup FROM JoinedTables) t1
FOR XML PATH('UserGroup'), ROOT('UserGroupsInfo')
The code does what I want but SSMS seems to have problems with the results. Every so often, if I click on the results, instead of opening a tab showing the XML, SSMS crashes.
I'm running SP1, version 11.0.3000.0.
how to backup and restore SQL Server databases using ssms in sql server 2012
View 2 Replies View RelatedI am working with SSRS. I do have admin rights. I can connect to the url https://localhost/Reports and can create the new reports and subscription. Delete the reports and subscriptions.
But when I tried to connect to Reporting Services from SSMS, I am getting below error.
I used the windows authentication. It is showing that the user name or password is not valid or you don't have permission to access this server
I can connect to the database engine and Integration services but unable to connect to the Reporting services.
I want to provide access to one of my users to a database on the production server. I do not want this user to be able to view anything other than the tables in that database, or the other databases on my production server.
I gave him access to one DB - TestDB- and made him data_reader on that DB. I had set that as his default database. However, when he logs in using SSMS he can see from Object Explorer a listing of all the databases on that server, although he can't access any of those. This is an external user and I don't want him to see any of that stuff, including other objects (SPs, Views, etc.,) even within TestDB.
To summarize, I want to grant access to a windows user to see/select from ONLY tables in TestDB of my production server, and I do not want him to be able to see any objects other than tables of this DB from SSMS.
I used the selfssl.exe tool to generate a certificate on my db server, successfully added to Personal Certificates in MMC, added R/W permissions for the service account to the cert, selected the new certificate in the protocols for mssql and restarted the service. So far so good, however when trying to connect from SSMS with encryption check box checked I get the following error:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)
The certificate chain was issued by an authority that is not trusted.
When I look in MMC, the only intended purpose for the certificate is "Server authentication" and I'm not able to add "Client authentication". Does selfssl.exe not support client authentication?
I setup an AG with a listener (AO1) on three servers.
Server1 = Primary, Readable Secondary = No
Server2 = Secondary, Readable Secondary = Read-intent only
Server3 = Secondary, Readable Secondary = Read-intent only
Connecting to AO1 with default settings, I end up on Server1.
If I attempt to connect to AO1 with specifying ;ApplicationIntent=ReadOnly (with or without specifying the db name) in SSMS, still lands me on Server1.
Also on topic, in this scenario I would actually want Server1 to be a readable secondary (in the event of a fail-over). So If I did set it as Yes, even If I do get the issue above working, won't I always land on Server1 then?
Version: SQL SERVER 2012 enterprise edition and SP2.
Due to one of the security reason, I have changed default port 1433 to another port number in configuration manger tools->protocols for MSSQLSERVER->TCP/IP --> IP ALL section mention another port number.
After restarted service, SQL SSMS able to connect server itself and new port number also LISTENING.
But SQL client SSMS not connected to one of the PC side after changing default port.
Using SSMS 2012, we are experiencing extremely slow response times when opening SQL job steps to edit and also deploying SSIS Pkg's. Sysadmins have no problem. Users in the ssis_admin role have no problem. It's the rest of the users who have issues.
View 1 Replies View RelatedThe environment is Server 2008 32-bit, SQL 2012 SP2 Standard 32-bit (11.00.5058), and Visual Studio 10 SP1 (10.0.40219.1).
I have a query that begins with MERGE <tablename> USING (SELECT blah, blah, blah While in SSMS, I can parse the query with no issues. I can execute the query and see the results I expect. But when I put the same query into an Execute SQL Task in SSIS, it won't parse. It gives me one of those very informative messages that I so love - Query failed to parse. Incorrect syntax near the keyword "MERGE".
I don't know if the 32-bit/64-bit thing has any bearing or not, but I have taken the exact same project folder, copied it to a 64-bit box, and it works fine in both SSMS and SSIS. That one is running Server 2012 R2, SQL 2012 SP2 Standard (11.00.5343), and Visual Studio 10 SP1 (10.0.40219.1).
is there a way to change SSMS 2012 default query save location?
Scenario:
1. Open SSMS
2. Create new query
3. Click Save
I see "DocumentsSQL Server Management Studio" folder, but I want to change it to be "d:". How do I do this?
I tried:
1. [URL]- in folder "DocumentsSQL Server Management StudioSettingsSQL Server Management Studio" there is a file NewSettings.vssettings, setting it to "d:" or "d:" didn't work.
2. Changing HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server100ToolsShell VisualStudioProjectsLocation didn't work too. There is no "Shell" under "110Tools"
Did I do something wrong, or is there another way?
ALTER TABLE [dbo].[TableNameExample] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)
The table is 110 GB, so will take time to compress. But it is one table in a database with 60 tables. Why would executing this statement, cause lock timeout on Object Explorer in SQL Server Management Studio? Users cannot drill down objects in this database without getting lock timeout.
When I cancel the compression script, users of SSMS can access objects in this database again from the GUI. Why does compressing a specific table affect access to all tables metadata? I cannot find anything on the internet but I am sure this has happened to other people.
Hi everybody,
I'm working on SQL Server 2005 & Reporting services to create a Projet model to deploy it on the server.
To explain me, i 'll recreate simply my problem on the the database "AdventureWorksDW".
I woudl like to see information on my product dimension.
In a matrix tab, i put "Product name" in row value and "customer name" on column.
I would naturally know the "Sales Amount" for my product by customers.
But, as i had not create aggregate, i can't put my numerique value on the cells indicators. It just allowed me to put the SUM(SALES_AMOUNT) by exemple, but it's not what i want.
How can i put my numerique value in this case ?
Regards,
Erwan
Toulouse, France