SQL Server 2012 :: Querying XML From SSMS?

May 5, 2014

I 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] ....

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Querying From XML Type

May 23, 2014

I have a plain XML variable

<BS>
<B Id="5" />
<B Id="3" />
<B Id="4" />
<B Id="6" />
<B Id="15" />
<B Id="7" />
</BS>

When I insert this into a temp table, the order get mixed up.

SELECT I.value('@Id', 'INT') ProductId
INTO #ProductList
FROM @Products.nodes('/BS/B') AS T(I)

When I select out of #ProductList the order is random and different from the XML.

View 8 Replies View Related

SSMS 2012 - Connect To Server Box

Sep 15, 2015

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 Related

SQL Server 2012 :: Query Parses In SSMS But Not In SSIS?

Oct 20, 2015

The 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).

View 5 Replies View Related

SQL Server 2012 :: Querying Table With Several Date Type Columns

Oct 30, 2014

I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.

I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.

I started with a normal select statement selecting each column with a join to the project and then a where clause using

(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...

The rest of the columns(all with OR between them).

The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.

Obviously because I have all the columns in the select statement... So I need something like

Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))

View 2 Replies View Related

SQL Server 2012 :: Querying A Supersession Two Column Table With Multiple Supersessions In Both Columns

Jan 29, 2014

I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:

Supersessions Table
----------------------

RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191

[code]...

The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?

View 9 Replies View Related

SQL 2012 :: SSMS 2012 Database Connection Dialog Hangs

Feb 13, 2015

I 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.

View 3 Replies View Related

SQL 2012 :: Connection Properties Versus SSMS Server Properties

Mar 16, 2015

I have an ODBC connection string that is working fine with the following properties:

Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.

I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.

Under Registered server name I've tried:

YYYYYYXXXXX

When I browse the server for the database instance list, I receive "network path was not found".

I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.

What am I doing wrong ?

View 1 Replies View Related

SQL 2012 :: How To Add SSC Add-ins To SSMS

May 4, 2014

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.

View 3 Replies View Related

SQL 2012 :: Querying The Transaction Log?

Jun 4, 2014

Just wondering if there was a way of querying the transaction log to ascertain particularly large queries filling it?

If you have taken over another persons solution and the transaction log seems to be filling very fast and want to break it down to find out what are the main causes, what would the best way be?

View 6 Replies View Related

SQL 2012 :: Cannot See Tables In SSMS

Aug 26, 2014

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 Related

SQL 2012 :: Login Failure For SSMS

Jun 24, 2014

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
------------------------------

View 6 Replies View Related

SQL 2012 :: SSMS Forgets Settings

Jul 9, 2014

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 Related

SQL 2012 :: Cannot Expand Error Log In SSMS

Aug 5, 2015

I 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)

View 5 Replies View Related

SQL 2012 :: Ssms Reports Library Not Registered

Nov 21, 2012

Windows 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.

View 4 Replies View Related

SQL 2012 :: Ssms Display In Vertical Tabs?

Feb 27, 2014

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.

View 2 Replies View Related

SQL 2012 :: Last Cumulative Update Applied To SSMS

Mar 4, 2014

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.

View 4 Replies View Related

SQL 2012 :: Pasting Query Results Into XL From SSMS

Mar 13, 2014

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.

View 9 Replies View Related

SQL 2012 :: Exporting Data To A File In SSMS?

May 16, 2014

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?

View 7 Replies View Related

SQL 2012 :: SSMS Crash When Viewing XML Output?

May 21, 2014

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.

View 3 Replies View Related

SQL 2012 :: How To Backup And Restore Databases Using SSMS

Jun 18, 2014

how to backup and restore SQL Server databases using ssms in sql server 2012

View 2 Replies View Related

SQL 2012 :: Can't Connect To Reporting Services From SSMS

Nov 17, 2014

I 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.

View 4 Replies View Related

SQL 2012 :: Restricting What A User Can View In SSMS

Feb 5, 2015

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.

View 1 Replies View Related

SQL 2012 :: Encrypting Client Connection From SSMS

Oct 28, 2015

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?

View 2 Replies View Related

SQL 2012 :: AlwaysOn Read-intent Via SSMS Not Routing?

Nov 15, 2014

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?

View 9 Replies View Related

SQL 2012 :: SSMS Not Connect After Change Port 1433

Feb 19, 2015

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.

View 3 Replies View Related

SQL 2012 :: SSMS Slow Response Time Editing

Jun 26, 2015

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 Related

SQL 2012 :: Detecting Open Tran In SSMS That Is Visible Indicator?

Mar 23, 2015

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.

View 0 Replies View Related

SQL Tools :: Change SSMS 2012 Default Query Save Location?

Dec 2, 2013

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?

View 6 Replies View Related

SQL 2012 :: SSMS Object Explorer Lock Timeout When Running Compression Script

Jan 21, 2015

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.

View 3 Replies View Related

SQL 2012 :: SSMS Auto-recovery / Auto-save New (unsaved) Queries

Feb 16, 2014

Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:

%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1

However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.

I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:

%userprofile%AppDataLocalTemp

View 2 Replies View Related

Use SSMS With SQL Server 7.0

Nov 1, 2006

Is there any way to use SQL Server Management Studio to manage a 7.0 database? I have a few old databases still in 7.0 and would really like to manage them through SMSS. Am I out of luck?

Thanks!

View 3 Replies View Related

Querying SQL Server Logs

Feb 17, 2005

I've been digging for a while to try to find something, even had a consultant looking for me, but came up empty haned.

What I want to do is this:

I activated the Security Audit level for all logins. I want to be able to report last login date for every user from the log, and all login events for SA or any other defined super users.

Is it possible to Query the SQL Server Logs to report this information?

This is for proposed Sarbox Database Security settings.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved