SQL Alert: following

Showing posts with label following. Show all posts

Tuesday, March 27, 2012

Accessing local temporary tables

I've been able to get the local temporary table name from sysobjects, but I still can't select from it.

for example, the following select statement won't work
select * from #temptable_____00015
It says invalid object name.

I've even stored the temp table name in a variable and tryed to execute dynamic sql to get to it--no luck.

I need this technique to handle two support situations: A user freezes during data entry to a temp table, I want to capture the data before they reboot, so they don't have to reenter.

Temp tables are used among several stored procedures and then crunched into other tables. I'm getting incorrect results and want to see the raw data in the temp tables to assist me in figuring out what's going on.Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.|||Originally posted by rnealejr
Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.

They exist on the hard drive. Maybe if I changed the status value in sysobjects, I could select from them?|||Please define in detail the issue you are trying to solve.

Accessing Linked Excel Server

I created a linked Excel server that is stored in a SQL2000 database.

I can run the following from the SQL server with no problem.

Select * From CSCNEDI...EDI$

When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)

[OLE/DB provider returned message: Unspecified error]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

Thanks

David Davis

Schuette Inc.

Hi, David,

The error above simply indicates a failure of the Provider to open a "connection", in this case - the MDB file. Unfortunately, this is quite generic. How are you connecting to the SQL Server? Are you using SQL or NT Authentication? Is your Excel file local on the SQL box or is it on a file share? What we might be facing here seems to be an authentication problem. Here're a couple of ideas:

== If the Excel file is on a share, try to put it locally on the SQL box (reconfigure the linked server) and try the query from the workstation again

== If you are using NT authentication, try using SQL authentication to see if this changes the effect

== To confirm if this is an authentication/permission issue, use FileMon tool (http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx) and capture the file activity when you get the failure (a good idea is to recycle SQL Server and capture the first attempt). Check the log for your excel file name and for error like "Access Denied".

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, March 22, 2012

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
John
"ipramod@.gmail.com" wrote:
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegroups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.
Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/default...b;en-us;270599
John
"ipramod@.gmail.com" wrote:

> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>
|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegro ups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing current active trace file

Hi,
I am using the following SQL query for retrieving the Trace file
information.
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
But I am getting following error:
"File 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
or is not a recognizable trace file. Or there was an error opening the
file."
So, can we get the data from the current active trace file?
Thanks and Regards,
Pramod.Hi
The trace needs to be stopped and the file closed before re-opening it.
See:
http://support.microsoft.com/defaul...kb;en-us;270599
John
"ipramod@.gmail.com" wrote:

> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>|||The trace cannot be read whilst active in SQL2000 however SQL2005 has
improved on this and does allow reading a currently active server side trace
using fn_trace_gettable or the Profiler GUI.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<ipramod@.gmail.com> wrote in message
news:1128422380.248267.85480@.f14g2000cwb.googlegroups.com...
> Hi,
> I am using the following SQL query for retrieving the Trace file
> information.
> SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc', default)
> But I am getting following error:
> "File 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\audittrace_20040822191
554.trc' either does not exist
> or is not a recognizable trace file. Or there was an error opening the
> file."
> So, can we get the data from the current active trace file?
> Thanks and Regards,
> Pramod.
>

Accessing cubes parallely from 2 clients

Hello,

When 2 logins try to access a cube(in MSAS 2000), we are getting the following error message,

"This database is locked. User (....) on computer (....) has locked (....). Description : Cube edotir has locked cube (....) for writting."

Is there anyway we can access a cube simultaneously from 2 client machines (read only type of access)?

Thanks in advance....

Revin

Hi Revin,

Is the Analysis Manager open and modifying the cube structure at the same time. If so, close the Analysis Manager and try again. It will place a lock on the repository file and will not allow other connections.

If this doesn't work then stop and start the Analysis Server to free up any locks that are hanging around.

Hope this helps,

David

|||

Thanks David!!!

Yes the analysis service is open at 2 clients.

What i would like to know is that, is there any way i can access (read only type) the cube while another user is editing the cube in devlopment environment?

Thanks,

Revin

Accessing conditional values in Groups

Hello

I am trying using following in a group footer and it gives me an error!

=Sum(IIF(Fields!COSTCENTRETYPECODE.Value <> "B1502",Fields!BUDGETAMOUNTOYR.Value,0)).

Can anyone please shed some light

Thanks

What exactly is the error message?

Is it about aggregating fields of varying data type? In that case, the following expression should work for you: =Sum(IIF(Fields!COSTCENTRETYPECODE.Value <> "B1502", CDbl(Fields!BUDGETAMOUNTOYR.Value), 0.0))

-- Robert

|||

Thanks Robert.

Casting the value to double solved it! But I still think this is a bug. As this only occurs when the contidion satisfies at some value, otherwise it works fine. For example:

Sum(IIF(Fields!COSTCENTRETYPECODE.Value <> "B1502", Fields!BUDGETAMOUNTOYR.Value, 0.0)) - Give an Error in the text box

But

Sum(IIF(Fields!COSTCENTRETYPECODE.Value <> "XXX", Fields!BUDGETAMOUNTOYR.Value, 0.0)) - Would sum all the amounts.

Data set contains value B1502 as Costcentretype code while XXX is not in the data set. So even though it will not include the amount where this cost centre is found you still got to convert it to double to not get an error. But when the condition never becomes true it will return the sum of the amounts without converting.

Regards

Accessing ASP datasource results through VB code

I have the following datasource...

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ***%>"

ProviderName="<%$ ***%>"SelectCommand='SELECT "var1", "var2", "var3" FROM "tbl1"'>

</asp:SqlDataSource>

Would it be possible to use VB code to read the reasults from this datasource? (There are conditions that restrict me from running the query itself through VB code.)

As always, any help is greatly appreciated. Thanks!

you should use the connection and recordset that is intrinsic to vb....I am not sure you can use vb6 or vb5 to look into a dataset.

you could use vb.net but i am stating the obvious here...

actually i found this

http://www.dotnet247.com/247reference/msgs/15/75021.aspx

looks like you can do it but will need some extra code.

Accessing a Report Server from within a winform

Sometime back I posted a message asking about how to access a Report Server
from within a winform. I got a message back that said the following:
"What I did is incorporate the WebBrowser ActiveX object onto a win form and
the set its URL to the URL that would be in a browser. So far this has
worked extrememely well, but I haven't deployed it yet."
My question is, how does on do this? That is, I so no WebBrowser ActiveX
object in the toolbox that can be dragged and dropped onto a winform. It
would help if from a winform I was able to access the Report Server. How
might this be done?You have a couple of options:
Using URL Access:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_6f6q.asp?frame=true
Using SOAP:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_7vqa.asp?frame=true
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Woody Splawn" <nospam@.splawns.com> wrote in message
news:OMvqySZgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Sometime back I posted a message asking about how to access a Report
Server
> from within a winform. I got a message back that said the following:
> "What I did is incorporate the WebBrowser ActiveX object onto a win form
and
> the set its URL to the URL that would be in a browser. So far this has
> worked extrememely well, but I haven't deployed it yet."
> My question is, how does on do this? That is, I so no WebBrowser ActiveX
> object in the toolbox that can be dragged and dropped onto a winform. It
> would help if from a winform I was able to access the Report Server. How
> might this be done?
>
>|||Woody:
In your forms application right click on the Toolbox and select
"Customize". On the COM components tab in the next dialog select
'Microsoft Web Browser' and click OK.
You should now have a web browser control in the Toolbox you can drop
on your form. It's a bit clunky since it is ActiveX based, but it does
work. VS2005 has a native component to do this, but that doesn't solve
today's problems now does it? :)
Hope this helps,
--
Scott
http://www.OdeToCode.com
On Fri, 13 Aug 2004 17:21:55 -0700, "Woody Splawn"
<nospam@.splawns.com> wrote:
>Sometime back I posted a message asking about how to access a Report Server
>from within a winform. I got a message back that said the following:
>"What I did is incorporate the WebBrowser ActiveX object onto a win form and
>the set its URL to the URL that would be in a browser. So far this has
>worked extrememely well, but I haven't deployed it yet."
>My question is, how does on do this? That is, I so no WebBrowser ActiveX
>object in the toolbox that can be dragged and dropped onto a winform. It
>would help if from a winform I was able to access the Report Server. How
>might this be done?
>

Monday, March 19, 2012

Access Violoation

The following fairly basic stored proc runs perfectly on my laptop (SQL
Server 2000 - 8.00.194 Personal Edition), but on my dev server (SQL Server
2000 - 8.00.760 Enterprise Edition, SP3) I get: SqlDumpExceptionHandler:
Process xx generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process..
Any help is most appreciated.
CREATE Procedure oContent_ins_Attachment
@.int_AttachmentTypeID int,
@.str_LinkPath text,
@.str_UserID varchar(100),
@.str_HistoryUserID varchar(100),
@.byt_FileContent image = null,
@.int_FileLength int = 0,
@.str_FileType varchar(50) = null,
@.str_LinkTitle varchar(255) = null,
@.str_FileName varchar(100) = null,
@.str_FileExt varchar(10) = null,
@.int_AttachmentID int output
AS
BEGIN
SET NOCOUNT ON
declare @.str_HyperlinkPath varchar(1000)
begin tran
select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
AttachmentDetail
if @.@.error != 0
begin
if @.@.trancount > 0
begin
rollback tran
select @.int_AttachmentID = 0
return -1
end
else
return -1
end
if @.str_FileExt is null
insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
LinkDate, LinkTitle, UserID, FileName)
values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
else
insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
LinkDate, LinkTitle, UserID, FileName)
values (@.int_AttachmentID,
@.int_AttachmentTypeID,
convert(varchar(1000),@.str_LinkPath) +
convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
getdate(),
@.str_LinkTitle,
@.str_UserID,
@.str_FileName)
if @.@.error != 0
begin
if @.@.trancount > 0
begin
rollback tran
select @.int_AttachmentID = 0
return -2
end
else
return -2
end
else
if @.@.trancount > 0
commit tran
insert into AttachmentDetail_H
select *, @.str_HistoryUserID, 'Inserted', getdate(), null
from AttachmentDetail
where AttachmentID = @.int_AttachmentID
if @.byt_FileContent is not null
begin
insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
@.str_FileType)
insert into AttachedFiles_H
select *, @.str_HistoryUserID, 'Inserted', getdate(), null
from AttachedFiles
where AttachmentID = @.int_AttachmentID
end
SET NOCOUNT OFF
ENDIf you give a full repro (e.g. table structure, sample data, an example call
to the proc, etc.) we can try to reproduce the AV on newer builds, so you
can know if it is already fixed on a post-SP3 build, or if you've found a
new bug.
See http://www.aspfaq.com/5006 for details.
(Also, update your laptop... you are practically begging to get infected by
Slammer.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"smay" <smay@.discussions.microsoft.com> wrote in message
news:4C55F48E-741E-41BF-8C13-37AE9EBBE5E2@.microsoft.com...
> The following fairly basic stored proc runs perfectly on my laptop (SQL
> Server 2000 - 8.00.194 Personal Edition), but on my dev server (SQL
Server
> 2000 - 8.00.760 Enterprise Edition, SP3) I get: SqlDumpExceptionHandler:
> Process xx generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process..
> Any help is most appreciated.
> CREATE Procedure oContent_ins_Attachment
> @.int_AttachmentTypeID int,
> @.str_LinkPath text,
> @.str_UserID varchar(100),
> @.str_HistoryUserID varchar(100),
> @.byt_FileContent image = null,
> @.int_FileLength int = 0,
> @.str_FileType varchar(50) = null,
> @.str_LinkTitle varchar(255) = null,
> @.str_FileName varchar(100) = null,
> @.str_FileExt varchar(10) = null,
> @.int_AttachmentID int output
> AS
> BEGIN
> SET NOCOUNT ON
> declare @.str_HyperlinkPath varchar(1000)
> begin tran
> select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
> AttachmentDetail
> if @.@.error != 0
> begin
> if @.@.trancount > 0
> begin
> rollback tran
> select @.int_AttachmentID = 0
> return -1
> end
> else
> return -1
> end
> if @.str_FileExt is null
> insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> LinkDate, LinkTitle, UserID, FileName)
> values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
> getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
> else
> insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> LinkDate, LinkTitle, UserID, FileName)
> values (@.int_AttachmentID,
> @.int_AttachmentTypeID,
> convert(varchar(1000),@.str_LinkPath) +
> convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
> getdate(),
> @.str_LinkTitle,
> @.str_UserID,
> @.str_FileName)
> if @.@.error != 0
> begin
> if @.@.trancount > 0
> begin
> rollback tran
> select @.int_AttachmentID = 0
> return -2
> end
> else
> return -2
> end
> else
> if @.@.trancount > 0
> commit tran
> insert into AttachmentDetail_H
> select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> from AttachmentDetail
> where AttachmentID = @.int_AttachmentID
> if @.byt_FileContent is not null
> begin
> insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
> values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
> @.str_FileType)
> insert into AttachedFiles_H
> select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> from AttachedFiles
> where AttachmentID = @.int_AttachmentID
> end
> SET NOCOUNT OFF
> END|||CREATE TABLE AttachmentTypes
(
AttachmentTypeID int not null,
TypeName varchar(20) not null,
TypeDesc text null
Primary Key (AttachmentTypeID)
)
GO
insert into AttachmentTypes select 3,'UploadedFile_DB','File uploaded into
the AttachedFiles table.'
go
CREATE TABLE AttachmentDetail
(
AttachmentID int not null,
AttachmentTypeID int not null references AttachmentTypes(AttachmentTypeID),
LinkPath text not null,
LinkDate datetime not null,
LinkTitle varchar(255) null,
UserID varchar(100) not null,
FileName varchar(100) null
Primary Key (AttachmentID)
)
GO
CREATE TABLE AttachedFiles
(
AttachmentID int not null references AttachmentDetail(AttachmentID),
FileContent image not null,
FileSize float not null,
MimeType varchar(50) not null
Primary Key (AttachmentID)
)
GO
CREATE TABLE AttachmentDetail_H
(
AttachmentID int not null,
AttachmentTypeID int not null,
LinkPath text not null,
LinkDate datetime not null,
LinkTitle varchar(255) null,
UserID varchar(100) not null,
FileName varchar(100) null,
HistoryUserID varchar(100) not null,
HistoryAction varchar(20) not null,
HistoryDateTime datetime not null,
HistoryComments text null
)
GO
CREATE clustered index AttachmentDetail_H_idc on
AttachmentDetail_H(AttachmentID, HistoryDateTime)
GO
CREATE TABLE AttachedFiles_H
(
AttachmentID int not null,
FileContent image not null,
FileSize float not null,
MimeType varchar(50) not null,
HistoryUserID varchar(100) not null,
HistoryAction varchar(20) not null,
HistoryDateTime datetime not null,
HistoryComments text null
)
GO
CREATE clustered index AttachedFiles_H_idc on AttachedFiles_H(AttachmentID,
HistoryDateTime)
GO
declare @.P1 int
set @.P1=NULL
exec oContent_ins_Attachment @.int_AttachmentTypeID = 3, @.str_LinkPath ='Production Chang', @.str_UserID = 'smay', @.byt_FileContent =0x50726F64756374696F6E206368616E6765733A0D0A0D0A2D2D205345435552495459202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A64726F702070726F636564757265206F53656375726974795F73656C5F417574686F72697A6555736572436F6D706F6E656E740D0A64726F702070726F636564757265206F53656375726974795F73656C5F417574686F72697A65557365724D6F64756C650D0A636F6D70696C6520616C6C205350730D0A696E6372656173652055736572494420746F2076617263686172283130302920696E3A0D0A0955736572732C2055736572735465726D696E617465642C2055736572496E666F2C205573657250617373776F7264732C20436F6D70616E69657355736572732C204D6F64756C6573436F6D706F6E656E747355736572732C2047726F75707355736572730D0A0D0A0D0A2D2D20454D455247454E4359204D534753202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A44524F502050726F636564757265206F456D657267656E63794D7367735F73656C5F4D6573736167654974656D730D0A636F6D70696C65206F456D657267656E63794D7367735F73656C5F4D73674974656D730D0A0D0A0D0A2D2D20474C4F42414C202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A44524F502050726F636564757265206F476C6F62616C5F73656C5F4170706C69636174696F6E5661726961626C65730D0A636F6D70696C65206F476C6F62616C5F73656C5F4170705661726961626C65730D0A0D0A0D0A2D2D204D454E55202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A696E6372656173652055736572494420746F2076617263686172283130302920696E204D656E75557365720D0A636F6D70696C65206F4D656E755F73656C5F5363726F6C6C526F77436F756E740D0A0D0A0D0A2D2D2053544154495354494353202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A696E6372656173652055736572494420746F2076617263686172283130302920696E2050616765537461746973746963730D0A636F6D70696C65206F537461746973746963735F696E735F506167655374617469737469630D0A0D0A0D0A,
@.int_FileLength = 1165, @.str_FileType = 'text/plain', @.str_HistoryUserID ='smay', @.int_AttachmentID = @.P1 output
select @.P1
"Aaron [SQL Server MVP]" wrote:
> If you give a full repro (e.g. table structure, sample data, an example call
> to the proc, etc.) we can try to reproduce the AV on newer builds, so you
> can know if it is already fixed on a post-SP3 build, or if you've found a
> new bug.
> See http://www.aspfaq.com/5006 for details.
> (Also, update your laptop... you are practically begging to get infected by
> Slammer.)
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "smay" <smay@.discussions.microsoft.com> wrote in message
> news:4C55F48E-741E-41BF-8C13-37AE9EBBE5E2@.microsoft.com...
> > The following fairly basic stored proc runs perfectly on my laptop (SQL
> > Server 2000 - 8.00.194 Personal Edition), but on my dev server (SQL
> Server
> > 2000 - 8.00.760 Enterprise Edition, SP3) I get: SqlDumpExceptionHandler:
> > Process xx generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
> SQL
> > Server is terminating this process..
> >
> > Any help is most appreciated.
> >
> > CREATE Procedure oContent_ins_Attachment
> > @.int_AttachmentTypeID int,
> > @.str_LinkPath text,
> > @.str_UserID varchar(100),
> > @.str_HistoryUserID varchar(100),
> > @.byt_FileContent image = null,
> > @.int_FileLength int = 0,
> > @.str_FileType varchar(50) = null,
> > @.str_LinkTitle varchar(255) = null,
> > @.str_FileName varchar(100) = null,
> > @.str_FileExt varchar(10) = null,
> > @.int_AttachmentID int output
> > AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > declare @.str_HyperlinkPath varchar(1000)
> >
> > begin tran
> > select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
> > AttachmentDetail
> >
> > if @.@.error != 0
> > begin
> > if @.@.trancount > 0
> > begin
> > rollback tran
> > select @.int_AttachmentID = 0
> > return -1
> > end
> > else
> > return -1
> > end
> >
> > if @.str_FileExt is null
> > insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> > LinkDate, LinkTitle, UserID, FileName)
> > values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
> > getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
> > else
> > insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> > LinkDate, LinkTitle, UserID, FileName)
> > values (@.int_AttachmentID,
> > @.int_AttachmentTypeID,
> > convert(varchar(1000),@.str_LinkPath) +
> > convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
> > getdate(),
> > @.str_LinkTitle,
> > @.str_UserID,
> > @.str_FileName)
> >
> > if @.@.error != 0
> > begin
> > if @.@.trancount > 0
> > begin
> > rollback tran
> > select @.int_AttachmentID = 0
> > return -2
> > end
> > else
> > return -2
> > end
> > else
> > if @.@.trancount > 0
> > commit tran
> >
> > insert into AttachmentDetail_H
> > select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> > from AttachmentDetail
> > where AttachmentID = @.int_AttachmentID
> >
> > if @.byt_FileContent is not null
> > begin
> > insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
> > values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
> > @.str_FileType)
> >
> > insert into AttachedFiles_H
> > select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> > from AttachedFiles
> > where AttachmentID = @.int_AttachmentID
> > end
> >
> > SET NOCOUNT OFF
> > END
>
>|||You didn't provide enough for a repro. The AV is likely coming from the
code in the stored procedure, oContent_ins_Attachment, which you didn't
include. Please make sure to include it and all dependent objects, and test
your repro on a blank database to make sure you've included all the
structure and data necessary for someone else to reproduce the problem.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I did include it in my original post. Here it is again.
CREATE Procedure oContent_ins_Attachment
@.int_AttachmentTypeID int,
@.str_LinkPath text,
@.str_UserID varchar(100),
@.str_HistoryUserID varchar(100),
@.byt_FileContent image = null,
@.int_FileLength int = 0,
@.str_FileType varchar(50) = null,
@.str_LinkTitle varchar(255) = null,
@.str_FileName varchar(100) = null,
@.str_FileExt varchar(10) = null,
@.int_AttachmentID int output
AS
BEGIN
SET NOCOUNT ON
declare @.str_HyperlinkPath varchar(1000)
begin tran
select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
AttachmentDetail
if @.@.error != 0
begin
if @.@.trancount > 0
begin
rollback tran
select @.int_AttachmentID = 0
return -1
end
else
return -1
end
if @.str_FileExt is null
insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
LinkDate, LinkTitle, UserID, FileName)
values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
else
insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
LinkDate, LinkTitle, UserID, FileName)
values (@.int_AttachmentID,
@.int_AttachmentTypeID,
convert(varchar(1000),@.str_LinkPath) +
convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
getdate(),
@.str_LinkTitle,
@.str_UserID,
@.str_FileName)
if @.@.error != 0
begin
if @.@.trancount > 0
begin
rollback tran
select @.int_AttachmentID = 0
return -2
end
else
return -2
end
else
if @.@.trancount > 0
commit tran
insert into AttachmentDetail_H
select *, @.str_HistoryUserID, 'Inserted', getdate(), null
from AttachmentDetail
where AttachmentID = @.int_AttachmentID
if @.byt_FileContent is not null
begin
insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
@.str_FileType)
insert into AttachedFiles_H
select *, @.str_HistoryUserID, 'Inserted', getdate(), null
from AttachedFiles
where AttachmentID = @.int_AttachmentID
end
SET NOCOUNT OFF
END
"Aaron [SQL Server MVP]" wrote:
> You didn't provide enough for a repro. The AV is likely coming from the
> code in the stored procedure, oContent_ins_Attachment, which you didn't
> include. Please make sure to include it and all dependent objects, and test
> your repro on a blank database to make sure you've included all the
> structure and data necessary for someone else to reproduce the problem.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||I don't get an access violation, but I'm using a build far later than 760
(and don't have a 760 handy to confirm your AV). You could try a newer
hotfix in the dev environment; the most recent I know of that is publicly
available is 878 (http://support.microsoft.com/?kbid=838166).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"smay" <smay@.discussions.microsoft.com> wrote in message
news:D75DE4EE-F63E-4FA7-B3CF-98CEE15C115E@.microsoft.com...
> I did include it in my original post. Here it is again.
> CREATE Procedure oContent_ins_Attachment
> @.int_AttachmentTypeID int,
> @.str_LinkPath text,
> @.str_UserID varchar(100),
> @.str_HistoryUserID varchar(100),
> @.byt_FileContent image = null,
> @.int_FileLength int = 0,
> @.str_FileType varchar(50) = null,
> @.str_LinkTitle varchar(255) = null,
> @.str_FileName varchar(100) = null,
> @.str_FileExt varchar(10) = null,
> @.int_AttachmentID int output
> AS
> BEGIN
> SET NOCOUNT ON
> declare @.str_HyperlinkPath varchar(1000)
> begin tran
> select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
> AttachmentDetail
> if @.@.error != 0
> begin
> if @.@.trancount > 0
> begin
> rollback tran
> select @.int_AttachmentID = 0
> return -1
> end
> else
> return -1
> end
> if @.str_FileExt is null
> insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> LinkDate, LinkTitle, UserID, FileName)
> values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
> getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
> else
> insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> LinkDate, LinkTitle, UserID, FileName)
> values (@.int_AttachmentID,
> @.int_AttachmentTypeID,
> convert(varchar(1000),@.str_LinkPath) +
> convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
> getdate(),
> @.str_LinkTitle,
> @.str_UserID,
> @.str_FileName)
> if @.@.error != 0
> begin
> if @.@.trancount > 0
> begin
> rollback tran
> select @.int_AttachmentID = 0
> return -2
> end
> else
> return -2
> end
> else
> if @.@.trancount > 0
> commit tran
> insert into AttachmentDetail_H
> select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> from AttachmentDetail
> where AttachmentID = @.int_AttachmentID
> if @.byt_FileContent is not null
> begin
> insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
> values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
> @.str_FileType)
> insert into AttachedFiles_H
> select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> from AttachedFiles
> where AttachmentID = @.int_AttachmentID
> end
> SET NOCOUNT OFF
> END
> "Aaron [SQL Server MVP]" wrote:
> > You didn't provide enough for a repro. The AV is likely coming from the
> > code in the stored procedure, oContent_ins_Attachment, which you didn't
> > include. Please make sure to include it and all dependent objects, and
test
> > your repro on a blank database to make sure you've included all the
> > structure and data necessary for someone else to reproduce the problem.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >

Access Violoation

Access Violation in SQL Server Native Client

Hi,
has anybody found a similar Error or could this verified?
When I run the following Statement:
insert into FACFG_SML_IDX
(FACFG_SML_IDX.WERK_ID,FACFG_SML_IDX.SML_ID,FACFG_SML_IDX.LAST_INDEX) values
(?,?,0)
I got an access violation here is the German error message:
"Es wurde versucht, im geschtzten Speicher zu lesen oder zu schreiben. Dies
ist hufig ein Hinweis darauf, dass anderer Speicher beschdigt ist."
This happens when I call SQLDescribeParam for the first parameter.
Here's the stack-trace from Visual Studio Debugger:
> msvcr80.dll!memcpy(unsigned char * dst=0x11080042, unsigned char *
> src=0x00000000, unsigned long count=158431312) Zeile 257 Asm
sqlncli.dll!337fbb90()
[Unten angegebene Rahmen sind mglicherweise nicht korrekt und/oder
fehlen, keine Symbole geladen fr sqlncli.dll]
sqlncli.dll!3387c7eb()
ntdll.dll!_RtlFreeHeapSlowly@.12() + 0x207 Bytes
ntdll.dll!_RtlFreeHeap@.12() + 0x16470 Bytes
ntdll.dll!_RtlLookupFunctionTable@.12() + 0x7d Bytes
ntdll.dll!ExecuteHandler2@.20() + 0x26 Bytes
ntdll.dll!ExecuteHandler@.20() + 0x24 Bytes
[Externer Code]
...
When the statement is change in the following way or the SQL Server driver
from XPSP2 is used there is no error:
insert into FACFG_SML_IDX (WERK_ID,SML_ID,LAST_INDEX) values (?,?,0)
I found this error in native client version 2005.90.3042.00 (SP2) and
2005.90.2047 (SP1).
Greetings
Dieter PelzHi, Dieter,
I understand that you encountered the Geman error message when you usd the
statement:
" insert into FACFG_SML_IDX
(FACFG_SML_IDX.WERK_ID,FACFG_SML_IDX.SML_ID,FACFG_SML_IDX.LAST_INDEX)
values
(?,?,0)" in SQLDescribeParam; however it succeeded when you used " insert
into FACFG_SML_IDX (WERK_ID,SML_ID,LAST_INDEX) values (?,?,0)".
If I have misunderstood, please let me know.
Appreciate your understanding that our MSDN Managed Newsgroups are focused
on English language support. It will be able to let us better understand
your issue clearly if you could do some translation in your future posts.
Anyway, after compare the following two articles:
http://msdn2.microsoft.com/de-de/library/33xdxt7x(vs.80).aspx
http://msdn2.microsoft.com/en-us/library/33xdxt7x(vs.80).aspx
, I know that the corresponding english meaning is "Attempted to read or
write protected memory. This is often an indication that other memory has
been corrupted".
I will write a test project to see if I can reproduce your issue and I will
let you know as soon as possible. Also, since the error indicates that this
is a memory related issue, you may also carefully check if there are some
array overflow issues.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
I have reproduced your issue at my side. Now I am consulting the product
team on this issue. I will let you know the response as soon as possible.
If it is convenient for your, I would like your sending me an email
response so that I can timely update you if I get the response from the
product team. My email address is changliw_at_microsoft_dot_com.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hello,
sorry I thought an "Access Violation Exception" is clear enough, so I don't
translated the German error message.
Best Regards
Dieter Pelz
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> schrieb im Newsbeit
rag
news:0ojLLawXHHA.3856@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Dieter,
> I understand that you encountered the Geman error message when you usd the
> statement:
> " insert into FACFG_SML_IDX
> (FACFG_SML_IDX.WERK_ID,FACFG_SML_IDX.SML_ID,FACFG_SML_IDX.LAST_INDEX)
> values
> (?,?,0)" in SQLDescribeParam; however it succeeded when you used " insert
> into FACFG_SML_IDX (WERK_ID,SML_ID,LAST_INDEX) values (?,?,0)".
> If I have misunderstood, please let me know.
> Appreciate your understanding that our MSDN Managed Newsgroups are focused
> on English language support. It will be able to let us better understand
> your issue clearly if you could do some translation in your future posts.
> Anyway, after compare the following two articles:
> http://msdn2.microsoft.com/de-de/library/33xdxt7x(vs.80).aspx
> http://msdn2.microsoft.com/en-us/library/33xdxt7x(vs.80).aspx
> , I know that the corresponding english meaning is "Attempted to read or
> write protected memory. This is often an indication that other memory has
> been corrupted".
> I will write a test project to see if I can reproduce your issue and I
> will
> let you know as soon as possible. Also, since the error indicates that
> this
> is a memory related issue, you may also carefully check if there are some
> array overflow issues.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>|||Hi, Dieter,
Never mind! I have received your email response. Also, I just get a
response from a person; however he would like to know if my code was same
as yours. I would like to attach my code here and if there is anything
different from yours, please let me know.
========================================
====================================
==
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLPOINTER rgbValue=NULL;
SQLRETURN retcode;
SQLWCHAR ConnStrOut[MAXBUFLEN];
SQLSMALLINT cbConnStrOut = 0;
SQLSMALLINT NumParams, i, DataType, DecimalDigits, Nullable;
SQLUINTEGER ParamSize;
SQLINTEGER ParamLenArray[2];
SQLWCHAR Statement[1000];
WCHAR retstr[10];
SQLWCHAR* PtrArray[2];
/*Allocate environment handle */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set the ODBC version environment attribute */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(void*)SQL_OV_ODBC3, 0);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Allocate connection handle */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set login timeout to 5 seconds. */
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT,(SQLPOINTER*)5, 0);
/* Connect to data source */
retcode = SQLConnect(hdbc, (SQLWCHAR*) L"myWOW", SQL_NTS,
(SQLWCHAR*) L"sa", SQL_NTS,
(SQLWCHAR*) L"Password1!", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
/* Allocate statement handle */
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
/* Process data */
WCHAR strStm[100]=L"INSERT INTO
ANT([ANT].[NAME],[ANT].[DESCRIPTION]) values (?,?)";
wcscpy(Statement,strStm);
retcode = SQLPrepare(hstmt, Statement,
SQL_NTS);
// Check to see if there are any parameters.
If so, process them.
SQLNumParams(hstmt, &NumParams);
if (NumParams) {
for (i = 0; i < NumParams; i++) {
SQLDescribeParam(hstmt, i+1, &DataType,
&ParamSize, &DecimalDigits, &Nullable);
PtrArray[i]=(SQLWCHAR *) malloc(50);
wcscpy(PtrArray[i],L"ABCDE");
SQLBindParameter(hstmt, i + 1,
SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 4,
0, PtrArray[i],
0,&ParamLenArray[i]);
}
// Execute the statement.
retcode = SQLExecute(hstmt);
free(PtrArray[0]);
free(PtrArray[1]);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
SQLDisconnect(hdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
}
========================================
====================================
================
If it is convenient for you, I recommend that you mail me a test project of
yours and I will forward to him.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Dieter,
I got another two responses from the product team. I would like to forward
them to you for your reference.
========================================
====================================
==
My book "A Guide to the SQL Standard" states this:
The general syntax is:
INSERT INTO table [ (column-commalist) ] source
Where "table" identifies the target table, the identifiers in parentheses
identify some or all of the columns of that table (by their unqualified
column names)
Italics are in the book to emphasize the names must be unqualified. So as
I was discussing with Warren the other day - I don't believe this is valid
syntax and probably should error. Reasonable consumers should not expect
this to work. However, if we have let them get away with it in the past we
may be stuck with some support.
========================================
====================================
==
There is some ambiguity here as to what is semantically correct. As an
example:
INSERT INTO [MyDB].[dbo].[Table1]
([Table1].[col1]
,[Table1].[col2]
,[SomeOtherTableThatMayOrMayNotExist].[col3]
,[Table1].[col4])
VALUES
(?, ?, ?, ?)
One wouldn't normally expect this query to work, yet it doesin fact, you
can put any n-part name on any of these columns in the query list and it
will still work so long as the column specifier identifies a column in the
table specified by the object name portion.
Which brings up some interesting secondary questionsfor instance, what
does OLEDB do with this query string when GetParameterInfo is called? Does
it know to remove the table portion from the column specifier? Should
ODBC/OLEDB error on SQLDescribeParam/GetParameterInfo with this query or
should they silently strip the table portion from the column specifier as
does the engine? I'm on a separate thread with some guys from the engine to
debate these pointswill follow up here once that's known.
========================================
====================================
==
Based on the two responses, I think that it is reasonable that column name
should not be used with table name in ODBC Driver.
Hope this helps.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support

Thursday, March 8, 2012

Access to SQL Server conversion - format number question

In Access the following sql
select Format(0.12345,"#.00###") from tblA
returns
0.12345
select Format(0.123,"#.00###") from tblA
returns
0.123
In SQL Server
select convert(decimal(6,5),0.12345)
returns
0.12345
But, select convert(decimal(6,5),0.123)
returns
0.12300 instead of 0.123
Is there a way in SQL Server to convert the number so that if the number of
character after decimal point is 2, show only those 2 without training 0's,
and if the number of character after decimal point is 5 shows all the 5
numbers ?
Thanks.Paul fpvt2 wrote:
> In Access the following sql
> select Format(0.12345,"#.00###") from tblA
> returns
> 0.12345
> select Format(0.123,"#.00###") from tblA
> returns
> 0.123
> In SQL Server
> select convert(decimal(6,5),0.12345)
> returns
> 0.12345
> But, select convert(decimal(6,5),0.123)
> returns
> 0.12300 instead of 0.123
> Is there a way in SQL Server to convert the number so that if the number o
f
> character after decimal point is 2, show only those 2 without training 0's
,
> and if the number of character after decimal point is 5 shows all the 5
> numbers ?
> Thanks.
Your client application determines how numbers are displayed, not SQL
Server. You should refer to the formatting capabilities of whatever
client application or language you are using. Unfortunately you didn't
specify that so we can't easily help you with it.
To have control over the formatting from SQL you'd have to return a
string rather than a number, which is not usually recommended.
David Portas
SQL Server MVP
--|||Thank you.
> To have control over the formatting from SQL you'd have to return a
> string rather than a number, which is not usually recommended.
I am sorry, I posted the message incorrectly, it should be string, not a
number, like the following:
in Access:
select Format('0.123',"#.00###") from tblA
returns
0.123
in SQL Server:
But, select convert(decimal(6,5),'0.123')
returns
0.12300 instead of 0.123
The language we are using is VB, but unfortunately the VB application
expecting the result of the query already formatted.
Is there a way to do the above in SQL Server ?
Thank you.
"David Portas" wrote:

> Paul fpvt2 wrote:
> Your client application determines how numbers are displayed, not SQL
> Server. You should refer to the formatting capabilities of whatever
> client application or language you are using. Unfortunately you didn't
> specify that so we can't easily help you with it.
> To have control over the formatting from SQL you'd have to return a
> string rather than a number, which is not usually recommended.
> --
> David Portas
> SQL Server MVP
> --
>

Access to SQL

Hello, How can I turn the following Access query into a SQL query?
Every time I try, I get Cartesian product
SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE, dbo_ADDRESS.COUNTR
Y
FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER =
dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
(QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
(QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER = dbo_ADDRESS.SEQUENCE_NUMBER)
AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));Hi
This may be a data issue
Your query looks fine:
SELECT C.CLIENT_NUMBER,
C.LNAME1,
C.FNAME1,
C.INIT1,
C.LNAME2,
C.FNAME2,
C.INIT2,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.CITY,
A.STATE,
A.ZIPCODE,
A.COUNTRY
FROM QNameAddress1 Q
JOIN dbo_CLIENT C ON Q.CLIENT_NUMBER = C.CLIENT_NUMBER
JOIN dbo_ADDRXREF X ON Q.MaxOfPOLICY_DATE_TIME = X.POLICY_DATE_TIME AND
Q.POLICY_NUMBER = X.POLICY_NUMBER
JOIN dbo_ADDRESS A ON X.SEQUENCE_NUMBER = A.SEQUENCE_NUMBER AND
C.CLIENT_NUMBER = A.CLIENT_NUMBER
WHERE A.ADDR_USAGE='1'
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
example data (as insert statements). It may also be worth posting expected
output from sample data.
John
"Patrice" wrote:

> Hello, How can I turn the following Access query into a SQL query?
> Every time I try, I get Cartesian product
>
> SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
> dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
> dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
> dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE, dbo_ADDRESS.COUN
TRY
> FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER
=
> dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
> (QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
> (QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
> dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER = dbo_ADDRESS.SEQUENCE_NUMBER
)
> AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
> WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));
>
>|||Plug that into the Query Analyzer and you should find the problem. Most
likely some or all of your INNER JOINS should be OUTER.
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:0D946FD4-B400-482F-8510-1AA01E1432BD@.microsoft.com...
> Hello, How can I turn the following Access query into a SQL query?
> Every time I try, I get Cartesian product
>
> SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
> dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
> dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
> dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE,
> dbo_ADDRESS.COUNTRY
> FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER
> =
> dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
> (QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
> (QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
> dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER =
> dbo_ADDRESS.SEQUENCE_NUMBER)
> AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
> WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));
>
>

Access to snapshot tempfiles is denied

I take a snapshot of a report an it views fine the first time but I get the
following error on subsequent viewings:
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\RSTempFiles\RSFile_6b4351c3-0cfb-4c51-bda1-ac280f1d7eea" is denied.
where the name of the file changes when i re-snapshot it. I've checked the
permissions on the RSTempFiles folder and both the System and ASPNET have
Full Control over it.
Any ideas why this is still happening?
TIA,
Dan Fell.Has anyone solved this issue? I am having the same exact problem with users
who are browsers.
"Dan Fell" wrote:
> I take a snapshot of a report an it views fine the first time but I get the
> following error on subsequent viewings:
> An internal error occurred on the report server. See the error log for more
> details. (rsInternalError) Get Online Help
> Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\RSTempFiles\RSFile_6b4351c3-0cfb-4c51-bda1-ac280f1d7eea" is denied.
> where the name of the file changes when i re-snapshot it. I've checked the
> permissions on the RSTempFiles folder and both the System and ASPNET have
> Full Control over it.
> Any ideas why this is still happening?
> TIA,
> Dan Fell.|||I am having the same exact problem too with snapshots but it doesn't
happen with all the reports. I am assuming that it's a new issue with
reporting services SP2, because snapshots always worked for me prior to
upgrading to SP2. Any help is greatly appreciated. Thanks.
T Robichaux wrote:
> Has anyone solved this issue? I am having the same exact problem with users
> who are browsers.
> "Dan Fell" wrote:
> >
> > I take a snapshot of a report an it views fine the first time but I get the
> > following error on subsequent viewings:
> >
> > An internal error occurred on the report server. See the error log for more
> > details. (rsInternalError) Get Online Help
> > Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\RSTempFiles\RSFile_6b4351c3-0cfb-4c51-bda1-ac280f1d7eea" is denied.
> >
> > where the name of the file changes when i re-snapshot it. I've checked the
> > permissions on the RSTempFiles folder and both the System and ASPNET have
> > Full Control over it.
> >
> > Any ideas why this is still happening?
> >
> > TIA,
> >
> > Dan Fell.|||I had the same Problem: Only one of many reports with the same settings
as the others (render with temporary copy for 30 min) creates always a
file in the RSTempFiles Folder.
I checked the Readme Files and found that since SP1 (section 4.4.4 New
Configuration Settings in sp2Readme_EN.htm) there are settings for
FileSharing.
I added the settings to the configuration
...
<Add Key="WebServiceUseFileShareStorage" Value="false" />
...
<WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
<FileShareStorageLocation>
<Path> XXXXX </Path>
</FileShareStorageLocation>
but it still creates the temporary file. You can see that the settings
are regarded when you change the Path. Then the tempfile is placed there
and the errormessage shows the access denied for this path. Either the
setting for the useFileShare = false is ignored or there is some other
setting (in the report?) which "overwrites" the setting.
WORKAROUND:
I gave read permission to everyone on the folder
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\RSTempFiles
and it works.
System:
SQL Server 2000, Reporting Services SP2 on as w2k Server, Windows 2003
AD, Integrated Security for SQLServer and IIS, mixed language (englisch,
german) for OS and Applications.
I hope the workaround helps and I still wait for a better solution or
explanation for this file creation.
Best regards,
Markus Mahlitz
Vasu Bojja schrieb:
> I am having the same exact problem too with snapshots but it doesn't
> happen with all the reports. I am assuming that it's a new issue with
> reporting services SP2, because snapshots always worked for me prior to
> upgrading to SP2. Any help is greatly appreciated. Thanks.
>
> T Robichaux wrote:
>> Has anyone solved this issue? I am having the same exact problem with users
>> who are browsers.
>> "Dan Fell" wrote:
>> I take a snapshot of a report an it views fine the first time but I get the
>> following error on subsequent viewings:
>> An internal error occurred on the report server. See the error log for more
>> details. (rsInternalError) Get Online Help
>> Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>> Services\RSTempFiles\RSFile_6b4351c3-0cfb-4c51-bda1-ac280f1d7eea" is denied.
>> where the name of the file changes when i re-snapshot it. I've checked the
>> permissions on the RSTempFiles folder and both the System and ASPNET have
>> Full Control over it.
>> Any ideas why this is still happening?
>> TIA,
>> Dan Fell.
>

Tuesday, March 6, 2012

Access to "C:/etc/etc/etc.rptproj.user" is denied

Hi All!!

I have recently come across the following problem when attempting to access the solution file for the project in question. I double click to access the file and VS2003 fires up, only for it to give me the above warning. At which point nothing is loaded in the tree!

When I check things out they do show up in Source Safe but if I need to add any new reports (which I do), I am unable to add them...

Cany anyone shed any light on the matter?

Thanks a million!!

I believe this answers your question:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=988559&SiteID=1

|||Issue been resolved thanks for the suggestion

Access takes 24-35 seconds while SQL Server takes only 2-3 seconds

I test my C# applicaiton both with Access and SQL Server database. I note
that the following code snippets takes 24-35 seconds with Access database
while takes only 2-3 seconds with SQL Server to fetch same number of records
(80,000 records).
//with Access Database
this.da = new OdbcDataAdapter("select Barcode, Description from
Items_Detail", DB.cn);
//with SQL Server
this.da = new SqlDataAdapter("select Barcode, Description from
Items_Detail", DB.cn);
//common code
this.da.Fill(this.ds, "Get_Quantity_Result");
grd_All_Recs.SetDataBinding(this.ds, "Get_Quantity_Result");
for my customer simplicity i want to give him solution with Access database
but with this un-acceptable delay i can't do this.
Please help in identifying that why it is taking much time to fetch records
from Access database.
Arif.Why Access? Why not SQL 2005 Express or SQL 2000 MSDE?
SQL Express and MSDE are free with most benefits of regular SQL Server
editions.
http://msdn.microsoft.com/library/d...r />
rview.asp
http://msdn.microsoft.com/library/d...br />
67ax.asp
ML

Saturday, February 25, 2012

Access Reports using IP Address

I have the following going on: A win 2k3 server running sql server and
reporting services. I have IIS configured on it and can run reports
internally on my network but when I try to access a report from the web I
get
nothing back. I can access Folders List and other properties of any selected
report but not able to view the
reports from web. I am using a static ip address to access the website for
now.
I deployed the reports using the IP address. Any ideas on what could be
wrong here?
by
Raj
In reporting services there is a config setting that tells report manager
where to find report server.
By default it is located at:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager\RSWebApplication.config
You need to change the ReportServerURL element to point to something that
you can 'see' externally. If you cannot see this external URL internally
thiswill break your internal reports.
"MSRS" <Mahashares@.yahoo.com> wrote in message
news:uMfvfkygFHA.3868@.TK2MSFTNGP14.phx.gbl...
>I have the following going on: A win 2k3 server running sql server and
> reporting services. I have IIS configured on it and can run reports
> internally on my network but when I try to access a report from the web I
> get
> nothing back. I can access Folders List and other properties of any
> selected
> report but not able to view the
> reports from web. I am using a static ip address to access the website for
> now.
> I deployed the reports using the IP address. Any ideas on what could be
> wrong here?
> by
> Raj
>
|||Thanks joe.
It is working.
by
Raj
"Joe" <noemail@.noemail.com> wrote in message
news:#nrmssygFHA.3940@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> In reporting services there is a config setting that tells report manager
> where to find report server.
> By default it is located at:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager\RSWebApplication.config
> You need to change the ReportServerURL element to point to something that
> you can 'see' externally. If you cannot see this external URL internally
> thiswill break your internal reports.
>
> "MSRS" <Mahashares@.yahoo.com> wrote in message
> news:uMfvfkygFHA.3868@.TK2MSFTNGP14.phx.gbl...
I[vbcol=seagreen]
for
>

Friday, February 24, 2012

Access Reports using IP Address

I have the following going on: A win 2k3 server running sql server and
reporting services. I have IIS configured on it and can run reports
internally on my network but when I try to access a report from the web I
get
nothing back. I can access Folders List and other properties of any selected
report but not able to view the
reports from web. I am using a static ip address to access the website for
now.
I deployed the reports using the IP address. Any ideas on what could be
wrong here?
by
RajIn reporting services there is a config setting that tells report manager
where to find report server.
By default it is located at:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager\RSWebApplication.config
You need to change the ReportServerURL element to point to something that
you can 'see' externally. If you cannot see this external URL internally
thiswill break your internal reports.
"MSRS" <Mahashares@.yahoo.com> wrote in message
news:uMfvfkygFHA.3868@.TK2MSFTNGP14.phx.gbl...
>I have the following going on: A win 2k3 server running sql server and
> reporting services. I have IIS configured on it and can run reports
> internally on my network but when I try to access a report from the web I
> get
> nothing back. I can access Folders List and other properties of any
> selected
> report but not able to view the
> reports from web. I am using a static ip address to access the website for
> now.
> I deployed the reports using the IP address. Any ideas on what could be
> wrong here?
> by
> Raj
>|||Thanks joe.
It is working.
by
Raj
"Joe" <noemail@.noemail.com> wrote in message
news:#nrmssygFHA.3940@.tk2msftngp13.phx.gbl...
> In reporting services there is a config setting that tells report manager
> where to find report server.
> By default it is located at:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager\RSWebApplication.config
> You need to change the ReportServerURL element to point to something that
> you can 'see' externally. If you cannot see this external URL internally
> thiswill break your internal reports.
>
> "MSRS" <Mahashares@.yahoo.com> wrote in message
> news:uMfvfkygFHA.3868@.TK2MSFTNGP14.phx.gbl...
> >I have the following going on: A win 2k3 server running sql server and
> > reporting services. I have IIS configured on it and can run reports
> > internally on my network but when I try to access a report from the web
I
> > get
> > nothing back. I can access Folders List and other properties of any
> > selected
> > report but not able to view the
> > reports from web. I am using a static ip address to access the website
for
> > now.
> >
> > I deployed the reports using the IP address. Any ideas on what could be
> > wrong here?
> >
> > by
> > Raj
> >
> >
>

Tuesday, March 27, 2012

Accessing local temporary tables

Accessing Linked Excel Server

Thursday, March 22, 2012

Accessing current active trace file

Accessing current active trace file

Accessing current active trace file

Accessing cubes parallely from 2 clients

Accessing conditional values in Groups

Accessing ASP datasource results through VB code

Accessing a Report Server from within a winform

Monday, March 19, 2012

Access Violoation

Access Violoation

Access Violoation

Access Violation in SQL Server Native Client

Thursday, March 8, 2012

Access to SQL Server conversion - format number question

Access to SQL

Access to snapshot tempfiles is denied

Tuesday, March 6, 2012

Access to "C:/etc/etc/etc.rptproj.user" is denied

Access takes 24-35 seconds while SQL Server takes only 2-3 seconds

Saturday, February 25, 2012

Access Reports using IP Address

Friday, February 24, 2012

Access Reports using IP Address

SQL Alert

Blog Archive

About Me