SQL query windows saves all the tabs in numerical sequence after you close each window. SO for example if you are typing query in window 1 and close it and open a new query windows it labels the new windows as SQL Query2 and so on.
What is the purpose of saving the SQLQuery windows like this? And how to retrieve all the windows.
SSMS 2012: when you open up many sql files in the IDE, it starts hiding some tabs and you have to click on the drop down at the right to navigate to the tab you want. Is there a way to make it display more than one row of tabs, so that tabs are not hidden and always displayed?
View 3 Replies View RelatedI 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.
writing data extracted from sql server to mutliple tabs within an Excel Spreadsheet?
View 1 Replies View RelatedUsing below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
I can retrieve the sql code of a view as follows:
exec sp_helptext @viewName
The result will be in several lines which shows the lines of the sqlcode...
Is it possible to return the sql but not the line numbers?
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
View 6 Replies View RelatedI am interested in retrieving the original SQL code of various stored procedures & views that I have created & pass them into a field within a table.
I have used one before but cannot remember whether I used a stored proc or system view to retrieve this information, can anyone be of assistance???
Hi everyone,
we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?
Thanks
I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected. The folder remains empty after running this query.
I have already configured server using
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),
[Code] .....
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
SELECT
s.facilityid,
s.sessionid,
s.MRN,
[code]....
Is there anyway we could retrieve the job history for all the jobs in all the sql server using Central Management Server?
View 0 Replies View RelatedI have a table with a datetime and an Item column.
I want to retrieve only the rows where item didn't change for a given value.
In the example below, given the value of 5 I only want the rows starting at 19:14:50 to 19:26:06.
Dateteime Item
2015-06-05 19:05:03.0002
2015-06-05 19:08:31.0002
2015-06-05 19:14:50.0001
2015-06-05 19:19:33.0001
2015-06-05 19:20:46.0001
[Code] ....
How can we get the result set as TESTING1,TESTING2 from below
DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
The view I am trying to create (crvKCLPRInsJob) is using another view (brvPRInsJob). I can provide a sample data, that's easy. I couldn't figure out how to get all the other info required.
I created the view below.
I created the following custom fields.
IncCode723Hours
IncCode748Hours
GrossEarn723
GrossEarn748
[Code]....
I need to create to create view on a table. Suppose if I have Firstname, Lastname & DOB in the physical table.
I need to mask the data from the original table. How can we do that?
Create view view_sample As
select ( null as Firstname, Hide as Lastname)
instead of the First name 'John', I just need to display null or something..
I have database with a large table (30 Billion rows) because it is so big I separated the data in quarterly tables and created a partitioned view (with hints for the date column) about 1 billions a quarter. (all in separated filegroups). The tables themselfes are partitioned by date again, so you slice out one day
However the full-backup of grows and grows and the mainpart of it is "old" but needed data.
So I was thinking to put the older data in a separate database (with separated backup) and then point to the table in my view.
While this is technical possible (leaving out the WITH SCHEMABINDING) I wonder what negative consequences it will have.
I already had to lose "with schemabing".
I have to use separate partioning functions - for each database its own - (partition schemas where already separated due to separated filegroups)
What about query optimization, does the optimizer care that there are two databases?
I want to create a login with some restriction like the following...
1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....
I have a view over 5 tables that has started giving unreliable results. There are three records that should be different, but in a production Access database, the view is giving three identical records where there should be three unique records. I have tested the view within SQL Server Management Studio and it gives the correct records there. But, I have attached this same view into the same Access database with two separate names. One instance of the view within Access database gives the correct records, and the other gives the incorrect (duplicated) records. I have attached screen shots that show these two separately named incarnations of the same SQL View, with the duplicated data, or the unique data highlighted.
I have also included the SQL query specs for this view.
what I can do to this view in order for it to always give us the unique records that we need, rather than sometimes the correct records, and sometimes the incorrect records.
Correct 3 records:
Incorrect 3 records:
[URL]
I'd like to find out whether or not people grant VIEW DEFINITION to their developers in UAT and PROD environments. My view is that a developer shouldn't be able to touch a PROD environment at all (we also include UAT as PROD), and any issue in a production environment should be investigated by a DBA and escalated to the dev if necessary.
View 1 Replies View RelatedI'm just wondering what is the resolution or execution order for a query like this (simplified version):
CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10
The thing I want to know is:
- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition
My real case is much more complex but that is an interesting point in my performance analysis.
I've created a table which will hold staffing data (name, grade, etc.) and any shifts that are going to be entered. I've got some test data in the table.
The base table looks like this:-
CREATE TABLE [dbo].[tbl_ForecastShifts_New](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ward] [nvarchar](255) NULL,
[StaffBand] [real] NULL,
[StaffMemberName] [nvarchar](255) NULL,
[Code] ....
I've attached a spreadsheet with the test data from the table and the required layout on the second tab.
I've created a view which displays the data as I want it and I need to attach this to an MS Access front-end for the users to input/edit the data in the table with the use of a Access form.
The view I've created looks like this:-
CREATE view [dbo].[vw_Forecast_Staffing] with schemabinding
as
select
s.Ward
,s.StaffBand
,s.StaffName
,d1.Date1
[Code] ....
It displays all the staff members and any shifts they've been given for a 7-day period with day 1 begin supplied by the user.
The user will also supply the ward they are interested in viewing.
When I attached the view to Access it becomes read-only as it doesn't have any indexes on it.
I can't create a clustered index on the view as there are derived tables.
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
How to find whether an object is used by any sp , view ?
I need a query which will result the sp , view names which uses the given object name .
SELECT [DEFINITION]
FROM SYS.SQL_MODULES WHERE [OBJECT_ID] = OBJECT_ID(@OBJECTNAME)
I know I can use SP_HELPTEXT to get linebreaks, The reason I want to use this is I can exclude comments section from SQL_Modules, but cannot exclude the comments section from SP_HELPTEXT.
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID
I have an indexed view and i can't get why after "fixing" one of index it became slower then before.
Goal was to switch index to INT column instead of VARCHAR, so the theory looks perfect !!!(?).
I drop index and built new one with same name. Could it be because of Statistics? should I refresh it all ?
The only thing I changed is one of 3 idx:
-- CustTypeCode VARCHAR(10) /* 1,2,3,4,5 */
-- CustTypeID INT /* 1,2,3,4,5 */
-- index Before:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeCode] ASC)
-- index After:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeID] ASC)
-- usage before:
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeCode = 2
-- usage after
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeID = 2
I would like to have rows presented as columns. That's why I use the PIVOT function at the end.The resultset will be presented in Excel using an external connection to the view.
When I try to save the view I get the error
Msg 4104, Level 16, State 1, Procedure _TEST, Line 47
The multi-part identifier "vk.OppCode" could not be bound.
Code (restricting the columns that I actually have to the relevant columns only):
USE [DBTest]
GO
/****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
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 want to create a check constraint on a table but the constraint values depend upon another table column as well, now one possible way is to create a function to check the column value. But I don’t want to use the function.
Can I do this in a view if so then how can I achieve this.
I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.
Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .
The view is called TEMP_EXPORT_1
I can either use the code below to return a list of columns that I want removed:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'
Or I can use the code below to return the list of columns that I want to keep:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'
Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...
Therefore I tried the following but I'm not sure of the syntax:
ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1
Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.
I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?