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

Hi All,
I am trying to migrate Goldmine companies with its hierarchies to another Db, does anyone did that before?
If yes, I need to know how you did that or what exactly represent hierarchies on Goldmine schema?
Also, I attach an image for a tree example that I need to migrate as is in the new Db.

Thanks, all.
TreeExample.png
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!

How to migrate SQL Server 2008 to Azure Sql Server 2016 AlwaysOn.
Find the details below:

SQL Server 2008 (Standalone) which contains around 50-60 databases and total size of all these Db's around 80 TB.
Almost all database are in Simple Recovery mode. Only 5-8 databases are in Full Recovery.

Now we are palnning to migrate all these DB's to Azure (SQL Server 2016) and configure Always with 2 secondary replicas.
Server are ready in Azure. Next, migrating 80 TB and configure Always.

Please help us what it is procedure to migrate 80 TB data?
0
Select * from OrderDetails inner join Products on Products.ProductID = OrderDetails.ProductID inner join Discounts on OrderDetails.DiscountID = Discounts.DiscountID where OrderID = 213

Open in new window


I'm trying to pull all the OrderDetails records where OrderID = 213.
Some OrderDetails records have Discounts (and a Discount ID)
If no discount has been applied to the OrderDetails, the value for DiscountID is Null.

I need to pull OrderDetails for ALL records for OrderID 213 - even those with a NULL value for DiscountID.

I would appreciate any help sorting out this query.  right now it's returning 1 record, it should return 6.

Thanks for your help!!!

Steve
0
Hello

I have a doubt about begin and commit and rollback.

I have a proc x and in this proc I started a begin trans, inside of my proc x, it calls a proc xy.

In the proc x I have two deletes for two tables (product, sallers) and inside of my proc xy I have one delete for my clie table.


if I call the rollback, the rollback will affect all the tables from proc x and proc xy?

thanks
Alex
0
Hello,

We want to insert clients information into salseforce.
We have all the information in an SQL Server database.
We want to use ssis to update the data in SalesForce in a daily basis.

We don't want to purshase any toolkit or ready to use component.

Can I find any  step by step tutorial to do so ? I came across some tutorials suggesting webservice option, but It didn't work in my case because of my poor knowlege in Webservices.

Any help is apprecited.

Thanks
Salma
0
A client recently asked me to start uploading a text file into a SQL Server database each day.  This file is provided by a third party, and reflects current values, not changes.  I've got this upload process working as a simple append query, but it is resulting in a bunch of duplicate values (records that are identical except for the upload_date field).

The SQL table contains fields like:

Upload_Date, Company_ID, Column1, Column2, Column3

I'm seriously thinking about modifying the upload, so that it is not a simple append, but a merge which would use all of the columns to perform the merge and if the record is matched, (everything but the upload_date), then I would simply update a counter field, but if the record is unmatched, I would perform the append, but I need to discuss this with the client first.

In the meantime, what I need to do is create a query which will identify the dates a companies data (Column1, Column2, or Column3) changed. Assuming these 5 columns, with data similar to

UPload_Date          Company_ID         Column1     Column2     Column3
2018-01-01                      1                         a                     b                  c
2018-01-02                      1                         a                     b                  c
2018-01-03                      1                         a                     b                  d
2018-01-04                      1                         a                     b                  d
0
Dear SQL Server Experts,

I am basically from Oracle DBA background and I don't have much experience in SQL Server. I hope I will get some experts advise and comments on my SQL Server DB issue :)ert

We have MS CRM DB with the size of 350 GB. We're experiencing DB locks (blocking) issue on production SQL DB. As per MS article "“Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency”. Having blockings detected is not by itself enough and sufficient to decide the problem"".

My Questions:
1. what was the reason for blocking?
2.any advice on optimizing that particular cause?
3. Is there any chance of more blocking  if I use more NOLCOK DB hint in update or insert or select operations ?
0
Our company have got new project for CRM dynamics and customer is asking to get involved in Hardware sizing. We are new to this topic, can anyone help me with some basic guidelines, templates etc. please?
0
I have been using Access for a very long time and have some, but not a lot of experience working with SQl Server as a backend.

I am working on a conversion of legacy system data using Access 2013.  I had all of my conversion logic written and tested using a subset of the data provided by the client.  However, when I got the full set of data problems began.  I am reading a fixed length .txt file in my Access application and writing the output to a SQL table.  The input file has over 200k records but my logic will never get past processing 35,000 or so.  The record size of the input record is over, 14,000 characters.  One of the fields is 14,000 characters long.

After I got the 3183 I tried including BeginTrans and CommitTrans in the loop but the 3183 is still thrown after about 35,000 records.

The exact message I am getting is:
Error
MY system has 32GB of Ram and over 80GB of disk space free.  My Accdb is only about 60MB when I get this message.

In all my many years of working with Access I have never seen this error before and don't know how to resolve this situation to be able to process the entire file.

Here is my code:
Public Sub readFixedLengthTextFile()

'On Error Resume Next

clearSQLTable_Sproc "SQCS_CostFile_Import"
' output file
Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset("SQCS_CostFile_Import", dbOpenDynaset, dbSeeChanges)


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText 

Open in new window

0
Hi,

I data into SQL database usage of an RDS server.
 Now I would like to show this in an HTML page, using power shell. F.e.  4 th January 8 am, 30 users, 10 am 50 users etc. then the sale for next days whole year round.
 I can create the HTML via power shell, but how to build/show the graph?

J.
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: 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 have the following query on prior help and I added a column 'text1', change the Comp5 heading to Comp15 and I can't get 'text1' and Comp15 values on the result:
DECLARE @TEMP4 TABLE
    (
        PID INT ,
        TEXT1 VARCHAR(10),
Comp1 VARCHAR(10),
 Comp2 VARCHAR(10),
Comp3 VARCHAR(10),
Comp4 VARCHAR(10),
Comp15 VARCHAR(10)
    );

INSERT INTO @TEMP4
VALUES ( 11122, '1212', NULL, NULL, NULL, NULL, NULL ) ,
         ( 12345, NULL, NULL, NULL, '123', NULL, NULL ) ,
       ( 23456, NULL, '234', NULL, 'ewr', NULL, NULL ) ,
       ( 34567, NULL, NULL, 'acc', NULL, NULL, 'def' ) ,
       ( 45678, NULL, NULL, NULL, 'jkl', NULL, NULL ) ,
       ( 56789, NULL, NULL, NULL, NULL, NULL, 'we1' ) ,
       ( 23450, NULL, 'abc', 'acc', 'exy', 'ert', 'def' );

WITH Unpivoted
AS ( SELECT *
     FROM   @TEMP4 T UNPIVOT(CompValue FOR CompType IN(Comp1, Comp2, Comp3, Comp4, Comp15)) U ) ,
     Reordered  
AS ( SELECT U.PID ,
                  U.TEXT1,
            U.CompValue ,
            'Comp' + CAST(ROW_NUMBER() OVER ( PARTITION BY U.PID
                                              ORDER BY U.CompType ASC ) AS VARCHAR(255)) AS NewCompType
     FROM   Unpivoted U )
SELECT *
FROM   Reordered R
    PIVOT (   MIN(CompValue)
              FOR NewCompType IN ( Comp1, Comp2, Comp3, Comp4, Comp15 )) P;
0
Dear Experts,

I have some queries which needs to be created as a Stored Procedure but also by that changing to contain the database information after the FROM clause.

Briefly for example query in such format:
SELECT TOP 10
	AD.LFA1.LIFNR
FROM
	AD.LFA1

Open in new window


will be need to be re-formatted as (SAP_DATA added so the database name):

SELECT TOP 10
	AD.LFA1.LIFNR
FROM
	SAP_DATA.AD.LFA1

Open in new window


Could you please advise regarding the best practice, how to add database information to the FROM clause relatively easily?

Actually my only idea is to replace text 'AD.' with 'SAP_DATA.AD.'  in Excel in the code

Thanks in advance,
0
Hi Team,

MS sql server is showing CPU 98% and Memory 99%. This is sql server 2016.

I didn't some testing. When i disable application user, the cpu and memory is coming down to just 5-10%, but at the moment when i enable applicaiton user, within 5-10 mins CPU and going to 99%. when when i run SP_who2, i found lot of blocking.
Please go thourgh the screenhot and suggest us, how to resolve.

els01blocking.JPG
0
I have a script component where I'm concactenating several strings then inserting the resultant string into a DT_NEXT field.  So I have something like this (s is the string).

if (!Row.chkenvironmentaldatapres_IsNull && !String.IsNullOrWhiteSpace(Row.chkenvironmentaldatapres.ToString()))
            s = s + "Environmental data pres: " + Row.chkenvironmentaldatapres.ToString() + Environment.NewLine;

        if (!Row.smokestatuscode_IsNull && !String.IsNullOrWhiteSpace(Row.smokestatuscode.ToString()))
            s = s + "Smoke status code: " + Row.smokestatuscode.ToString() + Environment.NewLine;

        if (!Row.txttobaccocessdiscuss_IsNull && !String.IsNullOrWhiteSpace(Row.txttobaccocessdiscuss.ToString()))
            s = s + "Tobacco cessation discuss: " + Row.txttobaccocessdiscuss.ToString() + Environment.NewLine;


        Row.SH.AddBlobData(GetBytes(s));

    }

    private byte[] GetBytes(string str)
    {
        byte[] bytes = new byte[str.Length * sizeof(char)];
        System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
        return bytes;
    }

}

Open in new window


The problem is the Environment.Newline isn't working.  My text shows up in the application looking like this:

Categorye One:  Patient Presents with this
Category Two: Patient Presents with this&#x0D

I should be seeing:
Category One:  Patient presents with this

Category Two:  Patient presents with this

What should I be using besides Environment.Newline?

James
0
Hi,

How to resolve probelm related to restoring of database backup in sql server 2016,backup and restore are of same version.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)
An In-Memory OLTP physical database was restarted while processing log record ID
0
Hello,

I have a need to select two columns from a list of views that may change over time, but the
view names all have the same 5 characters - FType.  Below is the code I'm trying to use, but
I get an error stating that the @varTN variable needs to be declared.  Can someone provide
a few hints on what I need to change to make this work - or perhaps suggest an alternate approach?

DECLARE @IDTable TABLE
(
  FID int ,
  IIN int
)
DECLARE @varTN varchar(50);
DECLARE TestCur CURSOR FOR select name from sys.Views where name like 'FType%';
OPEN TestCur
FETCH NEXT FROM TestCur INTO @varTN;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @IDTable(FID,IIN) Select ID_A,ID_B from @varTN
    FETCH NEXT FROM TestCur INTO @varTN;
END
SELECT * FROM @IDTable

Thanks,
Carl
0
Relatively new to SSRS, but have been doing a bunch of work modifying reports and have had no problem deploying them on one "Project".  Client asked me to create a new report in another project, but when I attempt to run the existing reports in that project I get an error message:

An Error occurred during local report processing.
An Error has occurred during report processing.
The execution failed for the shared dataset 'AdoptionReferrals'.
Canno creat a connection to the datasource 'Data source for shared dataset'.
A network-related or instance-Specific error occurred while establishing a connection to SQL Server....
error: 40 - Could not open a connection to SQL Server.

When I created a new report the wizard created a new Shared Data Source allowing me to select the appropriate SQL Server and database, and I was able to get my report working in design/preview mode.  But when I attempted to deploy the report, I received the error:

Cannot deploy the report because the shared data source '/path/subpath/Shared Date Source Name' that the report references does not exist on the report server.

and it provides a filename 'Adoption Incentives.rdl'

So how do I deploy the Shared datasource name to the report server?
0
Hi

Currently I have the following columns
Analog1
Analog2
Analog1Raw
Analog2Raw

I now need to add upto 60 more analog columns, and have decided to switch to one column to store the analog data as

[    
    {
        "an1":2103,
        "an1r":2220
    },

    {
        "an2":5999,
        "an2r":6000
    },
etc..
]

Open in new window


The data is only ever queried by 1 report, but I need the report to be very fast

Therefore I need help with the following
1. what data type should i store the data as in sql server
2. is sql any good at querying this type of JSON data direct

I'll want to do stuff like
select jsonColumn.an1 
where mydate between 2 dates

Open in new window


i'm using .net framework 4.5 so can do other logic here to pull out data, but was hoping to query it directly if its performant

I'm using sql server 2014 on live, 2016 on dev, and we are planning an upgrade to 2017 on both environments.
0
I am wondering what happens to the SQL full backup and differential backup relationship in one backup product if another backup product is also doing full backups on a daily basis.

We are using one backup solution to protect the sql server locally on a daily basis and this solution performs a full SQL backup.  We also have a second backup solution that backups up the SQL DB offsite to the cloud and it performs a full backup once a week and a differential backup everyday.  What happens to the Full backup Differential backup relationship of the cloud solution if another solution is creating full backups.  Which system keeps track of the backup LSN numbers, is it SQL or the backup solutions?

For example if I try to do a restore of the cloud backup solution will the differential backup restore work or will it be linked to the on premise full backup as apposed to the cloud full backup?
0
Concerto Cloud for Software Providers & ISVs
LVL 5
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

I have another question   here     open explaining the situation.

That question is asking how to transfer this table directly to SQL server using ms Access 2013.  At this point there has been no resolution so I am going to asking the question specific to SQL Server.

How can I import this very large, fixed length field .txt file directly into a SQL Server table.

This is the layout of the table:

CREATE TABLE [dbo].[SQCS_CostFile_Import](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CostName] [nvarchar](30) NULL,
	[MuniCode] [nvarchar](4) NULL,
	[LotBlock] [nvarchar](17) NULL,
	[TaxType] [nvarchar](1) NULL,
	[MuniCode02] [nvarchar](4) NULL,
	[ControlNumber] [nvarchar](7) NULL,
	[TaxType02] [nvarchar](1) NULL,
	[CostDetail01_100] [ntext] NULL,
	[GRBNumnber] [nvarchar](30) NULL,
	[GDNumber] [nvarchar](30) NULL,
	[Remarks] [nvarchar](76) NULL,
	[upsize_ts] [timestamp] NULL,
 CONSTRAINT [aaaaaSQCS_CostFile_Import_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window


And this is the layout of each record in the .txt file
                                 Col  Lgth
Cost Name               1   30      ShortText
MuniCode               31  4       ShortText
LotBlock                  35  17     ShortText
TaxType                  52  1       ShortText
MuniCode02          54   4      ShortText
ControlNumber    57    7     ShortText
TaxType02             64   1      ShortText
CostDetail              65   14000          Long Text
GRBNumber    14065  30     ShortText
GDNumber      14095  30      SHortText
Remarks          14125   75      ShortText

Can someone explain how to  import the file 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\CostSheet\TQCSFILE.txt' into the SQL table?

This is someting that will have to be done more than once so it would be best if everything needed was contained in a Stored Procedure.
0
I just installed SQL Server 2016 Developer on a Windows 10 Professional laptop.  I set it up to use Windows authentication.  I'm having trouble with what seems to be very simple DB permissions.

I am the administrator for the laptop and have sa rights for the instance of SQL Server.  

Under the SECURITY > Logins tree of the instance my login is shown (i.e. MYLAPTOP\CalBob).  If I right-click on the properties of my login I have user mappings to the db (i.e. ANALYTICS) I need access to.  However, when I go to the db, ANALYTICS  >  Security, my User name/account is not listed, and when I try to add it, I get one of two error messages telling me:

1. If I try to add MYLAPTOP\CalBob I get message saying it is invalid because it contains invalid characters.
2. If I try  to add CalBob it says CalBob is an invalid login or I don't have permissions.

Ultimately the problem is if I want to give myself permissions to a table in that database I'm told the user does not exist.  For example.

GRANT SELECT on tablename1 TO CalBob

I receive an error indicating the user doesn't exist or I don't have permissions.  And I understand that CalBob doesn't exist as a User in the database, but I can't add CalBob either.

This seems simple but I'm obviously missing something.  Any ideas?

Thanks.
0
powerpivot-help-ee.xlsxYour help please
I'm using power pivot from Microsoft Excel to make some reports.
The report consists of dimensions, measurements and calculated fields
  1. I have a calculated field "camp_3" that depends on two other calculated fields "camp_1" and "camp_2"
  2. The calculated field "camp_3" at the record level is correctly calculated, the problem arises when I want to make a subtotal for the dimensions of the report.
  3. The subtotal I want to be equal to the sum of its component records and not as the result of the calculation of the fields "camp_1" and "camp_2"
0
Windows Server 2012 R2
SQL server 2012

We are moving our data systems from SQL 2008 to 2012.  In Sql 2008 (on Windows 2008) I import:

- .dbf IV files using the Visual FoxPro driver
- Access 2007 files using the Microsoft Office 12.0 Access Database OLE DB Provider

Testing on the new Sql Server 2012 I do not see either of these 2 drivers in the Data Source list (Import wizard).  

On our Windows 2012 R2 server, I downloaded the Visual FoxPro driver and installed it.  I can now see it on the list, but as soon as I choose "Properties", the wizard crashes and asks to be closed

For Access, I downloaded and installed the AccessDatabaseEngine driver for both 2007 and 2010 (32 bit).  The install seemed to go fine and I see both in "Add Remove programs", but I DO NOT see it in the SQL Server Data Source list (Import wizard).

HELP!  As I understand, if I can get the "Microsoft Office 12.0 Access Database OLE DB Provider" to show properly, I might be able to import both my Access 2007 tables AND my dBase 4 files.  Why can't I see it on my Import wizard Data Source list?

Max
0
When you host a website that pulls data from a SQL server, I believe you have to create an account in IIS that has the privileges to access the SQL server.

If this is correct, where in IIS can I find this information?
0
I'm getting a connection error when trying to render a report using SSRS 2016 Report Manager.  Here is my setup:

For testing purposes, I installed SQL Server 2016 Developer on a Windows 10 Professional laptop.  As part of the SQL Server installation I installed SSRS 2016, on the same laptop.  Then I configured SSRS 2016 and everything seems good to that point.

I created a new data source (DataSource1) and a test report in Visual Studio.  The report previews perfectly while in Visual Studio, so I deployed both the data source and the report to the Report Manager.  

I see the report and data source when I open Report Manager web page.  However, when I run the report I get the following error:

"...Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection).  A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible..."


When I test the data source in Visual Studio it works.  When I test the data source from the Report Manager web page I get the same error as above.  

It seems simple enough but I can't figure out what the issue is.  I am using Windows authentication and I'm the admin for the laptop as well as the SQL instance.

Any help would be appreciated.  Thanks.
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.