query equivalent: databases

Showing posts with label databases. Show all posts

Wednesday, March 28, 2012

Loosing ODBC connection

Hi all,

We recently converted an application from accecc97 to access2003.
We used to have 2 databases : 1 with all and only the data (axelD.mbd), 1 with all the forms, query's, ... (axelP.mdb)

We replaced the data (axelD.mdb) with an MSDE database, through UPSIZE and so on. That all worked fine.
This database is placed on the dataserver (DATA1), the converted programm is on the FRONT2 server (FRONT1 stil usess access97 until conversion is totally ready).

We are able to connect to the MSDE by using ADODB recordsets.
We are also perfect able to link the MSDEtables in our axelP.mbd database

We started to test some heavier parts of our application and started to get the following error:

"ERROR 3151"
"ODBC--connection to 'JOROSOFT' failed"

Nothing more.

This happened in a quite complex routine, whitch updates a lot of records, several times and so on.
We could reproduce the same error when executing the following code:

Public Function TestBestellingen()
Dim sql As String
Dim intervalset As DAO.Recordset
Dim planset As DAO.Recordset
Dim tellerke

x = Opendatabases()
tellerke = 0
sql = "Select bestelnummer from vkpbestellijn where bestelnummer > 100000"
Set planset = Db_AxelP.OpenRecordset(sql, dbOpenDynaset)
Do While Not planset.EOF
tellerke = tellerke + 1
Debug.Print tellerke & " - " & planset!Bestelnummer & " --> " & IsKlantStof(planset!Bestelnummer)
planset.MoveNext
Loop
End Function

Function IsKlantStof(p_bestelnummer)
Dim sql As String, rst As DAO.Recordset
Dim hulp_klantstof As Boolean

hulp_klantstof = False
x = Opendatabases()
sql = "select klantstof from vkpbestellijn where bestelnummer = " & p_bestelnummer
Set rst = Db_AxelP.OpenRecordset(sql, dbOpenSnapshot)

With rst
Do While Not .EOF
If !KlantStof = True Then
hulp_klantstof = True
.MoveLast
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
IsKlantStof = hulp_klantstof
End Function

Function Opendatabases()
If Db_AxelP Is Nothing Then
Set Db_AxelP = DBEngine(0)(0)
End If
End Function
I know this code makes no sence in a real-time environment, but it was to reproduce the error.
This code basically runs trough a 30.000 records and does a little check for certain fields
After 1981 records we get the above error.
(sometimes after 1979 or 1980 or 1982 records)

1977 - 100859 --> True
1978 - 100860 --> True
1979 - 100860 --> False
1980 - 100861 --> True
1981 - 100861 --> True

"vkpbestellijn" is a table that is linked in the AxelP.mdb trough an ODBC connection.
We get the same error (and after 1981 times) if I change the where to a totally other range of "bestelnummer"

I have installed the latest patches of Jet 4.0, MSDE-server, ...

I've been searching the internet for abour 3 days now, and just cant even find the smallest clue what could make this happen.

I hope someone here has an idea.
If you need more details, just ask.

tia,
Axel.

P.S. I'm from Belgium, so my english aint "correct to the point".MSDE auto closes when the last user disconnects.

ALTER DATABASE MyDB AUTO_CLOSE OFF might take care of this.|||I tried that, but it didn't changed a single thing.

The exact same problem still occurs. :eek: :eek:

Getting desperate here. :o|||Is there no one around who recognises the problem or came across that problem?

I've tried playing with the Query governor, but that didn't changed a single thing.

The strange thing is that when i try the same code on a identically server that is in use by users, i have it less frequent.
It looks like the MSDE server cant keep up with all the quesry's executed. And when the server has more users working, it has enough time to release some things.

does that makes sence top anyone?

Monday, March 26, 2012

looping through results of a OSQL query

Hello,

I have the following problem.

I wrote a batch file that runs a sql script on SQLServer 2000. This
script must be executed on several databases. This works fine.

The problem is that in my database is a table that holds a databases i
have to update with this script. What i want is run a query with osql
in my batchfile to retrieve this these records so I can loop through
them an run the script for those databases.

I managed to execute the query that return the records and write them
to a textfile.

What i want is store the results in some kind of a resultset so i can
loop through them in my batchfile.

Anyone have the solution

Thanks in advance

Patrickpatrick (pveechoud@.xs4all.nl) writes:
> I have the following problem.
> I wrote a batch file that runs a sql script on SQLServer 2000. This
> script must be executed on several databases. This works fine.
> The problem is that in my database is a table that holds a databases i
> have to update with this script. What i want is run a query with osql
> in my batchfile to retrieve this these records so I can loop through
> them an run the script for those databases.
> I managed to execute the query that return the records and write them
> to a textfile.
> What i want is store the results in some kind of a resultset so i can
> loop through them in my batchfile.

A simple-minded approach is to write the query so that it generates
a complete OSQL command and you write that to the file. Then you
execute the file.

But it would probably better to write this in some script language
like VBscript or Perl from which you can submit queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Looping through databases in stored proc

I am trying to loop through the databases on a server (SQL 2000) and
dynamically run sp_helpfile against each database on the server. Of course
that means I need to store the name of the database as a variable or
parameter.
When I use the following code I am told "a USE database statement is not
allowed in a procedure or trigger.":
use @.dbname
go
exec sp_helpfile
When I use the following code I am told "Incorrect syntax near '.'"
exec @.dbname..sp_helpfile
Any suggestions?
Message posted via http://www.webservertalk.comLook at the "Undocumented" stored procedure sp_MSforeachdb in the Master
database.
Don't forget, use at your own risk. Since it is undocumented, it may change
or disappear in the next version or service pack. If this is a one-time
thing, go ahead, but don't use in production code.
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com|||You'll need to use dynamic SQL. Its complaining about the variable database
name in your EXEC statement. Check out this excellent article regarding
dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
Just curious... What are you using this info for? Some sort of SQL admin.
application?
Paul
"Robert Richards via webservertalk.com" wrote:

> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Try,
use northwind
go
create table #t (
dbn sysname,
fileid int,
filen sysname,
fileg sysname null,
size_ varchar(15),
maxsize_ varchar(15),
growth varchar(15),
usage varchar(128)
)
declare @.sql nvarchar(4000)
declare @.db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]
open databases_cursor
while 1 = 1
begin
fetch next from databases_cursor into @.db
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'use [' + @.db + N'] execute sp_helpfile'
insert into #t
execute sp_executesql @.sql
end
close databases_cursor
deallocate databases_cursor
select
*
from
#t
order by
dbn, fileid
drop table #t
go
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Robert Richards via webservertalk.com wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of
> course that means I need to store the name of the database as a
> variable or parameter.
> When I use the following code I am told "a USE database statement is
> not allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
>
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Yuu could try declaring and executing a string within your stored procedure
(I assume that you are using a cursor):
declare @.str varchar(255)
set @.str = 'exec ' + @.dbname + '..sp_helpfile'
--optional
select @.str AS TheStringToExecute
exec (@.str)
or you can use the undocumented stored procedure that is shown below:
EXEC sp_Msforeachdb 'PRINT (''?''); EXEC sp_helpfile'
Keith
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com

Friday, March 23, 2012

loop to run 'Create Trigger' script?

I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@.TriggerName' error.

if exists (select * from sysobjects where id =
object_id (N'dbo.tgUsersAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgUsersAudit
go

CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = tblUsers

..................from here the code is the same for all

You can add to this script.

set nocount on
declare @.cursor cursor,
@.triggerName sysname

set @.cursor = cursor for (select name
from sys.triggers)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.triggerName
if @.@.fetch_status <> 0
break

select @.triggername

end

If you are using 2000, use:

select name
from sysobjects
where xtype = 'tr'

for the select statement...

|||

The triggers don't yet exist, so they won't be in sysobjects.

I tried putting the 'create trigger' code in a sp, with table name and trigger name as parameters but get a syntax error when using a variable.

|||

Just to clarify based on a comment from a related thread-

"On a related note, this is really not a very good approach. You should create a different trigger for every table (I like the approach of creating the triggers automatically using the loop, but not like this, as it will be problematic and slow)."

I am creating different triggers for each table, but they are created by a common script that dynamically determines the column names, etc. I would like a loop to run the create script for each table, as opposed to editing the table name and trigger name and running the script manually for each table.

|||

Sorry, I misunderstood :) Will this work for you:

set nocount on
declare @.cursor cursor,
@.tableName sysname

set @.cursor = cursor for (select name
from sys.tables)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.tableName
if @.@.fetch_status <> 0
break

select 'if exists (select * from sysobjects where id =
object_id (N''dbo.tg' + @.tableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)
drop trigger dbo.tg' + @.tableName + 'Audit
go

CREATE TRIGGER tg' + @.tableName + 'Audit on ' + @.tableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.tableName + '''

<more code here>
'

end

|||Yes, this is along the lines of what I want to do- however... my code is just too ugly and I'm struggling with getting the string all to concatenate correctly (can't get the quotes right and don't have time to play with it right now). I guess for now I'll just have to run it separately for each table- boo hoo!|||

Though you have said you want to do it one table by one table, I am still put this code here incase it can save you some energy.

select 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + name + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + name + 'Audit
go'
from sys.tables

SELECT '
CREATE TRIGGER tg' + name + 'Audit on ' + name + ' FOR insert, update, delete
AS
DECLARE name varchar(128)
SET name = ''' + name + '''
' + char(13) +
'<more code here>
' + char(13) +
char(13)
from sys.tables

This will generate two batch. First one clean existing trigger. The scaond one has the head part of each trigger. Replace "<more code here>" with your common code. That will give you the excutable batch to do the job.

|||

I really want to get it to work, but am short of time and just can't seem to get it right.

I would generate the table names from a populated cursor vs from sysobjects because not all of the tables in the db should be audited by this trigger, so I was thinking something like the following.

set nocount on
declare @.cursor cursor
declare @.tblTableNames table (TableName varchar(30))

insert into @.tblTableNames (TableName) values ('tblCompanies')
insert into @.tblTableNames (TableName) values ('tblDepartments')
insert into @.tblTableNames (TableName) values ('tblManagementLevels').......

set @.cursor = cursor for (select TableName
from @.tblTableNames)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.tableName

if @.@.fetch_status <> 0
break

end

close cursor
deallocate cursor

Also, something I am having trouble with is that my <create script code here.....> is full of somewhat complex code, embedded strings, etc. Posted below for your reading enjoyment...

if exists (select * from sysobjects where id =
object_id (N'dbo.tgDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgDepartmentsAudit
go

CREATE TRIGGER tgDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'

DECLARE @.fieldname varchar(128),
@.pkJoinClause varchar(1000),
@.sql nvarchar(2000),
@.UpdateDate varchar(21),
@.UserName varchar(128),
@.TriggerType nchar(1),
@.rowId int,
@.maxRowId int,
@.str1 varchar(100),
@.str2 varchar(100)

SET @.UserName = SYSTEM_USER
SET @.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

--detemine type of trigger
IF exists(select * FROM inserted) AND exists(select * from deleted)
SET @.TriggerType = 'U'
else
IF exists(select * FROM inserted)
SET @.TriggerType = 'I'
ELSE
SET @.TriggerType = 'D'

--get all Column names for table
SELECT c1.COLUMN_NAME as colName, c1.ORDINAL_POSITION as RowId into #tblFieldNames
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c1 ON t1.TABLE_NAME = c1.TABLE_NAME
WHERE t1.TABLE_NAME = @.tableName

-- Get PRIMARY KEY columns
select c.COLUMN_NAME as colName INTO #primaryKeyFields
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on (c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
WHERE pk.TABLE_NAME = @.tableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'

--Create Join clause for primary key field(s)
SELECT @.pkJoinClause = coalesce(@.pkJoinClause + ' and', ' on') + ' i.' + PKF.colName + ' = d.' + PKF.colName
FROM #primaryKeyFields PKF

--Throw error if no primary key
IF @.pkJoinClause IS NULL
BEGIN
raiserror('no PK ON TABLE %s', 16, -1, @.TableName)
RETURN
END

-
-- the 'inserted' and 'deleted' tables have limitations, dump to temp tables for greater control
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

--get number of columns
select
@.rowId = min(RowId),
@.MaxRowId = max(RowId)
from #tblFieldNames

-- Loop through fields and build Sql string
while @.RowId <= @.MaxRowId
BEGIN
SELECT @.fieldname = colName FROM #tblFieldNames WHERE RowId = @.RowId

SELECT @.sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @.sql = @.sql + ' select ''' + @.TableName + ''''
SELECT @.sql = @.sql + ',''' + @.TriggerType + ''''
SELECT @.sql = @.sql + ',''' + @.fieldname + ''''
SELECT @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',''' + @.UpdateDate + ''''
SELECT @.sql = @.sql + ', 1'
SELECT @.sql = @.sql + ',''' + @.UserName + ''''
SELECT @.sql = @.sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @.sql = @.sql + @.pkJoinClause
SELECT @.sql = @.sql + ' WHERE (''' + @.TriggerType + ''' = ''I'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''D'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''U'' AND '
SELECT @.sql = @.sql + '((i.' + @.fieldname + ' <> d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ' OR (''' + @.fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NULL AND d.' + @.fieldname + ' is NOT null)'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NOT NULL AND d.' + @.fieldname + ' is null)))'

EXEC (@.sql)
set @.RowId = @.RowId + 1
END

Drop Table #ins
Drop Table #del
Drop Table #tblFieldNames
Drop Table #primaryKeyFields

Incidentally, if you're interested in what the actual audit table looks like, run this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAuditAdmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAuditAdmin]
GO

CREATE TABLE [dbo].[tblAuditAdmin] (
[UpdateDate] [datetime] NOT NULL ,
[TableAltered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateNumber] [int] NULL ,
[Action] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblAuditAdmin] ADD
CONSTRAINT [PK_tblAuditAdmin] PRIMARY KEY CLUSTERED
(
[UpdateDate],
[TableAltered],
[FieldName]
) ON [PRIMARY]
GO

|||

Sorry. Forgot to tell you how to use the code I posted yesterday.

Change the output of Query Analyzer to 'text'. excute the code. Copy the output to edit portion of Query Analyzer. Then replace "<more code here>" with the common part of your code. Now you will have the script.

This makeshift works well, If you don't want to spend too much time.

|||

Oh, I get it! This worked great, thanks to Aego!

For what it's worth, the following is what I ended up with.

-- To create this trigger for each table, insert the table name into the 2nd line of code (set @.TableName = 'TblCompanies'),
-- then highlight and execute the "first block of code" (inside dashed lines). Then paste the output from this into the
-- second block. Select that code all the way to the bottom and execute to create the trigger for that table. Do this
-- for each table name that you want the trigger created for. Alternatively, you can just set the table name and trigger
-- name manually for each table in the second block of code.

--First block of code...
declare @.TableName varchar(50)
set @.TableName = 'tblDepartments'

declare @.string varchar(5000)
set @.string = 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + @.TableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + @.TableName + 'Audit ' + + char(13) +
'go ' + char(13) +

'CREATE TRIGGER tg' + @.TableName + 'Audit on ' + @.TableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.TableName + '''
' + char(13)

print @.string

--Second block of code...
--replace code in this block with the output from above block and execute from here down
if exists (select * from sysobjects where id = object_id (N'dbo.tgtblDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgtblDepartmentsAudit
go
CREATE TRIGGER tgtblDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'

...... the rest of my code.......