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'm using SSIS 2008.

I have 2 ssis packages.
QUESTION:
1. can packageA call packageB ?   How (please show example code)?
2. Will packageA task that executes packageB wait until packageB is completed before packageA goes on to process a subsequent task?

Thanks.
0
10 Tips to Protect Your Business from Ransomware
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Hi.
I have a weird issue running a SQL proc that returns a picture as a blob.

My laptop, LAP1, is on our corporate domain "CORP", and I am a domain user "CORP\bgood".
On that domain is a MS SQL Server "SQL1".   SQL1 runs a database DATA1.
I also have a SQL Login on SQL1 called "good_b".

There are picture files on a domain server FILE1, which LAP1 and SQL1 seem to be able to access, using File Explorer from each machine.  Both have the the picture folder mapped as "L".

From LAP1, I connect to DATA1 using SQL Server Management Studio with Windows Authentication (CORP\bgood).  Executing the procedure (similar to code below) returns a row but the Pic1Blob column is NULL.   HOWEVER -- Using  SQL SERVER authentication (good_b), the row returns nicely with the picture in Pic1Blob!

This seems backwards to me -- I would have expected Windows Authentication to work since the Windows Domain user bgood has access to the domain files.

ALSO -- If I log on to the actual SQL1 machine as "CORP\bgood" and connect to DATA1 using Windows Authentication, I can run the procedure fine, with data in the Pic1Blob column.  So, I am thinking there may be some sort of NT SERVICE or NT AUTHORITY permissions issue.

I hope I've described this right.  Any suggestions?     I can give you any more information you may need.
Thanks!
Rob

BEGIN TRY	
	CREATE TABLE #Temp1(Pic1Blob VarBinary(max))

	DECLARE @PathToPhoto varhar(50) = 'L:\Images\MyFace.jpg'
	DECLARE @SQL 

Open in new window

0
What's the difference between Oracle GoldenGate and Oracle ODI? Which one is the ETL tool?

Why two tools? (Their product descriptions are fuzzy enough that it's hard for the layman to tell the difference).

if the two tools are appropriate, isn't that a big learning curve to learn two separate (albeit complementary) tools?

It seems to me GoldenGate is a "data replication tool", layman's view: it moves data from point A to point B.
- but it also says it does "transformations", so doesn't that translate to an E-T-L tool?

The ODI product home page doesn't say anything about transformation.

This is a good article but still my understanding on the differences is fuzzy ...

related article ...

===== background =====

I'm a data warehouse consumer and designer of a relational d.b. data warehouse. Never got into the multi-dimensional or star schema stuff.

So I'm not building the infrastructure for the data warehouse, but I might write the ETL specs. In other words, I know the data and know my reporting requirements, with a strong SQL knowledge and understanding of the business.

But I need to know some of the tools to best understand the environment and work with the builders of the data warehouse.
0
Hello. I have modified a  SQL Server query that I got from a friend to output the total execution for each process. It outputs the results as 00:00:00 and the datatype is varchar. I want to combine all the results and get an average. However, I cannot figure out how to convert it from varchar to decimal so I can get the average. Any assistance is appreciated.

Current Results
Total
00:52:34
01:31:33
0:15:27

Desired Results
Total
00:53:11

Query
;WITH CTETotal AS (SELECT CASE  
	WHEN DATEDIFF(dd, tsstart.Time, tslast.Time) > 0 THEN 
	CAST(DATEDIFF(dd, tsstart.Time, tslast.Time) AS varchar(2)) + 'd ' 
	ELSE '' 
	END + CONVERT(varchar(8), tslast.Time - tsstart.Time, 108) Total
FROM 
( 
	SELECT 
	MAX(ExecutionTime) AS Time, 
	AdvertisementID, 
	ResourceID 
	FROM v_TaskExecutionStatus 
	WHERE LastStatusMessageID = 11140 AND Step = 0 and
	ExecutionTime  > DATEADD(month, -1,GETDATE())
	GROUP BY AdvertisementID, ResourceID 
) tsstart  
INNER JOIN 
( 
	SELECT 
	AdvertisementID, 
	ResourceID, 
	LastStatusTime as Time 
	FROM v_ClientAdvertisementStatus
	WHERE LastState = 13 AND
	AdvertisementID = 'MXXXXX'
) tslast ON tsstart.AdvertisementID = tslast.AdvertisementID AND 
tsstart.ResourceID = tslast.ResourceID AND 
tslast.Time > tsstart.Time)
SELECT Total
FROM CTETotal

Open in new window

1
Failure on SQL Statement:  Too Few Parameters expected 1  

Dim strSQL As String
strSQL = "UPDATE UserActivityLog SET UserActivityLog.Activity2 = ""LogOff"" AND UserActivityLog.LogOffTimeStamp=Now()" & vbCrLf & _
"WHERE (((UserActivityLog.Activity2) Is Null) AND ((UserActivityLog.UserName)=[Forms]![frmLogin]![Name1]))"


CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
i recently had a backup job go dormant for a few weeks and didn't notice the problem soon enough.  is there a strategy that someone might point me to that is not overly complex, but its pretty rock solid as far as making sure your backups are working properly.

all the backup jobs and maintenance are running through ssms maintenance plans.

i have 3 maintenance plan set ups.
Backup full
Backup differential
and one called "maintenance" - this one cleans out old back up files

on the email notifications on the sql agent job... question.
Look at attachment 1 for reference.  I have this set to "when the job completes".  does this mean it will fire off an email when the job succeeds or fails?

2 follow up questions.

i want to test this job to see what it does when it fails.  i see what it does when it completes successfully.  is there an easy way to fire this backup job off and make it fail... a certain setting i should try or something like that.

follow up question 2.  i would like to be immediately notified if the sql agent service turns off... best way for this?
Attachment1.JPG
0
One of my clients is a single server environment, Windows server 2016 Essentials, running SQL Server (Express) 2014.  

They are running Thompson Reuters PracticeCS with the database hosted on this server.

Until about 4 weeks ago, things were running along normally.  Then, logins started failing until a variable time between 8:00 am and 8:30 am.  Once the logins start working, they work for everyone the reset of the day.  

Backups run at 12:00 am and finish within minutes.  The database is "Always on".

I have contacted the vendor and they claim that it is a SQL problem and refuse to help troubleshoot.

Any help is appreciated.  Below are the recent logs.  At 8:37, users could log in.

Date      Source      Severity      Message
9/18/2018 8:37      spid53      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:31      spid20s      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:27      spid60      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:17      spid53      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:15      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:11      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 8:11      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.62]
9/18/2018 8:11      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: …
0
I have a table having fields as Country,Product,Years,Value. I want to subtract Values of two product having same Country and Years.

Below is a sample data:

ID      Country      Years      Product      Values
1      4                 2010      CO                50
2      4              2011      CO               200
3      4              2010      TO               50
4      4             2011      TO              122

Desire Result:
Country      Years      Product      Values
4             2010      CO-TO             0
4             2011       CO-TO         78
...
Thank you.
0
We've split our floor plan project in 2 parts:

1. FP Builder
2. FP Viewer

For FP Builder we've used SVG Edit (https://github.com/SVG-Edit) and for FP Viewer we've used MAPPLIC FP Viewer (https://www.mapplic.com/plugin/docs/).

Currently in FP Builder when we do changes it gets saved in svg file. And we load the same svg in Viewer. We need to save svg shapes in database and retrieve svg from database. E.g. If there are 10 stands present in our floor plan there should be 10 database rows in database table. So that we have connectivity between our FP Builder and database.

And we need to add split and merge stand functionality for stands.
0
MSSQL count differences by a group of items.

I have a SQL trigger that captures adds, changes and deletes that happen for items in a ERP table. There is a primary item number (TCC) and a secondary partner number (TCC_SE).

In some cases the partner number has been changed and I need to find which item numbers have a partner number that has changed.

I have attached a spreadsheet that has two item numbers with multiple entries and within each item number there is a secondary partner number that has been changed.

At this point I simply want to see the item number if there is a secondary partner number that has changed. (i.e. in the attached spreadsheet there is a primate item number of M4/6D2 which also  a secondary partner number of M4/6D2 and M46D2)

Hope this make sense

Thanks
Book3.xlsx
0
Managing Security Policy in a Changing Environment
Managing Security Policy in a Changing Environment

The enterprise network environment is evolving rapidly as companies extend their physical data centers to embrace cloud computing and software-defined networking. This new reality means that the challenge of managing the security policy is much more dynamic and complex.

I need to create a query that calculate the car average mileage  per day. I have the query but it is calculating the average per ride not per day. Because you may have multiple rides per the same day and tat will be the sum not the average for that day


SELECT carkey , TotalMilesDriven , ISNULL([Monday],0) [Monday] ,
			ISNULL([Tuesday],0)[Tuesday],ISNULL([Wednesday],0)[Wednesday],ISNULL([Thursday],0)[Thursday],ISNULL([Friday],0)[Friday]
			,ISNULL([Saturday],0)[Saturday],ISNULL([Sunday],0) [Sunday],carnumber,model,make FROM 
			(
				SELECT DISTINCT model,make,carnumber,carkey, _WEEKDAY , AVG(milesDriven) OVER() TotalMilesDriven, AVG(milesDriven) OVER (PARTITION BY _WEEKDAY) MilesPerCarWeekDay
				FROM 
				(	
					select CD.carkey,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY,C.carnumber,C.model,C.make
					from car_details CD
					inner join Car C on C.carKey = CD.carKey
					inner join Users U on U.userKey = CD.updateuser
					inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
					WHERE 1=1
					and CD.carKey = 32
					and SU.sessionStart BETWEEN '09/14/2018' AND '09/15/2018'
				)AS T
				WHERE T.milesDriven > 0
			)k
			PIVOT ( MAX(MilesPerCarWeekDay) FOR _WEEKDAY IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) )y1

Open in new window

0
Hi Experts,

I'm getting the following error when trying to import an Access table to SQL Server (2008).

- Copying to [dbo].[Skilled_Nursing_Visit_Note] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid date format".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with input column "Date_Of_Birth" (382) on input "Destination Input" (284). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (284)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (284)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Skilled_Nursing_Visit_Note" (271) failed with error code 0xC0209029 while processing input "Destination Input" (284). The identified

Open in new window

0
Hi,

Many thanks for taking a look at my question.

Given the below SQL query that works

;with cte as
(
      select tr.tr_rowid_debtor as lp_rowdebtor_id, tr.tr_posted_date,
      (0-tr.tr_to_agency+tr.tr_to_client) as amount,tr.tr_account,
      ROW_NUMBER() over (partition by tr.tr_rowid_debtor order by tr.tr_posted_date desc) idx
      from de_transaction as tr
    where tr.tr_account = 16
)
Select d.de_number, lp_rowdebtor_id, tr_posted_date, amount, tr_account 
from cte 
inner join debtor as d on d.de_rowid  = lp_rowdebtor_id
where idx = 1

Open in new window


I need it to do exactly what it does above but also - 1 additional item I need, is that somehow I need a count of the each transaction it finds per by "tr.tr_rowid_debtor" and only give back a result for the files that have multiple instances of where tr.tr_account = 16.

many thanks!!
0
Dear Experts,

 How do I know in Oracle SQL for specific table when ( the time ) it was last updated or changed ?

Thanks
0
I have written a procedure to update SSRS subscription schedules. The procedure appears to work because the values are updated in ReportServer.dbo.Schedule. However when I view the schedule through SSRS Report Manager, the dates are not updated.

The code I am using is below -

declare 
--@SubscriptionID varchar(100) = 'E65E6D4A-A631-4C17-A8DB-001FB0AD0855'
@Path varchar(max) = '/QTS Internal/RTM/Service Disruption Report'
, @SPLC varchar(max) = '123456'
, @State varchar(max) = 'VA,NC'
, @Country varchar(max) = NULL
, @Carrier varchar(max) = NULL
, @Location varchar(max) = NULL
, @CustomerID varchar(max) = '1'
, @Disruption varchar(max) = 'Hurricane Florence'
, @StartDate datetime = '20180914 04:19:00'
, @EndDate datetime = '20181002'

create table #subs (SubscriptionID varchar(200), [Path] varchar(200))

insert into #subs

select s.SubscriptionID, c.Path

from Catalog c
join subscriptions s on c.ItemID = s.Report_OID
where c.Path = @Path

-----------------------------------------------------update schedule dates

UPDATE ReportServer.dbo.Schedule
SET StartDate = @StartDate, EndDate = @EndDate
from Catalog c
LEFT OUTER JOIN Subscriptions s WITH (NOLOCK) ON s.Report_OID = c.ItemID
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] RS WITH (NOLOCK) ON s.[SubscriptionID] = RS.[SubscriptionID] 
LEFT OUTER JOIN ReportServer.dbo.[Schedule] SC WITH (NOLOCK) ON RS.[ScheduleID] = SC.[ScheduleID]
JOIN #subs subs on s.SubscriptionID = subs.SubscriptionID

Open in new window


Does anybody have experience with this?
0
Hi,
How to convert an Integer value to TeXt Value
using DAX
0
WITH DATA 
AS (
select CAST(enc_timestamp AS DATE) as [Date of Drug Administration], p.person_nbr AS [Medical Record], enc_nbr AS [Encounter Number],
'OP' as [Patient Type], pp.service_item_id as [Charge Code], pp.service_item_desc AS [Charge Description], pp.units AS [Dispensed Quantity],
pm.payer_name, cob from patient_encounter pe
JOIN person p ON p.person_id = pe.person_id
JOIN encounter_payer ep ON pe.person_id = ep.person_id AND pe.enc_id = ep.enc_id
JOIN payer_mstr pm ON pm.payer_id = pe.cob1_payer_id
JOIN patient_procedure pp ON pp.enc_id = pe.enc_id
where CAST(enc_timestamp AS DATE) >= dateadd(day,datediff(day,365,GETDATE()),0) 
and pe.billable_ind = 'Y' and pe.clinical_ind = 'Y' and pe.practice_id = '0001'
and pp.service_item_id IN ( 'J0585','J0696','J0702','J1030','J1040','J1050','J1071','J1100','J1200','J1650','J1885','J2060','J2175','J2270','J2300',
							'J2405','J2550','J2791','J2930','J3030','J3301','J3420','J3430','J3490G','J3490TH','J7298','J7613','J7620'
							)
and pp.amount <> 0.00 and pp.delete_ind = 'N')
select * 
from DATA d
	Pivot (MAX(payer_name)
			FOR cob IN ([1], [2], [3] )) as P;

Open in new window



This is working---however the payer name is the same in 1, 2 or 3 (depending on the patient's number of insurances).  For example, if a patient has Medicare and a supplement--it will at least recognize there are two entries (cob 1 and cob 2)--but instead of the supplemental name, it is supplying the same insurance name (Medicare) for both entries.  If the patient has 3 insurnaces, then it would have 3 Medicare names across 1, 2 and 3.
0
Hi,

I'd like to create charts based on selected listbox items & entered dates from a form.

The first graph works fine, but the second one isn't loading the correct data.

I think the problem is that the second table uses "ID" instead of "WorkerID" and the main form's record source

is based on a specific table.

I've tried to create relationships between tables  tWorker,tReg and tAbsence, but it did not recognize WorkerID in tReg.

Is there a solution to this?

Thank you.
Charts_14-09-2018.accdb
0
Hi Database Experts,

Scenario:
As part of code deployment, we also execute Database DML and DDL. Recently a cursor statement (select / update) badly affected our PPTE environment. Since we have less data in lower environment. we actually couldnt catch this. however it took 3+ hrs in PPTE.

Is there any automated way to check this poor coding when executing in lower environment please?
E.g
  1. Can we add custom sql (as part of post sql on every deployment) to generate profile or gather stats when executing in lower env? So we check this report every time
  2. Anything with non-sys or non-system user. Basically with the user account who executes SQLs.
  3. SonarQ profile
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.

Hi,

I want to fetch data from 3 table for time periods , i am using UNION All but its show error.
My sql code is
$sql_search = "(SELECT invoice_amount_wt_gst, cr_note, company, client, invoice_no, job_no FROM psb_billing WHERE invoice_date BETWEEN '$recv_date' AND '$invoice_end_date' and currency = '$currency' and company = $comp)
	UNION ALL
(SELECT on_ac, tds, client, job_no, invoice_no FROM receiving_report WHERE receiving_date BETWEEN '$recv_date' AND '$invoice_end_date' and currency = '$currency' and company = $comp)
	UNION ALL
(SELECT bf_amount, bf_credit_amount FROM psb_clients_bbf WHERE currency = '$currency' and bf_company = $comp)";	

Open in new window



i don't understand where i am wrong. and when i am using join query its show me lots of data. i think Its also fetch the wrong data.
my join query is
"SELECT PB.invoice_amount_wt_gst, PB.cr_note, PB.company, PB.client, PB.invoice_no, PB.job_no, PB.invoice_date, PB.currency, R.receiving_date, R.client, R.on_ac, R.tds, R.currency, BB.clients, BB.bf_amount, BB.bf_credit_amount, BB.currency, BB.bf_company FROM psb_billing PB left join receiving_report R on PB.company = R.company left join psb_clients_bbf BB on PB.client = BB.clients WHERE PB.invoice_date BETWEEN '$recv_date' AND '$invoice_end_date'  ";

Open in new window

 
i attached the table sql file.
I appreciate your's help.
Thank you
0
I have Write the DAX Function
See below
TotalDays = DATEDIFF([ChangeDate],TODAY(),DAY)
But same date giving 2 differents values
See attached Screenshot and see example
Change Date   TotalDays
12/09/2018     10
12/09/2018      2
Any idea appriciated
DAX-Issue.JPG
0
Hi, I have a MS SQL Server running in a data centre and I would like to move it into a new SQL Instance running on AWS EC2. I know i can take a backup of the DB and restore it to the AWS Instance but i was wondering if there were any other options for migrating the database to the new server. I know AWS have a database migration tool, just wondering if anyone had had experience using it ?
0
I have been tasked with creating some reports out of VS and SSRS from an Oracle 12c database.  I am an Oracle developer but I don't know much about VS or SSRS.  I have both VS 2015 (14.0.25431.01 Update 3) and 2017 (15.7.5) installed and I have installed ODTwithODAC for 12c.  I have also installed SSDT for both VS 2015 and VS 2017.  

Using VS 2017, i have created a new project but I am unable to create a shared data source.  As soon as I select Oracle Database in the Type drop-down, the Edit button next to the connection string becomes grayed out.  

Using VS 2015, I have cloned an existing project that works fine for my colleague.  However, when I first tried to load the project I received a message that the version of the report server project is not supported and the project must be upgraded.  If I say no, then the project won't load.  If I say yes, the project loads and everything seems to be ok. However, when I try to Preview an existing report from the project, I receive the following error:
"An error has occurred during local report processing.  An error has occurred during report processing.  An attempt has been been made to use a data extension 'ORACLE' that is either not registered for this report server or is not supported in this edition of Reporting Services."  There is nothing in the Error List when this happens.  

The only difference between my colleague's setup and mine is that he has SQL Server Reporting Services 14.0.806.134 and I have …
1
Hi,

How can convert date to yyyy-mm-dd, i tried some options in converter and nothing:

The following query works but i think the format is mm-dd-yyyy
select cod,ISNULL(datep,CONVERT(datetime, '03/09/2018 18:06:53',120)) as datep from Refmb
this code not work get error when converting, changing 03/09 to 13/09
select cod,ISNULL(datep,CONVERT(datetime, '13/09/2018 18:06:53',120)) as datep from Refmb

best regards
0
Im using sql server 5 and I want a query to loop through a table adding a value in each row to a counter and break when i reach a max value - eg TotalQty

eg table
'records'
name qty
lou 1
paul 2
alan 1
jess 3
.. loop through this table and stop when adding qty in each row = TotalQty

eg TotalQty = 4

should return counter = 3
 as it would have looped through rows 1 to 3
if eg TotalQty = 2 it should return counter = 2

pseudocode eg
select * from records
counter  = 0
recordcounter = 0

while counter <  TotalQty
 counter = counter + table.qty
recordcounter = recordcounter  + 1
wend

any ideas welcome
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.