Questions tagged [sqlserver]
The sqlserver tag has no usage guidance.
70
questions
0
votes
1
answer
63
views
SQL Query Issue to get last 12 Months data from a DE in SFMC
I am trying to get the data from a DE for last 12 months but when I am running my Query, I am only getting data November 2022 but I want to have data from each month after from November 2022 till now ...
1
vote
1
answer
370
views
Decrypt Symmetrically/Asymmetrically Encrypted Data in MS SQL (T-SQL) Server via Apex Crypto Class
Hope you're doing well. Recently, I have been working on a project and haven't been able to find a solution to this problem.
What I want to achieve is: To decrypt the data in Apex using the crypto ...
0
votes
2
answers
294
views
Why does selecting from _Sent and LEFT JOIN _Job return null values from _Job
When I select from _Sent and LEFT JOIN _Job it returns null values for fields selected from _Job.
I'm querying from the Parent BU so this shouldn't be the BU specific issue.
SELECT
s.SubscriberKey,
j....
- 21
0
votes
0
answers
78
views
Convert function with date formatted as text
I have a DE1 with field named 'DATE_A' with datatype text. This field contains records that are formatted like this: yyyy-mm-dd hh:mm.
Using sql query activity I should retrieve records with field ...
- 71
0
votes
0
answers
25
views
Salesforce marketing cloud date and time [duplicate]
In salesforce marketing cloud UI (Tracking) , I see a delivered date as 8/12/2021 9.04.
In the sql on Jobs data view , i get delivered date 12/8/2021 2:04:00 AM
I know ,
both dates are same and ...
1
vote
1
answer
9k
views
SQL Query : Using JOINS with CASE Statement [closed]
I've 3 DE
MASTER DE with fields SubscriberKey(PK), donottrack, Createddate, Modifieddate
Staging DE with field CustomerID(PK), Createddate
Reference DE with field donottrack default value false.
...
- 61
2
votes
2
answers
160
views
How to set default time in DE date field time using SQL in marketing cloud
whenever a record is created how to set the time in the Date field time value to 8AM by default.
SELECT CustomerID,
CreatedDate,
DATEADD(Day, 1, CreatedDate) As NextDate
from TestCustomers
instead ...
- 405
0
votes
1
answer
1k
views
SQL query to get Open rate by domain
I'm trying to build an SQL query to get the Open Rate Percentage by domain for an email send through Journey build or scheduled send. Below is the query I have trying to figure out, but it's giving me ...
- 61
0
votes
1
answer
295
views
Invalid column name in row_number() function
Why am I getting this error?
An error occurred while checking the query syntax. Errors: Invalid column name 'Id'.
SELECT
(L.Id) as 'SubscriberKey',
L.ID__c as 'ID_C',
L.Email as '...
- 81
3
votes
1
answer
2k
views
How to use string_agg/group_concat in salesforce marketing cloud
I am trying to write something similar in query studio on SFMC. But keep getting string_agg is not recognized as a built-in function name. Please help. Also, what could be the alternative.
Thanks
...
0
votes
3
answers
2k
views
Pushing data from one data extension to another
In Marketing Cloud, I have two data extensions. Both Data Extensions are being populated via entries in a journey. One runs off of the contact object and the other runs off the opportunity object. The ...
- 1
0
votes
1
answer
376
views
how to write a trigger without nested FOR loop
I wrote a trigger but My professor asked me to write it without nested loop and I don't know how to do it.
I want to do is when a contact gets created i need to take field named- 'Description' of ...
- 1
0
votes
1
answer
392
views
Pull data from sql to salesforce
I am looking to generate reports and dashboards on Salesforce and the data reside in my SQL server.
How do pull data from SQL to Salesforce in order to create reports & dashboards in salesforce
- 5
0
votes
1
answer
187
views
I want to copy over another field but remove data in SFMC SQL [closed]
I have two columns, one titled "Account_Number" and the other titled "Customer_Number". There are the same, except Customer_Number has "C" in front of it. If I have all ...
- 134
0
votes
2
answers
513
views
Checking for a non ascii character in SQL Query activity
I have certain records in my DE, where the first name contains '�'. I wish to extract such records. Obviously a like wouldn't work. Is there a way to do this in SQL Query Activity.
- 1,646
0
votes
1
answer
435
views
Apex triggers to interact with sql server [closed]
We are in the beginning stages of getting set up in Salesforce, and there is something that I am having a little trouble getting my head wrapped around;
We currently have a local SQL server ...
- 103
0
votes
0
answers
105
views
transact-sql seems is outdated and not anymore being supported
As soon as I opened the official resource for Transact-SQL from the link provided on the official support page (bottom links), a notice from outdated appears at the top of the Microsoft page.
This ...
- 360
0
votes
1
answer
1k
views
Multiple SELECT in subquery
In Marketing Cloud, we would like to check for the existence of records from one table in multiple other tables based on the unique identifier - Customer_ID. However, I'm not sure whether I can ...
- 538
0
votes
1
answer
106
views
Get all records inside a given date
I need to get all the records that are inside a given date. What I have now is giving me only one of them randomly every time I run my query. Any suggestions on how to fix that?
SELECT table1.Number ...
- 1
0
votes
1
answer
223
views
SSIS with Salesforce [closed]
I have a requirement where in we need to push data from sql server to Salesforce on daily basis. Can anybody suggest me the best possible way to do the same ?
Also After googling things, it seems ...
- 33
2
votes
1
answer
281
views
How can I setup a scheduled sync from a SQL Server database to Salesforce Big Objects?
What is the best way to have a daily scheduled insert/update of a SQL Table into Salesforce Big Objects? We currently have a C# adapter to update Salesforce standard object records with SQL database ...
1
vote
2
answers
277
views
Importing data from multiple external tables to a single object
I am trying to use a production SQL Server database to send relevant data into Salesforce to generate reports. Some of the data I would like to use however is not consolidated into a single database ...
- 11
2
votes
1
answer
294
views
Date and Time function not usable anymore
Good day,
I use in Automation Studio, an SQL query using functions like dateAdd() and Datediff().
Since yesterday, this activity is errored with this message : " An error occured while checking the ...
0
votes
1
answer
2k
views
Updating a Salesforce object directly from a SQL server Stored Procedure [closed]
I want to update a Salesforce Object from a Stored Procedure inside Microsoft SQL Server. Is this possible?
I have use case that will get data from 'X' database .From 'X' db SQL server will get ...
- 169
1
vote
2
answers
2k
views
Salesforce DB connect with SQL Management Studio
I'm trying to connect SQL Server Management Studio to Salesforce so I can see tables and columns but unable to connect. Do I need any ADO.net connector or Salesforce plugin for this to work? Do I need ...
- 1,167
2
votes
1
answer
107
views
Transferring data between DE 1 and DE 2 when record not present in DE 3
I have 3 data extensions: Master, Local and additional, supporting one which essentially functions as historical Storage of all the records that ever landed in Local. So the data flows like this ...
- 97
0
votes
1
answer
1k
views
Exact Target SQL comma separated values within a field
We are having a field called - ‘VisitOverdueMailingList__c’ on the Account object populated with comma separated values. The field contains data in the following format (only for certain Accounts that ...
0
votes
3
answers
4k
views
Query Failing: Time Out
I have a query that has started failing due to the fact that it has over 400M rows. Although there are joins and fuzzy matching (LIKE '%XXXX%') it is still failing. Using NO LOCK too
I have used ...
- 6,553
0
votes
1
answer
2k
views
Using CASE to update a field
I'm getting an error on my case statement in SFMC. Is it a simple mistake of syntax?
SELECT *,
CASE WHEN status <> 'active' THEN 'active' ELSE status END AS status
FROM [sample table]
WHERE ...
- 133
1
vote
1
answer
650
views
Error with Duplicate Primary key in Marketing Cloud SQL
We have multiple newsletters that generate leads for marketing emails. I'm trying to automate the process of consolidating those leads into one master data extension after they have been on a ...
- 31
0
votes
1
answer
182
views
How to capture Data Loader error when called from Shell script via SSIS package?
Salesforce Gurus:
Thank you in advance for any insights you may have.
For years now I have been extracting data from a custom Salesforce object named Branches__c using the following method...
...
- 297
1
vote
0
answers
59
views
query gets wildly varied results on sql server vs sf of same dataset
the data is 1:1 with SF on the sql server with same named columns, yet...
SELECT ID,TIER__C FROM [salesforce_stage].[dbo].[SFDC_ACCOUNT] where TIER__C NOT IN ('C1', 'E1', 'E2', 'E3', 'E4', '...
- 313
0
votes
1
answer
1k
views
Query Error - cannot convert nvarchar
I am trying to return details associated with a certain email address . I am using the query below:
select emailaddress
,subscriberkey
,record_type_name__c
from
mastersubscriberde
where ...
- 6,553
2
votes
1
answer
1k
views
Store Data from Salesforce Marketing Cloud to my local SQL database server
Am new to using Marketing Cloud. I want to know if there is a possibility of storing the data from Marketing cloud campaigns and Subscriber activity, Conversion tracking data to my local SQL server?
...
1
vote
1
answer
365
views
Marketing Cloud - Replacing Multiple hardcoded values in JOINS with values from Parameter Table
Salesforce Marketing Cloud query activities do not allow variables or temporary tables according to the "SQL Support" section of this official documentation (http://help.marketingcloud.com/en/...
- 111
0
votes
0
answers
1k
views
SF Marketing Cloud - Query Variable Table for value used in SQL function
Salesforce Marketing Cloud queries do not allow variables or temporary tables according to the "SQL Support" section of this official documentation (http://help.marketingcloud.com/en/documentation/...
- 111
0
votes
1
answer
136
views
Query not returning values
I have the written the query below to return two values. I have also check the DE to ensure that it has the correct data types. Unfornately, the query is not returning any values:
SELECT
M....
- 6,553
0
votes
0
answers
317
views
SQL server table not locating the respective Record ID
My SQL server administrator could establish a connection with our salesforce sandbox instance and could bulk insert brand new records. However, he couldn't update the existing records as the record ID ...
- 109
0
votes
1
answer
68
views
How can I setup an integration between Salesforce CRM and Microsoft SQL Server using PHPToolKit?
How can I setup an integration between Salesforce CRM and Microsoft SQL Server using PHPToolKit? We dont want to use any ETL toolkit to integrate this two environments. We would like to pull the SQL ...
- 180
0
votes
1
answer
1k
views
SFMC Query : Count Not Working
I am trying to count to number of subscribers have made a transaction. I am using the code below:
SELECT
M.Id SubsriberKey
,COUNT (Transaction_ID__c) OVER (PARTITION BY M.Id) AS ...
- 6,553
3
votes
2
answers
4k
views
How do i resolve 'Error while processing square brackets' when there are no square brackets in the query?
this is the query:
select
opp.*
from
(
select
opp.*,
row_number() over (partition by opp.contact_email_address order by opp.status_date desc) as row_number
from
opportunity_data opp
...
- 31
2
votes
1
answer
72
views
Save error: Constructor not defined: [Sample11.EventData].<Constructor>(Id, Datetime, Datetime, String)
I'm trying to expose user related events using REST API but I get the error at line 33. Why?
Error Line:
EventData eveData = new EventData(eve.Id,eve.StartDateTime,eve.EndDateTime,eve.subject);
...
- 85
1
vote
1
answer
612
views
Creating Data Extensions by running queries [closed]
Is it possible to create data extensions by running a query with the CREATE TABLE function?
- 6,553
0
votes
1
answer
2k
views
Square Brackets sql query error
I am receiving an error:
The query syntax is invalid
Error while processing square brackets
I have tried to run it with and without brackets and still receive the same error. Please also note that ...
1
vote
1
answer
1k
views
Exacttarget SQL Query Help: Find all Subscribers that opened at least 2 emails
I am trying to write a query to identify all subscribers that opened at least 2 emails in the last 6 months.
I will pull from a data extension called "Clients" and look for all opens in the "_open" ...
2
votes
2
answers
6k
views
Query Activity Failing
I have set up a data extension to capture a query and return it's results. In order to track the query, I am running it in automation studio. However, the query keeps failing. I have checked that the ...
- 6,553
0
votes
1
answer
42
views
Saleforce list to include data from SQL Server [closed]
I need to create a list in Salesforce that include leads from Lead.com, Radius AND leads from my company’s own list in SQL server.
It seems simple enough to create a list in SF that includes leads ...
- 105
0
votes
1
answer
131
views
SFMC query speed
I'm automating a bunch of queries and sends in SFMC and am curious about how long SQL queries take to run.
Is there a log where I can see the time taken for a query to finish?
- 81
0
votes
1
answer
722
views
Open rate by domain over time - SQL
I'm trying to figure out open rate over time based on domain however, it seems to error or timeout every time I run it. I've limited it to just gmail, yahoo, and AOL for now.
SELECT
o.domain
, s....
- 27
0
votes
1
answer
2k
views
SFMC Filter Activity - Date + -
When creating a filter in the Marketing Cloud, say when filtering a Data Extension, you can base any of the statements in the filter off of a date field. The question I have relates to when using the ...
- 2,113