Database

Re: Import diagram to SQL-server 2000

6-Sep-02 1:47

Is there any other easy way to import a diagram to an SQL-server 2000 DB than doing a restore of a backup? (Do not want to do a restore...)

notadood6-Sep-02 2:56

notadood

6-Sep-02 2:56

Yes it is pretty simple. All database diagrams are stored in the table dtproperties. This link will show you the query you need to run.

http://www.mssqlcity.com/FAQ/TranMove/MoveDiag.htm

Re: Import diagram to SQL-server 2000

Rulle6-Sep-02 6:57

Identity Seed/Increment Customize

6-Sep-02 6:57

Hmm

I've tried both examples now and they all resulted in that a copy was created of an already existing diagram with exactly the same name(!). When I deleted one of the diagrams, SQL Enterprise Manager crashed every time I tried to look at a diagram...

insert into
Example..dtproperties (objectid, value, lvalue, version, property)
select
objectid, value, lvalue, version, property
from
Northwind..dtproperties
where objectid =
(select objectid from dtproperties where value = 'Relationships')

You have to add "Northwind.." in the last statement.

I don't see how to transfer diagrams from different servers, only between databases on the same server.
Have I missed something?

Automation Error

bugbuster5-Sep-02 11:35

bugbuster

5-Sep-02 11:35

Hi everyone,

I'm having trouble with an Access query.
My program does one connection to the db and several queries, but only the first one is going through, the second one throws Error '-2147417851 (8001015)' which is an automation error.

I'v searched for the cause and it says it's because the server is throwing an exception.

I read in Microsoft's support site and found out that I need to do late binding, but I believe that's what I'm doing.

The code is this:

<br />
int CDBAccess::cargaConfig(char *pszComponente, CConfig *poConfig) {<br />
<br />
	_RecordsetPtr         rs;<br />
	HRESULT               hr;<br />
	_variant_t            conn;<br />
	char                  szQuery[256];<br />
	char                  szTemp[30];<br />
	FieldPtr              campo;<br />
	<br />
	try {<br />
		hr = rs.CreateInstance("ADODB.Recordset");<br />
		if(FAILED(hr)) {<br />
			m_nError = 506;//error al crear rs<br />
			return m_nError;<br />
		}<br />
		conn = (IDispatch*) m_Conec;<br />
	}<br />
	catch(_com_error & err) {<br />
		MessageBox(NULL,(LPCTSTR) err.Description(), "CargaConfig", MB_OK);<br />
		return -1;<br />
	}<br />
	//se hace el query<br />
	sprintf(szQuery, <br />
			"select * from configuracion where componente = '%s'", pszComponente);<br />
	_bstr_t qry(szQuery);<br />
	FILE *fd;<br />
	fd = fopen("C:\\usr\\sia\\query.txt","w");<br />
	fprintf(fd,"%s",(char *)qry);<br />
	fclose(fd);<br />
	//MessageBox(NULL,(char *)qry, "cargaConfig",0);<br />
	try {<br />
		hr = rs->Open(qry, conn, adOpenStatic, adLockOptimistic, adCmdText);<br />
		if(FAILED(hr)) {<br />
			m_nError = 507;//error al abrir rs<br />
			return m_nError;<br />
		}<br />
	}<br />
	catch(_com_error & err) {<br />
		MessageBox(NULL,(LPCTSTR) err.Description(), "CargaConfig", MB_OK);<br />
		return -1;<br />
	}<br />
<br />
	try {<br />
		//se leen los datos<br />
		campo = rs->Fields->Item["timeout"];<br />
		_bstr_t to = campo->Value;<br />
		sprintf(szTemp, "%s", (char *)to);<br />
		poConfig->iTimeout = atoi(szTemp);<br />
<br />
		campo = rs->Fields->Item["canal_comunicacion"];<br />
		_bstr_t can = campo->Value;<br />
		sprintf(poConfig->szCanalComm, "%s", (char *)can);<br />
<br />
		campo = rs->Fields->Item["id_canal"];<br />
		_bstr_t idcan = campo->Value;<br />
		sprintf(poConfig->szIdCanal, "%s", (char *)idcan);<br />
<br />
		campo = rs->Fields->Item["objeto"];<br />
		_bstr_t obj = campo->Value;<br />
		sprintf(poConfig->szObjeto, "%s", (char *)obj);<br />
<br />
		campo = rs->Fields->Item["prioridad"];<br />
		_bstr_t pri = campo->Value;<br />
		sprintf(poConfig->szPriori, "%s", (char *)pri);<br />
<br />
		hr = rs->Close();<br />
		if(FAILED(hr)) {<br />
			m_nError = 508;//error al cerrar rs<br />
			return m_nError;<br />
		}<br />
		//rs = NULL;<br />
	}<br />
	catch(_com_error & err) {<br />
		MessageBox(NULL,(LPCTSTR)err.Description(),"CargaConfig",0);<br />
		return -1;<br />
	}<br />
	return 0;<br />
}<br />

Can anyone tell me what might be wrong??

Greetings from México

Re: Automation Error

notadood6-Sep-02 2:53

notadood

6-Sep-02 2:53

Is this the second query that is failing? It looks like your syntax is for SQL Server, using wildcard %. If you are trying to wildcard it, and you are using Access your query needs to say:

select * from configuracion where componente like '*s'

Otherwise the query will never return any records.

Robby5-Sep-02 5:25

Robby

5-Sep-02 5:25

Can someone give me an example of how I can customize by PK? Instead of just a number incrememnted by 1 for every row, I want to change it to NUR1, NUR2, NUR3, etc...basically add a NUS in front of it. How can I accomplish this? I am going to have a different ID for different tables, this one happens to be "New User Request", hence the NUR1, NUR2, NUR3, etc. Any thoughts? Someone mentioned an "INSTEAD OF" trigger, but didn't offer any further details on "HOW" to do it. Thanks

Re: Identity Seed/Increment Customize

Jon Hulatt6-Sep-02 3:55

Jon Hulatt

6-Sep-02 3:55

Maintaing all your PK columns with the same name is extremely bad practise. I would recommend naming the pk as TableNameId.

eg...

CREATE TABLE dbo.Jobs (
JobId int not null identity (1,1),
Jobtitle...
)

it's a nightmare when you start doing joins if all your keys are identically named.

STL is a religeon. Enquiries to Reverend Christian Graus

LEFT OUTER JOIN leaves rows

Knappen4-Sep-02 10:18

Knappen

4-Sep-02 10:18

I have to tables
1: Main table
2: Text table
the relation is one to many.
If there isent any matches in the text-table i want the post from the
main-table

My query: (Swedish columname´s)
------------------------------------
SELECT arrProdKat.prod,arrProdKat.lev,arrProdKatText.sprak,
rubrik =CASE
WHEN(arrProdKatText.sprak is null) THEN arrProdKat.rubrik
ELSE arrProdKatText.rubrik END,
info =CASE
WHEN(arrProdKatText.sprak is null) THEN arrProdKat.info
ELSE arrProdKatText.info END

FROM arrProdKat
LEFT OUTER JOIN arrProdKatText ON
arrProdKat.arr=arrProdKatText.arr AND
arrProdKat.lev=arrProdKatText.lev AND
arrProdKat.prod=arrProdKatText.prod AND
arrProdKat.arrKat=arrProdKatText.arrKat
WHERE arrProdKat.arr=@arr AND arrProdKat.arrKat=@arrKat
AND(arrProdKatText.sprak=@sprak OR arrProdKatText.sprak is null)

-------------------------------------------
The result dont find the post in the main-table if ther are
child-posts in the text-table, even if they dont equals @sprak.

a sql query question

freeworldbird4-Sep-02 7:54

4-Sep-02 7:54

i want to use ,such as :
select t1.a,sum(t1.b),sum(t2.b) from
(Select a,b from t1) t1 left outer join
(select a,b from t2) t2 on t1.a=t2.a
group by t1.a

some rows exist in t1,but it not exist in t2
so in the result the sum(t2.b) is NULL

my database is Microsoft Access,so can't use function isnull(t2.b,0),---the function isnull(column,replacevalue) only used in Microsoft SQL Server
How can i do?

but can't use such as :
create table table1(
column2 float DEFAULT 0,

Mazdak4-Sep-02 7:59

Mazdak

4-Sep-02 7:59

use select count(b) from table1 first.It retreive the number of rows in that table.So you can figure out if it is 0 or not.I have never used it in Access but it works in SQL Server.

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

freeworldbird4-Sep-02 8:07

4-Sep-02 8:07

sorry , i don't know what's you meaning yet.
can you write a demo use sql?

Mazdak4-Sep-02 23:24

Mazdak

4-Sep-02 23:24

First you run this query:

select count(columnname) from tablename

It will return only one record and its number of rows in that column and table,so if there is no row in that table the returning record value will be 0 so there is no problem if it is null or not,if it is bigger that 0 then you can run your query.

Clear?

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Nick Parker4-Sep-02 9:31

Nick Parker

4-Sep-02 9:31

Not exactly sure what it is you are trying to find, if you only want to find the entries in one table that aren't in another then you simply need to do an outer join against the table like such:

SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.a = t2.a

This should grab everything that is in table1 that is not in table2 where there is a relation between t1 and t2 on field a. Does this help, or are you trying to do something else?

Nick Parker

freeworldbird4-Sep-02 16:35

4-Sep-02 16:35

in fact i used it,such as :

select t1.a,sum(t1.b-t2.b) from
(Select a,b from t1) t1 left outer join
(select a,b from t2) t2 on t1.a=t2.a
group by t1.a

so ,the sum(t1.b-t2.b) will return NULL as long as t2.b is NULL.

freeworldbird5-Sep-02 16:08

Datetime in SQL server<>ADO

5-Sep-02 16:08

of cause it can work,table define such as

create table t1(
a varchar(10),
b float
);

create table t2(
a varchar(10),
b float
);

can you test in your computer?

Rulle3-Sep-02 23:01

Re: Datetime in SQL server<>ADO

3-Sep-02 23:01

Hi!

I use the Datetime dataype in SQL-Server 2000 to store date and time eg "2002-09-04 09:18:20". When I insert data in through VB6.0 and ADO 2.6 i set the in-parameter to an adDBTime and it seems to work fine.

Could there be problems in the future and/or is there a better datatype in ADO for this case?

Jon Hulatt4-Sep-02 5:46

Jon Hulatt

4-Sep-02 5:46

adDBTime was designed for that purpose. Who knows what could happen in the future though.

You can use adVarChar too, if you like

STL is a religeon. Enquiries to Reverend Christian Graus

Re: Datetime in SQL server<>ADO

Rulle4-Sep-02 20:54