|
PROFESSIONAL NEWSGROUPS
WINDEV, WEBDEV and WINDEV Mobile |
| | | | | |
| WinDev MS SQL not using dbo SCHEMA |
| Started by guest, Mar., 10 2014 12:59 PM - 4 replies |
| |
| | | |
|
| |
| Posted on March, 10 2014 - 12:59 PM |
Hi,
I`m making a small import utility for one of my clients, but struggle with their choosen Schema name in MS-SQL (I`m using windev v18)
I`m currently trying to connect to a MS-SQL Database that`s not using the default [dbo] schema, but [DataBridge].
So a table will bee in the form [database].[schema].[tablename] = [DataBridge].[DataBridge].[Transactions]
1. When trying to connect via the "Import existing table" in the analyze there is no place to specify SCHEMA and I get an error?
2. If I rename the schema on the server side to [dbo], I can import the table, but will I now hack up for the "main" app using the [DataBridge].[DataBridge].[Transactions] table?
3. My App is just an Import a XML file into Database i "Live" mode, and at clients sites there`s no SQL Management studio available so what to do? Can I change the schema in my connection name on the fly (Via code) to [DataBridge]?
Cheers
Tor-Bjarne |
| |
| |
| | | |
|
| | |
| |
| Posted on March, 10 2014 - 5:25 PM |
I don't know if you are using OLEDB or native access but probably this note in the hchangename function can help you.
http://doc.windev.com/en-US/… |
| |
| |
| | | |
|
| | |
| |
| Posted on March, 11 2014 - 12:38 PM |
I'm afraid I think you're going to be in for a bit of a renaming session. I had this before when using non-DBO tables, for some bizarre reason WinDev won't let you import them.
I asked PCSoft Tech Support on why this wouldn't import and got told it was a Microsoft problem with the ODBC driver. Yeahhhhhhhh, that's why I could import the same tables on every other dev platform I had, with the exception of WinDev, using the same ODBC /OLE driver then! |
| |
| |
| | | |
|
| | |
| |
| Posted on March, 14 2014 - 1:06 PM |
Hi,
Thanks Paulo and Peter J for your input 
Paulo, the hChangeName did the trick.
Here`s how for future recerence:
1. First I Had to set up a user that is allowed to change schema names, seems like the SA user is now allowed to do this.
2. Right click on each table I want to import and change the schema from whatsever there ([Databridge] in my case) to default [dbo] schema.
3. Import table(s) into analyze.
4. Rename Schemas back or Deattach/attach the "Backup of original DB" - Since the "Main" app. needs the correct schema.
5. After the HChangeConnection I run the code below:
// Change owner on tables sTablelist is string = HListFile(DBNONLOCAL) //DBONLOCAL is a connection variable. sOwner is string = "DataBridge" //DataBridge is owner name (And DB Name just to confuse people) s is string FOR EACH STRING sT1 OF sTablelist SEPARATED BY CR IF sT1 = "MT_Sync" THEN //Created from Analyze not in original DB CONTINUE END s = """"+sOwner+"""."""+sT1+"""" WHEN EXCEPTION IN IF HChangeName({sT1},s) = False THEN Error(HErrorInfo()) ELSE Trace("File: '"+sT1+"' Changed owner to: "+s) END DO Trace("File: '"+sT1+"' untouched. Probably not in my Analyze") END END
Now I can use the tables in a normal way in WinDev as they where HF/CS tables.
Cheers
Tor-Bjarne |
| |
| |
| | | |
|
| | |
| |
Registered member
7 messages |
|
| Posted on March, 21 2020 - 1:31 PM |
Thanks very much indeed, Tor-Bjarne, for the very useful code you posted above! I had exactly the same situation with 3 different schemas in a Sql Server database, and your code helped me overcome the fact that WinDev does not seem to allow importing of tables from anything but the default dbo. schema.
Thanks again! |
| |
| |
| | | |
|
| | | | |
| | |
| | |
| |
|
|
|