resize the multi select drop down

I need to resize (increase the width of) the multi select drop down box in report manager. Has anyone had any luck doing this?The width is not configurable. This is a suggestion I have seen several times and it is under consideration for future versions of the Reporting Services product.

Reseting Select Permissions for Public Role

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.I'd use GRANT SELECT ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) within the script.

-PatP|||That did the trick. Thanks for all the help.sql

Requiring / Trapping the View Report button

I would like to require that the users select the "view report" button
before the report renders.
How can I do that?
Also, can I trap the event for when that button is selected? how/
where?
Thank you.The report only renders automatically if there is either no parameters or if
all the parameters have defaults. You need one parameter that requires user
input to get the behavior you desire.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jobs" <jobs@.webdos.com> wrote in message
news:1193312927.881290.217200@.k79g2000hse.googlegroups.com...
>I would like to require that the users select the "view report" button
> before the report renders.
> How can I do that?
> Also, can I trap the event for when that button is selected? how/
> where?
> Thank you.
>|||thank you. That makes sense and works great from VS.NET. However, and
this is odd, when make the change to default the two date params to
null and deploy, I continue to get the original defaults that I had
oriiginally. I've cleared my cache, I've even made other cosmetic
changes to make sure i am deploying. It's almost as if somewhere on
the report server it's remembering the last set value. It continues to
automatically load the report with the original default values.
here's part of my xml source, note no VALUES tag:
<ReportParameter Name="iStartDate">
<DataType>DateTime</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="iEndDate">
<DataType>DateTime</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
Thanks.|||Perhaps I forgot to mention, my issue is through reportviewer over
asp.net.
Also, and maybe this would help, I am also passing some other internal
security related parameters from the codebehind. I'm doing that under
the on load subroutine when postback is false. This code has been
working nicely, but maybe this is what is forcing the report to
render, but somehow i doubt it and agree with your statement about the
empty parameters. the only mystery now is where the report continue to
get the original date values'
as a test, i tried changing the value, reloading, clearing cash, but
the original date defaults of 1/1/2007 and 1/1/2099 continue to come
back. It's just strange.
Protected Overrides Sub OnLoad(ByVal e As EventArgs)
If IsPostBack = "False" Then
Dim Roles() As String =GetRolesForUser(Page.User.Identity.Name.ToString)
Dim cred As New Retailer.ReportServerCredentials("myuser",
"mypassword", "mydomain")
ReportViewer1.ServerReport.ReportServerCredentials = cred
Dim param As New ReportParameter("r_user",
Page.User.Identity.Name.ToString)
Dim param2 As New ReportParameter("r_role", Roles(0))
Dim p() As ReportParameter = {param, param2}
ReportViewer1.ServerReport.SetParameters(p)
ReportViewer1.ServerReport.Refresh()
End If|||Drive deleting the report in Report Manager and re-deploy.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jobs" <jobs@.webdos.com> wrote in message
news:1193446029.715262.142250@.z24g2000prh.googlegroups.com...
> thank you. That makes sense and works great from VS.NET. However, and
> this is odd, when make the change to default the two date params to
> null and deploy, I continue to get the original defaults that I had
> oriiginally. I've cleared my cache, I've even made other cosmetic
> changes to make sure i am deploying. It's almost as if somewhere on
> the report server it's remembering the last set value. It continues to
> automatically load the report with the original default values.
> here's part of my xml source, note no VALUES tag:
> <ReportParameter Name="iStartDate">
> <DataType>DateTime</DataType>
> <AllowBlank>true</AllowBlank>
> <Prompt>Start Date</Prompt>
> </ReportParameter>
> <ReportParameter Name="iEndDate">
> <DataType>DateTime</DataType>
> <AllowBlank>true</AllowBlank>
> <Prompt>End Date</Prompt>
> </ReportParameter>
> Thanks.
>|||What about the trapping of the click event for "View Report"? Is this
possible?

requete SQL select d'une SP

Comment faire ce genre de requete ?
select top(10) T.*
from ( exec SEL_MODELES 767 ) T
WHERE T.ID > 50
Que SEL_MODELES fait-il ?
tudiez
http://msdn.microsoft.com/library/de...reate_7r1l.asp
John
"Ch." <christophe@.ipnet.fr> wrote in message
news:uwQLEJMKGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Comment faire ce genre de requete ?
> select top(10) T.*
> from ( exec SEL_MODELES 767 ) T
> WHERE T.ID > 50
>
|||On Fri, 3 Feb 2006 14:03:44 +0100, Ch. wrote:

>Comment faire ce genre de requete ?
>select top(10) T.*
>from ( exec SEL_MODELES 767 ) T
>WHERE T.ID > 50
>
Hi Ch.,
CREATE TABLE #T
(Column1 ... ,
Column2 ... ,
...)
go
INSERT INTO #T (Column1, Column2, ...)
EXEC SEL_MODELES 767
SELECT TOP (10) *
FROM #T
WHERE ID > 50
ORDER BY ...
go
DROP TABLE #T
go
Pour l'aide en Franais, tu peus aller meilleure vers
microsoft.public.fr.sqlserver.
Hugo Kornelis, SQL Server MVP

requete SQL select d'une SP

Comment faire ce genre de requete ?
select top(10) T.*
from ( exec SEL_MODELES 767 ) T
WHERE T.ID > 50Que SEL_MODELES fait-il ?
Étudiez
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp
John
"Ch." <christophe@.ipnet.fr> wrote in message
news:uwQLEJMKGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Comment faire ce genre de requete ?
> select top(10) T.*
> from ( exec SEL_MODELES 767 ) T
> WHERE T.ID > 50
>|||On Fri, 3 Feb 2006 14:03:44 +0100, Ch. wrote:
>Comment faire ce genre de requete ?
>select top(10) T.*
>from ( exec SEL_MODELES 767 ) T
>WHERE T.ID > 50
>
Hi Ch.,
CREATE TABLE #T
(Column1 ... ,
Column2 ... ,
...)
go
INSERT INTO #T (Column1, Column2, ...)
EXEC SEL_MODELES 767
--
SELECT TOP (10) *
FROM #T
WHERE ID > 50
ORDER BY ...
go
DROP TABLE #T
go
Pour l'aide en Français, tu peus aller meilleure vers
microsoft.public.fr.sqlserver.
--
Hugo Kornelis, SQL Server MVP

requete SQL select d'une SP

Comment faire ce genre de requete ?
select top(10) T.*
from ( exec SEL_MODELES 767 ) T
WHERE T.ID > 50Que SEL_MODELES fait-il ?
tudiez
_7r1l.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_7r1l.asp
John
"Ch." <christophe@.ipnet.fr> wrote in message
news:uwQLEJMKGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Comment faire ce genre de requete ?
> select top(10) T.*
> from ( exec SEL_MODELES 767 ) T
> WHERE T.ID > 50
>|||On Fri, 3 Feb 2006 14:03:44 +0100, Ch. wrote:

>Comment faire ce genre de requete ?
>select top(10) T.*
>from ( exec SEL_MODELES 767 ) T
>WHERE T.ID > 50
>
Hi Ch.,
CREATE TABLE #T
(Column1 ... ,
Column2 ... ,
..)
go
INSERT INTO #T (Column1, Column2, ...)
EXEC SEL_MODELES 767
--
SELECT TOP (10) *
FROM #T
WHERE ID > 50
ORDER BY ...
go
DROP TABLE #T
go
Pour l'aide en Franais, tu peus aller meilleure vers
microsoft.public.fr.sqlserver.
Hugo Kornelis, SQL Server MVP

request issue

Hi,
I've got two primary keys in a table:

Constraint(QueryId, ConstraintName)

In a stored procedure I select {QueryId, ConstraintName} couples that
match some criteria, and what I want to do is specifying in my a SELECT
statement that I want all of the {QueryId, ConstraintName} that are not
in my stored procedure result. With only one field, it would be easy :

Select * from Constraint where QueryId not in (Select QueryId from
OtherTable)

My explanations are not great but I think it's enough to understand
what I want.

Select * from Constraint where QueryId and ConstraintName not in
(select QueryId ,ConstraintName from OtherTable)
--> of course not correct, but then how can I do that ?

ThxI've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||See this thread:

http://groups.google.ch/group/comp...62403e78dd78646

Simon|||Use NOT EXISTS rather than NOT IN:

SELECT *
FROM [Constraint] AS T
WHERE NOT EXISTS
(SELECT *
FROM OtherTable
WHERE queryid = T.queryid
AND constraintname = T.constraintname)

CONSTRAINT is a reserved word and therefore not a good choice for a
table name.

--
David Portas
SQL Server MVP
--|||Try using a LEFT JOIN:

SELECT a.columnList --don't use *, explicitly name your columns
FROM TableA a LEFT JOIN TableB b ON a.Col1 =b.Col1 AND a.Col2 =b.Col2
WHERE b.Col1 IS NULL

HTH,
Stu|||I've actually solved this problem yesterday. I've done it the way David
suggested, using NOT EXISTS and it works just fine.
David, actually my table is called ConstraintMessages :) I wrote
Constraint as it's quicker to type!

Thx

Request a web page from SQL Server

Hi,

I want to request a webpage from sql server every hour.

I tried to make a job which contains a single ActiveX step.
I select VBScript and type

Dim Http
Set Http = CreateObject("Microsoft.XMLHTTP")
Http.Open "GET", "http://localhost/updater_watch.aspx", False
Http.Send
Set Http = Nothing

I set a schedule and on every execution I get an error in the event log.

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/11/2003
Time: 8:15:00 PM
User: N/A
Computer: NEXUS
Description:
SQL Server Scheduled Job 'updater' (0x444D7F519E70534C9EF81B1570382AEE) - Status: Failed - Invoked on: 2003-08-11 20:15:00 - Message: The job failed. The Job was invoked by Schedule 1 (s1). The last step to run was step 1 (invoke).

Is this the right way to do this task? When I execute the VBScript without the sql server, it runs without a problem.

Thanks, PanayotWhy not set it up as a ongoing job?|||Thanks for the reply garrydawkins,

that is what I have done.
I open eterprise manager and go to management-sql server agent-jobs-new job.
Then create the job, set a its type to activex, make a schedule to execute it every hour. it executes but exits with error. I suspect that it cannot create the xmlhttp object properly in the VB code.

If I only leave two simple statements:

dim somevar
set somevar = nothing

then the job exits with success.

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS
SQL database stored locally
created using MSDE.
TIAStart with this:
http://www.microsoft.com/downloads/...&displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net...
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS SQL database stored locally
created using MSDE.
TIA
Start with this:
http://www.microsoft.com/downloads/d...displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net. ..
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS SQL database stored locally
created using MSDE.
TIAStart with this:
http://www.microsoft.com/downloads/details.aspx?familyid=08e3d5f8-033d-420b-a3b1-3074505c03f3&displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net...
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

Reposted - Troubles setting variables

Yes. I need to set some variables from other queries in my SP that will be
used in the final Select statement. This is the only one I need returned.
I have modified the script as per other posts, but it still brings back 3
recordsets.
Here is the current version..
CREATE PROCEDURE dbo.Property_DisplayResults
(
@.ES varChar(10),
@.SS varChar(50),
@.PT varChar(10),
@.F varChar(10),
@.T varChar(10),
@.RF varChar(10),
@.RT varChar(10),
@.BM varChar(10),
@.BMX varChar(10),
@.CS varChar(10),
@.MLS varChar(10),
@.S varChar(10),
@.RM varChar(10),
@.MR varChar(10),
@.RSD varChar(50),
@.UserID varChar(10),
@.StateID varChar(10)
)
AS
SET NOCOUNT ON
DECLARE @.SQLWhere varChar(1000)
DECLARE @.SQLOrder varChar(200)
DECLARE @.PostcodeRange int
DECLARE @.ThisSuburbsPostcode int
EXEC sp_executesql
N'Select Top 1 PostcodeRange from dbo.SystemParameters',
N'@.PostcodeRange int OUTPUT',
@.PostcodeRange OUTPUT
SET NOCOUNT ON
DECLARE @.SQLCODE Nvarchar(500)
Set @.SQLCODE = 'Select SuburbPostcode From dbo.Suburbs where SuburbID = ' +
@.ES
If @.ES <> ''
EXEC sp_executesql
@.SQLCODE,
N'@.ThisSuburbsPostcode int OUTPUT',
@.ThisSuburbsPostcode OUTPUT
Set @.SQLWhere = '(dbo.viewDisplayPropertyResults.PropertyStatus = 4)'
IF @.ES <> ''
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.SuburbID =
' + @.ES + ')'
IF @.SS <> ''
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.StateID =
' + @.StateID + ' AND dbo.viewDisplayPropertyResults.SuburbID Between ' +
(@.ThisSuburbsPostcode - @.PostcodeRange) + ' AND ' + (@.ThisSuburbsPostcode +
@.PostcodeRange) + ')'
IF @.PT <> ''
SET @.SQLWhere = @.SQLWhere + ' AND
(dbo.viewDisplayPropertyResults.ListingTypeID = ''' + @.PT + ''')'
IF (@.F <> '' and @.T <> '')
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.SalePrice
Between ' + @.F + ' and ' + @.T + ')'
IF (@.RF <> '' and @.RT <> '')
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.WlyRent
Between ' + @.RF + ' and ' + @.RT + ')'
IF (@.BM <> '' and @.BMX <> '')
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.NoBedrooms
Between ' + @.BM + ' and ' + @.BMX + ')'
IF @.MR <> ''
SET @.SQLWhere = @.SQLWhere + ' AND
(dbo.viewDisplayPropertyResults.MinimumReturns => '+ @.MR + ')'
IF @.RM <> ''
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.RegionID =
'+ @.RM + ')'
IF @.RSD <> ''
SET @.SQLWhere = @.SQLWhere + ' AND
(dbo.viewDisplayPropertyResults.ForRentSaleOrDeveloper = ' + @.RSD + ')'
IF @.UserID <> ''
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.UserID =
'+ @.UserID + ')'
IF @.S <> ''
SET @.SQLOrder = 'ORDER BY ' + @.S
Print @.PostcodeRange
Print @.ThisSuburbsPostcode
Print @.SQLWhere
Print @.SQLOrder
EXECUTE ('SELECT * from viewDisplayPropertyResults WHERE ' + @.SQLWhere +
@.SQLOrder)
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eIoWNt8%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
> Daren
> Let me understand you
> you have a statement as
>
> Now you are saying , you'd like to have a variable @.PostcodeRange
> assigned to 'something' without running dbo.System_GetSystemParameter
> 'PostcodeRange' store procedure. Am I right?
>
>
> "Daren Hawes" <newsgroups@.webdesignmagic.com.au> wrote in message
> news:%23dAp8T8%23FHA.2812@.TK2MSFTNGP09.phx.gbl...
>On Fri, 9 Dec 2005 19:31:44 +1000, Daren Hawes wrote:

>Yes. I need to set some variables from other queries in my SP that will be
>used in the final Select statement. This is the only one I need returned.
>I have modified the script as per other posts, but it still brings back 3
>recordsets.
>Here is the current version..
(snip)
Hi Daren,
I just replied to this question in the original thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
Alan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/default...b;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact[vbcol=seagreen]
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.
> that
> missing
>
|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File[vbcol=seagreen]
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/default...b;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen]
> run
> data.
files).
>
|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling
> files).
>
|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and[vbcol=seagreen]
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is
>
|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error. OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned
0x80004005: The provider did
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When[vbcol=seagreen]
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file
> is
>

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driv
er] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initializ
e
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> CortexAlan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/defaul...kb;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
>
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.[vbcol=seagreen]
> that
> missing
>|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/defaul...kb;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen
]
> run
> data.
files).[vbcol=seagreen]
>|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling[vbcol=seagreen]
> files).
>|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is[vbcol=seagreen]
>|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error. OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned
0x80004005: The provider did
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file[vbcol=seagreen]
> is
>