Ive been given a query analizer for MSDE are there others

Greetings,

I've been sent an alternative "Query Analizer" thingy to play with and it seems to do the job - but it ain't my area.

I've posted the tool in a free public place for a short time:
http://www.adoanywhere.com/members/yeohray/7C1_SQLTool.zip

But, for the benefit of people reding this from the archives, please follow
the registerable author link [ http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1&TPN=1 ]

Apart from needing documentation (we all know about that old scenario) it seems good.

It didn't waste my time (and I wouldn't waste yours I hope). Could I have your comments please ?

Also, I'm looking for other tools that do the same as this one - so I can compare functionality. Even without try the freebie, any links please ?

TIAyou've "been sent"? by whom? where's it from? did you virus-check it?

okay, i tried it (yes, i took a chance, i'm stupid that way)

it actually works nicely -- a damn site better at submitting queries to MSDE than the way i was doing it up till now (via a pass-through query in access)

it's cute, too|||Good point about the security!

I'll address that issue with the author, virus scanners here should have picked them up though.

I got it from a chap I was assisting with some Delphi knowledge.

Thanks for checking it out, I didn't understand all of it myself.|||so, this isn't yours, then? i mean, it isn't adoanywhere's? don't they have something similar? how come you posted it on their forum? do you work for adoanywhere? do they mind you mentioning what appears to be a competing product? just what does their product do, anyway? and where did you say you got this little Delphi app, in case it screws somethying up on my machine and i feel like visiting the said chap at his place of residence with a clue-by-four?

:D|||Ther author was having problems with bugs in Delphi and retrieval of stats & plans through ADO. With adoanywhere forum help he eventually got it resolved and posted the tool for us to play with. I've used it witout problems since it was uploaded but I'll make it clearer in future posts and on the forum that all these third party apps are at downloaders risk.

I work for adoanwyhere - I wrote adoanywhere, and it's no problem adding other tools to forum if you know of any. Glad to 'ave 'em.

Mike (adoanywhere)|||okay, thanks for the clarification

by the way, does your product allow me to run queries against MSDE too?

i should mention i haven't a clue what ADO is (and no particular desire to find out)|||Yeah, you can query MSDE with adoanywhere.

ADO is an interface (bit like ODBC), so you can query databases that have and ADO "driver" - called provider in ado lingo.

Most databses have an ADO provider. Problem is though that ADO has loads of properties are that may or may not be available at any give time in your application.

Adoanywhere lets you fiddle with the ado settings. So although you can query data with it, it does more complex stuff at its heart.

I generally use adoanywhere for querying & checking ado properties - then switch to proprietry tools (like query analizer and Enterprise manager) for database management stuff. Horses for courses as they say.

All the best, Mike.sql

Issues copying data to linked server

I'm having an issue with a job that calls a stored procedure. The SP is fairly complicated, but basically creates a temp table, then inserts the records from the temp table into a table on a linked server (via VPN). The basic premise here is that I'm trying to update a web-accessible server from the production server. The funny thing is that the process works correctly for 4 out of 5 companies (company is the only parameter fed to the SP). The company that fails is by far the largest, but the process has worked in the past.

I'm not strong with all the tools available in SQL Server that might help me figure out what's going wrong. In the SQL Server Agent, it simply tells me that the job failed. If I use Query Analyzer and run the SP in Debug mode (if I'm stating that correctly), I get this error for any company I try it for, even companies that run fine (line 51 is the DELETE line):

Server: Msg 7391, Level 16, State 1, Procedure procStmtDataToReno, Line 51
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

So I suppose my long-winded question is, how can I determine why this SP fails for the one company? If relevant, the job is simply:

EXEC procStmtDataToReno 'B'

and some snippets of the SP:

CREATE PROCEDURE [dbo].[procStmtDataToReno]

@.CoCode as varchar(2)

AS

--various declarations and such

DELETE
FROM [10.1.1.4].OnlineAR.dbo.StatementData
WHERE CompanyID = @.CoCode AND (Line1Date < Convert(varchar(10), GETDATE() - 60, 101) OR Line1Date >= Convert(varchar(10), GETDATE() - 5, 101) )

--code to build temp table

INSERT INTO [10.1.1.4].OnlineAR.dbo.StatementData
SELECT * FROM #Statement
GO

TIAI normally would create a stored procedure on the remote server side and call it via openquery() or sp_executesql. That is the *fastest* way to do and DML.

Anyway, I suggest you take a look at the following article for guidance on resolving 7391 error.

http://support.microsoft.com/kb/306212|||Thanks for that. Because some companies worked, my suspicion all along was that it was "data" related rather than "process" related. I finally stumbled into the answer late last night. I had tried changing the SP to simply select the records, and it ran fine from Query Analyzer. I changed the SP back to an INSERT and executed it again from QA, and it failed, but I finally got a message that made sense:

Server: Msg 8152, Level 16, State 4, Procedure procStmtDataToReno, Line 389
String or binary data would be truncated.
The statement has been terminated.

My problem was as simple as tracking down the field that was wider on the source side than it was on the destination side. The process now works fine. I just didnt know what tool to use to get better info on what was going wrong.

I am interested in your recommendation to set it up on the remote side. I would have thought it was better to have the SP on the source side, so it would only have to transfer the results of the process over the wire. If the SP were on the destination side, wouldnt it have to bring all the data across to work with?|||yes, you have to pass the data to the remote server in order for it to be processed.

e.g.

@.sql='exec my_sp ' + @.inputvar

exec linked.db..sp_executesql @.sql

issue with update

Hello,

I have the following PERFORMANCE issue:

I have created a job that fill and update a database from a source db with same structure.

The problem is that at the beginning performance was good, now the source db and destination db are very large and time to import/update is to big.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk.

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

How can I increase performance and reduce time of importing?

Thank

You can try the replication service instead of doing it manually, it will be faster than the DTS job.

If you want to it manually do the following operations,

1. Delete

2. Update

3. Insert

|||

Hello,

thank, but at the beginning we tried with replication but there are some problems, so we decides to use other methods such as the above one.

Now, I am thinking to create a trigger on each big tables to identify what record is inserted new or updated. The trigger populate a table that represent only these pk. In this way I can make a subset of record that are only new or updated.

Issue with job run setup in Enterprise Manager

Hello All,
I have a job that consist of over 120 steps. I was able to setup it in Enter
prise Manager and run it. However, the job history can only show display 100
job steps 20 other job steps were missing. Is there a limit set on sysjobhi
story table or am I looking
at the wrong table? Please advice.
Thanks.
DavidThat is very interesting, and I do not know if it is related to the number
of histories per job, but maybe worth a try...
In SEM right click SQL Agent and go to the tab with Job history setup and
try both checking unlimited, and/or max histories per job to something like
300... See if that helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:A58F9373-EFBA-4426-A2CB-2E14328B18B9@.microsoft.com...
> Hello All,
> I have a job that consist of over 120 steps. I was able to setup it in
Enterprise Manager and run it. However, the job history can only show
display 100 job steps 20 other job steps were missing. Is there a limit set
on sysjobhistory table or am I looking at the wrong table? Please advice.
> Thanks.
> David

Issue with job run setup in Enterprise Manager

Hello All,
I have a job that consist of over 120 steps. I was able to setup it in Enterprise Manager and run it. However, the job history can only show display 100 job steps 20 other job steps were missing. Is there a limit set on sysjobhistory table or am I looking
at the wrong table? Please advice.
Thanks.
David
That is very interesting, and I do not know if it is related to the number
of histories per job, but maybe worth a try...
In SEM right click SQL Agent and go to the tab with Job history setup and
try both checking unlimited, and/or max histories per job to something like
300... See if that helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:A58F9373-EFBA-4426-A2CB-2E14328B18B9@.microsoft.com...
> Hello All,
> I have a job that consist of over 120 steps. I was able to setup it in
Enterprise Manager and run it. However, the job history can only show
display 100 job steps 20 other job steps were missing. Is there a limit set
on sysjobhistory table or am I looking at the wrong table? Please advice.
> Thanks.
> David

Issue with job run setup in Enterprise Manager

Hello All
I have a job that consist of over 120 steps. I was able to setup it in Enterprise Manager and run it. However, the job history can only show display 100 job steps 20 other job steps were missing. Is there a limit set on sysjobhistory table or am I looking at the wrong table? Please advice
Thanks
DavidThat is very interesting, and I do not know if it is related to the number
of histories per job, but maybe worth a try...
In SEM right click SQL Agent and go to the tab with Job history setup and
try both checking unlimited, and/or max histories per job to something like
300... See if that helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:A58F9373-EFBA-4426-A2CB-2E14328B18B9@.microsoft.com...
> Hello All,
> I have a job that consist of over 120 steps. I was able to setup it in
Enterprise Manager and run it. However, the job history can only show
display 100 job steps 20 other job steps were missing. Is there a limit set
on sysjobhistory table or am I looking at the wrong table? Please advice.
> Thanks.
> David

Issue during update and insert (trigger problems)

Hello,

I have created a job that fill and update a database from a source db with same structure.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

I tried replication, but it does not work on that db.

So I created a trigger for each table where new insert or updated row must be detected. The PKs are saved on tables.

The problem is that when I run the application it hold-on, when triggers are disabled the application run fine.

How can I use trigger on several tables without affectrunning application?

Thank

can you post your triggers?|||can you post your triggers?
|||

That's the piece of code:

INSERT INTO TempPkDB.dbo.tabella (pk) SELECT i.pk FROM inserted i

WHERE NOT EXISTS (SELECT pk FROM TempPkDB.dbo.tabella t

WHERE t.pk = i.pk)

This code is applied to each tables where tables must be moved to the destination table.

thank