I am trying to write a simple program for test purpose with VB .Net 2005 and SQL Server Express 2005. My server is not even in the same state as me, but I have the following on the server:
Server 2003 Web Edition SP1
SQL Server Express 2005
I am writing in the following:
Visual Basic 2005 Pro Edition
I have read where you can setup Express to allow TCP/IP connection, and I have done what it stated HERE (http://www.datamasker.com/SSE2005_NetworkCfg.htm). However, I am still not able to connect to the DB on the remote server.
I go to:
Tools --> Connect to Server --> Type IP and get the connection failed to connec to server "xxx.xxx.xxx.xxx"
I even tried:
Tools --> Connect to Server --> Type IP\SQLEXPRESS and get the connection failed to connec to server "xxx.xxx.xxx.xxx"
Has anyone been able to do this, or am I wasting my time? If I can get the SQL Server to do as requested then I am going to purchase and work with it, however, I want to test out before I spend the $$$$$ to begin the full project. If anyone can be of assistance here I would appreciate it.
Thanks in advance.Can you access the server on a remote machine on the same location as the server is (that is, in the same domain, at least without going through a firewall) ? It might have something to do with TCP/IP port 1433 that is not opened in the firewall ... and believe me, you don't want that !!!
Is your SQL server directly accessible from the Internet with IP address xxx.xxx.xxx.xxx ? Or should some routing be done because it's behind a firewall ? I would check your network admin for some assistence ...
Gr,
Yveausql
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Friday, March 23, 2012
Office Interop Assemblies and SSIS
Hi guys,
Putting the DLLs in the .NET Folder seems to have done the trick.
I'm trying to write an SSIS script that will create an excel file and append data to it.
I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.
However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.
If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.
Am I missing anything obvious?
Thanks for your help in advance
Mike
Do you have the Visual Studio Tools for Office 2007 installed?
Just curious, why create the excel file in the script task?
|||
Mike_Gaziotis wrote:
Hi guys, I'm trying to write an SSIS script that will create an excel file and append data to it.
I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.
However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.
If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.
Am I missing anything obvious?
Thanks for your help in advance
Mike
This should help.
VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-VSA-requires-DLLs-to-be-in-the-Microsoft.Net-folder-_2800_but-not-all-the-time_2900_.aspx)
-Jamie
|||The VSA designer's "Add Reference" dialog does not look for assemblies in the GAC but only in the %ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies and %windir%\Microsoft.NET\Framework\v2.0.50727
We recomend that you copy them to the SQL server location pointed above for the "Add Reference" to work.
|||Thanks very much Jamie and Silviu!Putting the DLLs in the .NET Folder seems to have done the trick.
In response to jwelch's question: I would have used the standard Data Flow task but it didn't seem to support some of the functionality i needed (ie dynamically building the file path and file name based on content etc)
Thanks again guys
Mike
|||
Actually, that sounds like a great opportunity to use variables or expressions. If you describe your scenario a little more, perhaps there might be some alternative approaches.
Not trying to change your mind on direction, just want to make sure you know what options are available.
|||Hi Jwelch,Always keen to find out if there are more approaches that i haven't considered. Even if they're not used in this scenario it's good to know of the possibilites
Here are my SSIS job requirements:
- Execute (several) SQL queries which retrieve report data
- Insert the data in worksheets of several excel files
- Save the files by:
a: Putting a timestapt in their name (ie reportA_04072007)
b: storing them in different locations on the server based on the report
(ie %Reports%/ReportA/reportA_04072007.xls)
- Send mails with excel files attached
This is to be a scheduled job managed by the SQL server agent.
It's not a job of enormous complexity i must admit, but i somehow i found myself (perhaps because of me being a developer rather than a DBA by nature) more keen to write code than having to use loops, data flow tasks and all the rest. In fact when i first had the problem with the assemblies i half considered writing a windows app that would do that job and then run that from the sql server agent. And really, is there such a big difference between writing an app or a script-only SSIS job?
Thanks and sorry for the babble
Mike
Wednesday, March 21, 2012
Odd pivot table type query
I've been unable to find a way to write the following
query.
Assuming this table:
Year Quarter
-- --
1990 1
1990 2
1990 3
1990 4
1991 1
1991 2
1991 3
1991 4
Does anyone know how to write an SQL query to generate the
following results?
Year Quarter
-- --
1990 1,2,3,4
1991 1,2,3,4
For documented method, you will have to make use of procedural code to
achieve this. See following example. OR you will have to use some 3rd party
tool to do it(www.rac4sql.com).
Eg:
-- sample table
-- if object_id('tab') is not null
-- drop table tab
create table tab(ID int,
SEQ_NUM int ,
ROUTE varchar(50))
go
-- sample data
insert into tab values(1 ,1 ,'AA')
insert into tab values(1 ,2 ,'BB')
insert into tab values(1 ,3 ,'CC')
insert into tab values(2 ,1 ,'AA')
insert into tab values(3 ,1 ,'VV')
insert into tab values(3 ,2 ,'XX')
go
-- t-sql code for generating report
if object_id('tempdb..#tmp') is not null
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @.id int
declare @.seq_num int
declare @.route varchar(50), @.f_route varchar(50)
select @.id=0, @.seq_num=0, @.route='', @.f_route=''
while @.id is not null
begin
select @.id=min(id) from tab where id > @.id
while @.seq_num is not null
begin
select @.seq_num=min(seq_num), @.route=min(route)from tab where id = @.id
and seq_num > @.seq_num
If @.seq_num is null and @.id is not null
insert into #tmp values(@.id, @.f_route)
select @.f_route = @.f_route + case @.f_route when '' then '' else ','
end + @.route
end
select @.seq_num=0, @.f_route=''
end
select * from #tmp
truncate table #tmp
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thanks for the response Vishal. Unfortunatly this
solution isn't going to work for me since I dealing with a
very large number of records the performace of populating
a new table is going to be too great.
I'm adding a "download" feature for the data so this would
result is a large number of records being retuned and
written to file.
What would be great is is I could use somethine like a
GROUP BY and SUM where the SUM would append strings
together.
SELECT year, APPEND(Quarter)
FROM mytable
GROUP BY year
>--Original Message--
>For documented method, you will have to make use of
procedural code to
>achieve this. See following example. OR you will have to
use some 3rd party
>tool to do it(www.rac4sql.com).
>Eg:
>-- sample table
>-- if object_id('tab') is not null
>-- drop table tab
>create table tab(ID int,
>SEQ_NUM int ,
>ROUTE varchar(50))
>go
>-- sample data
>insert into tab values
(1 ,1 ,'AA')
>insert into tab values
(1 ,2 ,'BB')
>insert into tab values
(1 ,3 ,'CC')
>insert into tab values
(2 ,1 ,'AA')
>insert into tab values
(3 ,1 ,'VV')
>insert into tab values
(3 ,2 ,'XX')
>go
>-- t-sql code for generating report
>if object_id('tempdb..#tmp') is not null
>drop table #tmp
>create table #tmp(id int, tmpval varchar(50))
>go
>declare @.id int
>declare @.seq_num int
>declare @.route varchar(50), @.f_route varchar(50)
>select @.id=0, @.seq_num=0, @.route='', @.f_route=''
>while @.id is not null
>begin
> select @.id=min(id) from tab where id > @.id
> while @.seq_num is not null
> begin
> select @.seq_num=min(seq_num), @.route=min(route)from
tab where id = @.id
> and seq_num > @.seq_num
> If @.seq_num is null and @.id is not null
> insert into #tmp values(@.id, @.f_route)
> select @.f_route = @.f_route + case @.f_route when ''
then '' else ','
>end + @.route
> end
> select @.seq_num=0, @.f_route=''
>end
>select * from #tmp
>truncate table #tmp
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||Shawn,
There is no function as such which will do the things for you.
You may try following approach using User defined function. But remember,
this is not a documented method, so it can not be reliable under all
cirumstances.
CREATE FUNCTION EmpPhones (@.ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.str VARCHAR(1000)
SELECT @.str = ISNULL(@.str + ',', '') + cats
FROM tab WHERE id = @.ID
RETURN (@.str)
END
-- sample data / result set.
if object_id ('tab') is not null
drop table tab
go
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'1-001')
insert into tab values(1 ,'2-002')
insert into tab values(1 ,'3-003')
insert into tab values(2 ,'1-011')
insert into tab values(3 ,'1-012')
insert into tab values(3 ,'2-022')
go
--And then you would call this UDF from within a SELECT statement, as
follows:
select distinct id,dbo.empphones(id) 'comma seperated value' from tab
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
query.
Assuming this table:
Year Quarter
-- --
1990 1
1990 2
1990 3
1990 4
1991 1
1991 2
1991 3
1991 4
Does anyone know how to write an SQL query to generate the
following results?
Year Quarter
-- --
1990 1,2,3,4
1991 1,2,3,4
For documented method, you will have to make use of procedural code to
achieve this. See following example. OR you will have to use some 3rd party
tool to do it(www.rac4sql.com).
Eg:
-- sample table
-- if object_id('tab') is not null
-- drop table tab
create table tab(ID int,
SEQ_NUM int ,
ROUTE varchar(50))
go
-- sample data
insert into tab values(1 ,1 ,'AA')
insert into tab values(1 ,2 ,'BB')
insert into tab values(1 ,3 ,'CC')
insert into tab values(2 ,1 ,'AA')
insert into tab values(3 ,1 ,'VV')
insert into tab values(3 ,2 ,'XX')
go
-- t-sql code for generating report
if object_id('tempdb..#tmp') is not null
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @.id int
declare @.seq_num int
declare @.route varchar(50), @.f_route varchar(50)
select @.id=0, @.seq_num=0, @.route='', @.f_route=''
while @.id is not null
begin
select @.id=min(id) from tab where id > @.id
while @.seq_num is not null
begin
select @.seq_num=min(seq_num), @.route=min(route)from tab where id = @.id
and seq_num > @.seq_num
If @.seq_num is null and @.id is not null
insert into #tmp values(@.id, @.f_route)
select @.f_route = @.f_route + case @.f_route when '' then '' else ','
end + @.route
end
select @.seq_num=0, @.f_route=''
end
select * from #tmp
truncate table #tmp
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thanks for the response Vishal. Unfortunatly this
solution isn't going to work for me since I dealing with a
very large number of records the performace of populating
a new table is going to be too great.
I'm adding a "download" feature for the data so this would
result is a large number of records being retuned and
written to file.
What would be great is is I could use somethine like a
GROUP BY and SUM where the SUM would append strings
together.
SELECT year, APPEND(Quarter)
FROM mytable
GROUP BY year
>--Original Message--
>For documented method, you will have to make use of
procedural code to
>achieve this. See following example. OR you will have to
use some 3rd party
>tool to do it(www.rac4sql.com).
>Eg:
>-- sample table
>-- if object_id('tab') is not null
>-- drop table tab
>create table tab(ID int,
>SEQ_NUM int ,
>ROUTE varchar(50))
>go
>-- sample data
>insert into tab values
(1 ,1 ,'AA')
>insert into tab values
(1 ,2 ,'BB')
>insert into tab values
(1 ,3 ,'CC')
>insert into tab values
(2 ,1 ,'AA')
>insert into tab values
(3 ,1 ,'VV')
>insert into tab values
(3 ,2 ,'XX')
>go
>-- t-sql code for generating report
>if object_id('tempdb..#tmp') is not null
>drop table #tmp
>create table #tmp(id int, tmpval varchar(50))
>go
>declare @.id int
>declare @.seq_num int
>declare @.route varchar(50), @.f_route varchar(50)
>select @.id=0, @.seq_num=0, @.route='', @.f_route=''
>while @.id is not null
>begin
> select @.id=min(id) from tab where id > @.id
> while @.seq_num is not null
> begin
> select @.seq_num=min(seq_num), @.route=min(route)from
tab where id = @.id
> and seq_num > @.seq_num
> If @.seq_num is null and @.id is not null
> insert into #tmp values(@.id, @.f_route)
> select @.f_route = @.f_route + case @.f_route when ''
then '' else ','
>end + @.route
> end
> select @.seq_num=0, @.f_route=''
>end
>select * from #tmp
>truncate table #tmp
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||Shawn,
There is no function as such which will do the things for you.
You may try following approach using User defined function. But remember,
this is not a documented method, so it can not be reliable under all
cirumstances.
CREATE FUNCTION EmpPhones (@.ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.str VARCHAR(1000)
SELECT @.str = ISNULL(@.str + ',', '') + cats
FROM tab WHERE id = @.ID
RETURN (@.str)
END
-- sample data / result set.
if object_id ('tab') is not null
drop table tab
go
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'1-001')
insert into tab values(1 ,'2-002')
insert into tab values(1 ,'3-003')
insert into tab values(2 ,'1-011')
insert into tab values(3 ,'1-012')
insert into tab values(3 ,'2-022')
go
--And then you would call this UDF from within a SELECT statement, as
follows:
select distinct id,dbo.empphones(id) 'comma seperated value' from tab
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
Monday, March 19, 2012
Odd ASP buffer/cache problem?
I am working with code I did not write for a web app that used to run
on an Access database. Due to performance problems I'm trying to move
the app to a different server running SQL Server 2000 SP4, running on
Server 2003 with MDAC 2.8 SP2.
On the new server, one of the queries is failing in an odd way.
Roughly, the query is
select * from a, b where a.1 = b.1 and b.2 = x
This is old fashioned ASP code using ADO.Recordsets. One of the columns
I should be getting from the query is called "Number_Sections".
sql = the query
set cRS = dbCon.exectue( sql )
somevar = cRS("Number_Sections")
cRS("Number_Sections") is returning NULL even though the column in the
database is not null. If I run the query using the SQL Analyer on the
server itself, the column is indeed populated as I expect.
cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
also return NULL.
However, while trying to debug I threw in this code:
For zz = 0 to cRS.Fields.Count-1
response.write cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).value & "<br>"
Next
After running through this loop, which prints out all 47 column names
and values *correctly*, cRS("Number_Sections") suddenly works. Take out
the loop and it stops working again.
This sounds like some kind of buffering problem to me, but I'm not
familiar enough with Microsoft products to know where I should be
looking for some setting to fix the problem. Is it ASP and IIS? Is it
SQL Server? I haven't been able to find any settings that look like
they address this issue and searching the MS Knowledge base hasn't
turned up anything helpful either.
Has anyone else seen this behavior? Can anyone point me in the right
direction to find a more graceful solution that leaving the for loop in
and setting the string to some dummy variable instead of sending it to
the response object?
Thanks.
-SeanStop using SELECT *. Do you really need 47 columns? If this column exists
in both a and b, then either alias it or only include it once. Another
thing you can try is somevar = cRS(n) where n is the 0-based ordinal
position of that column (which will be fun to figure out if you continue to
insist using SELECT *).
I have never seen this issue but without a better example, DDL/sample
data/code and a repro, it's tough to guess at what is happening.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
>I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>|||First, what you are describing sounds like an ASP coding issue, and not a
SQL Server issue. You would have better luck posting on an ASP newsgroup.
However, if you post more of your code, starting with where you define
dbCon(less the actual connection string of course), and ending with closing
your connection, folks will be able to offer more advice.
It sounds to me like you are not properly looping through your dataset, but
I can only guess without seeing the actual code.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
> I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>
on an Access database. Due to performance problems I'm trying to move
the app to a different server running SQL Server 2000 SP4, running on
Server 2003 with MDAC 2.8 SP2.
On the new server, one of the queries is failing in an odd way.
Roughly, the query is
select * from a, b where a.1 = b.1 and b.2 = x
This is old fashioned ASP code using ADO.Recordsets. One of the columns
I should be getting from the query is called "Number_Sections".
sql = the query
set cRS = dbCon.exectue( sql )
somevar = cRS("Number_Sections")
cRS("Number_Sections") is returning NULL even though the column in the
database is not null. If I run the query using the SQL Analyer on the
server itself, the column is indeed populated as I expect.
cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
also return NULL.
However, while trying to debug I threw in this code:
For zz = 0 to cRS.Fields.Count-1
response.write cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).value & "<br>"
Next
After running through this loop, which prints out all 47 column names
and values *correctly*, cRS("Number_Sections") suddenly works. Take out
the loop and it stops working again.
This sounds like some kind of buffering problem to me, but I'm not
familiar enough with Microsoft products to know where I should be
looking for some setting to fix the problem. Is it ASP and IIS? Is it
SQL Server? I haven't been able to find any settings that look like
they address this issue and searching the MS Knowledge base hasn't
turned up anything helpful either.
Has anyone else seen this behavior? Can anyone point me in the right
direction to find a more graceful solution that leaving the for loop in
and setting the string to some dummy variable instead of sending it to
the response object?
Thanks.
-SeanStop using SELECT *. Do you really need 47 columns? If this column exists
in both a and b, then either alias it or only include it once. Another
thing you can try is somevar = cRS(n) where n is the 0-based ordinal
position of that column (which will be fun to figure out if you continue to
insist using SELECT *).
I have never seen this issue but without a better example, DDL/sample
data/code and a repro, it's tough to guess at what is happening.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
>I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>|||First, what you are describing sounds like an ASP coding issue, and not a
SQL Server issue. You would have better luck posting on an ASP newsgroup.
However, if you post more of your code, starting with where you define
dbCon(less the actual connection string of course), and ending with closing
your connection, folks will be able to offer more advice.
It sounds to me like you are not properly looping through your dataset, but
I can only guess without seeing the actual code.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
> I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>
Monday, March 12, 2012
ODBC: Call Failed 3146
Hello,
I have a vb program that connects to odbc to an sqlexpress server. When
i try to write data to the db, i get this message:
Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
"de oproep is mislukt" is dutch for "Call failed"
It is an on/off problem, sometimes it works perfectly, but when i try
for the second time i get the error message again. Also found a
suggestion on the internet to walk through the errors of the
dbengine.error but this gave me nothing more then the shown error.
I am using windows xp and a local SQLexpress 2005 server. I am using
the sql server odbc driver 2000.85.1117.00 or should i been using the
sql native client version 2005.90.1399.00 ?
Can someone explain to me what i must do to correct this problem. ?
Thnx,
Johan
The Netherlands
ODBC call failed can be anything so it's not real fun to
troubleshoot. I think the easiest way though is to turn on
ODBC tracing on the client until you get the error. Having
ODBC tracing on will bog down the client so you want to try
to get the error soon and turn tracing off after that. Once
you hit the error, you can go through the ODBC trace log to
find the specific call that had the error.
You turn on ODBC tracing from the ODBC Data Source
Administrator. Go to the tracing tab and click on Start
Tracing now. Note where the log is going to - you can change
the location if you want. You turn off the tracing from the
same place. Just make sure you turn it back off.
-Sue
On 24 Sep 2006 02:54:21 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
>Hello,
>I have a vb program that connects to odbc to an sqlexpress server. When
>i try to write data to the db, i get this message:
>Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
>(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
>"de oproep is mislukt" is dutch for "Call failed"
>It is an on/off problem, sometimes it works perfectly, but when i try
>for the second time i get the error message again. Also found a
>suggestion on the internet to walk through the errors of the
>dbengine.error but this gave me nothing more then the shown error.
>I am using windows xp and a local SQLexpress 2005 server. I am using
>the sql server odbc driver 2000.85.1117.00 or should i been using the
>sql native client version 2005.90.1399.00 ?
>Can someone explain to me what i must do to correct this problem. ?
>Thnx,
>Johan
>The Netherlands
|||Thank you very much, the problem is solved. Did not know about the
tracer, but when i used it, it gave me just the info i needed. It
seemed i had a wrong unique index ;)
Fixed it and now everything runs smoothly !
Sue Hoegemeier wrote:[vbcol=seagreen]
> ODBC call failed can be anything so it's not real fun to
> troubleshoot. I think the easiest way though is to turn on
> ODBC tracing on the client until you get the error. Having
> ODBC tracing on will bog down the client so you want to try
> to get the error soon and turn tracing off after that. Once
> you hit the error, you can go through the ODBC trace log to
> find the specific call that had the error.
> You turn on ODBC tracing from the ODBC Data Source
> Administrator. Go to the tracing tab and click on Start
> Tracing now. Note where the log is going to - you can change
> the location if you want. You turn off the tracing from the
> same place. Just make sure you turn it back off.
> -Sue
> On 24 Sep 2006 02:54:21 -0700, "Johan"
> <hello_2you@.hotmail.com> wrote:
|||Glad it's fixed - thanks for posting back!
-Sue
On 25 Sep 2006 01:40:45 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
[vbcol=seagreen]
>Thank you very much, the problem is solved. Did not know about the
>tracer, but when i used it, it gave me just the info i needed. It
>seemed i had a wrong unique index ;)
>Fixed it and now everything runs smoothly !
>
>Sue Hoegemeier wrote:
I have a vb program that connects to odbc to an sqlexpress server. When
i try to write data to the db, i get this message:
Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
"de oproep is mislukt" is dutch for "Call failed"
It is an on/off problem, sometimes it works perfectly, but when i try
for the second time i get the error message again. Also found a
suggestion on the internet to walk through the errors of the
dbengine.error but this gave me nothing more then the shown error.
I am using windows xp and a local SQLexpress 2005 server. I am using
the sql server odbc driver 2000.85.1117.00 or should i been using the
sql native client version 2005.90.1399.00 ?
Can someone explain to me what i must do to correct this problem. ?
Thnx,
Johan
The Netherlands
ODBC call failed can be anything so it's not real fun to
troubleshoot. I think the easiest way though is to turn on
ODBC tracing on the client until you get the error. Having
ODBC tracing on will bog down the client so you want to try
to get the error soon and turn tracing off after that. Once
you hit the error, you can go through the ODBC trace log to
find the specific call that had the error.
You turn on ODBC tracing from the ODBC Data Source
Administrator. Go to the tracing tab and click on Start
Tracing now. Note where the log is going to - you can change
the location if you want. You turn off the tracing from the
same place. Just make sure you turn it back off.
-Sue
On 24 Sep 2006 02:54:21 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
>Hello,
>I have a vb program that connects to odbc to an sqlexpress server. When
>i try to write data to the db, i get this message:
>Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
>(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
>"de oproep is mislukt" is dutch for "Call failed"
>It is an on/off problem, sometimes it works perfectly, but when i try
>for the second time i get the error message again. Also found a
>suggestion on the internet to walk through the errors of the
>dbengine.error but this gave me nothing more then the shown error.
>I am using windows xp and a local SQLexpress 2005 server. I am using
>the sql server odbc driver 2000.85.1117.00 or should i been using the
>sql native client version 2005.90.1399.00 ?
>Can someone explain to me what i must do to correct this problem. ?
>Thnx,
>Johan
>The Netherlands
|||Thank you very much, the problem is solved. Did not know about the
tracer, but when i used it, it gave me just the info i needed. It
seemed i had a wrong unique index ;)
Fixed it and now everything runs smoothly !
Sue Hoegemeier wrote:[vbcol=seagreen]
> ODBC call failed can be anything so it's not real fun to
> troubleshoot. I think the easiest way though is to turn on
> ODBC tracing on the client until you get the error. Having
> ODBC tracing on will bog down the client so you want to try
> to get the error soon and turn tracing off after that. Once
> you hit the error, you can go through the ODBC trace log to
> find the specific call that had the error.
> You turn on ODBC tracing from the ODBC Data Source
> Administrator. Go to the tracing tab and click on Start
> Tracing now. Note where the log is going to - you can change
> the location if you want. You turn off the tracing from the
> same place. Just make sure you turn it back off.
> -Sue
> On 24 Sep 2006 02:54:21 -0700, "Johan"
> <hello_2you@.hotmail.com> wrote:
|||Glad it's fixed - thanks for posting back!
-Sue
On 25 Sep 2006 01:40:45 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
[vbcol=seagreen]
>Thank you very much, the problem is solved. Did not know about the
>tracer, but when i used it, it gave me just the info i needed. It
>seemed i had a wrong unique index ;)
>Fixed it and now everything runs smoothly !
>
>Sue Hoegemeier wrote:
ODBC: Call Failed 3146
Hello,
I have a vb program that connects to odbc to an sqlexpress server. When
i try to write data to the db, i get this message:
Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
"de oproep is mislukt" is dutch for "Call failed"
It is an on/off problem, sometimes it works perfectly, but when i try
for the second time i get the error message again. Also found a
suggestion on the internet to walk through the errors of the
dbengine.error but this gave me nothing more then the shown error.
I am using Windows XP and a local SQLexpress 2005 server. I am using
the sql server odbc driver 2000.85.1117.00 or should i been using the
sql native client version 2005.90.1399.00 ?
Can someone explain to me what i must do to correct this problem. ?
Thnx,
Johan
The NetherlandsODBC call failed can be anything so it's not real fun to
troubleshoot. I think the easiest way though is to turn on
ODBC tracing on the client until you get the error. Having
ODBC tracing on will bog down the client so you want to try
to get the error soon and turn tracing off after that. Once
you hit the error, you can go through the ODBC trace log to
find the specific call that had the error.
You turn on ODBC tracing from the ODBC Data Source
Administrator. Go to the tracing tab and click on Start
Tracing now. Note where the log is going to - you can change
the location if you want. You turn off the tracing from the
same place. Just make sure you turn it back off.
-Sue
On 24 Sep 2006 02:54:21 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
>Hello,
>I have a vb program that connects to odbc to an sqlexpress server. When
>i try to write data to the db, i get this message:
>Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
>(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
>"de oproep is mislukt" is dutch for "Call failed"
>It is an on/off problem, sometimes it works perfectly, but when i try
>for the second time i get the error message again. Also found a
>suggestion on the internet to walk through the errors of the
>dbengine.error but this gave me nothing more then the shown error.
>I am using Windows XP and a local SQLexpress 2005 server. I am using
>the sql server odbc driver 2000.85.1117.00 or should i been using the
>sql native client version 2005.90.1399.00 ?
>Can someone explain to me what i must do to correct this problem. ?
>Thnx,
>Johan
>The Netherlands|||Thank you very much, the problem is solved. Did not know about the
tracer, but when i used it, it gave me just the info i needed. It
seemed i had a wrong unique index ;)
Fixed it and now everything runs smoothly !
Sue Hoegemeier wrote:[vbcol=seagreen]
> ODBC call failed can be anything so it's not real fun to
> troubleshoot. I think the easiest way though is to turn on
> ODBC tracing on the client until you get the error. Having
> ODBC tracing on will bog down the client so you want to try
> to get the error soon and turn tracing off after that. Once
> you hit the error, you can go through the ODBC trace log to
> find the specific call that had the error.
> You turn on ODBC tracing from the ODBC Data Source
> Administrator. Go to the tracing tab and click on Start
> Tracing now. Note where the log is going to - you can change
> the location if you want. You turn off the tracing from the
> same place. Just make sure you turn it back off.
> -Sue
> On 24 Sep 2006 02:54:21 -0700, "Johan"
> <hello_2you@.hotmail.com> wrote:
>|||Glad it's fixed - thanks for posting back!
-Sue
On 25 Sep 2006 01:40:45 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
[vbcol=seagreen]
>Thank you very much, the problem is solved. Did not know about the
>tracer, but when i used it, it gave me just the info i needed. It
>seemed i had a wrong unique index ;)
>Fixed it and now everything runs smoothly !
>
>Sue Hoegemeier wrote:
I have a vb program that connects to odbc to an sqlexpress server. When
i try to write data to the db, i get this message:
Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
"de oproep is mislukt" is dutch for "Call failed"
It is an on/off problem, sometimes it works perfectly, but when i try
for the second time i get the error message again. Also found a
suggestion on the internet to walk through the errors of the
dbengine.error but this gave me nothing more then the shown error.
I am using Windows XP and a local SQLexpress 2005 server. I am using
the sql server odbc driver 2000.85.1117.00 or should i been using the
sql native client version 2005.90.1399.00 ?
Can someone explain to me what i must do to correct this problem. ?
Thnx,
Johan
The NetherlandsODBC call failed can be anything so it's not real fun to
troubleshoot. I think the easiest way though is to turn on
ODBC tracing on the client until you get the error. Having
ODBC tracing on will bog down the client so you want to try
to get the error soon and turn tracing off after that. Once
you hit the error, you can go through the ODBC trace log to
find the specific call that had the error.
You turn on ODBC tracing from the ODBC Data Source
Administrator. Go to the tracing tab and click on Start
Tracing now. Note where the log is going to - you can change
the location if you want. You turn off the tracing from the
same place. Just make sure you turn it back off.
-Sue
On 24 Sep 2006 02:54:21 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
>Hello,
>I have a vb program that connects to odbc to an sqlexpress server. When
>i try to write data to the db, i get this message:
>Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
>(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
>"de oproep is mislukt" is dutch for "Call failed"
>It is an on/off problem, sometimes it works perfectly, but when i try
>for the second time i get the error message again. Also found a
>suggestion on the internet to walk through the errors of the
>dbengine.error but this gave me nothing more then the shown error.
>I am using Windows XP and a local SQLexpress 2005 server. I am using
>the sql server odbc driver 2000.85.1117.00 or should i been using the
>sql native client version 2005.90.1399.00 ?
>Can someone explain to me what i must do to correct this problem. ?
>Thnx,
>Johan
>The Netherlands|||Thank you very much, the problem is solved. Did not know about the
tracer, but when i used it, it gave me just the info i needed. It
seemed i had a wrong unique index ;)
Fixed it and now everything runs smoothly !
Sue Hoegemeier wrote:[vbcol=seagreen]
> ODBC call failed can be anything so it's not real fun to
> troubleshoot. I think the easiest way though is to turn on
> ODBC tracing on the client until you get the error. Having
> ODBC tracing on will bog down the client so you want to try
> to get the error soon and turn tracing off after that. Once
> you hit the error, you can go through the ODBC trace log to
> find the specific call that had the error.
> You turn on ODBC tracing from the ODBC Data Source
> Administrator. Go to the tracing tab and click on Start
> Tracing now. Note where the log is going to - you can change
> the location if you want. You turn off the tracing from the
> same place. Just make sure you turn it back off.
> -Sue
> On 24 Sep 2006 02:54:21 -0700, "Johan"
> <hello_2you@.hotmail.com> wrote:
>|||Glad it's fixed - thanks for posting back!
-Sue
On 25 Sep 2006 01:40:45 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
[vbcol=seagreen]
>Thank you very much, the problem is solved. Did not know about the
>tracer, but when i used it, it gave me just the info i needed. It
>seemed i had a wrong unique index ;)
>Fixed it and now everything runs smoothly !
>
>Sue Hoegemeier wrote:
ODBC Write Failures
I've got an odd occasonal problem when writing to our server. In two
different applications, one an access app, and the other VB6 we will
experience a condition where we are able to read data, but when we attempt
to save the data we get ODBC timeouts. When this occurs, I can "fix" the
problem by going into one of the tables of the database in question and
modifying any value.
Configuration is a dual Xeon with 2gb of memory running SQL Server 2000.
Both databases are on this system and are accessed via different DSNs. One
is a web app, and is accessed by com dlls, the other is an app that was
created in MS Access and then modified so that it gets its data from linked
tables.
Any insite, or even better a good description of troublshooting techniques
would be appreciated.
Sam RobinsonHi Sam,
According to your description, I am not sure what your problem is. I would
like you to provide the following information so that I can narrow down
this issue.
1. Please provide me with the detailed error message. To do this, you can
take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
additional information regarding how to obtain the screen shot, please
review [Item 1].
2. How did you read and write data with Access to SQL Server, via a linked
table or programming? Can you describe it in detailed?
3. I would like to know when the error occurred. It seems that the error
occurred when you updated the table on SQL Server. Did the error message
occur when you connected to SQL Server?
4. When the error occurs again, please perform the problematic SQL
statements using Query Analyzer. Does the same error message occur again?
It helps us to identify the problem related to the SQL Server including SQL
statements and your VB code.
5. Please provide the SQL Server error log (usually under C:\Program
Files\Microsoft SQL Server\MSSQL\LOG)
On the SQL Server side, there is a query wait option. This query wait
option helps us specify the time in seconds (from 0 through 2147483647)
that a query waits for resources before timing out. We can increase the
value of this option trying to avoid the timeout error message. For
additional information regarding this query wait option, please refer to
the following article on SQL Server Books Online.
Topic: "query wait Option"
Also, such issues occurring randomly tend to be complex and take up
extensive research time. I'd like to set your expectations that it may take
a while for us to help you narrow down the problem and we may eventually
redirect you to PSS to continue working with a dedicated Support
Professional. If this is critical, I'd recommend contacting PSS and opening
a support incident troubleshoot this further. If you need any help in this
regard, please let me know.
[Item 1]
Try to obtain the screen shot of the error message.
When the error occurred please perform the following steps to capture the
screen shot of the error message.
1. Press the "Pr Scrn" button on the keyboard.
2. Run the Paint tools (Start 'All programs 'Accessories ' Paint).
3. Press Ctrl+V to copy the screen shot from the memory.
4. Save as a JPEG file.
Thank you for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:2tLJaornDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> According to your description, I am not sure what your problem is. I would
> like you to provide the following information so that I can narrow down
> this issue.
> 1. Please provide me with the detailed error message. To do this, you can
> take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
> additional information regarding how to obtain the screen shot, please
> review [Item 1].
>
I assume that you're referring to the error message that is being seen in
access. These are not terribly informative, and do not provide error
numbers. The errors being returned to the web app (which seems to have
somewhat better error handling) are timeouts.
> 2. How did you read and write data with Access to SQL Server, via a
linked
> table or programming? Can you describe it in detailed?
>
Access is using linked tables. These were created using the standard table
linking wizard from the file menu.
> 3. I would like to know when the error occurred. It seems that the error
> occurred when you updated the table on SQL Server. Did the error message
> occur when you connected to SQL Server?
>
No, it didn't. The applications are still able to read data when this
happens, which is what has me foxed. The connection seems to be almost
healthy.
> 4. When the error occurs again, please perform the problematic SQL
> statements using Query Analyzer. Does the same error message occur again?
> It helps us to identify the problem related to the SQL Server including
SQL
> statements and your VB code.
>
Unfortunately, the same SQL statements succeed more often than not.
> 5. Please provide the SQL Server error log (usually under C:\Program
> Files\Microsoft SQL Server\MSSQL\LOG)
>
I can certainly do this, but what I'm seeing there is just that the backups
ran correctly.
> On the SQL Server side, there is a query wait option. This query wait
> option helps us specify the time in seconds (from 0 through 2147483647)
> that a query waits for resources before timing out. We can increase the
> value of this option trying to avoid the timeout error message. For
> additional information regarding this query wait option, please refer to
> the following article on SQL Server Books Online.
> Topic: "query wait Option"
> Also, such issues occurring randomly tend to be complex and take up
> extensive research time. I'd like to set your expectations that it may
take
> a while for us to help you narrow down the problem and we may eventually
> redirect you to PSS to continue working with a dedicated Support
> Professional. If this is critical, I'd recommend contacting PSS and
opening
> a support incident troubleshoot this further. If you need any help in this
> regard, please let me know.
>
I did find that the transaction logs had grown to truly monumental sizes (18
and 15 gb respectively) I cleaned that up last night, and am watching the
system. Does this seem like a likely culprit? I know that the log is broken
into virtual segments, but if the segment count gets very high can this
cause long lookup/write times that might impact external timers?
I can certainly appreciate your comments regarding intermittent problems and
how difficult they can be to resolve... or even to know if they're resolved.
I'm mostly looking for an approach to troubleshooting, and your posting has
given me several ideas.
Thanks for the help. As I see the problem (or don't as I'm hoping for!) I'll
post more information.
Sam Robinson|||Hi Sam,
Thanks for your feedback. Based on my experience, when server is busy, such
as performing many jobs on the background, the timeout error will occur.
According to your description with the monumental sizes of transaction
logs, it is possible that there are many jobs working on the background,
such as backup jobs, which impact the performance of the server. Because
the error occurs randomly, it is hard to us to find out the root cause of
this problem. When the error occurs again on your side, please feel free to
post in the newsgroup, I will glad to work with you continously. Of course,
I also hope it do not occur again.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:%239I3130nDHA.2464@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> Thanks for your feedback. Based on my experience, when server is busy,
such
> as performing many jobs on the background, the timeout error will occur.
> According to your description with the monumental sizes of transaction
> logs, it is possible that there are many jobs working on the background,
> such as backup jobs, which impact the performance of the server. Because
> the error occurs randomly, it is hard to us to find out the root cause of
> this problem. When the error occurs again on your side, please feel free
to
> post in the newsgroup, I will glad to work with you continously. Of
course,
> I also hope it do not occur again.
> Thanks for using MSDN newsgroup.
>
Michael, et al
Well, it's been a week now and I think it's safe to say that truncating and
setting up a backup maintenance plan has resolved the problem. My
speculation is that the problem came from the necessity to log the
transaction in the increadably huge transaction log. This would seem to
follow as we didn't get a timeout when reading, only when trying to commit a
record.
Wow. Given the size the logs had gotten, I'm amazed that the thing was
working as well as it was.
Thanks for your help!
Sam Robinson
different applications, one an access app, and the other VB6 we will
experience a condition where we are able to read data, but when we attempt
to save the data we get ODBC timeouts. When this occurs, I can "fix" the
problem by going into one of the tables of the database in question and
modifying any value.
Configuration is a dual Xeon with 2gb of memory running SQL Server 2000.
Both databases are on this system and are accessed via different DSNs. One
is a web app, and is accessed by com dlls, the other is an app that was
created in MS Access and then modified so that it gets its data from linked
tables.
Any insite, or even better a good description of troublshooting techniques
would be appreciated.
Sam RobinsonHi Sam,
According to your description, I am not sure what your problem is. I would
like you to provide the following information so that I can narrow down
this issue.
1. Please provide me with the detailed error message. To do this, you can
take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
additional information regarding how to obtain the screen shot, please
review [Item 1].
2. How did you read and write data with Access to SQL Server, via a linked
table or programming? Can you describe it in detailed?
3. I would like to know when the error occurred. It seems that the error
occurred when you updated the table on SQL Server. Did the error message
occur when you connected to SQL Server?
4. When the error occurs again, please perform the problematic SQL
statements using Query Analyzer. Does the same error message occur again?
It helps us to identify the problem related to the SQL Server including SQL
statements and your VB code.
5. Please provide the SQL Server error log (usually under C:\Program
Files\Microsoft SQL Server\MSSQL\LOG)
On the SQL Server side, there is a query wait option. This query wait
option helps us specify the time in seconds (from 0 through 2147483647)
that a query waits for resources before timing out. We can increase the
value of this option trying to avoid the timeout error message. For
additional information regarding this query wait option, please refer to
the following article on SQL Server Books Online.
Topic: "query wait Option"
Also, such issues occurring randomly tend to be complex and take up
extensive research time. I'd like to set your expectations that it may take
a while for us to help you narrow down the problem and we may eventually
redirect you to PSS to continue working with a dedicated Support
Professional. If this is critical, I'd recommend contacting PSS and opening
a support incident troubleshoot this further. If you need any help in this
regard, please let me know.
[Item 1]
Try to obtain the screen shot of the error message.
When the error occurred please perform the following steps to capture the
screen shot of the error message.
1. Press the "Pr Scrn" button on the keyboard.
2. Run the Paint tools (Start 'All programs 'Accessories ' Paint).
3. Press Ctrl+V to copy the screen shot from the memory.
4. Save as a JPEG file.
Thank you for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:2tLJaornDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> According to your description, I am not sure what your problem is. I would
> like you to provide the following information so that I can narrow down
> this issue.
> 1. Please provide me with the detailed error message. To do this, you can
> take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
> additional information regarding how to obtain the screen shot, please
> review [Item 1].
>
I assume that you're referring to the error message that is being seen in
access. These are not terribly informative, and do not provide error
numbers. The errors being returned to the web app (which seems to have
somewhat better error handling) are timeouts.
> 2. How did you read and write data with Access to SQL Server, via a
linked
> table or programming? Can you describe it in detailed?
>
Access is using linked tables. These were created using the standard table
linking wizard from the file menu.
> 3. I would like to know when the error occurred. It seems that the error
> occurred when you updated the table on SQL Server. Did the error message
> occur when you connected to SQL Server?
>
No, it didn't. The applications are still able to read data when this
happens, which is what has me foxed. The connection seems to be almost
healthy.
> 4. When the error occurs again, please perform the problematic SQL
> statements using Query Analyzer. Does the same error message occur again?
> It helps us to identify the problem related to the SQL Server including
SQL
> statements and your VB code.
>
Unfortunately, the same SQL statements succeed more often than not.
> 5. Please provide the SQL Server error log (usually under C:\Program
> Files\Microsoft SQL Server\MSSQL\LOG)
>
I can certainly do this, but what I'm seeing there is just that the backups
ran correctly.
> On the SQL Server side, there is a query wait option. This query wait
> option helps us specify the time in seconds (from 0 through 2147483647)
> that a query waits for resources before timing out. We can increase the
> value of this option trying to avoid the timeout error message. For
> additional information regarding this query wait option, please refer to
> the following article on SQL Server Books Online.
> Topic: "query wait Option"
> Also, such issues occurring randomly tend to be complex and take up
> extensive research time. I'd like to set your expectations that it may
take
> a while for us to help you narrow down the problem and we may eventually
> redirect you to PSS to continue working with a dedicated Support
> Professional. If this is critical, I'd recommend contacting PSS and
opening
> a support incident troubleshoot this further. If you need any help in this
> regard, please let me know.
>
I did find that the transaction logs had grown to truly monumental sizes (18
and 15 gb respectively) I cleaned that up last night, and am watching the
system. Does this seem like a likely culprit? I know that the log is broken
into virtual segments, but if the segment count gets very high can this
cause long lookup/write times that might impact external timers?
I can certainly appreciate your comments regarding intermittent problems and
how difficult they can be to resolve... or even to know if they're resolved.
I'm mostly looking for an approach to troubleshooting, and your posting has
given me several ideas.
Thanks for the help. As I see the problem (or don't as I'm hoping for!) I'll
post more information.
Sam Robinson|||Hi Sam,
Thanks for your feedback. Based on my experience, when server is busy, such
as performing many jobs on the background, the timeout error will occur.
According to your description with the monumental sizes of transaction
logs, it is possible that there are many jobs working on the background,
such as backup jobs, which impact the performance of the server. Because
the error occurs randomly, it is hard to us to find out the root cause of
this problem. When the error occurs again on your side, please feel free to
post in the newsgroup, I will glad to work with you continously. Of course,
I also hope it do not occur again.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:%239I3130nDHA.2464@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> Thanks for your feedback. Based on my experience, when server is busy,
such
> as performing many jobs on the background, the timeout error will occur.
> According to your description with the monumental sizes of transaction
> logs, it is possible that there are many jobs working on the background,
> such as backup jobs, which impact the performance of the server. Because
> the error occurs randomly, it is hard to us to find out the root cause of
> this problem. When the error occurs again on your side, please feel free
to
> post in the newsgroup, I will glad to work with you continously. Of
course,
> I also hope it do not occur again.
> Thanks for using MSDN newsgroup.
>
Michael, et al
Well, it's been a week now and I think it's safe to say that truncating and
setting up a backup maintenance plan has resolved the problem. My
speculation is that the problem came from the necessity to log the
transaction in the increadably huge transaction log. This would seem to
follow as we didn't get a timeout when reading, only when trying to commit a
record.
Wow. Given the size the logs had gotten, I'm amazed that the thing was
working as well as it was.
Thanks for your help!
Sam Robinson
Friday, March 9, 2012
ODBC to MSDE
I need to write a VB.Net application in VS.net 2003 that copies several tables from an ODBC data source to MSDE tables. I do not have Enterprise Manager or DTS to use. Is there an example of this in VS? I was hoping VS has some built in tools for this. If not, bulk Insert?
Help appreciated.
thanks,
FrankI would consider using MS Access for this, as it accesses both ODBC and MSDE data sources quite well (presuming this is not something that needs to run like a service).|||I ruled out Access because of several reasons. My next step is to schedule the scripts thru the SQL agent that MSDE comes with.
Help appreciated.
Thanks,
Frank
Subscribe to:
Posts (Atom)