Microsoft SQL ServerSponsored by Flatiron School

161K

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,

for big data and data science solution of MS SQL, how many piece of puzzle we need? I know MS SQL has R service/server included in the MS SQL installation, I don't think only one server can do the job, what is the full picture?

each need separate cost ?
0
Get expert help—faster!
LVL 12
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

i am trying to store the values in the database but its showing the error ''Object of class PDOStatement could not be converted to int in C:\xampp\htdocs\payment\thankyou.php on line 51'' below is my code

try {
    $response = $api->paymentRequestStatus($payid);


    $payment_ID = $response['payments'][0]['payment_id'];
    $status =$response['status'];
    $Type =$response['payments'][0]['status'];

    $query = $connect->query("SELECT * FROM payment_done where payment_ID = '$payment_ID' ");

    //echo "RowCount:".mysql_num_rows($query);
    if($query  == 0){

    $result = $connect->query("INSERT INTO `payment_done`(`payment_ID`, `status`, `Type`) VALUES('$payment_ID ', '$status', '$Type')");
   

    if($result){
       
        echo 1;
        } else{
       
        echo "Something went wrong. Try again";
    }
  }
0
How do I find rows in the Sql Server 2016 database that have a '#' in the string value?
I have a inventory table with a comments field with some values like these:
'Bowl # XM21QR  RSeq #1A'
'Bowl # XM21QR  RSeq #2A'
'Bowl # XM21QR  RSeq #3A'

select * from inventory where comments like 'Bowl # XM21QR%' doesn't work
or if I just want to select 1 specific row above doesn't work either..
select * from inventory where comments = 'Bowl # XM21QR  RSeq #1A'

How to query the '#' or a special character.
Thanks
0
I need instruictions on how to open port 1434
0
Hi,

I have the following SQL code which returns the relevant records.  Is it possible to have the code just return the SUM of all the FrontsCaptured and BacksCaptured fields?

SELECT dbo_ScanBatches.CreatedUser, dbo_ScanBatches.FrontsCaptured, dbo_ScanBatches.BacksCaptured, dbo_ScanBatches.CreatedDate, Month([CreatedDate]) AS ScanMonth
FROM dbo_ScanBatches
GROUP BY dbo_ScanBatches.CreatedUser, dbo_ScanBatches.FrontsCaptured, dbo_ScanBatches.BacksCaptured, dbo_ScanBatches.CreatedDate, Month([CreatedDate])
HAVING (((Month([CreatedDate]))=Month(Now())));

Open in new window


Thanks,
Anthony
0
Greetings

I am moving data from an Oracle to SQL Server using VB.NET. I have found in some comments smart quotes (the curly quotation marks) in the comments. I am unable to find a way to identify the curly quotes in the using the Replace method.

I have tried this with no success.

tmpField = tmpField.Replace(ChrW(148), "Chr(34)")
tmpField = tmpField.Replace(ChrW(147), "Chr(34)")

Any thoughts?
0
I need to run several   select queries(Sql Server 2014 ) and write the results to an excel sheet

Example
select count(docname)  from TbDocs where plant = 'Plant1'   ------   Write "Plant1" in first column , count in second column of first row
select count(docname) from TbDocs where plant = 'Plant2'   -----   Write "Plant2" in first column , count in second column of second row
select count(docname) from TbDocs where plant = 'Plant3'   -----   Write "Plant3" in first column , count in second column of third row
.. has 20  plants total
(It is desirable, If I can have a header row as first row . But not a must)
Can I have a batch sql script running and writing it to a PlantCount excel file in one shot?
0
SQL 2014 Enterprise Cluster
Windows2012 R2 Cluster Servers

Cannot get sqlcmd to run

PS C:\Windows\system32> sqlcmd  -d master -Q "EXECUTE dbo.DatabaseBackup @Databases  =  'Master',  @Directory  =  'F:\SQL-BackupData\',  @BackupType  =  'FULL',  @Verify = 'Y',  @CleanupTime  =  672" -b >>$LogFolder\$LogFile

sqlcmd : Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
At line:1 char:1
+ sqlcmd  -d master -Q "EXECUTE dbo.DatabaseBackup @Databases  =  'Mast ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (Sqlcmd: Error: ...L Server [2]. .:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server
is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books

That is running on the SQL Cluster server

From my windows 10 computer
Microsoft Windows [Version 10.0.16299.371]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>sqlcmd -S tgcs014-n1-sqlc -E
1> use {master}
2> go
Sqlcmd: Error: Microsoft SQL Server …
0
I have this program I’ve been asked to manage. It’s a Java + Apache Camel program which is running via a windows service. The program sends and receives XML messages and saves them to SQL server. For the most part I have some Java experience but I’m still new and I don’t know too much about camel (other then that it looks to be the Interface between the Java core and any sort of network traffic)

It currently runs fine on its own deployed on a specific machine, but I want to run it on a different windows machine to play around with. So I copied over the entire root folder that contains all the jar files/configuration files etc, edited the configuration (so all pathnames are correct for the new location, and points to a different SQL database), and I created the new windows service (using prunsrv and prunmgr) and edited the service to have similar parameters to the existing windows service.

It all looks like I am 99% done, but when I run the service, the program generates some error, where there was an error creating a bean for some class and at the bottom mentioning a Java lang class not found exception for org.apache.camel.component.sql.swlcomponent

I don’t quite see what the issue is if I copied everything over. I went one step ahead and copied over all the Java and apache maven folders from the original machine to the new one and set up the environment variables to pick up from these folders instead but the issue still remains

If I build a jar file from the original …
0
q)When joining more than 2 tables, what rule is true?

1) All tables must be related to each other.
 2)Every table must be related to at least one table.
3) A table does not have to be related.
 4)There must be one table that is directly related to all other tables.

q) When using a join, which option is true about the joining columns in each table.

1) Must have the same name  
2)Must have the same data type
3) Must have the same name and data type.  ]
4)Must have a PK FK relationship.
5)  Must have been joined in the past.
0
What Kind of Coding Program is Right for You?
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Hi Experts!

I am wondering if I can create a trigger in SQL SERVER 2016 that will use a view instead of the table to perform a log of update/insert action?

Here is my scenario:

1. I have a view called Products (this view contains 2 table joins in order to generate this view)

2. I have created a table named product_log. It has the exact same structure as the Product view.

3. I created a trigger named insUpdProd_trig with these settings:
   a. Trigger Type: View
   b. Table Name Products
   c. Fire: INSTEAD OF (this is the only option)
  d. On Event: I checked Insert and Update
  f. Def Type: SQL Statement
 g. Execute as Caller (this is under Advanced Tab)
 h code is:
   declare @productID uniqueidentifier
 
   select @ProductID = productID from inserted

  Insert into product_log SELECT * FROM Products WHERE productID = @ProductID


If I use this trigger on the table version I can get the trigger to work when user update the product or insert a new product from Product table (I have the Trigger type set to Table and Table View to Product and trigger will insert the row into product_tbl_log. When I checked the product_log there are no rows been inserted.

QUESTION:

1. can I track the product update and insertion from a view?

2. If I can do so then what do I need to change in my trigger code in order for this to happen as when user makes changes that it will insert a row into the product_log table?

Thanks
0
SQL Log Analyzer
Here is how to restore SQL Server database to the point in time. Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
0
I would like to clean up my code and avoid having to make changes in multiple locations. This is my exact scenerio:

       SELECT
       CASE
       WHEN… Then
       WHEN… Then
      Just assume a ton of logic statements in here….
       END AS ContribToComm,
       
       SUM
          (
          CASE
       WHEN… Then
       WHEN… Then
       Just assume a ton of logic statements in here THAT ARE ALL THE EXACT SAME AS ABOVE !
            END
          )
       OVER (PARTITION BY L1.PayTo,MonthValue ORDER BY L1.PayTo, IssueDate, CheckNum) AS RunningTotal
   
       FROM….

How would you re-write this to avoid duplicating logic?
0
I would like to use a batch file to send an email via a SMTP mail server with attachments.

I have all the settings because I use the SMTP within my SQL server.  The purpose of this  batch file is to take a 'txt' file and forward it to a recipient.  One other wrinkle is that it has to be conditional based on the value within a file, Y or N.

Is this possible?

Thanks

Glen
0
I am trying to connect  a PHP 7.2.2 application to a SQL server database, using windows authentication, but am seeing this error

Array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. ) [1] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. ) )

Open in new window


Please advise on what to need to do to resolve the problem.
0
Can someone disect the join statement below and provide me with an explanation of the syntax?    dbo.smr_Cost_ID is a Scalar function.  I am unsure what the values nested in the parenthesis () are doing.    r.store_id, r.v_id,r.vi_id are all apparently aliased parameters in the function.  ip_start date is a field in a view referenced in the select statement.



 LEFT JOIN tciinstore.dbo.VENDOR_COST vc ON
              vc.VENDOR_COST_ID = dbo.SMR_COST_ID( (CASE WHEN r.store_id = 0 then 142 ELSE r.store_id END),  r.V_ID, r.vi_id, ip_start_date)
0
I have to convert several Oracle's CTL script files to a SQL Server 2014 format files.  I have 90% of the conversion complete, with the exception of the date fields.  Within the DAT file that we receive from our client, if a date value is not given, the value of "00000000" is specified (as oppose to leaving the field null).  

When I try to do a BULK INSERT, I get an error message indicating "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row #, column 11 (ADMSN_DT)".  The cause of the error is the string value "00000000".

Is there a way of specifying the following Oracle statement in T-SQL of a format file?

ADMSN_DT                   DATE 	'YYYYMMDD' NULLIF ADMSN_DT="00000000"

Open in new window


My format file has the field defined as data type "DATE".  Is there a way of imbedding logic into the format file to null out the field if it reads the value "00000000" for a give date field (as done in Oracle)?  

thank  you.
0
I have a dataset that looks like this

LoginDate|LoginTime|Secret|IdentityName|Action
1/4/2018|12:01|User1|Secret1|Checkout
1/4/2018|12:04|User1| Secret1|Checkin
1/4/2018|12:15|User2| Secret1|Checkout
1/4/2018|12:16|User2| Secret1|Checkin
1/4/2018|12:17|User3| Secret1|Checkout
1/4/2018|12:45|User3| Secret1|Checkin


What I need to be able to do is to build a query/queries that will provide actionable data showing when multiple users are checking out a secret within a certain time frame.

So if within a single hour, more than one user has checked out a particular secret  I need to know the time between when user1 checked out Secret 1 and user 2 checked it out.

I will then create a report that shows the percentage to show the contrast between when this scenario is met against the activity we see on a normal basis.

Any help as to an approach would be much appreciated!
0
We have 2 tables, one table is Customers, with each one's location as a Geography column, and one table is Branch Offices also with each one's location as a Geography column (we populate the Geography columns from latitude and longitude columns)

The tables are simple: Cust_Locations has CustNum (integer, PK) and CustLoc (geography, with spatial index), Branch_Locations has BranchNum (integer, PK) and BranchLoc (geography with spatial index).

We need to run a query (view) that's intended to show the closest branch office to each customer, based on Geography columns, and it runs fine with a couple of thousand customers. We just received a big job that needs to run with 700,000 customers and it takes hours to run. Can anyone suggest any ways to speed up this SQL?

WITH CLOSEST AS (
    SELECT *, ROW_NUMBER() 
    OVER (
        PARTITION BY CustNum
        ORDER BY Miles
    ) AS RowNo 
    FROM 
(
SELECT 
CustNum,
BranchNum, 
CONVERT(DECIMAL(10, 6), (BranchLoc.STDistance(CustLoc)) / 1609.344) AS Miles
FROM 
Branch_Locations
CROSS JOIN
Cust_Locations
) AS T
)
SELECT TOP 100 PERCENT CustNum, BranchNum, Miles, RowNo FROM CLOSEST WHERE RowNo = 1 ORDER BY CustNum, MILES

Open in new window


I have no ida what to try. One thought was could there be a way to put the distance comparison into the JOIN? Nothing else comes to mind so far...

Thanks for any suggestions!
0
Simple Misconfiguration =Network Vulnerability
Simple Misconfiguration =Network Vulnerability

In this technical webinar, AlgoSec will present several examples of common misconfigurations; including a basic device change, business application connectivity changes, and data center migrations. Learn best practices to protect your business from attack.

Hi,

I have a table which has to columns namely start range and end range with datatype as varchar(200) receptively. There are thousands records, now i have to filter out the records which are withing the range of 500-1000? How can i achieve this using sql query?
0
Trying to combine multiple fields into one where other column is duplicate.

Select S.SerialNumber,  P.Packaging
From Serial S inner join Packaging P on S.Item = P.Item

Example of Results
SerialNumber            Packaging
1111                            10 Carton/Metal Can
1223                            10 Carton/Metal Can
2222                            12 Carton/Wood Container
2333                             15 Carton/Wood Container

What I would like is
SerialNumber            Packaging
1111, 1223                 10 Carton/Metal Can
2222                            12 Carton/Wood Container
2333                             15 Carton/Wood Container

It's for a SSRS query. Using SQL Server 2008 R2 and SSRS 2008 R2.
0
In MS SQL Server 2017
When I highlight some sql
and right click

What is EXEC INLINE ??
0
Hello,
i have to addcomposite to an existing table .
The table already has primary key defined .

Any suggestions?

Thanks
0
I have a SQL Server 2008 R2 instance with a database which is currently set to use the Simple recovery model. The database files are distributed between the primary file group and several additional file groups. I'd like to change this database over to use the Full recovery model, taking a Full database backup every 6 hours and transaction log backups every 15 minutes. Do I need to do anything additional to handle backups for those additional file groups or will the Full database backup file include those files? My understanding of the recovery process under the Full recover model would just be to restore the Full database backup and then sequentially restore any transaction log backups that occurred after that Full backup was taken. However, I just want to be certain I'm not missing anything with those additional database files & file groups.
0
Hey all I am in need of some help setting up TFS on my Azure windows server 2016 VM.

I think i have all my firewall rules set up but i'm not that sure if i do or not. I followed this site as an example.



However, I am not able to reach it outside of my server using my domain name like so:

http://mydomain.me:8080/tfs


I have also tested out my connecting outside of my server for my SQL 2017 and it seems i cannot connect to that either.


What am i missing in order for me to connect to both the SQL and TFS from outside the server?
0

Microsoft SQL ServerSponsored by Flatiron School

161K

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.