Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Microsoft SQL Server

160K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Share tech news, updates, or what's on your mind.

Sign up to Post

I created a fulltext catalog using the following command.

CREATE FULLTEXT CATALOG [DMSRepositoryFTCat] WITH ACCENT_SENSITIVITY = ON
AS DEFAULT

Before I can create a full-text index on the VIEW  , I am trying to create a Unique Index using the following command.
CREATE UNIQUE INDEX UI_DMS_REP_ID ON DMS_MANAGEMENT_REPOSITORY_VIEW(ID)

But I am getting the error "Cannot create index on view 'DMS_MANAGEMENT_REPOSITORY_VIEW'. It does not have a unique clustered index."

How can I create a Unique Cluster Index on the view  DMS_MANAGEMENT_REPOSITORY_VIEW which has outer join within it. or is there something I am doing wrong.

I am attaching the view script aswell.

Please suggest.
0
Become an Android App Developer
LVL 11
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

I have a simple SPROC in my SQL Developer 14 DB that selects records to return to the calling program.

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[aConvertspRead_bView_buildProd_TaxAuthorityRecs_02]    Script Date: 1/20/2018 12:26:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
Create PROCEDURE [dbo].[aConvertspbuildProd_TaxAuthorityRecs_02]
      -- Add the parameters for the stored procedure here
AS
BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT *
      From bView_buildProd_TaxAuthorityRecs_02
      Order By MuniCode, ControlNum, TaxType



END
I would like to return the number of records in the result set back to the calling program so I can show a processing status bar as the records are processed in the calling program.

How can I do that?
0
hi,
iam having two tables ...
( Eg :  sample  ) one table have 1  (loannumber ) record other table have 3  (distinct  loan number ) records .
 i need to insert  matched record need to update not matched need to insert  how to do iam attaching sample table structure.

Like that matched records need to update with out matched need to insert


 /* This table i need 3 records as like this


      Lnno                               ExeType      Status      LstUpdt
      H1CDGRN0300164            F                 N                  Null
      BDCLMTF1403310001       P                 N                  NULL
      NRCDKSH0200004            P                 N                   NULL


 */
sample-table-structure.sql
0
I am fairly new to SQL Server and using SQL 14 Developer.  I know what I want to accomplish but I'm not sure how to accomplish it.

This involves 2 table, 'tblProperty' and 'SQHD_PropHeader_Work'.

The pertinent field in 'tblProperty' are
Muni                             int
LotBlock                       nvarchar(17)
OldControlNumber     int

in 'SQHD_PropHeader_Work'.
MuniCode                  small int
LotBlock                      nvarchar(17)
ControlNumber         int

The combination of Muni and LotBlock is Unique in both tables

For every MuniCode, Lotblock combination that exists is tblProperty  a select statement to join them would look like this:
SELECT        dbo.SQHD_PropHeader_Work.MuniCode, dbo.SQHD_PropHeader_Work.LotBlock, dbo.SQHD_PropHeader_Work.ControlNumber, 
                         dbo.tblProperty.OldControlNumber
FROM            dbo.SQHD_PropHeader_Work LEFT OUTER JOIN
                         dbo.tblProperty ON dbo.SQHD_PropHeader_Work.MuniCode = dbo.tblProperty.Muni AND dbo.SQHD_PropHeader_Work.LotBlock = dbo.tblProperty.LotBlock

Open in new window



I want to update the 'OldControlNumber' field in 'tblProperty' with the corresponding 'ControlNumber' field in  'SQHD_PropHeader_Work

I don't know how to create the update query to accomplish this.  Is it possible.
0
Hi,

If i put Subscriber database in Single Usermode, what is the impact? will it show any errors?

This is Transactional replication in sql server 2008
0
I have a sql 2014 query that returns the last 3 months of customer order history

Looking for a way to combine them into a single row

here is a sample of data

customerid  month   year    orderquantitytotal   orderdiscounttotal  orderamounttotal
1                     11          2017   200                              10                              4000
1                     12          2017   400                              50                              10000
1                     01          2018   100                               0                               1000

What I'm hoping to get as a result is a single row ( due to space constraints it is wrapping here)

customerid  month1   year1    orderquantitytotal1  orderdiscounttotal1    orderamounttotal1   month2  year2  orderquantitytotal2
1                     11          2017       200                              10                                    4000                              12           2017   400

orderdiscounttotal2   orderamounttotal2   month3   year3    orderquantitytotal3   orderdiscounttotal3  orderamounttotal3
400                                 50                                 01             2018     100                               0                                    100


note: the stored proc that will executes this query has input of customer id as parameter so only looking for 1 customer at a time

Looking for some suggestions on the query
0
Hi Experts,
I'm updating a field in my table with a dropdown, which is populated from a different table:
This is the dropdown:

<select name="DestQueueId">
<%
Dim rsName
set rsName=Server.CreateObject("ADODB.recordset")
sql="SELECT distinct(Name), QueueEnvironmentId FROM QueueEnvironment order by Name"
Set rsName = AdoCon.Execute(sql)
%>
<option value="">Change Environment:</option>
<%
Do While Not rsName.EOF
Response.Write "<option value='" & rsName("QueueEnvironmentId") & "'>"& rsName("Name") &"</option>"
rsName.MoveNext
Loop
Response.Write "</select>"
%>
<input type="Submit" name="Submit" value="Submit" />

Open in new window


After I submit, the DestQueueId column is updated in my table with the value of QueueEnvironmentId.
QueueEnvironmentId is an integer.
Name is a string.
When I view  <%=rs("DestQueueId")%> on my updated page, the integer is displayed.
How could I have the Name string displayed instead?

I would appreciate your help.
0
I am creating a table and I don't want duplicates in the id_user field.  I am using below SQL statements but I can still add duplicate id_users.

CREATE TABLE SYS_PromisedDateNotif (
	pdf_id	INT	NOT NULL IDENTITY(1,1) PRIMARY KEY ,
	id_user	INT NOT NULL,
	CONSTRAINT FK_id_user FOREIGN KEY (id_user) REFERENCES BASE_User(id_user),
)

Open in new window

0
Hi,

I would like to get a information for the date 01/18/2018 created UTC column.

Alert me
Question
Vote as helpful
0
Vote
Hi,

I have a table name BtPrintJobs

I have tried the following way,

select top 3 * from BtPrintJobs

results:

CreatedUTC
636482124361400000
636482118335560000
636482118264740000

i ran this following query to get last utc

select MIN(CreatedUTC) from BtPrintJobs

Declare @TickValue bigint
Declare @Days float


Set @TickValue = 635283186097710000
Select @Days = @TickValue * POWER(10.00000000000,-7) / 60 / 60 / 24


Select DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE))
    + Cast( (@Days - FLOOR(@Days)) As DateTime)

where i get the date information 2013-11-07
0
After you archive subset of records from original production table to the archive table daily, now both tables have similar data and increasing in size, what do you do next with the records on the production table?

Would you start deleting same records  from the prod table or the archive table, but they are archive purposes but the tables are growing rapidly?
0
Upgrade your Question Security!
LVL 11
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Hi

I have a table with bookings in, they have a date and time, but will always be am/pm bookings.

I need to display the data as per the attached grid view image (the screen shot was done with a test static dataset).

How can I best transform my data into this shape? I'm using MS SQL 2008.

Table:
CREATE TABLE [dbo].[Bookings] (
   [Id] [int] NULL,
   [Name] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL,
   [Owner] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL,
   [Location_Id] [int] NULL,
   [Location] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL,
   [Start] [datetime] NULL,
   [End] [datetime] NULL
)
GO

Insert demo data:


INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(7,N'Test meeting 1 (8-1)',N'Site Administrator',140,N'Meeting Room 1','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(8,N'Test meeting 1 (8-1)',N'Site Administrator',140,N'Meeting Room 1','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(17,N'Test meeting 1 (8-1)',N'Site Administrator',89,N'Meeting Room 2','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(18,N'Test meeting 1 (8-1)',N'Site Administrator',89,N'Meeting …
1
What does this error mean and how does it get resolved?

An error occurred while assigning a value to variable "TruncateLength": "Single Row result set is specified, but no rows were returned."

I am trying to load data using a SSIS package and receive the error above. Does it have to do with data in the files I am trying to load (for example some of the data needs to shortened in length or a comma) or is it something wrong with the SSIS package? I have two types of files and mapping instructions for each type I have four different files with regards to the data for each type. The same error comes up for each file. This leads me believe it is not with data, but with the SSIS package itself.  

Please let me know if any additional information is needed.
0
In ms sql

delete table1 from table2 where table1.field=table2.field

Works ok

How can I do the same for 2 tables in mysql

Any ideas
0
I have MSSQL 2016 Always-On availability group. I want to use powershell script to run the failover and fail back from N1 to N1 and from N2 to N1 without using SSMS

How to do that?
0
I have a t-sql I'm using against a table, where I want to count the number of records within a certain category. They are number of accounts, per company. I've been using

SELECT CompanyName, Format(Count(Account), '###,###,###') AS [# Accounts]
FROM tblAccountInformation
GROUP BY CompanyName

My issue is, I copy this out to Excel to use all the time and have to convert it to number once in Excel. The values come out as I want them, using commas to separate to the left of the decimal, but never show the actual decimal values, as it's only ever working with integer values. Is there any way I can have the t-sql format the number the same way, but have it stored as a numeric value, and not as a text value?
0
We have an access application using a SQL Server database back-end.  Connection to the database is via ODBC.  We are setting up a Dev environment - Dev Access front-end and a dev SQL Server database.  ODBC has been created pointing to the Dev database.  How do we now update the table link so that they are pointing to the Dev database and not the original production DB?  I clicked External Data--> ODBC Database and it prompts me to 2 options -
1) Import the source data into a new table in the current database
2) Link to the data source by creating a linked table.

It seems that I need to select option 2.  Then it will prompt me to the Data Source which will allow me to select the Dev database.  Then prompts me to select tables.....Am I doing the right thing???  I've never worked with Access before.
0
I've written a lot of SQL over the years, but I recently ran into a situation where I wanted to select records from a SQL table based on values in a table which is local to Access.  I can simply create the query in Access joining the linked table to the Access table, but I'm wondering if there isn't a more efficient method.  This stemmed from an EE question in which the user indicated that they have read-only permissions on the SQL Server

This is for a report, so the recordset returned from SQL Server does not have to be updateable.  With the help of a couple of Access MVPs, I found several syntax which worked in a pass-through query:
SET NOCOUNT ON 

CREATE TABLE #TempData (PO_Number nvarchar(10))
Insert into #TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * 
FROM yourTable INNER JOIN #TempData ON yourTable.FieldName = #TempDate.PO_Number

Open in new window

However, I doubt this syntax would work in a read-only connection to the server.
SET NOCOUNT ON 

declare @TempData as table (PO_Number nvarchar(10))
insert into @TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * 
FROM yourTable INNER JOIN @TempData ON yourTable.FieldName = @TempDate.PO_Number

Open in new window

and one of the MVPs' recommended a syntax of:
;WITH
cteC AS
(SELECT 'abc123' PO_Num
 UNION ALL SELECT 'xyz456'
 UNION ALL SELECT 'mno789'
)
SELECT SomeTable.*
FROM SomeTable INNER JOIN cteC ON SomeTable.PO_Number = cteC.PO_Num

Open in new window


So, what are the advantages/disadvantages of each of these?  

Do any of these techniques allow you to create an index on the new table (temp, cte, or variable)?  

If the tempdb method will not work with a read-only connection, can you modify the syntax of that example so that it would run with a read-only connection? Maybe with a Run As command?

I don't know what version SQL Server the OP is using, so can someone tell me what versions of SQL Server that the CTE and table variables became available.

Thanks for your assistance.
0
I have a always-on databases, I want to have a script to do

When the always-on availability  group failover is successful from primary to secondary, run this stored procedure. If not successful, do nothing. how to do that? I want to do that in an agent job or something, when the failover happened, automatically kick off this job. My always SQL is 2016 enterprise
0
I'm configuring RBS in our system with SharePoint Server 2016 and SQL Server 2014 SP2 Enterprise.
The article we're following: https://technet.microsoft.com/en-us/library/ee748631(v=office.16).aspx
Our system has:
- 1 DB Server with SQL Server 2014 SP2 Enterprise
- 3 SharePoint Servers 2016 Enterprise as farm

When we following article from Technet above, when we run at step "To install the RBS client library on the on the first Front-end or Application server", we used RBS.msi file download from Microsoft SQL Server 2014 SP2 Feature Pack (https://www.microsoft.com/en-us/download/details.aspx?id=53164) but it cannot completed.

When we checked in Content Database we configured, the tables need for RBS wasn't created.

The log file in attachment.

Please help us on this issue. Thank-you very much!
I'm sorry to miss upload log file at previous post.
rbs_install_log.txt
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I had this question after viewing SHA 224 on excel.

I started to program/build an Excel template with a plain sight SQL Connection String within its VBA Code, but now I need to learn how to use a hash to hide its ID and Password. However, I do not know the required steps to make it work within VBA Excel: Should I store the hash somewhere else?, Should I compare that hash with the one inside the VBA?, and the most important step how to make a query and the SQL will recognize the true Password if in the Excel VBA code is only the hash string? Can somebody elaborate on how to do that in my VBA Excel/SQL Server situation?  On the other hand, this VBA code in the post looks fantastic, and I have found it on several other sites, but the Function Nz(sMessage, "") stops the run in error since it is nowhere to be found within this code. Please, SMEs help is really appreciated.

Thanks!
0
I have 2 columns in SQL table.

Combine or concatenate these 2 columns and the output should be in single column but in row order. See attached.

Please help.

Thank you for your help.
Concatenate2ColumnsIntoOneButInRowO.xlsx
0
I have never done much in SQL with running scripts but I am supposed to run one today.  The instructions say I should run the .sql script against the Viewpoint database.  Viewpoint is the location of data for our accounting system.  See the screenshot and let me know where I should navigate to run the sql script.  The script is located on the desktop.
Screen-Shot-2018-01-18-at-7.36.25-AM.png
0
i want to develop a mvc project with database sql server 2014

i want to make a registration form with fields Id,fullname,email,mobile,age,gender,photo path and photo binary data in database.

user should be able to see his details once registered and could be able to update his details as well as the photo he uploaded

please help me with this as i am new in mvc
0
Hi ,
  I have an Sql Server database with table Documents
  There are many duplicates rows with same Document_No  . But I need to get only one row from each document no

From the following sample data , I am looking for a query

   Doc1 Proj1 Path1 ......
   Doc1 Proj1 Path2
   Doc2 Proj1 Path2
   Doc3 Proj1 Path3

Query Should return rows with  Doc2 and Doc3 and one of the Doc1 row (it does not matter which one)
0
Our 2014 sql server ent had issues for 5 mins yesterday

All the apps that use the db had entries in the log files at same time with similar results along lines of
"Connection Timeout Expired."

1. Is there anything I can do to find out what was happening between these times to see where issue was?

2. And is there a way to automatically write sp_whoisactive logs when I get this issue in future?
0

Microsoft SQL Server

160K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.