SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

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

Sign up to Post

Hi,

I need some help to know what is the best approach to calculate some values to pay.

Attached xls with values and  example
Example2.xlsx
0
5 Ways Acronis Skyrockets Your Data Protection
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

hi,

any one know if SQL 2017/2019 support microservice platform ?

fully supported or any feature doesn't work on top of it?

any URL for it?
0
Hi,

In current production database of Mcirosoft sql server 2008, we recently performed maintenance activity of truncating log table that consumed 110 gb of storage space.
After truncate job is complete, we ran reorganize task for tables and indexes plus update statistics job on the table and indexes (full scan). The job was successfully complete
Even after performing the above task, the application performance had degraded.  
New to the Microsoft sql server world, please recommend any pointers to improve the performance.

Regards
0
Hello, I am looking for simple code that shows how to connect to a MS SQL Server using nodejs. Everything I have found is overly complicated.
I want code that shows how to connect to the server. It's remote.
Also run a simple query. Querying one row.

The code below is what we were using to just connect to a local Access Database.
I need it modified to connect to MS SQL Server.
Thanks.

describe('test db connection', function () {
  it('tests db connection', function () {      
                var ADODB = require('node-adodb');
                var connection = ADODB.open('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=CDPDB.accdb;');
                connection
                  .query("Select @@Version")
                  .then(data => {
                                console.log(JSON.stringify(data, null, 2));
                  })
                  .catch(error => {
                                console.error(error);
                  });
   browser.sleep(5000);
});
});
0
Hi Guys,

A quick one for you - In PG sql I want to select the current date minus a number of days as a variable at TIME ZONE 'US/Pacific'.

For instance

 select (now() -  cast( :p as integer) AT TIME ZONE 'US/Pacific');

( :p is a number i.e 2 to be passed when running a query ).

I want the above select to return the current date/time AT TIME ZONE 'US/Pacific' minus a number of days .

How can I achive it please ?
0
Just installed SQLExpress 2008 and SP3.  I called it SQLEXPRESS2008. When I open Configuration Manager that is the name that is displayed.  

I cannot log in with Windows Authentication or SQL Authentication.  

In configuration manager I checked the user name and re-entered the password.  Still cannot get in.

What can be done?

SQL2.jpg
0
I'm experiencing a rather bazar issue I've never run into previously.

I'm inserting data into an Access 2003 text field (255 char) where several rows are null or blank but when I test the length len([Field]), they all come out as over 100 characters wide.

The source of this field (from a customer) is a SQL Server table with the same characteristic.


How can I transfer this field and end up in Access with a zero length value?
0
Actual issue :  there is a PF with 5.000.000 records  (reclen 1000 Bytes, but with a BLOB field.) PF occupies about 1,5 TB (!)
A deletion Job deletes older records  periodically. REUSE  is set to *YES.
Problem: BLOB field-size will not be reduced, also if new records have smaller documents (because of optimized application) as in the old, deleted records.

Theoretical solution:  set PF  to RERUSE = *NO,  write new records  - with smaller documents/smaller BLOB-size  on new diskspace, and then do a RGZPFM,
to remove deleted records (with larger BLOBs) may reduces filesize.
But there is not enaugh diskspace for a RGZPFM.
Is there any other solution to reduce unused BLOB-space in a BLOB field ?
(OS400 with V7.3)
0
Hi,

I have 4 simple tables containing a list of countries along with a sales amount
Fields names Country and Sales Amount
Each table is for a different stage of the sales process (Let us call them Sales1, Sales2, Sales3, Sales4)

How can I create 1 table with one row  of data for each country.

The problem I have is that not all countries have  sales at the different stages, and where this is the case there is no data.
So table Sales1 may have 3 rows e.g. UK, Finland, Germany each with a corresponding sales amount.
Table 2 may only have two rows for UK and Germany, as Finland has no data at this stage
Table 4 might have four countries etc.

What I need is an outcome like the following
Country    Stage1     Stage2    Stage3     Stage4
UK             42500      13500      2390        0
Finland     14000      0               0              3000
Germany  27000     25000       43000     18500
Ireland      0              0               1000       27200

Many thanks for any help
0
Hi I am relatively new to SQL, so please forgive the basic nature of the question

I have a table (called Opportunities) with a number of fields including
Country
Sales Stage
Amount

The sales stage contains a value "Stage 1" , "Stage 2", "Stage 3", "Stage 4"

What I want to do is create a new table (Called StageAmounts) with the following fields

Country, "Stage 1", "Stage 2", "Stage 3", "Stage 4"

However not all the country and stage combinations will have an amount, it will not be 0 the data will just not exist in the Opportunities Table

Therefore how can i create the StageAmounts Table, with a fully populated dataset with 0's (or blanks) where there is no corresponding data in the Opportunities table.

Many thanks for any assistance

Steve
0
Expert advice: How to get hired in cyber security
Expert advice: How to get hired in cyber security

Phil Richards knows cyber security. He’s the Chief Information Security Officer for Ivanti—and he has great advice for anyone looking to build a career in cyber security.

I need help with those dates in my Store procedure  in SQL Server :
I am doing calculation based on time ranges of rolling month, rolling  3 months and rolling  a whole year.

Rolling over 2 year
To determine those dates  I know   I Should use DateAdd() function but I need to be sure how they looks like for particular date , just that I will not make mistakes.
In procedure I am passing Start_Date that user will select.
--Range_Month:

Declare @Month_Range_Start_Date Date                           e.g. if start_Date is 03/15/2019 this date should be 02/15/2019  
Declare @Month_Range_End_Date Date                             e.g  if Start_Date is 03/15/2019 this date should be the same 03/15/2019

Declare @Previous_Month_Range_Start_Date Date            e.g. if  Start_Date is 03/15/2019 this date should be 02/15/2018
Declare @Previous_Month_Range_End_Date Date              e.g. if  Stard_Date is 03/15/2019 this date should be 03/15/2018


--Range_Quarter:
Declare @3M_Range_Start_Date Date                         e.g. if Start_Date is 03/15/2019 this date should be three months back 12/15/2018
Declare @3M_Range_End_Date Date                          e.g. if Start_Date is 03/15/2019 this Date should be the same 03/15/2019

Declare @Previous_3M_Range_Start_Date Date       e.g. if  Start_Date is 03/15/2019 this date should be 12/15/2017
Declare @Previous_3M_Range_End_Date Date         e.g. if Start_Date is 03/15/2019 this date should be 03/15/2018


0
working with Oracle Linked server in SSMS 2008R2

do i need a terminator(or something else)  between  OPENQUERY/dynamic sql statements

I have this line of code

TRUNCATE TABLE [dbo].[XREF_HEADER_DETAIL_ORACLE_EXTRACT_2019_STAGING]
begin try

DECLARE @StartDate AS VARCHAR(10) = '07-JAN-19';
DECLARE @EndDate AS VARCHAR(10) = convert(varchar, getdate(), 101);
DECLARE @Statement NVARCHAR(MAX) = N'
    SELECT *
    FROM OPENQUERY(
        XREF_PROD,
        ''SELECT


etcx......  code


EXECUTE sys.sp_executesql @Statement;



end try
BEGIN CATCH  
   select
   ERROR_NUMBER() AS ErrorNumber,
		ERROR_STATE() AS ErrorState,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
END CATCH



--- do i need additional code here between statements/dynamic sql statements  ?

--- next dynamic sql statement  

TRUNCATE TABLE [dbo].[ORACLE_CUST]


    SELECT *
    FROM OPENQUERY(
        XREF_PROD,
        ''SELECT reportsTor,


code etcccc...

Open in new window



Thanks
fordraiders
0
Here is a query

select STU_WBL_X.WBL_EXPCODECCR
from S_SC_STU_WBL_X;

The results from this particular query could be
Could be C or I
But I don't want the letters C or I in the results.
I want Cooperative for C and Internship for I.

What is the correct syntax to get the results spelled out?
Thanks
D
0
I have a table dc_split in which I split a bill total of $2000 three ways.
First and second person's share is 33.3333
The last person's share is 33.3334

create table dc_split (fullname varchar(70), billpercent numeric(7,4), billtotal numeric(12,2), theirshare numeric(12,2))

insert into dc_split (fullname, billpercent, billtotal)
values('Lori', 33.3333, 2000)
insert into dc_split (fullname, billpercent, billtotal)
values('Pete', 33.3333, 2000)
insert into dc_split (fullname, billpercent, billtotal)
values('Joey', 33.3334, 2000)

After updating the table to get their total share:
update dc_split set theirshare=billpercent * billtotal/100

The results are that the share for each person is 666.67 which sums up to a total of 2000.01

I need a way of getting their split values instead to be 666.66 for the first two people
and 666.68 for the last person.  So basically for the first two people, theirshare should not
round up and the last person's share should make up the extra 2 cents.

Is there a way to do this in the sql?
0
I have SQL Server 2017 running on a Windows Azure Virtual Machine.
I have an issue with connectivity.
I am getting the following error when attempting to connect using HTTP://serverip/OLAP/msmdump.dll
The error message is
Either a connection cannot be made to the XXX.XXX.XX.XXX server, or Analysis Services is not running on the computer specified.
Client access is over the Internet connections.
SSAS and IIS are on the same server.
The Azure VM was deployed using Resource Manager.
0
using REPLACE and RIGHT to delete anything to the right of a decimal. including the decimal
or something else ?


in a Function i have

Declare @Result varchar(Max)
SET @Result = Replace(@Input,'!','')
Return @Result

I have data in a field that looks like  "1.00"
or
1.45677

I need to strip anything to the Right of the decimal.

Thanks
fordraiders
0
I looked online I cant seem to find the MS SQL 2016 backup command with "MAX" compression.

I have seen plenty statements with compression Keyword but none of them have the level specified 0-10 or 4 not even sure what the max is but thats what I am looking for right now.

Native SQL backup with max compression for SQL server 2016.
0
Hi Experts,

I am in need of some help in order to convince manager to agree on designing a table in a normalized manner.

Attaching two versions of the form, the first one is a none normalized version, while the second is in normalize state.
Also attaching how table definitions would be according to each of those forms.

Basically would need a list of advantages vs. disadvantages we are to expect by choosing each path, in this case in particular.

Since users are used to enter data manually in sheets in a weekly bases (similar look to the denormalized version) they have preference on taking that route.

FYI-Table in question is PatientsMedications (posting test data).

Thanks
Untitled.png
Untitled1.png
Untitled2.png
Untitled3.png
0
I'm looking for a way to "evaluate" an assembled string that is stored in a table.
The table has string data with many types of special characters such as nchar(160) or nchar(8204).  Many columns of it in many rows.
As a simple example let's take '>' + char(160) + '<' as the string data, and I want it to return "> <".

Looking for something that would be akin to SELECT EVALUATE_STRING(StringData) FROM Table.

DECLARE @TestTable TABLE(Seq INT IDENTITY, StringData nvarchar(100))
INSERT INTO @TestTable VALUES ( '''>'' + char(160) + ''<''' )  --non-breaking space

SELECT Seq, StringData 
	FROM @TestTable

--Shows the desired result when not selected from a table
SELECT '>' + char(160) + '<' AS 'Direct_Select__Desired_Results' 

--If I try a select statement, it doesn't work.
SELECT Seq, (SELECT StringData) AS 'Subqueried_Data' 
	FROM @TestTable

--How do I get the desired results when selecting from a table?  
SELECT Seq, /*??*/ StringData AS '??Evaluated_StringData??' 
	FROM @TestTable

Open in new window

Suggestions?  I hope it's simple.

Thanks.

SQL Server 2008
0
Active Protection takes the fight to cryptojacking
LVL 2
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

whats the best way to make an app that recieves messages from udp port (string )  convert into and object  and send it to a  db  by store procedure . using .net or .net core   ... is favorable to use MSQM ? (using a buffer ?) or  is way better to use  sql server vs MSQM .. what about MSQM over WCF ? ane suggestions.  .. it means  massive (5000+/sec) messages    and convert it  to an object and  use a store procedure
0
does anyone have a query which could be used to determine object level permissions in a database, e.g. table level permissions. I need to see if management are following a role based access control model, and also what permissions 'public' and 'guest' has to individual objects in each DB on an instance, e.g. ability to read data from specific tables.
0
i just installed ms sql developer 2016 server on window 10. see attached. and I do not make any custom changes.
and also install ms sql management.

It seems like everything is installed successfully, but when i try to connect localhost or pc name or lan ip which i think it is the database.
It does not let me connected.

1. where i can make the changes e.g. create sa account and password?
2. Or if you know how to connect, please share with me.

Thanks
msdevsql2017.PNG
0
I need to learn how to document permissions on reports in SQL Server reporting services. Are the permissions all detailed in the databases local to the reporting server itself, and if so are there any SQL scripts that can be used to list all reports, and all users who have access to them? From what I can gather we just have a few 'shared' SSRS setup, which are reports based on databases from numerous different SQL Server instances across the network. Being able to document for each report, what data source it uses as well would be very interesting. Knowing which server roles as well exist in SSRS if that is the correct phrase would be useful, to determine who can do what with each report, and where exactly you can get a report of who has was SSRS roles.

second part of my question, from within a SQL instance, which stores usual user/system databases, can you determine which (if any) remote SSRS reporting services the instances/databases are connected to, and if so, how?
0
Please can someone advise the best way to store an array in a database?

I normally pass arrays via a session cookie, but there are times that I need to store and retrieve an array in a database. I store a serialised version, as below:

<?php
$params=array($foo, $bar);
$_SESSION['params']=serialize($params);
?>

Open in new window


When I retrieve the session cookie and apply the $params (usually to a parameterised sql query, the array works.

However when I store it in a database, it works for a time, then after I update the database record, either updating or leaving the array alone, the array gets returns with added slashes,

The working unserialized array looks like this and works:
string(181) "array ( 0 => 'WROES', 1 => 'john', 2 => 'john%', 3 => 'baker', 4 => 'baker%', 5 => '', 6 => '%', 7 => '', 8 => '', 9 => '', 10 => '', 11 => '', 12 => '', )"

Open in new window


After any database manipulation, it looks like this and fails:
string(181) "array ( 0 => \'WROES\', 1 => \'john\', 2 => \'john%\', 3 => \'baker\', 4 => \'baker%\', 5 => NULL, 6 => \'%\', 7 => NULL, 8 => NULL, 9 => NULL, 10 => NULL, 11 => NULL, 12 => NULL, )"

Open in new window


The code I am using to update the database (SQLserver and PHP with sqlsrv_query) that seems to cause the problem is:

if(($_POST['postback']=='update-profile')){

$id=($_POST['id']);
$paramsprofile=$_POST['paramsprofile'];
$profilename=($_POST['profilename']);

$sql="update tblprofiles set profilename=?, sql=?, params=? where 

Open in new window

0
Hello

Since failing over one of our Databases in SQL Server 2016 to our secondary node and then failing it back, we have been unable to take a full backup of the database due to a sysft_FT file stuck in a restoring state.

The full error from the failed backup is:

The backup of the file or filegroup "sysft_FT....." is not permitted because it is not online. Container state: "Restoring" (8). Restore status: 8. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

When I run the following query against the database, I can see the state is stuck in RESTORING:

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

Open in new window


The actual full text catalog file for the database is online (ftrow_FT....). I have seen articles advising to rebuild this file, however I am looking for guidance on the correct way to approach this issue.

Thank you
0

SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.