8
There's a number of offerings on the app exchange for connecting SQL server to Salesforce. One of the well regarded ones is dbamp - there's full details of their offering at:
http://www.forceamp.com/
You could also achieve this via an ODBC driver - again there are a few providers - here's one example:
https://www.easysoft.com/support/kb/kb01050.html
I'...
5
The problem is that you already have status by select * (status was already present in the table). I would advise you either don't use the select * or change the alias of the case function e.g. Substatus.
If you target de has a primary key, we could speed this alot by Setting the Write Mode to Update and doing this:
SELECT
WhichEverColumnIsYourPrimaryKey
,...
5
I've used it earlier and it calculates itself based on the days in the month. So, it does not default to 30 days and adjusts based on the days in the month (I believe it handles leap year as well). You can run a quick test to verify it before you automate it.
Unfortunately, this is not very well documented in SFMC guides.
5
I think the differences between SQL and SOQL would limit the usefulness of SQL Server Management Studio. You would probably be better off with a tool that directly builds SOQL.
Some options include:
Workbench SOQL Query
SoqlXplorer
FuseIT SFDC Explorer (Disclosure - this is a free tool from my current employer)
If you really want to use SQL Server ...
4
Adding to the above answer a very good tool which is also open source is Talend that can connect sfdc and SQL servers
http://www.talend.com/products/data-integration
Talend provides outofbox connectors and drivers and you will need to just handle the mappings more like informatica on demand .Informatica is paid tool while basic version of Talend is open ...
4
You've got a curly quote in there: ‘MHAT_Email1'
SELECT
s.SubscriberKey
, ss.EmailAddress
, MIN(s.EventDate) as 'FirstClickDate'
, o.ACCT_NUM
, o.ACCT_NM
, o.BUYER_CONTACT_UID
, o.CONTACT_GUID
, o.FIRST_NM
, o.LAST_NM
, o.EMAIL_ADDR
, o.PHONE
FROM _Click s
JOIN _job j ON (j.JobID = s.JobID AND j.accountid = ...
4
Salesforce does not use SQL Server, so you can't just connect from SQL Server Management Studio. A quick Google search found at least one product that claims to allow you to connect from Management Studio: https://www.devart.com/news/2015/odbc-salesforce.html.
I've also used a product called DBAmp to connect to Salesforce from SQL Server, but I don't think ...
4
Date data types in SFMC are DateTime (or DateTime2) data types behind the scenes, so the time is always included. It's best to use AMPscript to format these, as you can't dictate the format stored in the data extension unless you store them as Text.
I generally just use the formatDate() function in AMPscript:
%%[
var @date_field
set @date_field = ...
3
No. Query Activities can only be Select statements.
Here is what's supported:
The SQL support for the Query Activity is based on SQL Server 2005
capabilities.
Only SELECT statements to data extension or data views in an account
or in the parent account
Nested Queries
UNION
JOIN
GROUP BY
Conditional Statements
IF ...
3
You can use Tracking Data Extracts and FTP them wherever you need.
The result of these extracts is a ZIP file that contains individual files for each of the options (e.g. Sent.txt, SendJobs.txt) for the date range you've configured. Whatever process on your side is consuming these files will need to unzip to a temporary directory and import.
Here are the ...
3
Boolean values can be represented with 1 or 0. Assuming the there's not always going to be a match in 4DaySend, I'd do something like this:
SELECT
M.EmailAddress EmailAddress
, M.PersonHasOptedOutOfEmail PersonHasOptedOutOfEmail
FROM MasterSubscriberDE M
LEFT JOIN [4DaySend] F ON (M.SubscriberKey = F.SubscriberKey)
WHERE m.PersonHasOptedOutOfEmail = 0
3
Typically if Query Activities fail, it's one of these 6 things:
Primary key violation -- your query results in duplicate rows not allowed by the primary key
Inserting a null value into a non-nullable field
Inserting a value too long for the field (truncation)
Timeout -- if your query doesn't complete within the 30-minute timeout window, it'll error out.
...
3
I've had passed success at using both Gaurav's ampscript approach and Arvind's SQL approach.
You can use Case statements to make Arvind's SQL query more fault tolerant.
SELECT
linkname
, CASE
WHEN CHARINDEX('-',linkname) > 0 THEN Substring(linkname,1,CHARINDEX('-', linkname)-1)
ELSE linkname
END as HEREISVALUE1
, CASE
WHEN CHARINDEX('-',...
3
Rather than handling this in the query itself, you can use the ampscript utility functions
IndexOf("Your String", "-") will give you the indices of the hyphens
Then use those indices with
Substring("Your String", "-") to get the 2 relevant sub-strings
Based on the example above
IndexOf("APPLE-ORANGES-52", "-") will return 6
SubString("APPLE-ORANGES-52", ...
3
I'm told that you can request Marketing Cloud Support to index specific fields in a specific Data Extension, which may help you here, or it may not.
The real issue is that Marketing Cloud simply isn't designed to process hundreds of millions of records. If it was, you wouldn't be running into this problem in the first place.
I have a client with 160 ...
3
If it's just summing column values of the records between two dates, here's a simpler approach:
select
t1.myDate
, t1.code
, sum(t2.record) record
from table1 t1
inner join table2 t2 on t1.code = t2.code
where t1.mydate >= convert(date, getDate()-1)
and t1.myDate < convert(date, getDate())
group by
t1.myDate
, t1.code
This just sums the t2.record ...
2
In SFMC, you can use an OUTER APPLY. That should work for your scenario:
SELECT
CASE de1.SurveyResponse
WHEN '' THEN 'N/A'
ELSE SurveyResponse
END AS SurveyResponse
, (CONVERT(DECIMAL(3, 1), COUNT(*) * 100.0 / (x.totalCount) )) AS ResponsePercentage
, x.totalCount AS TotalUnsubscribes
FROM DEName AS de1
OUTER APPLY (
SELECT
...
2
System Data Views have to be enabled by SFMC Support. There's no cost.
2
I would write your query like this. The IsUnique = 1 will limit it only unique opens by a subscriber for a specific send.
select
TriggeredSendCustomerKey
, subscriberkey
, jobid
, EventDate
from _Open
where
IsUnique = 1
and TriggeredSendCustomerKey in ('Trigger_Name','Trigger_Name_B')
The System Data View schemas can be found on this page on the SFMC ...
2
Late to the party with this answer but hope it helps others...
Salesforce - SQL integration has been a key part of my job for the last 9 years for both on premise backups to SQL of Salesforce and SSIS packages to run adhoc / scheduled uploads to and extracts from Salesforce via SQL / SSIS.
Relational Junction
...by Sesame Software has been very effective ...
2
Here is an article on how to create data extensions. This may help, but it is not that easy of a task to do what you are looking to do unless you are fairly technically savvy.
https://help.exacttarget.com/en/documentation/exacttarget/subscribers/data_extensions_for_exacttarget_marketing_cloud/creating_a_new_data_extension/
Keith
kkonetzni@nimblejack.com
2
SELECT Owner.Name FROM Account WHERE ...
Any time you've got a standard field which is a lookup, you can just remove 'Id' from the end and add '.Name' (or whatever other field you want). If you're dealing with a custom field (ending in __c), you can do the same with '__r.Name'
2
The error is kind of misleading, but you're missing a quote:
and opp.first_mkt_medium not in ('partner', inbound_outbound')
Should be:
and opp.first_mkt_medium not in ('partner', 'inbound_outbound')
The syntax highlighting in the question (at least on my device), revealed the error.
In the future, I suggest using something like Programmer's Notepad in ...
2
Your constructor accepts startDatetime and endDatetime as String parameters, but you are trying to pass in Datetime values. You have two options:
Change the parameter types to Datetime.
Pass in the values as String data.
I would prefer option 1. You can still put them into the map as strings in a variety of ways, the simplest of which is to call Datetime....
2
Unfortunately at this time the only method to debug failing queries is to open a support case and advise support of which Automation the Query Activity is used in. Support can then check the logs to see what the error is.
FYI, in the near future Automation Studio will show you the actual error message for failed Activities in an Automation (yay!) — refer ...
2
There is a hidden, indexed field in every data called _customObjectKey and it's fast. While it seems counter-intuitive, you can leverage it in your queries that are timing out by adding an additional join:
select
isnull(sl.JobID,0) JobID
, isnull(sl.ListID,0) ListID
, isnull(sl.BatchID,0) BatchID
, isnull(sl.SubID,0) SubID
, isnull(sl.TriggeredSendID,'') ...
2
You can find those records containing multi-byte characters by converting the First Name field to a single-byte data type and then comparing against the First Name for equality:
SELECT
FirstName
FROM
MyDataExtension
WHERE
FirstName != cast(FirstName as varchar(1000))
If you're specifically interested in locating records that contain the ...
1
Because you are only interested in distinct leads I would use this:
SELECT
Row_Number() Over(PARTITION BY EmailAddress ORDER BY CreatedDate DESC) AS Row
,L.CreatedDate
,L.EmailAddress
,L.ListID
,L.Status
FROM ent._ListSubscribers L
where l.ListID in (20798913, 20757098, 20828774, 20233021, 20551644, ...
1
There are many samples in the official page.
https://developer.salesforce.com/page/PHP_Toolkit_20.0_Samples
You should be able to query
<?php
// SOAP_CLIENT_BASEDIR - folder that contains the PHP Toolkit and your WSDL
// $USERNAME - variable that contains your Salesforce.com username (must be in the form of an email)
// $PASSWORD - variable that ...
Only top voted, non community-wiki answers of a minimum length are eligible
