[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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 created a scalar function to encrypt the plaintext CreditCardNumber using this SQL function with a strong phrase and varbinary "salt" of tableid and recordid.  The server is also encrypted and physically secure...the passphrase is stored in a text file that requires admin user account to access and must be read each time the function is executed.  

The decrypt function that makes it to front end UI is masked with only the last 4 showing.

Is this enough to be compliant?
0
The 7 Worst Nightmares of a Sysadmin
LVL 1
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Hello Experts,
                         We are attempting to disable TLS 1.0 and 1.1 in our servers. When we did that, our classic asp application did not work and we found that it is using SQL SERVER driver with SQLSRV32.DLL. It threw SSL Security error.
 
This is the actual connection string we use for Classic asp:
DRIVER={SQL Server};SERVER=XXXXXXXX;DATABASE=XXXXXXX;uid=XXXXXX; pwd=XXXXXXX
 
We changed the driver to the SQL SERVER NATIVE CLIENT 11.0. and it solved the problem. Everything else worked except for one page, the monthly calendar page. In the page, we generate the whole HTML for the page data in the database base table and append it directly on the .asp page using response.write.  The page does not throw any error, it simply does not load the data that we get from Database using a Stored Procedure.
 
When this did not work we used AJAX to get the html data. Even this did not work.
 
We tried saving HTML data in a variable which also did not happen.
 

We tried adding Response.ContentType = "text/html"
https://msdn.microsoft.com/en-us/library/ms524327(v=vs.90).aspx
 
we installed Microsoft® OLE DB Driver 18 for SQL Server with SQL Server Native Client 11 mentioned as Provider in the connection string. we got other errors.
 
 Is there any restriction with TLS older versions when we get the HTML directly and append it in a page?
 
 
0
I have read turning on auto shrink on databases in SQL Server is a bad idea as it can cause 'fragmentation'. What is your view on this - and from a tech free risk assesment what may fragmentation cause in terms of performance or any other visisble symptom to  a database and its over arching application. Is there any official guidance from microsoft on the risk vs reward of turning on auto shrink on databases.
0
I don't know if there's another way of doing this besides what I have in mind and I'll note it below...

Some users have access to all Scheds and some users only to specific ones. When FilterSched is false, it means user has access to everything and has to get all the rows. If FilterSched is true, means user has to get all the rows.

I'll go step by step with an example.

1. I create the temp tables
CREATE TABLE #Main -- main table that has all the rows
(
  SchedID int
)

CREATE TABLE #Authuser -- all the users
(
  ID INT,
  FirstName VARCHAR(100),
  FilterSched bit
)

CREATE TABLE #AuthuserSched -- users and the scheds they have access to
(
  ID INT,
  SchedID int
)

 CREATE TABLE #FilterScheds -- holds list of scheudles user has access to
 (
   SchedId int
 )

Open in new window


2. Populate tables

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 88, 'Edward', 1 -- has filter and will get data with the INNER JOIN I have because #FilterScheds table holds whatever Sched this user has access to

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 14307, 'Camillia', 0 -- doesn't have filter so this user gets all the data (*** but that INNER Join is going to fail because #FilterScheds doesn't get populated)

----

INSERT INTO #Main -- all my data
(
    SchedId
)
SELECT 9999

INSERT INTO #Main
(
    SchedId
)
SELECT 555

INSERT INTO #Main
(
    SchedId
)
SELECT 4567

---

INSERT INTO #AuthuserSched -- user 88 has access to Sched 9999
 SELECT 88,9999

Open in new window


4. Now I join to get the data for user (I have this in a stored proc )

 --1. see if this user has a filter
 DECLARE @filter BIT
 DECLARE @userId INT = 88 --- run it for user 88 first, then user 14307
 
 SELECT @filter = filtersched FROM #Authuser
 WHERE id = @userId

 -- 2. now that user has filter, get their Scheds
 IF @filter = 1 --user 88 needs filtering
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
  
  ---3. now get the results. I have all Scheds user has access to. So, I'm doing an inner join

  SELECT * FROM #Main
    INNER JOIN #FilterScheds ON #FilterScheds.SchedId = #Main.SchedID --ISSUE is here --*** for user 88, I only need what Scheds he has access to. For user 14307...users that don't have filter...all the rows should come back
	                                                                      -- 
  
  TRUNCATE TABLE #FilterScheds -- clear the table and run it again for user 14307

Open in new window


My solution is to do this. A better way of doing this?

IF @filter = 1
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
else
 begin
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #Main --- get all the scheds that MAIN has for users that have access to everything. So, #FilterScheds is always populated
end 

Open in new window

0
I have an SSIS project that I created in Visual Studio.  It includes two packages.  One package exports data from SQL tables into an Excel spreadsheet.  The other iimports data from an Excel spreadsheet into a SQL table.  When I run it in Visual Studio on my development machine, both packages work properly.  But when I deploy it to another server as an Integration Services Catalog SSISDB package, the export from SQL to Excel works, but the import from Excel to SQL fails with the following message:

"SSIS Error Code. DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager 'SQLSERVER' failed with error code 0xC0202009."

Then a second error pops up:

"An OLE DB record is available. Source: 'Microsoft SQL Server Native Client 11.0'. Hresult: 0x80004005. Description: 'Named Pipes Provider. Could not open a connection to SQL Server [53]."

Both packages use the same connection manager (called SQLSERVER).  I made sure that the Run64BitRuntime setting is set to False and DelayValidation is set to True.

Again, the export from SQL to Excel runs without error.  It is the import from Excel to SQL that is throwing the errors.

T
0
I just upgraded a series of SSRS reports from SQL 2014 to SQL 2017. For some reason on two of these reports SSRS is inserting page breaks in between the groupings. There are no breaks and there were none when displayed using SQL 2014. What can I can examine that would explain this? I am using VS 2017 Community to deploy the reports.
0
I am trying to use child rows datatables found at:  https://www.datatables.net/examples/api/row_details.html

I have a Controller that returns my SQL View as a List:

 public ActionResult List()
        {
            MyDB DB = new MyDB();

            List<vw_Report> data = (from x in DB.vw_Report
                                                    select x).ToList();

            data = (from x in vw_Report.GetRows()
                    select x).ToList();

            return View(data.ToList());
        }

My View follows the example provided:

    @model List<master.path.Models.vw_Report>
        @{
            ViewBag.menu = false;
        }

        <!DOCTYPE html>
        <html>
        <head>
            <meta name="viewport" content="width=device-width" />
        </head>

        <body>

            <h1 style="text-align:center">Report</h1>

            <div style="padding: 25px;">
                <table class="DTStyle" id="List">
                    <thead>
                        <tr>
                            <th>

                            </th>
                            <th>
                                Text Field 1
                            </th>
                            <th>
                                Text Field 2
                            </th>
                            <th>
                                Text Field 3
                            </th>
                            <th>
 …
0
SQL Server 2012 Database. I have a table with a Clob column (This Table was Loaded from Oracle DB via Net Change. The source Oracle table stores BLOB(PDF files) as CLOB for ease of query).

I have been trying to convert the CLOB column back to PDF.

An Example: -----Begin Example-----------------------

%PDF-1.5 %ýýýý 1 0 obj <>>> endobj 2 0 obj <> endobj 3 0 obj <>/XObject<>/ProcSet[/PDF/Text/ImageB/ImageC/ImageI] >>/MediaBox[ 0 0 595.32 841.92] /Contents 4 0 R/Group<>/Tabs/S/StructParents 0>> endobj 4 0 obj <> stream xýýX[oý6 ~,ýEýývýýý ,ýý <9ý'ý)ýý ýT ýýyý ýýy6ýl/ýSý"ýrýýýýýýýýýýýýýýýýýýý/ýýýýýý ýýýr?ýýý ýýýý*ýýrT\ýýý0%" )ýý %jýoýýz!ýW ýýýý ýý7ýiý3ý)ý ýDý0G wý ýHFýbý(ýýRX ýý\ýý yPýýý! VA ýýýCý ýGýQx ý'ýCqýý b"ýýV94ý?ýNýý ýý ýýý3>ý 8týxý ýCý #ý ýqý ýýrý] ýCýýý8 ýBSýCýW cýRýýa ý 'ý 0Sý< ýaývWýý 3ý:ý9ý6ýýjý ýmxýo#ýYý2ýýz ýqýmýW ýýV ýýý)ýýýnoýýýýýý ýýýpýýý ýýn _ýN6 ýnýý ;ýýH ý#ýý nO$Qýý-ýý}ýýHýpýý= ný`ý^yýpýýý , 'ý Frý3ýý# ýýýSýbý1GýCýýýýý ýýýýý2aý ýýýýýýpýýýýý ýýg"ýýýýsýýnvýZýýýý ý&aýýýý ý DýýGýýý ýýýýýý[Lý%G bý/- ý:Psý/ > stream xýý} Týýýýý5ý9ýýýýýýýýýZ{ý=ý

-----End Example-----------------------

I know Adobe should be able to interprete the code(example abve): http://lotabout.me/orgwiki/pdf.html

Is there a way to convert the above example back to PDF using anything possible, Adobe tool, any other tool, Structured Query Language(SQL) etc?

Kindly assist.
0
Hello Experts

Can some please take a look at the attached table and help me write a SQL query that will

a.      Group by 'FK_DATE' column and order.
b.      For each value of FK_DATE, lookup on a static table identify the Date_Range_ID
c.      Append the records for that date to a <Date_Range_ID> file.

I hope this makes sense to you guys

Thanks
testsql.xlsx
0
Hi Experts,

We have data in Caspio that we are trying to export.
When we export them to Excel or CSV they work.
However when we export them into Access, some records fail.
This is what their support team responded...
“Despite of Microsoft's declaration that record row in MS Access is limited to 4000 symbols excluding Text64000 fields, this is not exactly true. If you download attached package, import it and open table in datasheet then add at least 1 symbol into TestColumn3 field (which is Text64k) you will not be able to download table in Access format anymore. Without this symbol it will work fine. In addition metadata of table is also present in some way in the record row so if you add any new column into attached table you won't be able to download it either. There is nothing developers can do with it as it's a Microsoft bug and beyond our reach. If you move the database to SQL there will not be any issues.”

Have you had any experience like that?

What do you suggest I do in order
1- figure out what is causing those records to fail.
2- fix/avoid it in the future.

Thanks
0
Making Bulk Changes to Active Directory
LVL 8
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

I need to get Microsoft SQL Server Mgmt Studio to connect to an Oracle database. I am having issues w/ getting the Oracle data access component -    OraOLEDB.Oracle available and showing on the SQL client under Linked Servers and Providers. Ticket-Details.docx
0
From time to time I see lines of code containing ADODB and connection strings.

Usually people just copy/modify code they find to achieve results, but someone had to originally write the code. I am wondering what the best resources are for me to learn all about this so that I can write my own from scratch?

For example, in the code at the link below, someone had to originally know that Provider, Extended Properties, Data Source, HDR and FMT existed and their proper syntax for their code to work. How did they figure that out?


 https://stackoverflow.com/questions/22947425/using-ado-to-query-text-files-terrible-performance
0
Hi Experts,

I have the following query that compares two lists (in different formats) and gives the unmatched records.

SELECT   distinct v.*,replace(convert(varchar(5),V.Schedule_ScheduleStartTime,108)+'-'+convert(varchar(5),V.Schedule_ScheduleEndTime,108),':','')
FROM V_TestHHASchedule AS V
WHERE   not Exists (SELECT NULL FROM [VisitReport] R 

                       where 
                       replace(RTRIM( LEFT( r.PatientName, CHARINDEX( ' (', r.PatientName + ' (' ) ) ),' ','') = replace(isnull(Patients_LastName,'') + isnull(Patients_MiddleName,'') + isnull(Patients_FirstName,''),' ','')
                       and replace(RTRIM( LEFT( r.aideName, CHARINDEX( ' (', r.aideName + ' (' ) ) ),' ','') = replace(isnull(Caregivers_LastName ,'') + isnull(Caregivers_MiddleName ,'') + isnull(Caregivers_FirstName,''),' ','')

                        and datediff(d,V.Schedule_VisitDate,R.visitdate) = 0

                        and replace(convert(varchar(5),V.Schedule_ScheduleStartTime,108)+'-'+convert(varchar(5),V.Schedule_ScheduleEndTime,108),':','') = R.scheduled
                       
                      
                      

)
and v.caregivers_lastname <>''
order by v.patients_lastname,v.patients_firstname, v.schedule_visitdate ;

Open in new window


I have some records showing up that cant figure out why.

Looking for some tips...

Here is an explanation of what the query supposed to be doing...
https://www.experts-exchange.com/questions/29126893/Query-comparing-two-tables-with-different-format-needed.html?headerLink=workspace_answered_questions

Thanks
0
UPDATE:  Added the 'which' columns to the query.

Database comparison of two tables.

I'm looking for a query that will compare two databases with identical schemas.  Here is a simple query that I've written:

select *
from
(
  select 'db1' as which, a, b, c
  from db1.tbl where attrib = 'test'
UNION ALL
  select 'db2' as which, a, b, c
  from db2.tbl where attrib = 'test'
)
group by a, b, c
having count(*) = 1
order by a, b, c

Open in new window


This will show the row differences but does not tell me which database a row as added to (db1 or db2). [UPDATE: added which so I believe I can see which db row was added to]
It will show if two rows differ but does not tell me specifically which column data was changed.
Also, when a row is delete from one of the dbs, it does not tell which db is was delete from (db1 or db2). [UPDATE: added which so I believe I can see which db row was delete from]

And the above query be amended support the three items?  I'm using SQLite (an older version) so I don't have the flexibility of stored procedures or functions.  Another possibility is to solve this using JavaScript.  I'm aiming for short and simple.
0
Hi
We have a production system which is mysteriously having lots of rows deleted from one table
We think its query somewhere but we cannot find it

I was thinking of putting a delete trigger on the table to try and find out the query that was run, if we see that and any other info we should be able to track down the issue (ideally SP name)

i've also looked at extended events, but it looks a little fiddly and we cannot afford for it to not work the next time this happens, we need to simply find the offending item and fix it urgently rather than tweaking stuff (thats why i figured a trigger would definitely catch it)

However i'm unsure what to write in the trigger to get the user/query/sp of the delete
any pointers or code appreciated

sql server 2016 enterprise
0
Thank you in advance,
I'd like to know if it is possible to upgrade the MS SQL server independent of a Windows Server version.
I have a legacy client with Windows Server 2008 R2. They of course have their SQL embedded as a 2008 R2 version of SQL as well.
This is a difficult client, and just for grins I'd like to know if SQL can be upgraded separately from the OS.
Thank you for your help regarding this.
Sam
0
Hello Experts:

Hope you can help me with an issue I am having.  I am developing in MS Access 2010, which I then export the various objects to Access 2016 (we're doing this because of TFS integration).  Additionally, I've created a view in SQL Server with an index With SchemaBinding so that I could use it as the recordsource to a form, which would allow me to update the underlying table from the view.  Initially, I was having an issue where the recordsource would be blank in my dev environment but have since resolved that issue.  So I export my objects, relink to my test db and try to run the form.  in my test env (Access 2016) the form is coming up blank.  If I remove the index, I  get the records as expected.  But if I try to add the index back, again, a blank form.  Would anyone know why this is occurring?  More importantly, the fix?  Is there a way to create the index in Access instead?

BTW: If I link my deve envir to the test db, it works, so I'm confident it's not a sql server issue (or at least I think I ruled that out)

Many thanks!

Juan
0
Sort a dropdown menu alphabetically in SSMS?

Please see attached screenshot. I'm wondering if it's possible to add code that would sort?
Or add another line with sorting command?screenshot
0
In Windows 2012 R2 with SQL 2014, I would like to setup traffic over 1433 to use IPSEC. I have ready multiple article on the setup by need clarification on the following.

1) Are certificates on both servers required
2) Is just setting the Firewall rule to access connections over IPSEC the only thing that is needed?
0
Turn Raw Data into a Real Career
Turn Raw Data into a Real Career

There’s a growing demand for qualified analysts who can make sense of Big Data. With an MS in Data Analytics, you can become the data mining, management, mapping, and munging expert that today’s leading corporations desperately need.

I am trying to print labels using a DYMO Developer SDK it uses javascripts here is my files PrintMeThatLabel2.js

I got most of it working with the help for you all. I have a from the user query sql then save the info to a json then the PrintMeThatLabel2.js get that info and prints the label. If the query get 10 recorded it prints 10 labels Great!


But if I want to print 1 label out of the 10 I don't know how to push the 1 json to the PrintMeThatLabel2.js for printing.


I got most of it working so when I press Print me button it reloads the json array with just 1 recorded then I have to click the big print button at the top to print just that 1 recorded.

If the user then want to print them ALL 10 he/she will have to query the db again for it to load all the recorded in to the json array then click the big print button to run PrintMeThatLabel2.js and print.

I am trying to not have the user rerun the page to print all label ones the just pick one of the record to print.


so 2 buttons 1 prints all the recorded and 1 prints just the one he/she picked.

Most of you will recognize this code

<html>
<head>
<title>Search</title>

<link rel="stylesheet" type="text/css" href="PrintMeThatLabel.css" />
<script src = "cookie.js" type="text/javascript"> </script>

<script src="https://code.jquery.com/jquery-3.1.0.js"></script>

<script src = "http://www.labelwriter.com/software/dls/sdk/js/DYMO.Label.Framework.3.0.js" type="text/javascript" charset="UTF-8">

Open in new window

0
Sql server 2008r2

Can anyone point me to a good example of
importing(insert) a pipe delimited text file in to a table.

Not using the import wizard please.
and i cant use Bulk insert
approx 12 million records

Thanks
fordraiderds
0
Hi Expert,

Could anybody please guide here how to user Oracle AQ functionality using Oracle provided pkg,  I don't know exactly which package has this functionality.

You suggestion is really appreciate.

Thanks,
Mihir
0
Hi

I am facing issue for logging row count in txt file. I have to log row count with date time in txt file, but below query is logging only date time not row count.

Below select query will return 0 row count.

select count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

Open in new window


Below query is not logging row count 0 in txt file. Please help me to find the root cause of this problem. I have to schedule job to run every 30 minutes using below query, the query output need to append (concat) single file as showed in the attached file
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Open in new window


I am herewith attaching Count_Final.txt
Count_Final.txt
0
I'm currently migrating a Microsoft Access client with a Microsoft Access backend to an Azure SQL backend.  I have a fully working version of the client, but that was before we implemented security with Application Roles.  In order to ensure the users are getting an elevated connection I've been slowly rewriting the client to use ADO recordsets with the SQL in the VBA rather than using access queries tied to linked views/tables and pass-through queries.  This has brought up several questions, but let me go ahead and just ask 2 for now and i can put the rest in another thread as they may not be fully related to this question (plus they're the most important).

Simple question first:
1) I am creating ADO recordsets that select views when forms load to tie them to the Form.recordset property.  I am using a client side cursor (Location) with adOpenDynamic (Type) and adLockOptimistic (LockType).  When I do this the forms are no longer editable.  I've confirmed in VBA that the recordset.iseditable property is true for the SQL statement.  In most cases the SQL statement is as simple as "SELECT * FROM <view name>".  The view is editable in SQL Server Management Studio, and the linked view is editable if I'm using an admin account that I don't need to elevate via application role so I know it's not an issue with joins.  Is there some other curor/recordset setting I'm missing to ensure that when I bind an ADO recordset to a form it remains editable?

2)  I also have a more …
0
Hi,
I have to create the following table in postgreSQL :
create table rbl_kyc_profile (
  id int8 not null,
  userid varchar(50) not null,
  kyctype enum ('PAPER','E_KYC','MINIMAL','SHORTFALL')
)

Open in new window


I got an error : SQL Error [42704]: ERROR: type "enum" does not exist
 
I saw on postgreSQL documentation page : https://www.postgresql.org/docs/9.2/datatype-enum.html
That one first needs to create an enum type in postgreSQL.
This is very different from MYSQL where you can just create a column of enum type by specifying the values...
I have the following questions..

1) Since the kycType in my java classes is an enum. And it has a fixed set of values should i create an ENUM in postgre for it or just create a varchar for it ?
2) Because postgreSQL uses enum types in a different way than MySQL does what impact it has if i create the above kycType as an enum type and then use it in the table ?

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