Transfer data (views) between databases in offline scenario

Question

Transfer data (views) between databases in offline scenario

werner.huysmans

SSC Enthusiast

Points: 134
More actions
July 25, 2020 at 9:09 pm

#3774762

I have this seemingly really simple problem, but I can't find a decent solution anywhere, been searching for days now...
Please bear in mind I'm not a full-fledged DBA. I could solve it completely in .Net code, but I had hoped there would be a relatively easy solution in Sql directly...
Here goes: I need to transfer (insert and update) data from one database to another. Sounds really simple, right?
But there are some constraints:
- the target database is not on the same network: it is not directly accessible, I can only transfer files.
- the data I want to transfer is not in "fixed" columns. They can change frequently. The target database has tables to receive the data, but the data I'm sending through is not always the full record. I may send 4 out of 10 columns, or 25 out of 30, or whatever. Always subsets of the complete records. Inserts are also possible, in that case the receiving table will allow null values or have default values.
- it should ideally be a stored procedure that creates the file(s) at the sending db, and a sp that reads the file at the receiving db. Can't use anything manual like SSMS wizards, and prefer not to use powershell due to limitations at the receiving end (those computers are not under my control).
Column names and data types are -of course- the same on sending and receiving end.
How can I do this without needing to build a whole bunch of sql commands dynamically? I was looking for an XML solution, but I can't find anything that's dynamic enough to do this. So any solution that lets me realize this, doesn't matter what file format, would work.
I really feel like a complete newbie to be asking this, but I just can't find anything that fits...
Any help is hugely appreciated!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

No, there is no simple SQL solution for this, because the Database Engine is not designed for reading or writing files, so there is a road block already there. OK, so you can read files with BULK INSERT or OPENROWSET(BULK), but then files must be in a location where SQL Server can read them. Would the other SQL Server instance be able to write there?

And then you have all the dynamic aspect of it all.

No, this screams for a solution which is orchestrated outside SQL Server by client-side program, be that C#, Python, PowerShell or whatever you fancy. Or for that matter SSIS.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

werner.huysmans SSC Enthusiast Points: 134 More actions · Answer 2

Yes, the files are transferred, that's not my main concern.

It seems so crazy to me that I can generate all the info I need in just a single, very simple command (SELECT * FROM MyView FOR XML AUTO, ELEMENTS XSINIL, XMLSCHEMA) but then there's no practical way I can do anything with that result on the receiving end.

All I really need is to write that result to a file, and do the reverse on the other end...

Anyway, I guess C# code it will be then.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

werner.huysmans wrote:

Yes, the files are transferred, that's not my main concern.
It seems so crazy to me that I can generate all the info I need in just a single, very simple command (SELECT * FROM MyView FOR XML AUTO, ELEMENTS XSINIL, XMLSCHEMA) but then there's no practical way I can do anything with that result on the receiving end.
All I really need is to write that result to a file, and do the reverse on the other end...
Anyway, I guess C# code it will be then.

There actually is but no one ever wants to hear about it because I use xp_CmddShell to do this along with a call to SQLCmd. Everyone thinks that xp_CmdShell is this super security risk when it's actually very secure and much more practical writing managed code or allowing someone to run PowerShell or what have you from the command prompt or SSIS or a thousand other annoyances. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

Sssh, Jeff, you were not supposed to say that.

And what I said stands: there is no way to write files from SQL Server. Yes, you can call SQLCMD or BCP from xp_cmdshell, but then you are only driving my point: there is no way to do it. Why jump out from SQL Server to call them, when you much easier can call them from the outside directly without going through SQL Server?

And if Werner would use any of BCP or SQLCMD, he would have to fight to get the XML file he wants with no extras and nothing missing.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

werner.huysmans SSC Enthusiast Points: 134 More actions · Answer 5

🙂

Well, thanks, Jeff, that sounds promising, and I definitely want to hear about it now...

Can you point me in the right direction just a little bit further?

Erland, I see your point, but let's just say that in my scenario there are benefits in firing this up from within SQLServer.

As for the file(s):

SELECT TOP 0 * FROM MyView FOR XML AUTO, XMLSCHEMA would give me the xsd

and

SELECT * FROM MyView FOR XML AUTO, ELEMENTS XSINIL would give me the data

That's all I need. No extras and nothing missing. That's the easy part, as far as I can see... Or am I missing something here?

Now I "just" need to save that to files and convert the files back to tables on the receiving end.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

And SQL Server cannot write files.

You can escape to the command line to call programs that write files, but don't you hear how akward that sounds? You could just as well write a CMD file. I mean, in any case, if you put this in a stored procedure, something must start that procedure. Then that something could just as well write the files.

And if you use BCP or SQLCMD to write the files, you may be in for a battle until you get it write.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

werner.huysmans SSC Enthusiast Points: 134 More actions · Answer 7

Well, OK, let's say I write code at the sending side to save the file.

I can write a very simple peace of code to execute and get the results from the SELECTs I wrote above, and put those in files.

But that still leaves the issue at the receiving end: SQLServer can READ files, can it not? The problem there is it needs to be fully dynamic. I could of course write some code to investigate the XML and create the correct table(s), but I would think this capability is somewhere in SQLServer already. That's the second part of my problem...

All I found so far relies on knowing the structure of the data, or parsing the XML myself, which seems at least as awkward.

I found XSD2DB command line tool, but as I understand, that creates an entire database, and it seems to be quite old (last update was 7 years ago, requires .Net 1.0), so I'm not too confident on using that... Or would that be a good solution?

I would then create a completely new temporary database, put the data in there, and update my real database using cross-database queries. And finally, remove that temporary database. Doesn't seem like an elegant solution either... I don't even want to think about performance... :-/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

Erland Sommarskog wrote:

Why jump out from SQL Server to call them, when you much easier can call them from the outside directly without going through SQL Server?

Because it's easy... easy to schedule... easy to manage... easy to log... easy to review logs. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Erland Sommarskog wrote:

And if you use BCP or SQLCMD to write the files, you may be in for a battle until you get it write.

I don't know why people think that. It's just not that difficult to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

werner.huysmans wrote:

🙂
Well, thanks, Jeff, that sounds promising, and I definitely want to hear about it now...
Can you point me in the right direction just a little bit further?

Are you allowed to use xp_CmdShell in your environment or not? If not, then it's a waste of time and I'm sorry to have wasted yours with the suggestion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 11

werner.huysmans wrote:

But that still leaves the issue at the receiving end: SQLServer can READ files, can it not? The problem there is it needs to be fully dynamic. I could of course write some code to investigate the XML and create the correct table(s), but I would think this capability is somewhere in SQLServer already. That's the second part of my problem...

You can read the files with BULK INSERT or OPENROWSET(BULK), and that's OK if you have tables created to load the data in. But you have not, you have a bunch of XML and an XSD schema that you will need to parse. Yes, you can do that in SQL Server, but there is nothing built-in, you will need to write a ton of dynamic SQL. And what is dynamic SQL? It's just a bunch of string processing. Which is not SQL Server's strongest capability.

You have a problem that requires a good screwdriver, but for some reason you want to use a hammer.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 12

But, OK, if you open the shaft to the hammer, there is actually a screwdriver inside. That is, if you really want to do this inside SQL Server, you can write a stored procedure in C# and the CLR for the task. But there is a bit of red tape to get an CLR assembly going. It can certainly be worth the hassle if you have a bigger SQL task which at one points requires logic which is best done in .NET, for instance RegEx matching. But here we are talking about something which has to be orchestrated from .NET with pieces of SQL added.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

werner.huysmans SSC Enthusiast Points: 134 More actions · Answer 13

Hi Jeff,

yes, I can use xp_CmdShell. We do that already for some other stuff.

@Erland: it just seems incredible stupid to me that there is such an elegant way of creating EXACTLY what I need in SQLServer (2 extremely simple SELECTs), and no way to actually use that result...

What's the use of creating that xsd in this environment if I can't create a table from it?

I don't want to get into the semi-religious discussions of what tool is better for what job, I'm just trying to find out if there is an easy way in SQLServer to do this. I don't know, you say no, Jeff says yes. I really appreciate both your views and input on this, but I don't have a final answer yet 🙂

Writing an SP in .Net does not fall into the "easy" category to me. In that scenario, I'm with you: that would be the screwdriver inside the hammer's shaft... If I'm going to do that, I'd rather write a separate program completely outside of SQLServer to do the job. But this is what I'd like to avoid.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

werner.huysmans wrote:

Hi Jeff,
yes, I can use xp_CmdShell. We do that already for some other stuff.

Set up a stored procedure to produce the output that you want to send to the the other machine in a file.
Execute xp_CmdShell to make a call to SQLCmd to run the proc using the switches for a trusted connection and the -o switch to write the output to a file. You can find more details at the following link for SQLCmd.

https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

Not sure what you're requirements on the receiver side are. I don't even know if it's an SQL Server nor how you intend to fly the file to it. If you provide more detail, we might be able to provide more detail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)