mssql integration

[richik]

hi,

sieht jemand den fehler? komme nicht dahinter? alles casesensitiv eingegeben

wir haben eine view 3CXPhonelist in unserer microsoft sql 2017 datenbank



lokal am sql server funktioniert die abfrage mit dem user:



benutzer ist berechtigt zuzugreifen und die 3cx läuft im selben subnetz

so sieht die abfrage aus:

SELECT Id as contactid, FirstName as firstname, Lastname as lastname, CompanyName as companyname, Email as email, PhoneMobile as phonemobile, PhoneMobile2 as phonemobile2, PhoneHome as phonehome, PhoneHome2 as phonehome2, PhoneBusiness as phonebusiness, PhoneBusiness2 as phonebusiness2, PhoneOther as phoneother, FaxBusiness as faxbusiness, FaxHome as faxhome, Pager as pager, PhotoUrl as photourl FROM dbo.[3CXPhonelist] WHERE phonebusiness LIKE '%[Number]%' or phonemobile LIKE '%[Number]%' or faxbusiness LIKE '%[Number]%'

habe auch

FROM [3CXPhonelist]
FROM 3CXPhonelist
FROM dbo."3CXPhonelist"
FROM dbo.3CXPhonelist
FROM "dbo.3CXPhonelist"

versucht

das habe ich eingestellt:


beim testen kommt folgender fehler:

mit FROM 3CXPhonelist:

Falsche Syntax in der Nähe von "3".



Started scenario with empty id
Request text: ConnectionString: Server=10.3.0.11\SQL2017,1433;Database=Databasename;User Id=username;Password=password;
Statement: SELECT Id as contactid, FirstName as firstname, Lastname as lastname, CompanyName as companyname, Email as email, PhoneMobile as phonemobile, PhoneMobile2 as phonemobile2, PhoneHome as phonehome, PhoneHome2 as phonehome2, PhoneBusiness as phonebusiness, PhoneBusiness2 as phonebusiness2, PhoneOther as phoneother, FaxBusiness as faxbusiness, FaxHome as faxhome, Pager as pager, PhotoUrl as photourl FROM 3CXPhonelist WHERE phonebusiness LIKE '%551234%' or phonemobile LIKE '%551234%' or faxbusiness LIKE '%551234%'

System.Data.SqlClient.SqlException (0x80131904): Falsche Syntax in der Nähe von "3".
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__126_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at CrmIntegration.SqlDatabaseClient.Execute(String statement, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.Execute(ProtocolScenario scenario, CancellationToken token)
at CrmIntegration.CrmProcessor.GetContactInfoFromCrm(String contactNumber, String destinationEmail, Boolean createIfNotFound, CancellationToken token)
ClientConnectionId:8ae018de-28f0-4703-ace4-553d166fe2ca
Error Number:102,State:1,Class:15

oder

mit FROM

Ungültiger Objektname "3CXPhonelist".
Started scenario with empty id
Request text: ConnectionString: Server=10.3.0.11\SQL2017,1433;Database=Databasename;User Id=username;Password=password;
Statement: SELECT Id as contactid, FirstName as firstname, Lastname as lastname, CompanyName as companyname, Email as email, PhoneMobile as phonemobile, PhoneMobile2 as phonemobile2, PhoneHome as phonehome, PhoneHome2 as phonehome2, PhoneBusiness as phonebusiness, PhoneBusiness2 as phonebusiness2, PhoneOther as phoneother, FaxBusiness as faxbusiness, FaxHome as faxhome, Pager as pager, PhotoUrl as photourl FROM "3CXPhonelist" WHERE phonebusiness LIKE '%551234%' or phonemobile LIKE '%551234%' or faxbusiness LIKE '%551234%'

System.Data.SqlClient.SqlException (0x80131904): Ungültiger Objektname "3CXPhonelist".
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__126_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at CrmIntegration.SqlDatabaseClient.Execute(String statement, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.Execute(ProtocolScenario scenario, CancellationToken token)
at CrmIntegration.CrmProcessor.GetContactInfoFromCrm(String contactNumber, String destinationEmail, Boolean createIfNotFound, CancellationToken token)
ClientConnectionId:8ae018de-28f0-4703-ace4-553d166fe2ca
Error Number:208,State:1,Class:16

mit FROM [3CXPhonelist]

Unknown expression: 3CXPhonelist. Part: 3CXPhonelist.
Started scenario with empty id
System.Exception: Unknown expression: 3CXPhonelist. Part: 3CXPhonelist.
at TcxLexicalParser.ValueManager.GetByPath(String[] path)
at TcxLexicalParser.LexEvaluator.Process(Element element, IValueManager valueManager, Boolean isFunctionArgument)
at TcxLexicalParser.LexEvaluator.ProcessInt(ConcatElement concatElement, IValueManager valueManager, Boolean isFunctionArgument)
at TcxLexicalParser.LexEvaluator.Process(Element element, IValueManager valueManager, Boolean isFunctionArgument)
at CrmIntegration.EscapedExpressionEvaluator.ParseExpression(String expression, Boolean asExpression, IValueManager customManager)
at CrmIntegration.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.Execute(ProtocolScenario scenario, CancellationToken token)
at CrmIntegration.CrmProcessor.GetContactInfoFromCrm(String contactNumber, String destinationEmail, Boolean createIfNotFound, CancellationToken token)

wie wäre der richtige syntax um auf eine view zuzugreifen?

danke

richard

 

[mn26x]

Ich glaube mal gelesen zu haben, dass Tabellen (Views und Columns dann sicher auch) nicht mit einer Zahl beginnen sollten. Ich meine, das bringt den SQL-Parser durcheinander. Benenn' die View mal in Phonelist3CX um...

 

[richik]

umbenannt in Phonelist3CX

FROM Phonelist3CX

Ungültiger Objektname "Phonelist3CX".

...
System.Data.SqlClient.SqlException (0x80131904): Ungültiger Objektname "Phonelist3CX".
...

diverse schreibweise versucht mit " und [] mit und ohne dbo

 

[UKortkamp]

Du hast explizit den Port 1433 mit angegeben - der gehört hinter die Server-IP und nicht hinter die Instanz (kannst sie aber auch weglassen, da default).

Habe EXAKT diese Integration vor 10 Minuten in unser CRM System vorgenommen ;-)

 

[richik]

danke, jetzt funktioniert es

1. port war falsch, angabe ist aber für port 1433 nicht nötig

funktioniert: 10.3.0.11\Instanzenname
geht nicht: 10.3.0.11,1433\Instanzenname
geht nicht: 10.3.0.11:1433\Instanzenname

2. die view mit 3cx... gefällt ihm nicht. umbenannt und es geht

funktioniert: Phonelist3CX
geht nicht: 3CXPhonelist

3. user geht mit 3cx. da ist es ihm egal

diese abfrage habe ich verwendet:

SELECT Id as contactid, FirstName as firstname, Lastname as lastname, CompanyName as companyname, Email as email, PhoneMobile as phonemobile, PhoneMobile2 as phonemobile2, PhoneHome as phonehome, PhoneHome2 as phonehome2, PhoneBusiness as phonebusiness, PhoneBusiness2 as phonebusiness2, PhoneOther as phoneother, FaxBusiness as faxbusiness, FaxHome as faxhome, Pager as pager, PhotoUrl as photourl FROM Phonelist3CX WHERE phonebusiness LIKE '%[Number]%' or phonemobile LIKE '%[Number]%' or faxbusiness LIKE '%[Number]%'

danke für eure unterstützung!

richard