Questions tagged [ssms]
SQL Server Management Studio, a graphical front-end tool shipped with SQL Server for managing and querying databases. It also supports managing other bundled systems such as SSAS.
1,211
questions
-1
votes
0
answers
22
views
for MS SQL in EC2 need a solution that the user can perform any admin activity but it should not able to query any data from tables [duplicate]
We have MSSQL DB in EC2, where we are using generic user (Active Directory User) for SQL to perform any activity inside the server.
This user requires to perform all admin tasks .But, as xyz being ...
- 1
0
votes
1
answer
66
views
need a solution that the user can perform any admin activity but it should not able to query any data from tables
We have MSSQL DB in EC2, where we are using generic user (Active Directory User) for SQL to perform any activity inside the server.
This user is having "sysadmin" permission and it requires ...
- 1
0
votes
0
answers
15
views
Affinity Mask Setting Error
We were just trying to move a couple of CPU from one instance to another in the processor page of the properties. The instance we were moving them from was sql server 16 and the one they were going to ...
-2
votes
1
answer
51
views
script to place all backup files(Full, diff &log) in same folder for all Databases in the server [closed]
I have downloaded script from here : https://ola.hallengren.com/sql-server-backup.html
Thank you for the SQL Server Backup script, it's creating separate folders & subfolders for each database.
...
0
votes
1
answer
22
views
How to migrate a database from 2016 to 2014 if there are encrypted/locked objects(SPs) associated with it?
I am trying to migrate a database from 2016 to 2014 through Task-->Generate Scripts. There are some stored procedures that are locked and seems like they are encrypted as well. They are causing ...
0
votes
1
answer
36
views
Create server login in SSMS
I am working with a database and I added a new server login (SQL Server) with the following code:
if not exists (select * from master.dbo.syslogins
where loginname = '...
Nikola Nikolic
0
votes
3
answers
40
views
Govt Office Env, No Server installed, MS SQL server Management Studio. Can I restore database & practice
I work in restricted govt office, I am provided with Microsoft SQL server Mgmt Studio but server is not installed and I cannot download or get access.
Question: I have downloaded "...
0
votes
1
answer
61
views
Central Managament Servers - List of servers
I am looking at the SSMS central management servers, I have setup a tree navigation of servers.
I would like to save this ?file? off to another location for future reference, or to pass it along to ...
- 143
0
votes
1
answer
49
views
What is the difference between a securable and permission in SQL Server?
In Microsoft SQL Server you have the possibility to set permissions at server and database level. You are also able to select a server login or database user and then set securables. I am wondering ...
0
votes
2
answers
53
views
exporting data from SQL query
I have a SQL CmdExec Job set up to run a query however the output data contains headers, job information and total numbers of returned rows as part of the output file and I am trying to figure out how ...
- 3
0
votes
0
answers
25
views
Is my output correct regarding the example data I've been provided for this database?
I've created a database in SQL with insert statements for some data. What I can't understand is, my registration table output looks much different than the example data I've been given for the ...
1
vote
2
answers
99
views
Issue with displaying Results to Grid, despite selecting the option
I am a learner who needs the community assistance to better my understanding and proficiency. I appeal for your time and expertise.
I am using SQL Server Express 2022 and SSMS v19.0.2, despite having ...
- 13
0
votes
1
answer
38
views
Creating import scripts with stored procedures - the difference between one large stored procedure and multiple nested ones
I am currently in my first couple of months of developing on MSSQL and currently writing some stored procedures for importing data from temporary tables. Writing stored procedures from start to finish ...
- 1
0
votes
1
answer
32
views
SSRS - Backup Encryption Key from SSMS
I have a SQL Server with SSRS installed.
Note that SQL runs as NT Service\MSSQLSERVER
SSRS runs as NT SERVICE\SQLServerReportingServices
Version: SQL Server 2017
I am trying to automate the backing up ...
- 133
0
votes
1
answer
68
views
Unable to create linked server to Power BI XMLA endpoint in SSMS
Unable to create linked server to Power BI XMLA endpoint in SSMS and getting below error:
The test connection to the linked server failed. Additional information: An exception occurred while ...
0
votes
1
answer
78
views
Restrict access on specific database from windows users
Is there a easy way to deny all access from Management Studio to a specific db except from sa and 1 another SQL user that will be used to access db from a remote app ?
i'd like that users connecting ...
- 1
-1
votes
1
answer
175
views
How to create a row for every day in a date range(til the end of year) using while loop in MSSQL?
I want the same condition to be copied until the end of the year. I mean add a row "every day" until '2023-12-31' Like second and third rows.
how to write query
0
votes
0
answers
20
views
Open queries from solution explorer without re-entering connection details
In SSMS, is it possible to open queries from the solution explorer without re-entering connection details?
I've already gone through the details in here this question, but this doesn't appear to be ...
0
votes
0
answers
46
views
SSMS 18 Object Explorer hangs when Expanding Databases list on a server with many DBs
This was noted in a previous question here with an accepted answer that it is fixed in SSMS 18. This is INCORRECT. The problem persists in version 18.12.1 and it is embarrassingly bad. It locks up the ...
-3
votes
1
answer
79
views
Identify slow and lagging queries in MSSQL
We have a system that was running smooth but suddenly we had system lagging due to high CPU usage for around 3 hours. Now, I need to identify the exact queries or whichever the objects that affected ...
3
votes
1
answer
44
views
Check if date ranges connect or have only specific rows in between
I store the employees' vacations in an SQL table as 2 columns of dates (StartDate : EndDate) along with the vacation type. I can't figure out the best approach to check for some rules, every time an ...
- 51
2
votes
1
answer
144
views
SQL Server - Restoring backup with SQL Server Management Studio restores another database than running a 'restore backup' query
These are the steps I have performed:
I have run a 'restore backup' query for an old database backup
Then, I restore a backup with SQL Server Management Studio. I changed the type of some columns and ...
- 123
4
votes
1
answer
238
views
How can I switch to the right or left editor tab in SSMS using a shortcut?
I have been using SQL Server Management Studio (SSMS) for a while now, but is there a way to switch to the right or left editor tab quickly using a keyboard shortcut? Currently, I have to use the ...
- 945
-1
votes
1
answer
56
views
Remove Databases from HA Group using TSQL - SQL Server
I am trying to automate following steps using TSQL (It is currently working fine using Manual -GUI interface of SQLServer), working on SQLServer 2022
Create a table, with columns to store DBName, ...
2
votes
1
answer
36
views
Forwarding SQL Server Error Logs to Another Server
I wasn't sure where to post this question. If this is not the right place, kindly point me in the right direction.
We have SQL Server 2012 and 2014 on-prem. My intention was to forward the error logs ...
- 123
2
votes
2
answers
636
views
Does Azure Data Studio have a debug Menu or option to debug a stored procedure?
I installed SSMS 19 Preview 4 and it doesn't have a Debug Menu or Debugging option. I also learnt that SSMS V17 was the last version with that capability. Then I decided to try Azure Data Studio, but ...
- 23
0
votes
1
answer
71
views
MS SQL Add New Login username for Windows Authentication doesn't work with FQDN
I am just wondering why when adding a new Login, I cannot use the FQDN\username
e.g. "muse.local\jsmith"
I have to instead use instead "MUSE\jsmith"? What is the difference please? ...
- 103
5
votes
1
answer
273
views
CTE Error when installing sp_WhoIsActive
I have several identical (near as I can tell) SQL Servers where I've recently added sp_WhoIsActive (showing some folks how much I like this tool) but one of them will not let me create the stored ...
0
votes
1
answer
186
views
SSMS - insert data through Edit top 200 in GUI, restricted by GUID
In some cases i work directly with data through the Edit top 200 (or what it is set to) in SSMS.
I find it particularly useful when I get some sort of data that is pre-worked through Excel or similar ...
- 13
1
vote
0
answers
260
views
SQL Server Management Studio compatible with Windows 10 32 bit
I'm running a VM with Windows 10 32 bit OS and I need to install SQL Server Management Studio.
The OS on my VM
OS Name: Microsoft Windows 10 Pro
OS Version: 10.0.19044 ...
- 159
3
votes
2
answers
325
views
How do I find the line that causes SSMS "Msg 8115, Level 16, State 2, Line 1"
I've got a 400 line query in MS SSMS. When I run it I get:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Line 1 is just the "Select&...
- 203
0
votes
1
answer
233
views
Can I connect Solution Explorer in SSMS to Azure DevOps like you do in Visual Studio?
I manage a few on-premise SQL Servers with SSMS, have been doing so for a while. Back in the day, I setup a local git repository to store all my scripts in, and this worked great for just me.
Times ...
- 3
1
vote
1
answer
287
views
I want to find the max number of characters of all varchar and nvarchar columns in DB
I want to find the max number of characters of all varchar columns in DB.
I have a query like this:
SELECT TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
...
- 13
0
votes
1
answer
83
views
Sql server Intellisense is not working
My sql server management studio intellisense is not working, below is my SSMS version and screen shot that I have tried, my SQL server is not installed locally but I am accessing the sql server ...
2
votes
1
answer
98
views
How to resolve linenumber in deadlock graph XML to line number displayed in SSMS sproc definition?
Captured the XML graph for a given deadlock involving some large / multi query sp's. However, not really sure how to interpret the line, stmtstart, stmtend of the frame element to determine which ...
- 1,062
0
votes
0
answers
212
views
Cannot edit maintenance plan?
Using SQL Server 2019 standard.
I know I previously was able to edit maintenance plans - I added a line to them last week to run a stored procedure on a new schema (it was already called on other ...
2
votes
2
answers
748
views
Unable to open database properties window in SSMS
I am using Azure SQL MI. Using SSMS, I backed up an SQL db to an Azure container. After that I am unable to open the Properties windows of that database using SSMS. When I right click on the database ...
- 257
0
votes
0
answers
34
views
Local to Azure backup errors and question
I am wanting to back up a local SQL Server 2016 database to Azure.
When I first started looking into it, I found this quote from the Azure usage calculator.
When I tried to set it up, all the ...
- 193
4
votes
0
answers
113
views
T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed
BACKGROUND
I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10.
I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
- 197
0
votes
1
answer
218
views
Connecting to a remote SQL named instance with named pipe
I found out that when I connect to a remote SQL named instance (ServerA\instanceA) from SSMS installed on ServerB, it does connect without error, but it uses the Named Pipe protocol.
I found that out ...
- 3,056
0
votes
0
answers
15
views
ContractStartDate & ContractEndDate returning dummy dates (1900-01-01 & 4000-01-01) - how can I remove them?
ContractStartDate & ContractEndDate returning dummy dates (1900-01-01 & 4000-01-01) - how can I remove them? I'll be using DirectQuery in Power BI to build a report but been asked to remove ...
0
votes
1
answer
95
views
How to get Database folders [closed]
Hello everyone thank you for your help.
I want to create schemas and have tables under schemas. I am able to do that.
However when browsing them, none of the tables come under the schema listed. The ...
- 9
0
votes
1
answer
30
views
Does SSMS support stored procedure parameter name completion?
As shown in below image, I typed part of a stored procedure parameter name and pressed ctrl+shift+space, SSMS pops up the parameter info.
Is it possible to let SSMS complete the parameter name for me?...
- 945
0
votes
1
answer
685
views
Can I install SQL Server Management Studio offline without internet access and create databases locally on my pc?
I need to create a database offline with no internet access preferably.
I've used SQL Server Management studio online plenty however I haven't done it locally without internet access.
And can it be ...
- 1
0
votes
1
answer
90
views
Unable to remove local login for linked server
I have added a linked server that was working fine. After this I added a local login to within properties > security on the linked server. After doing this I get an error when trying to run a query ...
0
votes
0
answers
17
views
Find out who deployed database in SSAS?
I have this SSAS running:
I see a database deployed and I am trying to check who deployed it, is there a DMV that I can execute somewhere that can retrieve that information?
- 251
3
votes
1
answer
600
views
Microsoft SQL Server 2019 Developer Edition suddenly throwing Internal Connection Fatal Errors
I have Microsoft SQL Server 2019 Developer edition installed on my computer for local development work with .NET. Everything was working well as of two months ago, when I was last working on a ...
- 33
2
votes
1
answer
199
views
How to get the columns of table in one row?
I've a db where schema is:
CREATE TABLE [dbo].[Student]([Id] [int] NULL,[Name] [varchar](255) NULL,[Mark] [int] NULL)
INSERT [dbo].[Student] ([Id], [Name], [Mark]) VALUES (1, N'Medi', 10)
INSERT [dbo]....
- 292
0
votes
2
answers
34
views
Does tail log backup option during restore (UI) truncate the tlog? And is there any way (UI option) to prevent truncation?
When restoring the db via SSMS UI, there is an option (checkmark) to take the tail log backup and optionally to place the db in NO RECOVERY mode.
Does such a tail log backup truncate the tlog? And is ...
- 3,205
0
votes
0
answers
30
views
How do I use this script to find why my recovery model changed from full to simple?
I have found a post that has a script that will provide details for my question. When I execute the script no results return. What do I need to change within the script to return results for the ...
- 37