Microsoft SQL Server

162K

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

VB.Net errorIm getting this error message and not sure if the sql command does not like the date or not?
0
Rowby Goren Makes an Impact on Screen and Online
LVL 12
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Hi,

I want to buy SQL Enterprise 2014 for windows server 2012R2 (4 servers working as cluster SQL). Each server has 64 core. What license I must buy?

Thanks
0
Is there any difference between declaring a cursor this way:

DECLARE @Rows CURSOR;

SET @Rows = CURSOR STATIC FOR
            SELECT VENDOR_NUMBER_POHD, PO_DATE_POHD, REQDDATE_POHD, PROMDATE_POHD, PRSHDATE_POHD
            FROM [MyTable].[dbo].[POPHEADR] H
            WHERE PO_NUMBER_POHD = @I_PONUMBER;

OPEN @Rows
FETCH NEXT FROM @Rows INTO @VENDOR_NUMBER, @PO_DATE, @REQDDATE, @PROMDATE, @PRSHDATE  -- Get the PO data

or this way:

DECLARE db_cursor CURSOR FOR
            SELECT VENDOR_NUMBER_POHD, PO_DATE_POHD, REQDDATE_POHD, PROMDATE_POHD, PRSHDATE_POHD
            FROM [MyTable].[dbo].[POPHEADR] H
            WHERE PO_NUMBER_POHD = @I_PONUMBER;

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @VENDOR_NUMBER, @PO_DATE, @REQDDATE, @PROMDATE, @PRSHDATE  -- Get the PO data
0
Hi,
I am running the following code against an instance of SQL Server using SSMS.
PRINT '2.2 Ensure ''CLR Enabled'' Server Configuration Option is set to ''0'' (Scored)';
PRINT 'NOTE: TEST AGAINST EACH DATABASE IN THE INSTANCE';
EXECUTE sys.sp_MSforeachdb 'SELECT ''?'' AS database_name, name,CAST(value as int) as value_configured,CAST(value_in_use as int) as value_in_use FROM   ?.sys.configurations  WHERE name = ''clr enabled '''; 

Open in new window


I output to file and below shows the results displayed in a browser window
file outputIs there someway I can display the results in a single table as opposed to what is currently happening, a new table for each database tested?
any guidance appreciated.
Thanks
0
We are designing a mobile app that will have a question and answer section, very similar to EE as a matter of fact.

I need some help with the database design.

I had something like this in mind, please don't laugh:

Questions Table:

id | category_id | question_text | status_id | user_id | created_at | views |

Answers:

id | question_id | answer_text |user_id | created_at | status

Categories:

id | category_text | desc

Status:

id | status_text | desc

Users:

id | username | nickname

Of course the tables will have more fields, but that would be the general way I would do something like this.

My Problem:

The reason I am posting here is because the last time I showed a coworker my table design for a project similar to this, he damn near laughed. He explained to me somewhat, that I needed to use attributes and that by doing so all the related tables would be unnecessary.

But he didn't elaborate. Attributes? I think he meant like using JSON in the field values or something. At least, that's what I got out of his rambling.

So can someone show me a professional, clean, efficient, modern, and functional approach to the above table design? Can this be done by using JSON as data with field names, such as my coworker suggested ( again, I think ).

Thanks! Any example would help.

Regards,

John
0
Database  Maintenance PlansHi,
 
 I have Microsoft SQL Server with 30GB of database and there are several DB Maintenance plans:
DB Maintenance Plan 1: It performs Optimization (runs 1am daily) and Integrity Checks (runs at 9:30pm daily).
DB Maintenance Plan 2: It backs up main SQL database to the shared folder off another PC on the network (runs at 10pm daily).
Transaction Log Backup: It backs up TRN files to the shared folder off another PC on the network every 30 minutes throughout the day.

1. What does Optimization and Integrity Checks?
2. Do they have to run Daily?
3. Between Database backup, Optimization and  Integrity Checks, what should be the right order of running these jobs as far as scheduling goes?
0
Hi all.

I have a select statement (see below) that shows accountnumber, itemnumber, contracttype, pricing (I have attached a sample of the data).

SELECT DISTINCT 
                     ContractAssignmentDetail.accountnumber,  [Contract Pricing].dbo.tblAll_ContractPricing.ItemNumber,[Contract Pricing].dbo.[tblPM_Contracts-All Contracts].contracttype, 
                      [Contract Pricing].dbo.tblContractPricing.pricing
FROM         ContractAssignmentHeader INNER JOIN
                      ContractAssignmentDetail ON ContractAssignmentHeader.CANumber = ContractAssignmentDetail.CANumber INNER JOIN
                      [Contract Pricing].dbo.tblContractPricing INNER JOIN
                      [Contract Pricing].dbo.tblAll_ContractPricing ON 
                      [Contract Pricing].dbo.tblContractPricing.ContractID = [Contract Pricing].dbo.tblAll_ContractPricing.All_ContractPricingID INNER JOIN
                      [Contract Pricing].dbo.[tblPM_Contracts-All Contracts] ON 
                      [Contract Pricing].dbo.tblContractPricing.PMContractID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].PM_Contract_ID AND 
                      [Contract Pricing].dbo.tblAll_ContractPricing.GPO_Contract_ID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].[GPO Contract ID] ON 
                      ContractAssignmentHeader.PMContractID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].PM_Contract_ID
WHERE     (ContractAssignmentHeader.Status = 

Open in new window

0
Is there an example somewhere where if a new value is inserted in a certain table  that it will send an email. it doesn't have to be a specific value, all i'm looking for is if a new value is inserted in that specific table I want an email notification to be sent. Id like for this to be real time and not to be created in an SSIS package and avoid creating a schedule to check the table and send an email.
0
Using SQL Server 2014 and trying to develop a Dynamic query and Pivot.

My input data looks like this:
 InputData.JPG
My desired output looks like this (I'd like it ORDERED BY order_desc):
OutputData.JPG
I want the values under the cnt/number columns to be the "status" field.
If there is some way to add the literal "Kbn" in front of the cnt number in the heading, that would be great too.
NOTE:  In this example these order_desc happens to have 5 cnt columns.   That is not always the case - so it needs to be dynamic.  Sometimes cnt could have 2 or 6 or whatever.
             Although for any given run of the query, I believe all the order_desc in that run will have the same number of cnt columns for each order_desc.

Here is the code I have so far:
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(cnt)
FROM (SELECT DISTINCT cnt FROM #tmpMfgKbn ) AS Kaban
 
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT DISTINCT order_desc, ' + @ColumnName + '
    FROM #tmpMfgKbn
    PIVOT(MAX([status]) 
          FOR cnt IN (' + @ColumnName + ')
		  ) AS PVTTable'
		  

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Open in new window


Here is the output I am getting:
 OutputBad.JPG
What am I doing wrong?

Thanks
0
All,

I'm trying to log into a SQL instance. I can't with Windows Login or SQL Server Authentication. I am entering the correct credentials. However I get this error no matter what password I type in.

I am getting the following error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

trying to find the differences between 2 tables based on 2 fields..
project_number
and
Sku

and return records from the table where records are missing from the other table

but i keep getting back repeated records.

select * from [Metrics].[dbo].[Header_Current_missing]
  inner join
  [Metrics].[dbo].Header_Detail_Current
  on
  [Metrics].[dbo].Header_Current_missing.PROJECT_NUMBER = [Metrics].[dbo].[Header_Detail_Current].PROJECT_NUMBER
  and
    [Metrics].[dbo].Header_Current_missing.sku <> [Metrics].[dbo].[Header_Detail_Current].SKU


Like this: Header_Current_missing
project_number       sku
111112                      2a228
222221                      2e342
444445                      2x454

Like this: Header_Detail_Current
project_number       sku
111112                      2a223
222221                      2e342
444445                      2x454


So the query should return this one record.
Like this: Header_Current_missing
project_number       sku
111112                      2a228




Thanks
fordraiders
0
I'm currently working on a stored procedure (sql server 2014) and could use a second set of eyes to make sure it's efficient, The SP returns different logging data we collect throughout a users session on our site, essentially a home grown custom (and much simpler than) google analytics system.

Here's a brief summary of what's going on in the SP so far:
- a temporary table (#tmp) is created, and then populated with the results of another SP (this SP contains all of the basic logging data that we tracked way before I came along). I cannot currently modify this SP as it's called elsewhere in our site, so I do need to populate the results in a temp table and add on to it.

- After it's populated, I add some additional columns to the #tmp table to take advantage of the new fields that have been added into the logging functionality

- Next (and the place I'm not sure is the best way to go), I update the groupID column. Essentially, I want to match up the groupID from the LogOfQueries table to the records already in the #tmp table. This is taking between 7-10 seconds to execute this update, which is a lot slower than I expected (for comparison, if I just run the Admins_History SP, it runs in under a second)

Both tables have a an ID column that links them to each other (visitID = visitLogID). I've tried doing a simple select * from #tmp statement, instead of implementing the paging in the dynamic sql creation, and I get the same results, between 7-10 seconds.

The sql …
0
Date question:
I need a where clause for a datetime field called "ExpirationDate"
2008-08-01 00:00:00.000
Formated as above.
This is a field that holds dates for future expirations


I need records that will always  be including todays date  >=    
and also 3 months prior from any given today date  ?


this where caluse is not working ?

where [ExpirationDate] >= GETDATE()
 
  or [ExpirationDate] <=DATEADD(MONTH, -3, getdate())
 
Thanks
fordraiders
0
Hi,
I have the following code
USE [<database_name>] GO SELECT name AS Assembly_Name, permission_set_desc FROM sys.assemblies WHERE is_user_defined = 1; GO

Open in new window

This is used to test if CLR assemblies are in use for a database.
I need to run this against an installation of SQL Server which houses about 50 Databases.
I would like to  code this so that it automatically changes the database name rather than me having to input each database name manually.
any guidance appreciated.
Thanks
0
Hello.  I want to write a SQL statement that will check one table against another and provide matching results.

  select t1.ID, t1.Name from Table1 as t1
  where exists (select t2.Name from Table2 as t2 where t1.name like t2.name)

The above code will work, but only if the full name matches.  I also want partial matches.  For example if I have "Mickey Mouse" in Table1 and "Mouse, Mickey" in Table2 then It should consider that a partial match and show it to me.  I want to be able to use a wildcard character - for example:  select t2.Name from Table2 as t2 where t1.name like %t2.name% or similar.

I also want to return values from both tables in the output so I can manually view the information.

Thanks for your help.

===========================
Table1:
John Smith
Jane Doe
Mickey Mouse


Table2:
John Smith
Mouse, Mickey
John Doe

===========================
0
I'm using the Northwind database  in sql server 2008.

-- Janet Leverling
UPDATE [Northwind].[dbo].[Employees]
SET [Title] = 'Lawyer'
WHERE [EmployeeID] = 3

-- Robert King
UPDATE [Northwind].[dbo].[Employees]
SET [Title] = 'Doctor'
WHERE [EmployeeID] = 7

I have two update statements like this.
When I run them individually they work fine.

I want to put these in a stored procedure, so when I run the stored procedure both records get updated.

Is there a syntax to combine both these statements into one statement?
0
I would like to do a function in SQL that uses a regex string to validate email addresses and which also is not going to bog things down.
0
I need to add a unique constraint across multiple tables.  I need [InvestmentProfileName] to be unique when paired with the [PeopleID]

Model
0
I need to convert this from an MS Access Query to a SQL Server view. How would I write this in SQL to get the format in hours & Minutes?

[PRTTotalMins]\60 & Format([PRTTotalMins] Mod 60,"\:00") AS PRT_Total_Mins

Open in new window

0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

What is the best option to interface SQL with Visual Studio's  .NET. I have a project that require connection to a SQL data.

If I wanted a free SQL download. where could I find it?
0
I have a simple stored procedure which generates insert statements, and I want to know how to execute them automatically.

I am calling this stored procedure from a button on a Classic ASP page., which is the reason for wanting the INSERT statements to be executed after they are created.

In its simplest form,  here is the statement that generates the INSERT statements :

SELECT 'INSERT INTO DEV_Test (''' + txtSchoolID + ''', '''', '''', '''', '''' )' FROM DEV_Test2

Open in new window


… and this is what is produced :

INSERT INTO DEV_Test ('150721346050', '', '', '', '' )
INSERT INTO DEV_Test ('3456407213', '', '', '', '' )
INSERT INTO DEV_Test ('3721313250', '', '', '', '' )
INSERT INTO DEV_Test ('3721388839', '', '', '', '' )

Open in new window


… and I want the stored procedure to execute these statements.

Can anyone help?

Thank you

Jim
0
Hello everyone.
When "upsizing" an Access application to SQL Server, the Upsizing Wizard offers the possibility concerning adding timestamp field to the tables: "Let system decide". Likewise, SSMA offers the same possibility: "Let SSMA decide". I'd like to ask: how exactly they "decide" about which table "needs" a timestamp field and which doesn't?
The result of such operations is that some of the tables get timestamps and some of them don't. But I couldn't tell how that decision was "taken".
First I thought that small tables (in the sense of the amount of fields they have) don't get timestamp field and big tables do, but then I noticed some small tables that got timestamp by SSMA, and some big ones that didn't. So what is the consideration that SSMA takes when making that "decision"? And when I want to make that decision myself, without SSMA or the Upsizing Wizard, what is the best practice? In other words: When is it recommended to add a timestamp (/rowversion) field to SQL tables? (Assume that it's going to be used as linked tables to MS Access in a multi-user environment, which demands to take good care of concurrency)... I was thinking it to be good practice to add rowversion to all tables. Or isn't it?
Thank you.
0
We have a Microsoft SQL Server database server that has 5 instances running on it. They all work just fine for our needs but one database on one instance is a little performance sensitive. The virtual machine has 4 CPU cores and 64 GB of RAM allocated to it. I'm wondering if there's a way to optimize performance on one instance so it hogs maybe half of all the available resources and let the other 4 share the rest.

I should point out that this is a development and not a production environment. I wouldn't consider loading a server up with 5 instances in a production setting.
0
I have changed the recovery model to Full since the DB configured for mirroring. The DB size is 200GB. After changing to Full recovery model users feels the slowness during transaction and reports.

SQL Server version: 2017 standard

Does the recovery model change create performance issue?  I have scheduled only full back every day around 11 pm.  No transaction log back up.
0
Hi Experts,

We are using Dynamics CRM 2016 on prem.  We have a subject tree built out in our CRM system that contains subject matters for Cases.

Our customer service people are in need of some reporting, and I think to get them the data they need I might need to use SSRS or Visual Studio, but if Advanced Find is an option I'm all for it because it gives them the ability to tweak their results on the fly without involving actually changing queries or reports.  Avanced find is tempting, as it will let me use the UNDER option, which is kinda cool, but I don't think it does enough.  It seems like I can only chose one subject matter at a time to report on.

Anyhow, they would like some reporting that captures each item we have listed as a subject.
They'd like this by year or by month and with a count of occurances.
So for example, I think they want something like this:

YR 2019
       Vendor Issues                     210
       Personnel Issue                    14
       Bad venue experience        45
       Guest Sickness                       6
       Complaint                             178
       Compliment                          57
        etc.


YR 2018
        Vendor Issues              196
       Personnel Issue               13
       Bad venue experience   48
       Guest Sickness               17
       Complaint                       92
       Compliment                   114
        etc.

Yr 2017
       Vendor Issues               …
0

Microsoft SQL Server

162K

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.