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

I'm creating a View and wanted to make sure that the table referenced is always the database the view sits in.  In the code below, the database is 'bps'.  If I rename the 'bps' database to 'bps2020', these views break and I'm wanting this as a template.

Is there a way to create the view without having to reference the database?

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `missingreps`  AS  select `cl`.`room` AS `room`,`cl`.`teacher` AS `teacher`,`cl`.`year` AS `year`,`cr`.`firstname` AS `firstname`,`cr`.`lastname` AS `lastname`,`cr`.`email` AS `email`,`cr`.`phone` AS `phone` from (`bps`.`classes` `cl` left join `bps`.`classreps` `cr` on((`cr`.`room` = `cl`.`room`))) where isnull(`cr`.`firstname`) order by `cl`.`year` ;

Open in new window

0
How to Generate Services Revenue the Easiest Way
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

what can I do instead of the following?  It takes too long.

--get all values
select  distinct
a.[Year],
b.Region,
b.County
from
(select distinct [year] from [dbo].[YTDDate] as a with(nolock)) a,
(select distinct Region, County from [dbo].[Geographics] as b with(nolock) ) b
0
I'm trying to design a report based on a function query in our ERP. (as in, under Database/Programmability/Functions/Table-valued Functions)

The FROM clause is this:

FROM p21_fnt_invoice_hdr('COMPNAME', @ls_begin_invoice_no,@ls_end_invoice_no, 0, 9999, '0', 'ZZZZZZZZZZ', 0, 999999999, ' ', 'ZZZZZZZZZZ', ' ', 'ZZZZZ', 0,999999999, {ts '1900-01-01 00:00:00.000'}, {ts '2019-12-05 23:59:59.000'}, 'Y', 'N', 'N','B','Y', 'N','','N')

Open in new window


If I run this in SSMS, providing the two parameters (@ls_begin_invoice_no  and  @ls_end_invoice_no),  it returns the data as expected.

But, I cannot figure out how to make SSRS work?

I get no results if I try & apply parameters..
No errors, just no results

thanks,

Rich
0
Hello Experts,

Writing to a file in peoplecode, results in data successfully populating the file. However, on continually overwriting to the same  file I not only end up with the data but extraneous information from our environment. I may be able to overwrite 5x in a row and on the 6th time I will get the extraneous environment data (pieces of config file, and other stuff) with the real data.

Below you will find the code, even if you are not familiar can you think of any suggestions why this happens?


/* Declare varaibles */
Local File &CSVFile;
Local Record &ResultsRecord;
Local SQL &SQLMonerisOut;

&Filename = "MonerisOut";

If MONERIS_AET.NC_REFUND.Value = "Y" Then
   &Filename = &Filename | "REFUND_" | DateTimeToLocalizedString(%Date, "yyyy-MM-dd") | ".dat";
Else
   &Filename = &Filename | DateTimeToLocalizedString(%Date, "yyyy-MM-dd") | ".dat";
   
End-If;

&CSVFile = GetFile("\\" | &envrionmentConfigDto.NC_CUR_FILE_SVR | "\AAT\SF\Moneris\" | &Filename, "W", %FilePath_Absolute);


If &CSVFile.IsOpen Then
   If &CSVFile.SetFileLayout(FileLayout.MONERIS_OUT_FILE) Then
     
      &ResultsRecord = CreateRecord(Record.MONERIS_PURV);
     
      &SQLMonerisOut = CreateSQL("%Selectall(:1)", &ResultsRecord);
     
      While &SQLMonerisOut.Fetch(&ResultsRecord);
         
         &CSVFile.WriteRecord(&ResultsRecord);
         
      End-While;
     
   End-If;
   
Else
   /* DO ERROR PROCESSING FILE NOT OPEN */
End-If;

0
I need help in deciding path forward for our MS Access database.

Recently my old company was bought out by another and the new company changed the network topography. They removed network drives and went completely cloud base. We are using OneDrive platform. OneDrive does not support MS Access functionality at all. I cannot share our MS Access database with multiple users, because OneDrive replicates back-end for every user that links to it. so I needed to place back-end on every user’s computer. As result, I have several versions of the back-end and we do repeat inspections as result.

I am looking for a way to solve this problem, but also a good path forward with MS Access database, and I need your advice.

I have considered the following things:

1.      Use a virtual server to host our Access database
2.      Install MS Access 2010, change database to web apps and then host it on an older SharePoint server.
3.      Use an SQL server, and migrate MS Access back-end to SQL or AZURE and somehow link it to the front end. I read about this online, but not sure how much work will be involved.
4.      Completely redesign the database to SQL version or other version
5.      Leave as it is and deal with it.

The company is willing to pay for the hosting, but I am also looking at path forward and future of this database. Its quite developed and very useful for many personnel.

I need your expertise advice since you all know ins and outs of other databases.

Thank you.
0
I ‘m using SQL Server 2016 and SQL Server Management Studio.

I have a script that looks like this.

-- ********** Script 1 - Start of Script ********** --

---------- Step 1 - if table exists drop it ----------
DROP TABLE IF EXISTS [dbo].[LooneyEmployees]
---------- Step 1 - if table exists drop it ----------

---------- Step 2 - Create table to insert data ----------
CREATE TABLE dbo.LooneyEmployees
(
    EmployeeID INT,
    LastName NVARCHAR(40),
    FirstName NVARCHAR(40)
)
---------- Step 2 - Create table to insert data ----------

---------- Step 3 - Builk insert the data from csv file ----------
BULK INSERT dbo.LooneyEmployees
FROM 'C:\test\LooneyEmployees.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
---------- Step 3 - Builk insert the data from csv file ----------

---------- Step 4 - Check to see if the data go inserted into the table ----------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
FROM [TestDatabase].[dbo].[LooneyEmployees]
---------- Step 5 - Check to see if the data go inserted into the table ----------

-- ********** Script 1 - End of Script ********** --

Open in new window


When I run this script, it exports the data from a comma delimited file and then creates a table and populates the table using BULK INSERT.
My comma delimited file I’m using for my script is called LooneyEmployees.csv I’m attaching it to this post.

If you look at LooneyEmployees.csv in a text editor like notepad it looks like this:

LooneyEmployees.csv
Notice how the column names are in the first row.

So when I run my script I get this. The script works fine.

 when executing my script
In my script, in Step 2 I create the table and declare the table columns.

Instead of declaring the columns like I am doing in Step 2, Is there a way to look at Row 1 of the CSV file and then creating the table using the column names from the column names found in Row 1 of the CSV file?

See sometimes the LooneyEmployees.csv file will look like this:

 LooneyEmployee.csv (initially)
Then after week 2 the LooneyEmployees.csv file will look like this.

 LooneyEmployee.csv (after 2 weeks)
The csv will always have these fields and they will always be of this datatypes:
EmployeeID INT, LastName NVARCHAR(40), FirstName NVARCHAR(40) so I can just hard code these columns.

The only field that will change are the Hours columns and those will always be Integer columns.
LooneyEmployees.csv
LooneyEmployeesAfter2Weeks.csv
0
I have a table that has  3 fields - Date, RecordBucket, RecordCount- every day there are new entries.  I want to run a query that compares data in the RecordCount field week over week.

So it looks for the data for every Monday and compares it to the previous Monday and then has a calculated field showing what percentage of increase/decrease.

Is this possible ?
0
TSQL script for deleting old backups does not see path.

Does not see path
EXEC usp_DeleteOldBackupFiles @BackupFolderLocation='E:\MSSQL12.MSSQLSERVER\MSSQL\Backup\database1\', @FilesSuffix='bak', @DaysToDelete=31

Does see path - But when I run the restore command it works fine, I am using the same path to the server for both.
restore database [database1] FROM DISK = 'E:\MSSQL12.MSSQLSERVER\MSSQL\Backup\database1\database1.bak' WITH replace;

I followed these instructions and I get this error message
MSSQL Tips
error
Any help is appreciated
John
0
Joining multiple files in as400 sql statement. need an example.
0
Our developer wrote a program that is dealing with a large amount of data from one DB and pushing it to another DB (after converting it from MSSQL. The statement she is using is:

 SELECT MYFIELD_SEQ.NEXTVAL NEW_ID FROM DUAL;

We've noticed that the program that is issuing this statement slows down after some time. Looking in the DB logs we see:

2019-12-04T10:34:18.937766-06:00
MO(4):WARNING: too many parse errors, count=1026276 SQL hash=0x23acadbc
MO(4):PARSE ERROR: ospid=9860, error=904 for statement:
2019-12-04T10:34:18.937766-06:00
MO(4):SELECT "NEW_ID", ROWID InternalRowid FROM DUAL
MO(4):Additional information: hd=0000012B2BB4CF00 phd=0000012B869366F8 flg=0x20 cisid=110 sid=110 ciuid=110 uid=110

It looks like Oracle is adding the " " to NEW_ID. The command works without the quotes

The client is 11.2.0.4 (because we are still using oo4o) and the database we are converting into is 12.2. How do we resolve so it doesn't add the quotes?
0
Learn SQL Server Core 2016
LVL 19
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

I have a table in Access linked to a hosted SQL Server DB. It is one of many tables in the DB but it is the only one that randomly, at least once per day, seems to lock up. When users try to add a record they get the ODBC linked table message with a timeout.

It is a standalone table with no relationships anywhere else. I do not host the database so don't necessarily have access to logs etc.

What can I do to either stop this happening, or see what the problem is?
0
hi,

For SQL server always on group, if I have to upgrade any SQL server nodes inside the group to latest service pack, what is the correct procedure and order to do it?

e.g. do i evne need to remove the nodes from the Always on Group before upgrading ?
0
I have an SQL db with 3 tables in it: -

T_Parents
Parent_No int
Parent_Forename nvarchar(50)
Parent_Surname nvarchar(50)

T_Pupils
Pupil_No int
Pupil_Forename nvarchar(50)
Pupil_Surname nvarchar(50)
Parent_No int

T_Temp_Report
Parent_No int
Parent_Forename nvarchar(50)
Parent_Surname nvarchar(50)
Pupil_Forenames nvarchar(100)

The T_Parent tables stores details of parents and the T_Pupils details of school pupils.  The Parent_No & Pupil_No are unique keys.

A record in the T_Parent table may be related to 1 or more records in the T_Pupils table.

I want to create a report which lists each parent, all related pupils and a lot of other stuff that I have left out for now as it works fine.  So, I created a table T_Temp_Report which I use to gather the data together and it is then listed on the report.

The issue that I am having is combining the pupils names into a single field. e.g.

Parent_No 1001 is Sally Smith who has 3 pupils related to her, John, Joan & Fred.

I want to copy Sally's record from the T_Parents table into the T_Temp_Report table and then add the 3 pupils names into the Pupil_Forenames field so that they are recorded as John, Joan, Fred

How do I do this?
0
Hi,
I am trying to fetch some data by doing a query to Salesforce.com DB.
For that I use a REST adapter and I have to pass a query in the REST URL.

I have the following Query to pass
select AccountId, CreatedDate, ActivatedDate, (select CreatedById, from OrderItems) from Order

and I am able to successfully pass it using the following URL string
https://XXXXXXXX.com/services/data/v20.0/query/?q=SELECT+AccountId%2c+CreatedDate%2c+ActivatedDate%2c+%28SELECT+CreatedById+from+OrderItems%29+from+Order

which results in the following output
{
    "totalSize": 1,
    "done": true,
    "records": [
        {
            "attributes": {
                "type": "Order",
                "url": "/services/data/v20.0/sobjects/Order/XXXXXXXXXX"
            },
            "AccountId": "YYYYYFFFFFFFFFF",
            "CreatedDate": "2019-11-22T01:30:16.000+0000",
            "ActivatedDate": null,
            "OrderItems": null
        }
    ]
}

My next task is to pass the "CreatedDate" value as "2019-11-22T01:30:16.000+0000" to fetch the same output result. So I am trying to figure out how I can build the query to pass the date string with all the encoded characters ?
0
I have a large table (1000 records) that is generated from a SQL database and PHP.

I have a button on each row that opens a fancybox screen overlay that contains information from another table, based on the id of the row that contains the button. This overlay table could contain 1000 records.

Therefore, the whole page could contain 1,000,000 (1000 X 1000 records), which would take too long and kill the server etc.

My code displays the 'master' row and the 'slave' rows as follows:

<?php
//master table header
echo "<table id='download' class='tablesorter'>";
echo "<thead><tr>";
echo "<th>Id</th>\n";
echo "<th>...</th>\n";
echo "<th>Order Ref</th>\n";
echo "</tr></thead><tbody>";

//master table sql
$sql= "select  [id], [orderref],.... from table";
$result=sqlsrv_query($conn, $sql);
while($row=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
$n=$n+1;

//master table content
echo "<tr>";
echo "<td>".$row['id']."</td>\n";
echo "<td>...</td>\n";


//button to show overlay with slace table
echo "<td>";
echo "<a data-fancybox data-src='#hidden-content".$n."' href='javascript:;'>".$row['orderref']."</a>";
echo "<div style='display: none;' id='hidden-content".$n."'>";

        //overlay content
        echo "<h1>Slave Table for Order ".$row['orderref']."</h1>";
        echo "<table id='slave' class='tablesorter'>";
        echo "<thead><tr>";
        echo "<th>Id</th>\n";
        echo "<th>...</th>\n";
        echo "<th>Order Ref</th>\n";
        echo 

Open in new window

0
Trying to connect to a Microsoft SQL 2016 db on another computer. When I try to connect, I get the following error.

Cannot connect to DESKTOP-UMNOU4K\APSQLEXP.

===================================

The target principal name is incorrect.  Cannot generate SSPI context. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
Server Name: DESKTOP-UMNOU4K\APSQLEXP
Error Number: 0
Severity: 11
State: 0
Procedure: GenClientContext


------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, …
0
Hi Experts,

I have a list of required fields, and trying to construct a SQL statement out of it as follows.

ListOfFields.
[input#InsertRecordVisit_Date.cbFormTextField, input#InsertRecordClient_Last_Name.cbFormTextField, input#InsertRecordClient_First_Name.cbFormTextField, input#InsertRecordDate_Of_Birth.cbFormTextField, select#InsertRecordShift_From_Hour.cbFormSelect, select#InsertRecordShift_From_Minute.cbFormSelect, select#InsertRecordShift_From_AMPM.cbFormSelect, select#InsertRecordShift_To_Hour.cbFormSelect, select#InsertRecordShift_To_Minute.cbFormSelect, select#InsertRecordShift_To_AMPM.cbFormSelect, textarea#InsertRecordPurpose_Of_Visit_Goal.cbFormTextArea, input#InsertRecordTemp.cbFormTextField, input#InsertRecordPulse.cbFormTextField, input#InsertRecordRR.cbFormTextField, input#InsertRecordBP.cbFormTextField, input#InsertRecordO2_Saturation.cbFormTextField, input#InsertRecordO2_In_Use0, input#InsertRecordO2_In_Use1, input#InsertRecordRespiratory_Lung_Sounds.cbFormTextField, input#InsertRecordRespiratory_Breathing_Pattern.cbFormTextField, input#InsertRecordBipap0, input#InsertRecordBipap1, input#InsertRecordCpap0, input#InsertRecordCpap1, input#InsertRecordTrach0, input#InsertRecordTrach1, input#InsertRecordVent0, input#InsertRecordVent1, input#InsertRecordCardiovascular_Skin_Color.cbFormTextField, input#InsertRecordSkin_Temp.cbFormTextField, select#InsertRecordCardiovascular_Edema.cbFormSelect, input#InsertRecordNeuro_Alert0, 

Open in new window

0
hi,

For SQL server DR and HA, since SQL 2012 we starting have AOG, so now is it say that we only has to configure the AOG then we handle both HA and DR across different far away location ?

how to setup AOG for different site and subnet for auto failover  ?
the write/primary nodes can failover to other long distance site ?

usually if other office has a an application and that application when failover, has to write to a long distance primary miles away and it will bring application latence, how we solve this problem? we need to setup a separate AOG for each office and setup merge replication between them so that each office update their own in their own AOG and then merge the result together?
0
Hi Experts,

I have a table containing 4 fields.

SNVID = (PK)
User_ID_num_SNV
Created_Date
Browser

This should contain the browser version users had while creating this record.

We are trying to get all users to upgrade their browser to the current version.

Would like to create a daily report showing how many users have upgraded their browser.

What is the easiest way to accomplish it?
0
Success in ‘20 With a Profitable Pricing Strategy
Success in ‘20 With a Profitable Pricing Strategy

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

I am running SQL 2017 on my local server in our domain and also on a cloud server which is not setup as a domain.

I have linked the 2 servers via SSMS 18.4 and when I run Test Connection it states that the connection is successful.  I can also see both servers & databases in SSMS.

If I create a SP on one server to insert records into an identical db on the other server and run it, records are copied from one server to another.  However, if I add an insert Trigger to the table in one server and add a record to it, it eventually errors with this error: -

The operation could not be performed because OLE DB provider "SQLNCLI11" for server "12.34.56.78" was unable to begin a distributed transaction.  OLE DB provider "SQLNCLI11" for the linked server "12.34.56.78" returned message "No transaction is active."

I have Googled this and followed the usual advice to change the Security settings in Local DTC Properties, but am getting nowhere with it.

Can anyone assist?
0
Dear expert

I would like to delete a dB table using a query, I would like to delete this bkcopy_* table. I already got one procedure to create one:
USE [TEST_dB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [bkcopy_test]
AS
DECLARE @datum CHAR(10) = CONVERT(CHAR(8), GETDATE(), 112);
exec ('SELECT * INTO [TEST_dB_'+ @datum +'] FROM [Test]' 

Open in new window

Above query create one table with a copy content of that test table, now I would like to first check if the table exist, if it exist then delete that table with name: Test_bB* Is this possible to do?
0
I build web applications that have .Net front-ends and MS SQL back-ends.  I would like to give my clients mobile versions through an App for IOS or Android.  
I started learning Xamarin and it feels like a heavy lift.  Can anyone recommend some other strategies?
Are there Apps that I can piggy back on to give my clients access to their data?  I think Microsoft has something that can be used internally at a company but I was looking for something available to the public.
0
Hi,

I d like to make a sql report on:
-Microsoft rds brokers: how many users, which users, collections etc
-VMWare Horizon

Please advise
J
0
Hi,

Can I create a sql report (ssrs) for a AppV  full infra?
I d like to show all s the statuses: applications, versions, deployed to etc

J
0
Hi,

I d need an sql query to query all applications on an sccm server based upon the folder they are and also the collections they are deployed to, preferably also the number of installs/fails etc.

Actually the same result when you do get-cmapplication  and get-collection
This might help?
http://eddiejackson.net/wp/?p=14764


J
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.