Database

Saving Recordset to a stream

14-Jan-05 13:55

First of all, if you table name is 'group' then you table name is a reserved word so you will need to wrap it in brackets, [group]. You will also need to include the createdate in the select query since the query is really a group by query with the distinct.
Give the following query a try.

select distinct top 3 categoryname, groupname, createdate from [group] order by CreateDate desc;

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

mysorian14-Jan-05 11:14

mysorian

14-Jan-05 11:14

What is this error due to?

Error Type:
Provider (0x80030103)
Can't save.

<br />
set cn =Server.CreateObject("ADODB.Connection")<br />
cn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI; Persist Security Info=False;Initial Catalog=pubs;Data Source=XPHTEK"<br />
set RS=Server.CreateObject("ADODB.RECORDSET")<br />
RS.Open "Select au_fname, phone, city from authors",cn<br />
RS.Save "C:\pubauthrs.xml",adPersistXML<br />
RS.Close<br />
cn.close<br />

----------
Both ADO 2.5 and 2,6 referenced.
IDE is Visual Studio 6.0
IIS 5.0

-Dr_X-14-Jan-05 14:06

14-Jan-05 14:06

Been a while since I have coding any ADO but at first glance I would say close the connection. Recode the above using disconnected recordsets and then try saving the recordset.

set cn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RECORDSET")

cn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI; Persist Security Info=False;Initial Catalog=pubs;Data Source=XPHTEK"
rs.CursorLocation = adUseClient
rs.Open "Select au_fname, phone, city from authors", CN, adOpenDynamic, adLockBatchOptimistic, adCmdText
rs.ActiveConnection = Nothing
rs.Save "C:\pubauthrs.xml",adPersistXML
rs.Close

This was not tested but should work.

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

mysorian14-Jan-05 18:08

mysorian

14-Jan-05 18:08

While the adUseClient does make it like a disconnected recordset, the program comes back with the same error. Search has yielded the following error type. C-coders may recognize what his implies.

0x80030103 (STG_E_CANTSAVE) --> 57 (IOError)

Thanks

-Dr_X-15-Jan-05 12:50

15-Jan-05 12:50

Take a look at this link.

http://www.webservertalk.com/archive126-2004-4-190097.html[^]
Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Re: SQL-select and insert

mysorian15-Jan-05 17:37

mysorian

15-Jan-05 17:37

I thank you sincerely for your efforts. The code you sent me did not work, the same message. I played around with all kinds of permissions, nothing worked. Finally I nailed down to an MSDN article.... The real reson besides the 'bug' that microsfot has found is tht the earlier versions do not support xml to the extent the 2.8 does.
---------------------
There is an explanation here. I have seen in Pre-MDAC2.8 books claims that the code works, I know not how it could have? It is exasperating to deal with something that does not work. Probably it is the 'race' to produce that is responsible for this irresponsible stuff.
---------------------------------
SYMPTOMS
When you try to save an ADO recordset to a file that was created by another user of the system, you may receive the following error message:
Run-time error '-2147286781 (80030103)':
Can't save.
You receive this error message if the application is in a read-only folder, even if the recordset file is in a folder that has write permissions for both users.
CAUSE
When you try to save the recordset to a file that already exists, the recordset object creates a temporary file in the folder of the application. This folder is a read-only folder. Therefore, the second user cannot overwrite the source file.
RESOLUTION
This problem is resolved in Microsoft Data Access Components (MDAC) 2.8. In MDAC 2.8, the ADODB recordset creates the temporary file in the same folder as the destination file that was used to save the recordset.

To download Microsoft Data Access Components (MDAC) 2.8, visit the following Microsoft Web site:
Note Microsoft recommends that you test your application with MDAC 2.8 in a test environment first, to make sure that you are aware of any compatibility issues before you decide to use (or upgrade to) the MDAC 2.8 version.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected in MDAC 2.8.
By the way, the preview of this message is not working.

SQL-select and insert

BrockVnm14-Jan-05 9:38

BrockVnm

14-Jan-05 9:38

Hey everyone,
I have not been able to figure this out. I have been searching the web to no avail. So here is my situation. I have a table that I want to insert a bunch of data into. The problem is I want to add data from a bunch of tables into it as well as inputing some values. I am putting the query into my code and excuting it that way. Basically here is what I am looking to do:

Insert into combinedTables
select table1.a, table2.a
from table1, table2
where table1.c = table2.c

I also have some variables:
INSERT INTO combinedTable(x)
values("userName")

Is there a way I can do this in one SQL statement?? I hope my question is clear...thanks to all for your help!!!

There are 10 kinds of people in this world. Those who understand binary and those who don't.

We shouldn't assume something's debugged just because everyone in the whole world has access to the source code.

-Dr_X-14-Jan-05 14:17

Re: SQL-select and insert

14-Jan-05 14:17

Not sure what your datasource is but chances are you can only insert into one table at a time. To ensure all rows are inserted into all tables use a transaction.

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Anonymous14-Jan-05 15:07

Anonymous

14-Jan-05 15:07

Thanks & Appreciate you Micheal

Now the query is working.

Sqlserver -gui ?

kaiowa5114-Jan-05 8:39

kaiowa51

14-Jan-05 8:39

Hi,anyone know if there exists a sql-server database gui for linux?

I have searched but nothing free found.

DataTable with nested Child Tables??

work_to_live13-Jan-05 8:09

work_to_live

13-Jan-05 8:09

Let's assume I have three tables, where Table1 is the topmost table, Table2 is a child relation to Table1 and Table3 is a child relation to Table2. I'd like to get all rows in Table1 where a column in Table3 is a particular value. This is fairly straight forward with an SQL query, but I can't figure out how to do it with DataTables/DataSets. Any suggestions?

The only solution I've been able to come up with involves getting every row in Table1, and then GetChildRows twice to get to Table3, and testing the column of interest in Table3...

foreach (DataRow row in Table1.Rows)
{
if (row.GetChildRows("Table1-Table2")[0].GetChildRows("Table2-Table3")[0]["ColumnOfInterest"] == something)
{
This is one of the desired rows, do something with it
}
}

Seems very cumbersomb. You'd think there'd be a way to execute a single "query" that would return the desired set of rows...

Re: DataTable with nested Child Tables??

Rob Graham13-Jan-05 16:09

Rob Graham

13-Jan-05 16:09

How about reversing the process, and preselecting just the columns of interest

DataRow[] interestingRows = Table3.Select("columnOfInterest = something");
foreach(DataRow r1 in interestingRows)
{
DataRow r2 = r1.GetParentRow("Tabel2-Table3").GetParentRow("Table1-Table2");
// do something with this if not null
}

Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg

Re: DataTable with nested Child Tables??

work_to_live14-Jan-05 4:56

work_to_live

14-Jan-05 4:56

Thanks... That would definitely do the trick, and I'll play around with it, but to my question, is there some way to perform this operation with a single Select "like" call? Maybe there isn't a way to do this, and that's fine, I'll continue working with nested foreach constructs, but I keep thinking the disconnected DataSet should be able to perform operations that are possible with SQL. As I'm sure you're aware, the SQL equilalent of what I'm trying to do with the DataSet is as follows...

SELECT * FROM TABLE1,TABLE2,TABLE3 WHERE TABLE1.Table2Link=TABLE2.Table1Link AND TABLE2.Table3Link=TABLE3.Table2Link AND TABLE3.ColumnOfInterest=something

Database schema

webhay13-Jan-05 1:15

webhay

13-Jan-05 1:15

Hello all,

I made a database in my server but when i generat a script for this database and run it in another server the Digram dosnt apper in the second server.How can if i change relation in the first digarm make this changes in the second database

note: the database filled with some data

hay

Re: Database schema

Colin Angus Mackay13-Jan-05 11:46

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

13-Jan-05 11:46

webhay wrote:
I made a database in my server but when i generat a script for this database and run it in another server the Digram dosnt apper in the second server

The diagrams are not scripted as they will change dynamically with the data model in the database. The diagrams are just a graphical representation of the tables in the database and their relationships.

webhay wrote:
How can if i change relation in the first digarm make this changes in the second database

Script the change. Don't use Enterprise manager - learn how to make these changes in script so you can test the changes on a development server, make sure that they work without breaking the database, then apply the script (after fixing any bugs) to the live system.

Generally it is a good idea to take a backup of the live system and restore the backup on a development box to test your change-script on so that the development environment is as close to the live system as possible.

Does this help?

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

i couldn't see Databases in SQL server

BSRK13-Jan-05 0:17

BSRK

13-Jan-05 0:17

I am working on Windows 2000 professional. I have installed MSDE 2000 and also SQL server 2000 (Client components only).

But when i go through the Enterprise Manager of SQL Server, it is not identifying any server. It is displaying something like:
Server is not found or Access denied. Connection Open

when i tried to run in command prompt also (C:/>osql ....) it is saying connection open error.

can anyone please help?
thank you

Colin Angus Mackay13-Jan-05 1:02

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

13-Jan-05 1:02

BSRK wrote:
Server is not found or Access denied

A couple of things to check:

Is the SQL Server service running?

Are you connecting with the correct user namd and password?

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

BSRK13-Jan-05 1:28

BSRK

13-Jan-05 1:28

I have checked from Administrative Tools -> Services, that sQL server service is running.
still i have this problem.

Can you tell me whether i need to install service pack SP3a in addition with MSDE 2000?

Colin Angus Mackay13-Jan-05 2:07

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

13-Jan-05 2:07

BSRK wrote:
Can you tell me whether i need to install service pack SP3a in addition with MSDE 2000?

Installing Service Pack 3a is generally a good idea anyway because, IIRC, it contains the security fix for the Slammer worm.

BSRK wrote:
I have checked from Administrative Tools -> Services, that sQL server service is running.

So, are you attempting to log in using the correct credentials? Have you typed the user name and password correctly? Did you set it to use Windows Authentication? and if yes, are you trying to connect using the same account that it was installed with (or with an account that has been granted access)?

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

we cannot use MSDE & Enterprise Manager

BSRK13-Jan-05 2:39

BSRK

13-Jan-05 2:39

I think we cannot use MSDE & Enterprise Manager in conjunction. i got similar info from the below link.

http://communities2.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=FAQ&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.msde&p=1&tid=2b4fe8b6-3d8d-4353-8f91-31c0f60bba14[^]

Re: we cannot use MSDE & Enterprise Manager

Colin Angus Mackay13-Jan-05 2:48

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

13-Jan-05 2:48

BSRK wrote:
I think we cannot use MSDE & Enterprise Manager in conjunction

Curious - I've never had a problem combining using Enterprise Manager with an MSDE database.

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

Re: we cannot use MSDE & Enterprise Manager

Michael P Butler13-Jan-05 8:42

Michael P Butler

13-Jan-05 8:42

MSDE and Enterprise manager work fine together. There is a grey area when it comes to the licencing of the software. But there is no technical reason why Enterprise Manager can't see your MSDE database.

Michael

CP Blog [^] Development Blog [^]