Query Syntax

52K

Solutions

20K

Contributors

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 am using the following code in ASP.net (VB.net) to read the contents of a spreadsheet
into a dataset. Instead of specifically reading "Sheet1" how would I adjust the following
line to just read the first sheet?
sql = "SELECT * FROM [Sheet1$]"

    Public Function Read_Excel_Into_DataSet() As DataSet

            Dim sMinAndSecond As String = Me.Second_Minute_of_Upload.Text 'workaround to 

            Dim connetionString As String
            Dim connection As OleDbConnection
            Dim oledbAdapter As OleDbDataAdapter
            Dim ds As New DataSet
            Dim sql As String
            Dim i As Integer

            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;" &
             "Data Source=" & Server.MapPath("~/Temp/Excel_Import" & sMinAndSecond & ".xls") & ";" &
             "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

            sql = "SELECT * FROM [Sheet1$]"

            connection = New OleDbConnection(connetionString)
            Try
                connection.Open()
                oledbAdapter = New OleDbDataAdapter(sql, connection)
                oledbAdapter.Fill(ds)
                oledbAdapter.Dispose()
                connection.Close()


                Return ds
            Catch ex As Exception
                MsgBox("Can not open connection ! ")
            End Try



    End Function

Open in new window

0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I have an Oracle 11g  table TB_Docs with fields
    DocUID,  DocID , DocName, …..

I have TB_DocAttr  with following  fields
   UID,   OBJID , PropertyVal ,  StrValue  
 
( OBJID in TB-DocAttr  is a foreign key to primary key DocUID  in TB_Docs)

sample   TB_Docs
   DocUID    DocID             DocName
    123          UID_Doc1         Doc1
    345          UID_Doc2         Doc2
    678          UID_Doc3         Doc3

 TB_DocAttr
    UID       OBJID   PropertyVal      StrVal
     111        123       Prop1                Val11
     222        123      Prop2                Val12
    333        123       Prop3                Val13
    444        345       Prop2                 Val22  
    555        345       Prop3                 Val23  
   
 I need a query that can give the properties and  values  in the same row  like this
    DocName     DocUID        Prop1         Prop2       Prop3
   Doc1                  123            Val11           Val12        Val13
   Doc2                   345                                Val22        Val23

    Not sure Max(Decode  will be able to do it correctly

  How can I get it in Oracle?
0
had an issue where sql data warehouse in Azure stuck in "scaling" since around 9pm last night. It was unreachable/inaccessible all night and through this morning.    
 Microsoft says it got stuck because they had some regional fabric updates going on over night and somehow that hosed the DW and they had to do something on their end to restart/fix it.  
has this happened to anyone else? What typically causes this? would a user have had to have initiated something fort hat to happen first?  
The only other recommendation we got was to schedule maintenance time slot on the Azure Portal which will prevent maintenance affecting it during working hours.
0
How to export sql to csv using BCP?

1) My sql server does not have office. So we are not planning to use SSIS package.

2) I have a stored procedure which has input parameter of getting the file name.

SP returns 25 columns. We wanted to save the data in a csv file. We will have another SP which will send email alert with this csv file attached to the email distribution list.

How to export a stored procedure which has input parameter to csv file?
0
Hi Experts,

I need to have the following in a sub form within a main form.

From the table Patients_Daily_Medications

with the fields below.
PatientID
Medication
Day
TimeGiven
Sequence

I need a crosstab query that would show me Medications as column heading and sequence as row heading and TimeGiven as the values.

See attached what the data looks like, and what I am trying to accomplish (not completed as you can see...)

At the end I would need this query as the record source of a form that has a patientID and a date as fields, so this should only return values of a sibgle patient at a particular day.

PS- Note TimeGiven has a date+time, we need to extract the Time only.

PS -, a VBA function that will open a recordset of that table, Accept two params (PatientID,Day) and return the following would also serve the purpose.
ALBUTEROL: 9:00 AM, 11:00 AM      BUDESONIDE: 11:AM, 1:00PM, 5:00 PM      MIRALAX: 3:PM

Thanks in advance.
Untitled.png
Untitled1.png
0
I had this question after viewing DTS: Non-normalized Flat-file to Normalized SQL Table.

I have a group of pipe delimited txt files which i need to load into sql server. The first problem is that one file can have three columns and the next could have 238. there are 220 files and they can all have a different number of columns with different names.  The second problem is that they contain illegal characters... we have been able to address the characters very efficiently by bulk loading them into a temporary 1 column table and doing the "clean" but my problem now is trying to parse them out to another "temp" table dynamically so that i can then insert them into a staging table.  I have been working on this for weeks... any help would be appreciated
0
After application of the latest round of Windows 10 updates SQL Server 2017 will no longer start. I see errors 17120, 17182, 17190 and 17826. The notes I find on this are for SQL 2005 or lack of memory. None of these appear to be applicable in this case. Before I start rolling back updates what can I check? SQL had been running for months prior to these updates.
0
Experts,
 We have one visual web part in share point where we are pulling data from external SQL database by SQL connection string. We used default connection string. We have concern about execution time of query and timeout. Which parameter used for command execution and what is the default limit?
0
In vb.net, how can I create recordset from multiple datatables?  For example, if I want to use a left join between two datatables to see all records in one datatable that don't have a matching record in another datatable, how would I do that?  I know that if I create a connection object to a SQL Server or Access database then I can then use a command object based on that connection object to specify a SQL statement that includes a left join between two tables in that remote database.  Can I create a connection object to connect to a local dataset resident in the memory of the .net application?  If so, can I base a command object on that connection object and specify the names of datatables in that dataset in a SQL statement of that command object?
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
Exploring ASP.NET Core: Fundamentals
LVL 12
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

We are using an older Rainbow Portal Intranet site at work which allows users to sign in (For certain permissions) or use without signing in to use default permissions.

We have set up a page which ALL associates must read. So we want to log the users name or LAN ID when they open the page, The site has a log built in , it logs the date, time, user name (which is always blank), what page they looked at and length of time they were there. This gives us everything we need except assoc name.

Is there away I can get the LAN name of the user if they have not signed into the site in the Stored Procedure.
If you have any questions I will try to answer, but trying to put code in the HTML of the site would be very difficult if possible
0
How to convert a string to money in the Data Conversion Transformation process in SSIS?

My Source file is an Excel File. I added the Data Conversion Transformation to convert the Price column to the Data Type "currency[DT_CY]".

Source value : $50,240.30
Expected result : 50240.30
SQL Destination Table has the column Price with the Data Type : Money

However, conversion fails with the following error :

The "Data Conversion.Outputs[Data Conversion Output].Columns[Price]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Out

Open in new window

0
I have code that imports sales orders into an ERP solution. There is an order header and then order detail lines. The lines are imported first. The problem I have discovered is that even though I have code that writes to a log file indicating success or failure I have orders that simply do not get imported and there is nothing in the log file to indicate failure. So when I check the log I will see order 1000,1001, 1003,1005, etc. So 1002 simply is just not there. My question here and I am seeking your expert opinions is, should I not use DYNAMICS SQL that combines all the inserts into one execute statement and insert one line at a time and capture the log for each insertion?  I included the code to give you a sense of what is being done.

      -- Build the dynamics sql statment to insert the lines
      SELECT @dynamicSQL += 'EXECUTE taSopLineIvcInsert @I_vSOPTYPE=' + LTRIM(RTRIM(CONVERT(CHAR(10),H.DOCTYPE_SOPH))) + ',@I_vSOPNUMBE=''' + LTRIM(RTRIM(H.SOPNUMBE_SOPH)) +  ''',@I_vCUSTNMBR=''' +
            LTRIM(RTRIM(CUSTNUMB_SOPH)) + ''',@I_vDOCDATE=''' + CONVERT(CHAR(10),DOCDATE_SOPH,111) + ''',@I_vLOCNCODE=''' + LTRIM(RTRIM(LOCNCODE_SOPH)) + ''',@I_vITEMNMBR=''' + LTRIM(RTRIM(ITEMNUMB_SOPL)) +
            ''',@I_vUNITPRCE=' + LTRIM(RTRIM(CONVERT(CHAR(10),CAST(UNITPRCE_SOPL AS NUMERIC(10,2))))) + ',@I_vQUANTITY=' + '0' + ',@I_vPRCLEVEL=''' + LTRIM(RTRIM(@I_PRCLEVEL)) +
            ''',@I_vQTYTBAOR=' + LTRIM(RTRIM(CONVERT(CHAR(10),CAST(CUST_QTY_SOPL AS NUMERIC(10,2))))) + ',@I_vPRSTADCD=''' + …
0
In the following query, I order the output based upon Agency at the end.  The problem though is that one of the agencies in the list needs to always be listed at the bottom after all the other agencies are listed in alphabetical order.  Is there any way to accomplish that in SQL?

With CTE_Hours as (select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther, H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , R.AgeCurrent, R.CommunityCommittee, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , cast(H.ActivityDate as Date) ActivityDate, H.Fiscal from tblOrgHours H inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 0 and 99 Where 
 (H.Agency = 'Administrator' OR H.Agency = 'Chicago Area Project -')  And H.Fiscal = 2019 And H.ActivityDate >= '07/01/2018' And H.ActivityDate < '12/31/2018' And R.RegDate >= '07/01/2018' And R.RegDate < '12/31/2018') select  H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Hours ,count(A.ActivityName) over (partition by H.Agency,A.ActivityName) as [ActivityCount] ,Count(H.RegID) as [# individuals] ,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther ,COUNT(CASE when R.CommunityCommittee = '1' then 1 end) as [CommunityCommittee]
,COUNT(CASE when 

Open in new window

0
Hello Experts

I'm trying to merge the attached tables using the following SQL query:

D_LC_PRODUCTTYPEHIST as T1 Right Join D_LC_PRODUCT as T2 
       on T1.COUNTRY_ID=T2.COUNTRY_ID AND T1.PK_PRODUCT=T2.PK_PRODUCT) AS A 
       LEFT JOIN D_LC_PRODUCTTYPE AS T3 ON A.COUNTRY_ID=T3.COUNTRY_ID AND A.PK_PRODUCT=T3.PK_PRODUCT) AS B 
       LEFT JOIN D_LC_PRODGROUP AS T4 ON B.COUNTRY_ID=T4.COUNTRY_ID AND B.FK_PRODUCTGROUP=T4.PK_PRODUCTGROUP AND B.AK_PRODGIFT=T4.AK_PRODGIFT) AS C 
       LEFT JOIN D_LC_PRODGIFT AS T5 ON C.COUNTRY_ID=T5.COUNTRY_ID AND C.FK_PRODUCTGROUP=T5.FK_PRODUCTGROUP AND C.AK_PRODGIFT=T5.AK_PRODGIFT

Open in new window

However, I keep on getting error here
D_LC_PRODUCTTYPEHIST as T1 Right Join D_LC_PRODUCT as T2

Open in new window

wrong syntax

Can someone please let me know where I'm going wrong?

Thanks
LCMS_MRD_Delta_ProductTypeHist.csv
LCMS_MRD_Full_Product.csv
0
Hi, Just dropping this question again with some more detail to see if i can get a work around.

I am in the process of converting a large number of Crystal Reports to SSRS. I have hit a snag with a number of Running Total issues. we have over 25 reports that need to been converted but they all have a running total as a grand total, typically evaluating on group, outside of the group. In some cases these running values are used in other calculations. It is a nightmare.

As you can imagine I can not get this to work as if I write a running value expression on group it is outside of the group scope and does not work, and if i set the scope to "dataset1" the value is not correct as it needs to evaluate on group.

I have read through many links but i can't see how they will work, but i might just have a skill gap, none of them cover where the running total evaluating on group are used as a total only.

I have attached an example of one of the reports that has a running total, it is only one of the possible layouts and ways the previous reports used the running totals. but does highlight the issue i am hitting. If possible i would rather do this within the ssrs report but i am at a loss on how to get it to work.

We are on SQL 2014 but are about to migrate to 2016

Any ideas on a way to get this to work?

Example-of-Running-total-in-Report.JPG
0
I have a bash shell script that I want to call a Oracle DB stored procedure with (procedure is in a package with public interface), I am running it on Cygwin:

#!/bin/bash

if [[ $1 = '' ]]; then

  echo
  echo "usage : copy_client_div.sh <master conect string> "
  exit 0
fi

test_db1=`$ORACLE_HOME/bin/sqlplus -s $1 << EOF
set heading off
set pages 0
set feedback off
select 999 from dual;

EOF`

test_db=`echo $test_db1`
echo test_db: ${test_db::-1}
echo ${test_db}


if [[ ${test_db::-1} != 999 ]]; then

  echo "Can NOT connect to database with the given connect string"
  echo
  exit 0

fi

CONNECT_STRING=$1

`$ORACLE_HOME/bin/sqlplus -s $CONNECT_STRING << EOF > /c/temp2/log.txt

set heading off
set pages 0
set feedback off

begin
      --GRANT EXECUTE ON COPY_STUDIES.ins_trial2 TO tsm10;
      execute PackageName.procedure_name(name=>'trialname',client_div_source=>'source1',client_div_target=>'target1',ftuser=>'user1');
      commit;
      exit;
end;
EOF
`

If I run the procedure by itself in a sql script, it works.  In the bash script, nothing happens and no error gets returned.  Can someone tell me what I am missing?  Thanks.
0
We are experiencing the Oracle12c - ORA-01792: maximum number of columns in a table or view is 1000 after an upgrade from 11g to 12c which involves a MINUS query. The individual queries execute without the error.
In other words lets say the query is SELECT A, B FROM HN1 MINUS SELECT A, B FROM HN2
The statement SELECT A, B FROM HN1 executes without error. Likewise SELECT A, B FROM HN2.
However when they are combine the above error shows. There are about 18 columns in the query and when Oracle does internal query processing creating inline temporary tables it may strike the 1000 limit. At the moment I will not reduce the amount of columns in the query. Is there an Oracle hint I can use to make it function or any other strategy.
0
Looking for SQL help to find clients who are not recent customers.
Here is the code - which nicely returns all entries made (with details) where the event is more than 6 months from today.
What I need to do is EXCLUDE any of those ClientID who have a record within the last 6 months.
As an extra layer I would also like it to return that client only once - at the moment it returns every instance of that client in the record.
So the end view would show a list of clients who have made entries, but not in the last 6 months - and only one instance of them in the list.
SELECT
dbo.tblShow.ShowStartDate,
dbo.tblEntry.EntryResultsMark,
dbo.tblClient.ClientSurName,
dbo.tblClient.ClientEmail,
dbo.tblClient.ClientForeName
FROM
dbo.tblClass
JOIN dbo.tblEntry
ON dbo.tblClass.ClassID = dbo.tblEntry.ClassID 
JOIN dbo.tblShow
ON dbo.tblShow.ShowID = dbo.tblClass.ShowID 
JOIN dbo.tblClient
ON dbo.tblClient.ClientID = dbo.tblEntry.RiderID
WHERE NOT
dbo.tblShow.ShowStartDate > DATEADD(month, -6, GETDATE()) AND [dbo].[tblEntry].[EntryResultsMark] is not NULL

Open in new window

Many thanks in advance
0
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Hi Experts,

I have a SQL view that is being linked to my Access app.
That view has a column named SNVNum, defined as follows
CAST(REPLACE(dbo.Skilled_Nursing_Visit_Note.SNV_ID, 'snv', '') AS int)

Open in new window

Now when I open the view and sort by that column it works perfectly fine in db container.
it also works fine when running the following in SSMS.
Select * from vw_SkilledVisitNotesFilterFrm Order by SNVNum

Open in new window

However when I assign the form the following SQL as record source.
        Me.RecordSource = "Select * from vw_SkilledVisitNotesFilterFrm  Order by SNVNum"

Open in new window

it gives me the error attached.
Untitled.png
0
WE have moved WSUS from 2008 to 2012 R2 server, I gave same name and IP address to new server, have installed the roles of WSUS and IIS, migrated the DB, for which it keeps showing its restoring (Screenshot attached) because DB size is 15 GB and free version of WSUS is 10 GB.
On the console of WSUS it doesn't show any updates (Screenshot attached).
So moving forward, should I install a full version of SQL Standard?
anything else I have to do to start see computers under WSUS Console?
thanks.
WSUS.JPG
DB.JPG
0
I have a BAK file from an MS SQL database, and I want to access the data in it. I have access to AZURE and have created an instance of a MS SQL database, but I can't figure out what to do next. I have read that one cannot restore a BAK file to AZURE, and instead one should restore it to a local database - but I don't know how to do that.

I have MS SQL SMS installed on my Windows 10 computer.
I have an azure account and already created my empty database, but I could delete it and create a new one.
I have the 8mb BAK file on my computer.

Thank you for your help.
0
I just discovered the SQLSERVER Module for Powershell and would like to use the.
get-ChildItem2 -Path w:\ -File -Recurse | Select  DirectoryName,Name,CreationTime,LastWriteTime,LastAccessTime,Extension,Length,Mode | Write-SqlTableData -ServerInstance 'sandboxhost' -DatabaseName 'NETAPP_INVENTORY' -SchemaName dbo -TableName 'files' -force

Open in new window

to write to a table.  The data structure is 11 TB with over 100 million files.  

From my initial testing of small directory structures it works well.  My fear is that the system will not be able to hold all the data before writing to the SQL server.  Is there a way to chunk this up or make it more efficient?

Here is what I am doing to help or not:

$Dir = 'W:\'
$DirLists = (Get-ChildItem2 -path $dir -Directory).FullName

Foreach ($dirlist in $DirLists)
{
Write-Host (Get-Date)
Write-Host $dirlist
get-ChildItem2 -Path $dirlist -File -Recurse  | Select  DirectoryName,Name,CreationTime,LastWriteTime,LastAccessTime,Extension,Length,Mode | Write-SqlTableData -ServerInstance 'sandboxhost' -DatabaseName 'NETAPP_INVENTORY' -SchemaName dbo -TableName 'DUMP' -force
Write-Host
}

Open in new window

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

Query Syntax

52K

Solutions

20K

Contributors

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.