Connect to XMPie SQL Database from XMpie Web Server

[SerjeantGrant]

Hi All
I have a custom Database that I have put (in SQL) on the XMPie SQL Server machine, which I can administer using SQL Mngt Studio and Remote Desktop.
However, I can't seem to connect to SQL from the XMPie Web Server machine? 
I want my website (on the Web Server) to be able to connect to my custom DB in SQL (on the Data Server)
Remote connections are enabled on the SQL config.
The Port and IP seem standard
(XMPie is on these two machines, Web Server and Data Server and it seems to connect fine)
I am using the simple Administrative Tools ODBC to test the connection...
Any help appreciated

[couch]

Are you trying to connect your web page to sql via .NET database
tools, or to use the XMPie uCreate XM and the port? If the latter, I
suggest that you use the RURL Wizard to create the website since this
will create the virtual directory, web.config file, proxy config,
setup sample aspx pages, and also the dreamweaver site configuration
file so that everything is setup and configured correctly for you...

[SerjeantGrant]

Ho couch,
Unfortunately the former.  I don't really want the standard web.config etc as I'm using web services etc - all setup in the web.config.
However, if I use the RURL Wizard can I overwrite the 'real' web directory with my .NET website? Merging the settings into the web.config...
I understand that the RURL Wizard lives on the 'Data Centre' machine of XMPie, so connection would be a dream if I installed the website on that machine
Its a combination of XMpie web services and database access etc in the website, using the XMPie Web services to talk to the Recipients table and uProduce to produce Print articles, but using .NET SQL Connections to get to the rest of the data...
Is the DMZ blocking my connection?  Telnet and the firewalls seem fine...
Many thanks

[SerjeantGrant]

Trying your suggestion couch,
When I try to adapt this campaign for the Web I get
"The XXXX Campaign doesn't have any Data Source that contains a Recipient Table. Add a Data Source that contains a Recipient Table to this Campaign, and try again."

...The Data source has an entry for the SQL Database (only the RECIPIENTS table)
...The (single) Port correctly identifies the Recipients table...

(I can use the Web service InsertADORS... with no problem, so it knows where the Recipient table is...)

[couch]

There is no need to "adapt for web" - this is only to convert file-
based data to a sql table. Just create the campaign in uProduce upload
the plan file and create a datasource to your SQL table. I would then
go back to the plan in uProduce and "generate" a proof set to ensure
the plan is compatible with your sql table. Then go to the wizard and
create the site. You can merge in other web.config settings as
required. (This is generally easiest, quickest, and less prone to
errors until you become more familaiar with the required settings.)

The wizard should be installed on the web server in your DMZ - the SQL
and uProduce should be inside the firewall on the LAN for security
reasons. When configured this way, only port 80 needs to be exposed
between the web and uProduce servers.

If you want to connect from web to sql, then you will also need to
enable a port for sql through your firewall (default is 1433).
Natrually, this is not so desirable; and possibly you should use a
different port for security. Either way, your firewall should lock
down the sql port to permit just the web/sql to communicate on the
selected port.

Also another trap for newbies is that XMPie generally uses a SQL
instance, so the connection string to the sql server should be use
both servername (or IP) together with the instance name: SERVER
\INSTANCE rather than just servername/ip.

[SerjeantGrant]

Hi couch
Thanks, will try the Wizard method you described.
But to clarify, I can produce a Proof set and even a Job Ticket with PDF proofs from Web Services, so I know the Plan and Table are compatible. 
When you say 'Go to the Wizard and create the site' do you mean the RURL Wizard and create a new site?  (which connects to my uProduce Campaign I presume)
Or is there another wizard in uProduce I am not finding...(Sorry not in front of PCs now to check)

I have checked the firewalls on both sides and 1433 is allowed access.  Perhaps there is a hardware firewall between them, as I have also tried with the firewalls off...This does seem like a hardware or firewall or Anti virus problem blocking the Port...

I do use the SQL convention of Server/instance, and have tried with IP address by itself and with IP/XMPIE ... and I have verified the instance name (XMPIE) and the server IP address....
Telnet seems OK when asked 'telnet xxx.xxx.xxx.xxx 1433'  (blank screen waiting instruction) and SQL has remote connections allowed

I do not want to change anything that will make the system drift away from the XMPie specifications, as I'm sure it is set up properly by the XMPie techs...

Just seems odd (and a showstopper at the moment) that the Web Server doesn't connect to the Data Server ...BUT perhaps it is designed that way with the DMZ in mind.
Thanks heaps, will try the Wizard so that my website is physically sitting where XMPie likes it.  But how will that solve this issue?  Is there an XMPie Webservice I can use to connect to any (arbitrary) database on the XMPie Database server?  This would probably be a more elegant way to manage a connection to the Data...(albeit slower, but speed not an issue)

[couch]

Yes, I mean the RURL Wizard.

Also, you are using "forward slash" for the server/instance instead of
the required "back slash" server\instance.

Anyhow, If the server is SQL 2008, you may need to drop the instance
name altogether. I have seen that this behaves differently with later
versions of .net where there is only one instance on the SQL 2008
server.

To test for hardware firewall, I would try creating an ODBC connection
from Web server (administrative tools) to sql (rather than just
telnet) since this will confirm if you need the instance name or not.
When setting up the sql connection, remember to click the "client
settings" button and change the default "dynamic" port to the "1433".

[SerjeantGrant]

Hi couch
Great, its the RURL Wizard then, I'll do that.

Don't worry about my syntax, been connecting to SQL for years and always get my slashes wrong, so I try them both or copy working connection strings...Yes it is 2008 R2 Express so I have tried it with and without the instance name.  Basically use the same syntax that I use in SQL Management Studio except I change the Server name portion to the IP address.

Telnet was simply a diagnostic, I can't remember how to use it for anything else :)

I am using the ODBC connection in Admin tools, and have turned off dynamic ports (most times) but I will check that it is forced to 1433 as I've been repeating so many combinations of SQL and login details I may have lost track...

From your assistance I assume that this is entirely possible and that it is simply a matter of fixing the technical issue (So that I can connect to the Data Server SQL from the Web Server)? 

OR - will the web site location created by the RURL Wizard (or the web.config settings) hold the key to connecting my web to the SQL Database directly?

Many thanks

[Steve Snyder]

Group,

 

Anyone out there using a classic ASP interface?  I use a combination of classic ASP with ajax, java and other languages to create a powerful platform that has worked well for us.  Hundreds of pages already written that support our clients, so I am not about to change them all to .NET in order to more easily interface with XMPie (that will be one small component within our overall product offering).

 

We have done very well so far to integrate XMPie and many of the SOAP commands work fine.  However, I am having trouble with the command to add a OrderProductID into the "Cart" so that you can see it from uStoreAdmin to process the order.

 

Does anyone have experience with the SOAP commands using a classic ASP interface?

 

Sure would appreciate the help.

 

Thanks,

 

Steve Snyder

[Timothy Perrett]

Steve,

I feel the need to clarify and correct a couple of points here:

1. Define classic ASP interface. Are you meaning the original API known officially as the Legacy API. As you refer to the SOAP API as well I have no reason to think you would still be using the legacy API as well as the SOAP version.
2. Using .NET does not improve your "integration" with XMPie service API. Period.
3. Which specific uStore API command are you having issues with? Be specific and include code if relevant.
4. SOAP commands with a classic ASP interface? This doesn't make sense, its one or the other in terms of what is provided.

Cheers, Tim

> --
> You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
> To post to this group, send email to xmpie...@googlegroups.com.
> To unsubscribe from this group, send email to xmpie-users...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/xmpie-users?hl=en.

[Steve Snyder]

Tim,

Thanks for you quick reply. Forgive me if I sound like a newbie to
XMPie ... I am, but hope to learn fast.

By classic ASP I mean not .NET.

When you read the uStore SDK API help file, it spends a lot of time
going over how the WSAPI calls work within a .NET environment, but there
is not much documentation on how to execute the SOAP 1.1 commands ...
And example is http://localhost/uStoreWSAPI/UserWS.asmx, where
'localhost' is the machine name on which uStore is installed.

I use SOAP command protocols to get a single URL to bring up the
Customization.aspx page. No problems there at all.

Once a user goes through the Customization page and gets the product
ready, I would like to automate the process upon which the product is
added to the cart (that may not be the right terminology) ... So that we
can see it in the uStoreAdmin in Orders to process.

So far, I have tried unsuccessfully to use "Submit Order" in the OrderWS
... Perhaps this is not the right one? The code is very simple to
execute the SOAP command as the only item it needs (beyond
username/password) is the OrderID.

I must be overlooking something simple. In advance, thanks for any
assistance you can provide,

Steve

[SerjeantGrant]

Hi couch
Yes connection is up and running, Thanks very much for your patience and help.
Must have been a combination of an MS update overnight (arrgh!) and forcing the Port 1433 (in ODBC test), and setting Outbound rule in the firewall.
And, Yes, the server name is: 192.168.xx.xx\XMPIE

Will still perform the creation of a new site with the RURL Wizard as advised
Thanks again

[Steve Snyder]

Tim,

Please advise if I answered all your questions and provided enough
relevant information for you to get back to me.

Steve

-----Original Message-----
From: xmpie...@googlegroups.com [mailto:xmpie...@googlegroups.com]
On Behalf Of Timothy Perrett
Sent: Wednesday, February 23, 2011 4:47 PM
To: xmpie...@googlegroups.com
Subject: Re: [xmpie-users] How to use SOAP Command to Submit Enter
Product into CART