FAQ Database Discussion Community


Why does storing a value from sp_executesql return NULL?

ssms
Once more I stumble upon an SQL mishappening. The following code: create trigger tc_trigger_olt_UPDATE on tick_orderline_type for update as declare @UserId varchar(32) -- declare @ClientId varchar(32) -- declare @CaseId varchar(32) -- declare @TableName varchar(64) --Used declare @RecordId varchar(512) --Used declare @Descr varchar(128) -- declare @RemoteIP varchar(16) --Used select @TableName =...

mix two insert with subquery?

sql,sql-server,ssms
I'm inserting data into DB, there is UnitChaine [id],[FK_Unit],[DateIn],[DateOut] And Personnal [FK_VW_PERSONNAL],[FK_UnitChaine] Here I have a list of FK_VW_PERSONNAL id's that are created and I have to insert them into Personnal and UnitChaine. I cound insert UnitChains then get the id of inserted then create personnal: INSERT INTO [dbo].[pstl_UnitChaine] ([FK_Unit]...

How to prevent user to see databases not owned by him in SQL Server management studio?

sql-server,ssms
When my SSMS (SQL Server management studio) is connected to my shared hosting db server, all databases including those not owned by me are listed in the left pane( the explorer of SSMS). Ideally, I want only databases owned by me to be shown here. To display others' database is...

SQL Server Management Studio not showing correct data space of a table

sql-server,database,ssms
Before I had a table with 8 columns. The one that is important here is called SerializedCriteria. It was text column with a lot of data. And by a lot of data I mean over 37000 strings with length of 20-30 kb of text. Anyway this is how the tabled...

In a two column only junction table with compound PK, do I need another index?

sql-server,tsql,sql-server-2012,ssms
Consider the following: tbl_Book BookID PK int identity(1,1) tbl_Author AuthorID PK int identity(1,1) tbl_BookAuthor BookID FK int AuthorID FK int I have a composite/compound primary key on (BookID, AuthorID). More specifically, default values based on SSMS designer: Columns: (BookID ASC, AuthorID ASC) Is Unique: Yes Type: Primary Key Create As...

LEN of value in the variable in TSQL

sql,sql-server,tsql,ssms
I have a variable @ColumnnName in T-SQL user defined function that basically contains the column Name. For example : declare @ColumnnName= 'firstName' which means I have a column with Name firstName in my table and have this column name stored in variable named @ColumnnName. I have a query like this:...

Trying to capture all of the Row_Numbers with Rows > 1

sql,sql-server,ssms,row-number
I'm sure this is actually a simple fix (it just sounds like it would be) but my brain cannot wrap around on how to get this to work. I'm currently using this query Select m.memberfirstname+' '+m.memberlastname [Name], m.MemberDOB, s.GroupID, c.PaperID, cr.PaperScanningStatusID, ROW_NUMBER() OVER(PARTITION BY m.memberfirstname+' '+m.memberlastname,m.MemberDOB,s.GroupID ORDER BY m.MemberDOB) rn...

SQL Server Management Studio - Results to Text still outputs to Window

sql-server-2008,ssms
I have a stored procedures that I am running. It generates print statements as a log of what is occurring along the way. By the end of it there would be about 14-15 million statements. I chose send results to text file, but the messages are still being sent to...

Install SSMS for power-users of business data? Why not?

sql-server,database,data,ssms,business-intelligence
Question: As a DBA/BI Developer, should we install SSMS on the PCs of power-users of business data? What are the risks with this approach? Context: I love SSMS. It's ergonomically designed and enables not just the exploration and management of the SQL Server, but also the data within it (e.g....

Get the SQL management studio date format

c#,sql,date,ssms
First i insert a date into a textbox, from a SQL Server. The user can edit the date when needed. When the 'Save' button is pressed, i need to check if the date in the textbox is valid for SQL. When i check the date format as 'yyyy-mm-dd' and the...

use variable as column name in where clause in sql

sql,sql-server,ssms
I want to use a variable as column name in my SQL query. Problem: I can not use dynamic SQL as the SQL query that I have is inside of a user defined inline table function. So, 1. Can not use dynamic SQL 2. Can not replace this function with...

Date Range for set of same data

sql,sql-server,ssms
I am trying to build a SQL query which will give me the date range for the dates with same prices. If there is a break in the prices, I expect to see it in a new line. Even if sometime during the month there are same prices, if there...

Migrate Excel Data to SQL

sql-server,excel,excel-vba,ssms
I have a large table of information (around 11,000 rows, 4 columns) in Excel that uses a macro and I need to import it to an SQL server, Microsoft SQL Server Management Studio, which will be utilized by another server to get the new information. Example: If I type into...

Dysfunctional Clone

database,ssms,sspi
A Database Server has been cloned. The Operating System is "Microsoft Windows XP Professional Version 2002 Service Pack 3". The Query Environment is "Microsoft SQL Server 2008 Management Studio 10.0.4000.0". After cloning, the following behaviour has been noticed. While attempting to connect from outside the machine, yet within the same...

Unable to import/deploy database to SQL Azure: “The service objective (Business/Web) specified is invalid.”

sql-server,azure,sql-azure,ssms,ssms-2012
I'm trying to move my databases to a newer Azure subscription. I used to simply click "Deploy to Azure" on a local database from SSMS and have it deployed to a specified Azure server. Importing/Exporting a BACPAC file also works. However, I can't seem to do any of this on...

Creating Hourly Counts Across Different Shifts

sql,reporting-services,mysqli,sum,ssms
I'm trying to create a query that divides a shifts production into hourly counts that can be ran through multiple shifts yet have the first hours of each shift fall under the same counts. Sum(CASE WHEN DATEPART(HOUR,[creation_time]) = '1' THEN quantity_increment_D ELSE 0 END) C1, Sum(CASE WHEN DATEPART(HOUR,[creation_time]) = '2'...

syntax error with case statement in CONTAINS

sql,sql-server,tsql,ssms
select Rowid, @counter from Patients where Contains ((case when @ColumnnName = 'firstname' then firstname --here when @ColumnnName = 'middlename' then middlename when @ColumnnName = 'lastname' then lastname end), SUBSTRING(@parameterFromUser, 1, @counter)); --here I am getting syntax error at case, then and @parameterFromUser. What seems to be wrong with syntax? SSMS...

Fill the columns of a table with data from an specific cell on SQL Server?

sql,sql-server,ssms,sql-server-2014
Is it possible? I have a lot of inventory lists, one per store and the stores have an Id, I need to get the store Id and copy on the column next to the information, it could be with temporalized tables I guess, for example: I wanto to transform this:...

SQL server bulkinsert errors

python,sql-server-2008,authentication,ssms,bulkinsert
When I run this query to bulkinsert a file on a shared drive to SQL server 2008 with username and password (not Windows authentication), I get these errors. DBA, system admins and network guys are all denying these errors are related to their teams and I am lost... Can anyone...

Optional relationship in SQL Server — How to implement in SSMS Schema Designer

sql-server,ssms,database-schema,relationships,table-relationships
I have two cases where I would like to set 'optional' relationship inside one table or between two tables. First: I want to set this relation as optional, in other words: there will be categories and subcategories in one table, so subcategory column is only optional. Second: Here I want...

Is there an SQL editor for the data itself?

sql,sql-server,ssms
I'm looking for an SQL (MSSQL) tool that will allow me to edit/insert/etc. data without the need to type sql statements. I want to simply enter data into a grid. I can't find this functionality in SSMS. Is there any tool that does that (preferably by MS)?

SQL Server : create a foreign key with a condition

sql,sql-server,sql-server-2012,ssms
I'm designing a new database for a company, trying to keep strict constraints with foreign keys etc for integrity. I have a table [Member] which holds companies on the system. This table has a column of [internalContact] for the user in our company who deals with this member which has...

Dynamic column name change based on date [T-SQL]

sql,sql-server-2008,tsql,ssms
Is there a way to make this work? change static, "13MonthsAgo" into January? Original > COUNT(CASE WHEN dateadd(MONTH, - 13, getdate()) > > HireDate AND dateadd(MONTH, - 13, getdate()) < > TerminationDate OR >TerminationDate IS NULL THEN 1 ELSE NULL END) AS 13Monthsago Preferred > COUNT(CASE WHEN dateadd(MONTH, -...

Missing SQL Server Management Studio in SQL Server 2014 Express

sql-server,ssms,sql-server-express
I have just downloaded SQL Server 2014 Express. In the setup client included, I chose "New SQL Server stand-alone installation or ...". I just left everything as default. However, at the end of the installation, I cannot find SQL Server Management Studio on my machine? I have check the installation...

SQL, Check if Rows are in another Table

sql,sql-server,ssms
I have two tables, Stock and Warehouse. I need to get the Items which are available in all Warehouses. Here an example: Stock Table: ItemID WarehouseID ItemAmount ------------------------------------------- 1043 1 20 1043 2 2 1043 3 16 1043 4 17 1044 1 32 1044 2 12 1044 4 7 1055...

sql counting based upon multiple values in other column - sql server management studio

sql,ssms
I have a table like below. I want to have distinct count of values in column usr for which pn is 2 as well as 3. In below case it will be 2 as usr =1 and usr =3 have values 2 and 3 in column pn. I wont be...

Show Lao language in SQL Server 2014 Management Studio

sql-server,ssms
I have tried for hours but nothing work This is my code CREATE TABLE Sample( ID varchar(10), name varchar(17), PRIMARY KEY(ID) ) INSERT INTO Sample VALUES('113','ພາ​ສາ​ລາວ') SELECT * FROM Sample Result: 111,???????? It should show 113 ພາ​ສາ​ລາວ on the table Any solution will greatly appreciated...

VS2013 & SQL Management Studio 2008

c#,sql-server-2008,visual-studio-2013,ssms
What I'm currently working on is generating reports based on SQL data. I need to make a "back-up" copy of a SQL table. Normally, I would use Excel and its object library to do this, but there are compatibility issues with running programs that use it on the older computers...

How do I select a true/false expression in SQL?

sql,select,ssms
I am trying to select a boolean expression. This does not work: select *, (Column = 'x') as isX from MyTable; -- Incorrect syntax near '='. But this does: select *, IIF(Column = 'x', 1, 0) as isX from MyTable; I thought the first solution should have worked. Is there...

How to edit MULTIPLE rows/data entries in SQL Server Management Studio [closed]

sql,sql-server,edit,ssms
I want to update/edit multiple rows in a database using SQL Server Management Studio. I know you can edit one entry using solutions provided here, here or here using Edit Top(200) function. But I want to edit >100 entries so this is not an adequate solution. I am using SSMS...

Count number of times a procedure is executed

sql-server,stored-procedures,ssms
Requirement: To count the number of times a procedure has executed From what I understand so far, sys.dm_exec_procedure_stats can be used for approximate count but that's only since the last service restart. I found this link on this website relevant but I need count to be precise and that should...

Return one role per ID

sql-server-2008,tsql,ssms
I want to return only one role per employee(EmpID), that being the last employee to work on a specific task(FixEndDate). Below are mockup tables. DECLARE @Problem TABLE ( ProblemID INT , ProblemDate DATETIME , LogBy NVARCHAR(50) ) INSERT INTO @Problem(ProblemID,ProblemDate,LogBy) VALUES (1,CAST('2015-01-29 10:53:46.000'AS DATETIME),'Carl') , (2,CAST('2015-01-21 10:53:46.000'AS DATETIME),'Paul') , (3,CAST('2015-01-21...

ssms tools pack unauthorized access exception

sql-server,sql-server-2008,ssms
Just installed tools pack for ssms 2008 http://www.ssmstoolspack.com/Download Keep seeing this on start up, most functions appear to work fine, just really annoying, cannot figure out what it's trying to do that is so bad, I'm running this as Administrator, still doesn't fix the error... Exception Type:System.UnauthorizedAccessException Error Messsage: Attempted...

Password mismatch between SQL Server Management Studio and config file

asp.net,sql-server,ssms
When I connect to my local instance of SQL Server Express using SQL Server Management Studio I get a screen with the password automatically filled in as per this image: I cannot remember what the password is. From this screen it appears to have 15 characters though. I also have...

Save decimal values in SQL Server Management Studio

sql,sql-server,decimal,ssms
I am looking to save decimal values in SQL Server Management Studio. They just come out as integer values rounded to nearest value. How is this done? If any more information is needed I can get it. ////////// Collumns ProductCode int Unchecked ProductName text Unchecked SupplierCode nvarchar(50) Unchecked Cost decimal(5,...

Include Column Names along with data in SELECT query to datatable

c#,sql-server-2012,ssms
I have a stored procedure which creates a temp table, populates, pivots the data and returns the lot to a datatable in my application The outcome is that this information will be placed in a report and opened by Excel. But when the information is returned there are no column...

How to connect to SQL Server using SQL Server Management Studio using Windows and SQL Authentication

sql-server,authentication,sql-server-2012,ssms
I am new to SQL Server 2012. I have installed SQL Server 2012 on my local machine successfully. When I try to connect to it using Management Studio, I am getting errors. I am not able to connect using Windows Authentication or SQL Server Authentication. I didn't get any screen...

Using EXISTS return wrong values in Script via Management Studio

sql-server,sql-server-2008,ssms,exists
I have a problem with following queries. Part 1 Updates the table. Part 2 Changes the column-name. If I rerun the script via Management Studio I get an error, The column "permissiontype_id" does not exist. The Select Statement in Exists shows the right result... I get such errors with some...

Computing value in select statement t-sql

sql,ssms
I am trying to compute a value in select statement itself but surprisingly it results in 0. SELECT Top(1) Name, LEN(Name) AS Equals, Abs(LEN('Johny') - LEN(Name)) AS NotEquals, LEN(Name)/(Abs(LEN('Johny') - LEN(Name)) + LEN(Name)) As Match FROM Demo WHERE Name = LEFT( 'Johny' , LEN(Name) ) ORDER BY LEN(Name) DESC Output:...

How to import a package from SSMS to Visual Studio for local machine?

visual-studio-2010,visual-studio,ssis,ssms
I'm trying to mess around with an SSIS package on my local machine. Currently, this package is in SSMS under a server. When I try to export this package, save it on my desktop and doule-click to open it, a deployment wizard comes up. What I want, is the package...

Possible steps to improve SQL Server query performance

sql-server,stored-procedures,ssms
I have a search procedure that is being passed around 15-20 (optional) parameters and the search procedure calls their respective functions to check if the value passed in parameter exists in the database. So, it is basically a Search structure based on a number of parameters. Now, since the database...

SSRS Not Printing All Records for Dataset, but Counts Them

reporting-services,output,ssms
I have an SSRS report (see image) that looks for open discipline reports. When I run the SSRS Dataset query from SSMS, it outputs 19 records. When I print it through SSRS it only outputs 17 records, but shows a total count of 19. In the image below I've shown...

linked server : “No transaction is active.” and “unable to begin a distributed transaction”

sql-server,transactions,ssms,linked-server,msdtc
I have a CRM software on my local machine. In the SQL DB of the software, I added a linked server (our test website with our test DB: Windows Small Business Server 2011). The linked server is set as RPC => True RPC Out => True Enable Promotion of Distributed...

SQL Server: changing collation of selected columns

sql-server,ssms,collation
In SQL Server how to change a collation of only selected columns? Is there a way to do that using SQL Server Management Studio? I was googling around but what I found where instructions how to change collation of a database not a specific column. EDIT: So I found this...

SSMS removing pre-BEGIN comments from my stored procedures

sql-server,stored-procedures,ssms
I'm running SSMS 12.0.2000.8 If I use the SSMS query editor to create a stored procedure (such as the one below) the comments before BEGIN are removed when I execute/save it: CREATE PROCEDURE myproc /* Say goodbye to this comment */ @var1 int -- this comment will disappear too AS...

DropDown in datagridView

c#,visual-studio-2013,datagridview,combobox,ssms
hello i have 2 datagridViews in a windows form and i have a combobox in 1 datagridview somwthing like this when i change the value of the combobox the tables in the datagridview should be updated.For example if i select the second combobox as outputmetadatafield1 then the columns of that...

Populating a Datetime Column

sql,sql-server,tsql,ssms
I want to populate a datetime column on the fly within a stored procedure. below is the query that I currently have that does same but slows down query performance. CREATE TABLE #TaxVal ( ID INT , PaidDate DATETIME , CustID INT , CompID INT ) INSERT INTO #TaxVal(ID, PaidDate,...

converting multiline Table valued function to inline in SQL

sql,ssms
Considering the performance issues, I wanted to make my multiline Table valued function, an inline TVF. Here is the sample code for Multiline TVF: CREATE FUNCTION MatchAptNumber (@AptNumberFromUser nvarchar(20)) RETURNS @MatchedData Table ( RowNumber int null , PercentMatch int null ) AS Begin Insert into @MatchedData(RowNumber) select dbo.Patients.Rowid from dbo.Patients...

T-SQL Percentage of 2 summed fields Error

sql,sql-server-2008,tsql,ssms
I'm currently trying to return the percentage of 2 summed fields. The code I've tried looks as follows: CAST( COUNT(et.NotEnrolled) + COUNT((etne.EmployeeID)) * 100.0 / Count(et.EmployeeID) as numeric (10,2)) as 'Not Enrolled %' The counts for each field are: et.NotEnrolled = 151 etne.EmployeeID = 707 My answer should be 21.36...

How do i test the table value function?

sql,database,ssms,mssql-jdbc
I just try to excute the below query .Its giving me ERROR: Msg 208, Level 16, State 3, Line 1 Invalid object name 'dbo.f_getPeopleTabRowCounts'. SELECT * FROM dbo.f_getPeopleTabRowCounts(7424,'YYYYYYYYYYYYY','abcd','Y'); Anyone can help me pls. How do I test a Table-Valued Function in SQL Server Management Studio? Thanks Sitansu Here is :...

SSMS: Create a table script from results of a procedure

sql-server,ssms
I have a piece of dynamic sql query. I'd like to keep the result in a temp table. But the query returns +100 fields so I'd prefer not to type the Create table script manually. Is it possible in Sql Server Management Studio to script out the results returned by...

SMSS 'Invalid column name'

sql,stored-procedures,triggers,ssms
Before you reprimand me about how 'this has been asked here:', I'd like to point out I did indeed google. I even went to page 3 in some cases. shudders So here's the deal: I'm trying to audit a database we have, setting triggers for UPDATE, INSERT, DELETE statements for...

View all tables referenced by foreign keys in SSMS

sql-server-2008-r2,ssms
I have a database with a lot of tables, the tables have a lot of foreign keys. Is there an easy way to view the other tables being referenced by the foreign key in a given table? I tried "view dependencies" but this doesn't list all the references. I've also...

Full text Search in SQL Server not finding my entries

sql,sql-server,full-text-search,ssms
My requirement is to be able to search imran in Simran, Simrankaur e.t.c I am using following query: select Name, Len(Name) from Demo where Contains(Name, '"*imran*"') My Demo table has Name column with following entries Simran, Simrankaur, SimranKaurKhurana but the query returns nil. Where am I doing it wrong?...

How to create “cell blocks” in SQL Server 2012

sql,sql-server,sql-server-2012,ssms
I have an SQL query and I want to visually separate parts of it for readability. This is in the query code itself and will only be for my own reference. My current solution is to type a long string of ---------- as a comment and I'd like to know...

SQL Query Using Entity Framework Runs Slower, uses bad query plan

c#,sql-server,entity-framework,ssms
am using entity framework generally successfully, but one query is running remarkably slowly. The query (generated by EF) is as follows: exec sp_executesql N'SELECT [Project1].[downtimeId] AS [downtimeId], CASE WHEN ([Extent12].[downtimeStart] > @p__linq__7) THEN [Extent13].[downtimeStart] ELSE @p__linq__8 END AS [C1], CASE WHEN ([Extent14].[equipmentID] IS NULL) THEN 0 ELSE [Extent15].[equipmentID] END AS...

How do I connect to a SQL Server which is usually a machine I remote desktop into

sql-server-2008-r2,ssms,remote-desktop,sql-server-authentication
Typically I remote into a machine with IP Address 00.00.00.00 and then I have an account in a domain, let's call it myspecialaccount\firstname.lastname. Then I use Windows auth to connect to SQL Server instance for example: ABCLACSQLC123\DEV04A So my question is HOW can I connect from my laptop through SSMS...

Sybase server linked in SSMS using Concatenate in openquery

concatenation,sybase,ssms,openquery
I have managed to link a Sybase server to SSMS and am able to query it using openquery but I get an error every time I try to concatenate columns. ie: Select * from openquery(SybaseServer, 'select top 1000 first_name + middle_name + Last_name as "full_name", * from dbname..tablename') I realize...

How can I use DataBinding (with SQL database in Visual Studio) on a ComboBox that will only display the drop-down information once (no duplicates)?

c#,data-binding,visual-studio-2013,combobox,ssms
I set the DataSource to the table. I set the DisplayMember to Area_Name and the ValueMember to Area_ID. The ComboBox shows all the values for Area_Name including duplicates. I want the ComboBox to just show a list containing 4 values: N/A, Unknown, Test, 4CL. The ComboBox has to account for...

Export query to relational xml

sql-server,xml,ssms
In management studio is it possible to create a relational XML file? example: select a.product, a.cost, b.productType, c.brand from products a inner join productTypes b on a.productTypeID = b.productTypeID inner join brands c on b.brandID = c.brandID to create XML File <brands> <brand>SomeBrand1 <productType>SomeProductType <product>Product1</product> <Cost>$10.00</cost> </productType> <brand> <brand>SomeBrand2 <productType>SomeProductType...