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

Working on a clients database.  Table contains columns:

ContactDate datetime with 00:00:00 as the time component
ContactStartTime datetime with 1899-12-30 as the date component

How can I add these together in SQL Server to get a valid date/time value?
0
Free Tool: Port Scanner
LVL 11
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hello,
I have an varbinary column which stores bytes for an image.
The query is throwing an exception Out of memory .
I am using a Stored Procedure to get the query result.
This is the code:
  Public Sub Populate(ByVal Command As SqlCommand)
            'Accept command as string and fill the datatable with the result of sql query
            
            Dim dt = New DataTable
            Dim SQLdr As SqlDataReader
        Try

            SQLdr = Command.ExecuteReader(CommandBehavior.SequentialAccess)
            dt.BeginLoadData()
            dt.Load(SQLdr)
            dt.EndLoadData()

            Command.Connection.Close()
            Application.DoEvents()
            Command.Dispose()
            Data_Table = dt

        Catch MyException As SqlException
            MessageBox.Show("Stored procedure Error: MySQL code: " &
            MyException.Number & "  " &
            MyException.Message)
            End Try
        End Sub

Open in new window


Any suggestions are appreciated as I have been struggling with this for quite a while.

Thanks
0
The company I work for is in the process of creating collaboration among 3 domains. I have been tasked with setting up snapshot replication between SQL Servers on 2 of these domains from a 3rd. Furthermore the distributor is SQL Server 2000 and the subscriber is SQL Server 2014. I am finding that this may be more complicated than I originally thought.

First of all I don't seem to be able to even get started with setting up the distribution. When I click to configure distributions I  get the following error:

     SQL Server is unable to connect to server 'sqlserver1.companyname.com'.
     
     Additional information
     SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, 'sqlserver1'.

I am thinking this may be related to the fact that I am having to connect using the FQDN of the server instead of the server name, but I am stumped after considerable research as to where to go from here. There also may be permission issues as I am logged in using credentials provided to me from admins on the other domain.

Supposedly all three domains have trusts enabled and we do have users logging in between domains.

Any insights would be greatly appreciated.

Thanks
0
I have the following data example:
text 1.234 X 5.678 abc
text 1.234X5.678 abc
text 1.2X 5.6 abc
text 1.2 X 5.6 abc
text 1X2 abc
text 1 X 2 abc

From this data I need to find the position of the "X" (always an uppercase "X") and get the data to the left and the data to the right.   The data to the left is the column named "width" and the data to the right is named "length"

After complete the results should look like this:
text 1.234 X 5.678 abc --> width = 1.234, length = 5.678
text 1.234X5.678 abc --> width = 1.234, length = 5.678
text 1.2X 5.6 abc --> width = 1.2, length = 5.6
text 1.2 X 5.6 abc --> width = 1.2, length = 5.6
text 1X2 abc --> width = 1, length = 2
text 1 X 2 abc --> width = 1, length = 2

Any help is appreciated.
0
Hello,

  I need to create a Link Server between MS SQL Server and DB2.

 The DB2 version is 8.1.12.99 and runs in a Windows 2000 Server.

 The MS SQL Server version is 2008 R2 and runs in a Windows 2008 Server R2 Standard.

 How can i create a link servers with this two databases ?

Thanks.
0
I can not get this to work out right. Not sure if I am putting the Parenthases in the right place.

basically I want to pull back everything that matches with the Proc_codes and POS_TYPE_C along with POS_Type_C = 11,17,19~ for the codes listed.

then only if  POS_Type_C =2 is found.

Then lastly POS_TYPE_C in 11,49, 50~ with a modifier of 95.

What is the proper way to setup these line?


AND eap1.proc_code IN ('98966','98967','98968','99441','99442','99443') AND (eap1.proc_code BETWEEN 99201 AND 99205) AND EAP.POS_TYPE_C IN ('11','17','19','21','22','23','31')  OR EAP.POS_TYPE_C = '2' OR EAP.POS_TYPE_C IN ('11','49','50','71','72') AND modifier = '95'
0
If I have the following data that could look like this:
#123A4 is a code we want
a code we want is #123A4 also
a code we want is #123A4   also

How can I extract the value 123A4 in all of these cases?
0
i have a school software which is no working and giving error SQL SERVER LOGIN but not  working when i press ok i gives connection failed SQL StAtE 08001 SQL server error 17
0
Hello,

Is it possible to check if tables needed update stats with full scan ?

if yes, how ?

Thanks

Regards
0
Good afternoon, this error came to me in a production server, apparently it was because I could not login, but checking in the task manager only tells me that the SQL Server process is triggered almost 100% in the processor and in the memory is not it triggers so much, in the event log and log of the SQL Server itself there is no error message, at most it tells me to restart it.
error_server.png
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.

The backend SQL server has recently been upgraded from Microsoft SQL 2008 R2 to SQL2016
An application was unchanged but is now failing to update a recordset.  

The ODBC SQLNCLI11 has been in use the past year with SQL 2008 R2.

      ' Get the transfer record from tblTransfers
   strSQL = "tblTransfers"
   strSQL = strSQL & " WHERE IMERef = '" & frm.IMERef & "'"
   Set rst = New ADODB.Recordset
   dcnn
   
   rst.Open strSQL, gcnn, adOpenKeyset, adLockOptimistic, adCmdTable
   
   ' SQL errors should not occur
   If rst.RecordCount <> 1 Then
     If rst.RecordCount = 0 Then
       msg2 = "Cannot find the Transfer record"
     Else
       msg2 = "More than one Transfer record has been found"
     End If
     GoTo ErrHandle
   End If

    rst!Modifier.Value = [TempVars]![Logon_UserID].[Value]
    rst!DateMod.Value = Now()
    rst!grossamount = frm.SendAmount 'v9_5_1 for future changes to fields
    rst.Update

Open in new window


Code Adding new records works.  

Any suggestions for issues associated with an SQL2016?
0
Dear Experts,

My client using Win 7 Pro x64 bit are able to access their inhouse software.

Now with the new Processor, we cannot downgrade to Win 7.

It is a Win 10 Pro x64.

The issue now is in the odbcad32, System DNS, I tried to add the SQL server

It says
"Connection failed:
SQLState: '08002'
SQL Server Error: 0
[Microsoft]ODBC SQL Server Driver]SQL Server version 6.5 and all previous versions are no longer supported
0
The company I work for runs a proprietary software that up till recently ran on PSQL.  Every day we have to be certain that all users are out of the database to begin the new day of business.  PSQL has a "Monitor" that will tell you who is logged in.  The software just upgraded to MS-SQL.  The software does not have a built in solution to see who is in the database.  Is there a 3rd party app that an enduser can use to see if someone is still in the database without requiring IT to run a query?
0
How do I join master locations table to the below lambda expression?

In SQL I would write:

SELECT * 
FROM ProviderLocations p
JOIN Location l
ON p.locationId = l.locationId
WHERE p.providerId = @providerId
AND p.isActive = true

Open in new window



            return PMTDBContext.ProviderLocations
               .Where(w => (w.providerId == providerId && w.isActive == true)).ToList();

Open in new window



Thanks.
0
I have a table called 'users', each row has a unique ID (autonumber) -- userid
I have another table which is called 'firm', and a field (int) called:  contactnumberstart

I need to update firm.contactnumberstart  and make it =  to the latest (or highest) userid + 1
This way I know which is the next ID.

I only need to update it once, the system will take care of updating that number whenever a new user id added.

It should be something like:

update firm
set contactnumberstart   =   MAX(users.userid) + 1 

Open in new window


Well, that is the idea.
0
This query comes up with 2 results.  What I want it to do is to show the stk_no when it is a case (CA), if it is anything else show NULL)

The result I am looking for is it to show 1 result where the stk_no = 30.



SELECT DISTINCT
        apvndmstr.vend_name AS 'MFG Name' ,
        partmstr.alt_part1 AS 'MFG #' ,
        partmstr.upc_code ,
        partmstr.alt_part2 ,
        pricemtx.part_code ,
        partmstr.part_desc ,
        partmstr.uom ,
        partmstr.avail ,
        partmstr.gros_wt ,
        partmstr.part_length ,
        partmstr.part_width ,
        partmstr.part_height ,
    'CASE QTY' = (SELECT CASE WHEN uommstr_1.uom ='CA' THEN uommstr_1.stk_no ELSE NULL END), 
                 
        partmstr.royalty_code AS 'Selling Units' ,
        vendpart.min_ord_qty ,
        partmstr.part_price AS LIST_PRICE ,
        MIN(CASE WHEN pricemtx.price_meth = 'C'
                 THEN ROUND(( ( pricemtx.part_price * .01 )
                              * partmstr.cost_no6 ), 2)
                 WHEN pricemtx.price_meth = 'R' THEN pricemtx.part_price
                 WHEN pricemtx.price_meth = 'M'
                 THEN ROUND(( partmstr.part_price + ( pricemtx.part_price
                                                      * partmstr.part_price )
                              / 100 ), 2)
                 WHEN pricemtx.price_meth = 'B'
                      AND pricemtx.disc_type = '%'
                 THEN ROUND(( ( 100 - pricemtx.part_disc ) * .01
         

Open in new window

0
Running 2 instances of SQL 2014 on same VM server. Both are replicating tables to other servers. When I create a new subscription to a server, it creates the subscription but never creates the table on the subscription server and does not send data. After looking the error in the SQL  Agent Job for the subscription,  It shows that it is waiting for a worker thread.
I then delete the subscription and redo it and sometimes it works.

I did not have this problem until we increased the CPUs from 2 to 4 and increased the memory.

Do I need to increase the worker threads? If so How much?
0
I am trying to Insert values into a table using the following query and I get Query completed with errors and I don't know what is causing it.
Declare @gSource int;
Declare @bSource int;

Set @gSource = 143692;
Set @bSource = 138450;

/*SELECT ProjectID, BinderSection,BinderTab
FROM hcc_tblProjects2Sources
WHERE SourceID = @bSource
EXCEPT
SELECT ProjectID, BinderSection,BinderTab
FROM hcc_tblProjects2Sources
WHERE SourceID = @gSource;
*/

INSERT INTO hcc_tblProjects2Sources(ProjectID, BinderSection, BinderTab, SourceID)
VALUES(
	(SELECT ProjectID, BinderSection,BinderTab
	FROM hcc_tblProjects2Sources
	WHERE SourceID = @bSource
	EXCEPT
	SELECT ProjectID, BinderSection,BinderTab
	FROM hcc_tblProjects2Sources
	WHERE SourceID = @gSource)
	,@gSource
);

Open in new window


The part commented out returns 6 rows. I want to put those rows into a table and then a hard coded value of @gSource for all of them. Values returned by commented out section
0
How to Bring Back the SQL Database from Suspect Mode to Normal Mode?
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
0
[Webinar] Database Backup and Recovery
LVL 11
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

I am unable to make the ODBC connection, and it appears to be a creds issue;

I make the connection with my domain admin account and for login "With Windows login ID..."

And the WORKS

but I change to the "With SQL sever auth ...."

And with the exact same use and password, it now FAILS.

I need this to work because I have a service account defined in SQL, and it has the same error.

Mixed mode is TURNED 'ON' on the SQL server

I have installed the latest patches, and SQL client on the ODBC server

The exact error is

Connection failed:

SQLState: '28000'

SQL Sever Error: 18456

[Microsoft][ODBC SQL Sever Driver][SQL Server] Login failed for user

supersecret'correct'user
0
Hi EE,

Is their anyway I could suppress this specific error message as I am unable to trace back to the device requesting the access and it's isn't part of a job that is executed when in the SQL server.

2018-01-22 01:00:00.28 Logon       Error: 18456, Severity: 14, State: 38.
2018-01-22 01:00:00.28 Logon       Login failed for user 'BDC\SQLMAN'. Reason: Failed to open the explicitly specified database. [CLIENT: 172.16.98.70]
2018-01-22 01:00:00.28 Logon       Error: 18456, Severity: 14, State: 38.
2018-01-22 01:00:00.28 Logon       Login failed for user 'BDC\SQLMAN'. Reason: Failed to open the explicitly specified database. [CLIENT: 172.16.98.70

Any assistance is welcome.

Thank you.
0
Hello,

Can you please advise why the deletion of a record from “ABCD” table is taking around 7 seconds where the deletion is based on the primary key of the table? We have around 30K ABCD records that need  to be deleted from the database, and if the time taken to execute for individual record is consistent, it will take days to complete this relatively small dataset.

begin TRANSACTION;

delete from ABCD where contactid = 'cdd7b2af-f0f7-4450-acd0-000a46d4012b';

ROLLBACK TRANSACTION;

Open in new window

0
We have large table containing all the tools of a specific department used; with date-used, time taken out, time return and tech name. We would are trying to write a ms sql script that would present the following:

Tooles  Date       9:00am thru 9:59am  10:00am thru 10:59am 11:00am thru 11:59am
------  --------   ------------------- -------------------- --------------------
Desk    2017-0103      2                   1                   2
Hammer  2017-0104      0                   1                   1

Open in new window


the data looks something like this:
tools    Technician  DateI	TimeI	    TimeR
------	 ---------   ----------	--------    --------
Desk     Joe         2017-0103    9:00:00   10:30:00
Desk     Shmow       2017-0103    9:15:00   9:35:00
Hammer   Jenny       2017-0104    10:40:00  11:40:00
Desk     Lenny       2017-0103    10:45:00  10:59:00
Desk     Ralph       2017-0103    11:00:00  11:50:00
Desk     Zules       2017-0103    11:15:00  11:45:00
Hammer   Ray         2017-0104    11:48:00  11:59:00

Open in new window


What is the optimum way to go about it?
0
How can we set up the SQL so that it will take certain amount of memory from the SQL server?
0
I need the following IF POSSIBLE.

I have a Guarantors table with 280,123 records with the following fields.
Guarantor, LastName
ISSUE:
I need to split the guarantors based on the 1st. letter of LastName into 4 buckets as near equal as possible and I need to know which 1st. letter of the LastNams made up the totals of each of the 4 buckets.
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.