Hi All,
Hoping someone can assist with this. Is there an SQL trace flag, or
other method, where I can get the SQL server error logs (2000 and
2005) to log database detachements, and changes in DB status (eg to
read only from online etc).
I thought about a trigger on sysdatabases (sys.databases) but am
highly skeptical of triggers on system tables.
Why am I doing this? I need to monitor the up time of individual
databases on an instance, and third party tools are not an option.
thanks in advance.Hi
If you are on SQL Server 2005 you can create TRIGGER on database level (see
BOL for details)
To see error log
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6908c21a-65e3-458f-a272-fee256d86448.htm
<snafu66@.hotmail.co.uk> wrote in message
news:1188442379.790535.109850@.m37g2000prh.googlegroups.com...
> Hi All,
> Hoping someone can assist with this. Is there an SQL trace flag, or
> other method, where I can get the SQL server error logs (2000 and
> 2005) to log database detachements, and changes in DB status (eg to
> read only from online etc).
> I thought about a trigger on sysdatabases (sys.databases) but am
> highly skeptical of triggers on system tables.
> Why am I doing this? I need to monitor the up time of individual
> databases on an instance, and third party tools are not an option.
> thanks in advance.
>
Showing posts with label error. Show all posts
Showing posts with label error. Show all posts
Tuesday, March 27, 2012
Monday, March 26, 2012
External Procedure error
I'm trying to compile the following function:
CREATE OR REPLACE FUNCTION shell(cmd IN VARCHAR2)
RETURN PLS_INTEGER
AS
EXTERNAL LIBRARY EXTPROCSHELL_LIB
NAME "extprocsh"
LANGUAGE C
PARAMETERS (cmd STRING);
but keep getting an error that says EXTPROCSHELL_LIB needs to be declared. Somewhere I read that this probably means that the shared library doesn't exist and try recreating it.
Our DBA assures me that he created the library in the Oracle home lib directory and I do see the compiled C program there and also saw the path of his library creation and everything looks fine.
Any suggestions here?Hello,
do you have created a library object with the same name ?
See "CREATE LIBRARY ..."
The error you have posted does not mean, that you dont have created the lib in the oracle directory.
Its says, that Oracle can not find the library object that points to the library in the oracle directoy ...
Hope that helps ?
Manfred Peter
Alligator Company GmbH
http://www.alligatorsql.comsql
CREATE OR REPLACE FUNCTION shell(cmd IN VARCHAR2)
RETURN PLS_INTEGER
AS
EXTERNAL LIBRARY EXTPROCSHELL_LIB
NAME "extprocsh"
LANGUAGE C
PARAMETERS (cmd STRING);
but keep getting an error that says EXTPROCSHELL_LIB needs to be declared. Somewhere I read that this probably means that the shared library doesn't exist and try recreating it.
Our DBA assures me that he created the library in the Oracle home lib directory and I do see the compiled C program there and also saw the path of his library creation and everything looks fine.
Any suggestions here?Hello,
do you have created a library object with the same name ?
See "CREATE LIBRARY ..."
The error you have posted does not mean, that you dont have created the lib in the oracle directory.
Its says, that Oracle can not find the library object that points to the library in the oracle directoy ...
Hope that helps ?
Manfred Peter
Alligator Company GmbH
http://www.alligatorsql.comsql
Monday, March 19, 2012
Extended Stored Procedure creation error
I've created an extended SP on my local machine (Win XP/ VS.Net 2003 / VC++)
.
It works fine when I tested on my local PC after I copied the created dll
file to binn directory and ran sp_addextendedproc.
I copied it to a Win 2000 Server/MS SQL's binn directory and
sp_addextendedproc completed successfully. However, it gives the following
error when I execute the stored procedure:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL "uxp_calc_sw_v1.dll", or one of the DLLs it references.
Reason: 126(The specified module could not be found.).
Is the dll rely on some other dlls? or should I use Visual C++ 6.0?I found the solution myself, I just copied the following two files to the
binn folder:
DLL Name: MSVCP70.dll
DLL Name: MSVCR70.dll
Or should I copy them to Winnt\system32?
"nick" wrote:
> I've created an extended SP on my local machine (Win XP/ VS.Net 2003 / VC+
+).
> It works fine when I tested on my local PC after I copied the created dll
> file to binn directory and ran sp_addextendedproc.
> I copied it to a Win 2000 Server/MS SQL's binn directory and
> sp_addextendedproc completed successfully. However, it gives the following
> error when I execute the stored procedure:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL "uxp_calc_sw_v1.dll", or one of the DLLs it references
.
> Reason: 126(The specified module could not be found.).
> Is the dll rely on some other dlls? or should I use Visual C++ 6.0?
>
>|||nick (nick@.discussions.microsoft.com) writes:
> I found the solution myself, I just copied the following two files to the
> binn folder:
> DLL Name: MSVCP70.dll
> DLL Name: MSVCR70.dll
> Or should I copy them to Winnt\system32?
There is some guidelines on this in MSDN Library. I had reason to
investigate the story about the Visual C DLL:s myself a while back. My
impressions is that the recommendation is that you should should distribute
the DLL:s with your code, and place in them together with your application.
Thus, you did put them into the right place.
If you want an answer from people who really understand this, you should
ask in a C++ newsgroup.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
.
It works fine when I tested on my local PC after I copied the created dll
file to binn directory and ran sp_addextendedproc.
I copied it to a Win 2000 Server/MS SQL's binn directory and
sp_addextendedproc completed successfully. However, it gives the following
error when I execute the stored procedure:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL "uxp_calc_sw_v1.dll", or one of the DLLs it references.
Reason: 126(The specified module could not be found.).
Is the dll rely on some other dlls? or should I use Visual C++ 6.0?I found the solution myself, I just copied the following two files to the
binn folder:
DLL Name: MSVCP70.dll
DLL Name: MSVCR70.dll
Or should I copy them to Winnt\system32?
"nick" wrote:
> I've created an extended SP on my local machine (Win XP/ VS.Net 2003 / VC+
+).
> It works fine when I tested on my local PC after I copied the created dll
> file to binn directory and ran sp_addextendedproc.
> I copied it to a Win 2000 Server/MS SQL's binn directory and
> sp_addextendedproc completed successfully. However, it gives the following
> error when I execute the stored procedure:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL "uxp_calc_sw_v1.dll", or one of the DLLs it references
.
> Reason: 126(The specified module could not be found.).
> Is the dll rely on some other dlls? or should I use Visual C++ 6.0?
>
>|||nick (nick@.discussions.microsoft.com) writes:
> I found the solution myself, I just copied the following two files to the
> binn folder:
> DLL Name: MSVCP70.dll
> DLL Name: MSVCR70.dll
> Or should I copy them to Winnt\system32?
There is some guidelines on this in MSDN Library. I had reason to
investigate the story about the Visual C DLL:s myself a while back. My
impressions is that the recommendation is that you should should distribute
the DLL:s with your code, and place in them together with your application.
Thus, you did put them into the right place.
If you want an answer from people who really understand this, you should
ask in a C++ newsgroup.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Extended Stored Procedure
Dear Mike
I've created this sp through Enterprise Manager. and when I execute
sp_addextendedProc it show me Error message:
Server: Msg 2714, Level 16, State 7, Procedure sp_addextendedproc, Line 26
There is already an object named 'xp_Session' in the database.Why would you execute sp_addextendedproc if you've already added it? You
only need to add the xp once.
http://www.aspfaq.com/
(Reverse address to reply.)
"mtgoli" <mtgoli@.discussions.microsoft.com> wrote in message
news:E42A61D7-41F7-490A-899E-EDAA6996256A@.microsoft.com...
> Dear Mike
> I've created this sp through Enterprise Manager. and when I execute
> sp_addextendedProc it show me Error message:
> Server: Msg 2714, Level 16, State 7, Procedure sp_addextendedproc, Line 26
> There is already an object named 'xp_Session' in the database.
>
I've created this sp through Enterprise Manager. and when I execute
sp_addextendedProc it show me Error message:
Server: Msg 2714, Level 16, State 7, Procedure sp_addextendedproc, Line 26
There is already an object named 'xp_Session' in the database.Why would you execute sp_addextendedproc if you've already added it? You
only need to add the xp once.
http://www.aspfaq.com/
(Reverse address to reply.)
"mtgoli" <mtgoli@.discussions.microsoft.com> wrote in message
news:E42A61D7-41F7-490A-899E-EDAA6996256A@.microsoft.com...
> Dear Mike
> I've created this sp through Enterprise Manager. and when I execute
> sp_addextendedProc it show me Error message:
> Server: Msg 2714, Level 16, State 7, Procedure sp_addextendedproc, Line 26
> There is already an object named 'xp_Session' in the database.
>
Friday, March 9, 2012
Expression Too Complex error - mdb
DataAdapter.update(dataSet) exception Error:
"Changes not saved to database. Expression Too Complex"
Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
Without diving into the details on the one-to-many 99 column Access (Main)
table , can someone tell me the reason for the error? Is it really the
complexity of the SQL statement? Would SQL server help me? Does SQL Server
allow update() of a queries. StevePlease send us the text of the query you are trying. Based on the little
data we have here, UPDATE() is likely not usable in your situation, since
this is only for use in triggers.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Steve B." <SteveB@.discussions.microsoft.com> wrote in message
news:6284FB15-F8CE-4BDA-9060-B4B09428F4B6@.microsoft.com...
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries. Steve|||hi steve
SQL Server allows updates. there might be an error in the sql string that u
were trying to pass.
please check the query and revert back.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL Serve
r
> allow update() of a queries. Steve|||> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries.
Just off the top of my head, each version of Access has a different maximum
limit on how long an SQL statement is allowed to be. I have actually gotten
that error. That's all I can think of though.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||I hope this is what your looking for (see below). In the mean time, I found
this (please see first few paragraphs):
http://support.microsoft.com/defaul...kb;en-us;192716
I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
thinking of dividing my Main table into 4 or 5 tables indexed on part number
(part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
workaround. What a mess..
The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexed
(dropdown) from their own table in the "Main" table but, every Characteristi
c
field is different (blueprint dimensions, note number, etc) . I really nee
d
30 characteristics not 21. Can't get there from here.
I don't know if a commercial database program like SQL server will help me.
Thats why I'm asking. Comments?
(copied from VS Dataadapter wizard)
SELECT
MainID,
PartNumber,
Nomenclature,
PartStatus,
CoverageBy,
CoverageByDate,
Engine,
Service,
Originator,
RequirementSourceCodes,
PartPointOfContact,
VerificationDate,
CharacteristicCodes1,
ProcessCodes1,
LocationCodes1,
Characteristic1,
CharacteristicCodes2,
LocationCodes2,
ProcessCodes2,
Characteristic2,
CharacteristicCodes3,
LocationCodes3,
ProcessCodes3,
Characteristic3,
CharacteristicCodes4,
LocationCodes4,
ProcessCodes4,
Characteristic4,
CharacteristicCodes5,
LocationCodes5,
ProcessCodes5,
Characteristic5,
CharacteristicCodes6,
LocationCodes6,
ProcessCodes6,
Characteristic6,
CharacteristicCodes7,
LocationCodes7,
ProcessCodes7,
Characteristic7,
CharacteristicCodes8,
LocationCodes8,
ProcessCodes8,
Characteristic8,
CharacteristicCodes9,
LocationCodes9,
ProcessCodes9,
Characteristic9,
CharacteristicCodes10,
LocationCodes10,
ProcessCodes10,
Characteristic10,
CharacteristicCodes11,
LocationCodes11,
ProcessCodes11,
Characteristic11,
CharacteristicCodes12,
LocationCodes12,
ProcessCodes12,
Characteristic12,
CharacteristicCodes13,
LocationCodes13,
ProcessCodes13,
Characteristic13,
CharacteristicCodes14,
LocationCodes14,
ProcessCodes14,
Characteristic14,
CharacteristicCodes15,
LocationCodes15,
ProcessCodes15,
Characteristic15,
CharacteristicCodes16,
LocationCodes16,
ProcessCodes16,
Characteristic16,
CharacteristicCodes17,
LocationCodes17,
ProcessCodes17,
Characteristic17,
CharacteristicCodes18,
LocationCodes18,
ProcessCodes18,
Characteristic18,
CharacteristicCodes19,
LocationCodes19,
ProcessCodes19,
Characteristic19,
CharacteristicCodes20,
LocationCodes20,
ProcessCodes20,
Characteristic20,
CharacteristicCodes21,
LocationCodes21,
ProcessCodes21,
Characteristic21,
Comments,
AnnualCSIConfirmation,
DataRowState
FROM
Main
WHERE
(AnnualCSIConfirmation = 'Yes') AND
(PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None On
Order (Inactive)') ORDER BY MainID
"Chandra" wrote:
> hi steve
> SQL Server allows updates. there might be an error in the sql string that
u
> were trying to pass.
> please check the query and revert back.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Yes!
i agree with you. The message mentions that, the query must be optimized.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Mike Labosh" wrote:
> Just off the top of my head, each version of Access has a different maximu
m
> limit on how long an SQL statement is allowed to be. I have actually gott
en
> that error. That's all I can think of though.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||hi
looks like u need to normalize your table:
something like:
MainID,
Charid,
CharacteristicCodes,
ProcessCodes,
LocationCodes,
Characteristic,
but, this is out of scope now:
just try to remove "ORDER BY MainID" and check, it might speed up the
process and u can escape from the error.
or
where part:
AnnualCSIConfirmation = 'Yes' AND
PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
(buy/make part)', 'None On Order (Inactive)' )
please revert back with the results
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> I hope this is what your looking for (see below). In the mean time, I fou
nd
> this (please see first few paragraphs):
> http://support.microsoft.com/defaul...kb;en-us;192716
> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
> thinking of dividing my Main table into 4 or 5 tables indexed on part numb
er
> (part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
> workaround. What a mess..
> The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexe
d
> (dropdown) from their own table in the "Main" table but, every Characteris
tic
> field is different (blueprint dimensions, note number, etc) . I really n
eed
> 30 characteristics not 21. Can't get there from here.
> I don't know if a commercial database program like SQL server will help me
.
> Thats why I'm asking. Comments?
> (copied from VS Dataadapter wizard)
> SELECT
> MainID,
> PartNumber,
> Nomenclature,
> PartStatus,
> CoverageBy,
> CoverageByDate,
> Engine,
> Service,
> Originator,
> RequirementSourceCodes,
> PartPointOfContact,
> VerificationDate,
> CharacteristicCodes1,
> ProcessCodes1,
> LocationCodes1,
> Characteristic1,
> CharacteristicCodes2,
> LocationCodes2,
> ProcessCodes2,
> Characteristic2,
> CharacteristicCodes3,
> LocationCodes3,
> ProcessCodes3,
> Characteristic3,
> CharacteristicCodes4,
> LocationCodes4,
> ProcessCodes4,
> Characteristic4,
> CharacteristicCodes5,
> LocationCodes5,
> ProcessCodes5,
> Characteristic5,
> CharacteristicCodes6,
> LocationCodes6,
> ProcessCodes6,
> Characteristic6,
> CharacteristicCodes7,
> LocationCodes7,
> ProcessCodes7,
> Characteristic7,
> CharacteristicCodes8,
> LocationCodes8,
> ProcessCodes8,
> Characteristic8,
> CharacteristicCodes9,
> LocationCodes9,
> ProcessCodes9,
> Characteristic9,
> CharacteristicCodes10,
> LocationCodes10,
> ProcessCodes10,
> Characteristic10,
> CharacteristicCodes11,
> LocationCodes11,
> ProcessCodes11,
> Characteristic11,
> CharacteristicCodes12,
> LocationCodes12,
> ProcessCodes12,
> Characteristic12,
> CharacteristicCodes13,
> LocationCodes13,
> ProcessCodes13,
> Characteristic13,
> CharacteristicCodes14,
> LocationCodes14,
> ProcessCodes14,
> Characteristic14,
> CharacteristicCodes15,
> LocationCodes15,
> ProcessCodes15,
> Characteristic15,
> CharacteristicCodes16,
> LocationCodes16,
> ProcessCodes16,
> Characteristic16,
> CharacteristicCodes17,
> LocationCodes17,
> ProcessCodes17,
> Characteristic17,
> CharacteristicCodes18,
> LocationCodes18,
> ProcessCodes18,
> Characteristic18,
> CharacteristicCodes19,
> LocationCodes19,
> ProcessCodes19,
> Characteristic19,
> CharacteristicCodes20,
> LocationCodes20,
> ProcessCodes20,
> Characteristic20,
> CharacteristicCodes21,
> LocationCodes21,
> ProcessCodes21,
> Characteristic21,
> Comments,
> AnnualCSIConfirmation,
> DataRowState
> FROM
> Main
> WHERE
> (AnnualCSIConfirmation = 'Yes') AND
> (PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
> part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None
On
> Order (Inactive)') ORDER BY MainID
>
> "Chandra" wrote:
>|||>> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
You have a poorly designed table. Any shuffling you do without a thorough
analysis of your business model and logic rules, might still leave the
schema unmaintainable and make the queries complex.
Instead of splitting up a single set of attribute into groups of {1st set of
cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the table
better. Understand and analyze the entities in question and identify each of
the attributes explicitly. Identify the dependencies among these attributes
and consider decomposing them into multiple tables appropriately, if
required.
Even, without considering any ill-effects of under normalized schemas, with
certain assumptions based on the query you posted, you might benefit from
having your table structured like:
CREATE TABLE Parts (
Part_nbr INT NOT NULL PRIMARY KEY,
.. ) ;
CREATE TABLE Characteristics (
Character_id INT NOT NULL ,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Code ...
Characteristic...
PRIMARY KEY ( Character_id, Part_nbr ) );
CREATE TABLE Processes (
Process_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Process_code ...
PRIMARY KEY ( Process_id, Part_nbr ) );
CREATE TABLE Locations (
Location_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Location_code ...
PRIMARY KEY ( Location_id, Part_nbr );
Now the query is as simple as:
SELECT p1.Part_nbr,...
c1.code, ...
p2.process_code,...
l1.location_code...
FROM Parts p1
INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
...
This could be your comparable resultset which you might consider using for a
variety of purposes. For the specific case you mentioned in your post, you
can simply pivot this data, either using your client programming language or
if done within the server, using the popular pivoting technique detailed in
MSKB ( support.microsoft.com ) : 175574
Anith|||Thank You Chandra. That was an easy fix but I regret it didn't work. It wa
s
definitly worth a try in order to escape. I agree. Thanks Steve
"Chandra" wrote:
> hi
> looks like u need to normalize your table:
> something like:
> MainID,
> Charid,
> CharacteristicCodes,
> ProcessCodes,
> LocationCodes,
> Characteristic,
> but, this is out of scope now:
> just try to remove "ORDER BY MainID" and check, it might speed up the
> process and u can escape from the error.
> or
> where part:
> AnnualCSIConfirmation = 'Yes' AND
> PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
> (buy/make part)', 'None On Order (Inactive)' )
> please revert back with the results
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Anith,
Thank You for your input. I've sent a copy of your post to a coworker who
understands database design more then I - such is the problem with using
wizards. A few questions so I (we) can better understand the design/context
.
Assumptions:
1. You appear to be dividing the fields into 4 tables – Parts,
CharacteristicsCode (identified in post as Characteristics), Process and
Locations. There is an additional field Characteristic that varies with eac
h
of the other three fields. So for each of the [possible] 30 Characteristic’s
each of them have an associated CharacteristicsCode , Process and Location.
I guess the structure of all 4 tables (except Parts) are exactly the same an
d
doesn’t matter. With that said, the CharacteristicsCode, Process, Locatio
ns
and Characteristic tables will have the exact same table structure and kind
of relationship.
2. It’s assumed the Parts table will contain the part numbers and
associated part data fields like t nomenclatures, etc
3. Does PRIMARY KEY (…) mean you somehow combine the both fields? Is thi
s
the actual relationship, for example, the join from Process_id to Part_nbr ?
4. SQL statement: what’s p1, c1, etc. and/or what’s p1.Part_nbr, c1.cod
e, etc
I think I’m going to have to create it this before I fully understand it.
However, I’m not sure the Visual Studio (VS) Query Builder inside the VS
environment will allow me to create such an Access SQL statement as
described. The DataAdapter might just expand the SQL statement again in the
DataSet so, I might be right back to the beginning again. I can’t use
external Access queries for SELECT, UPDATE, DELETE etc. Please keep the
post open. Thank You.
Steve
"Anith Sen" wrote:
> You have a poorly designed table. Any shuffling you do without a thorough
> analysis of your business model and logic rules, might still leave the
> schema unmaintainable and make the queries complex.
> Instead of splitting up a single set of attribute into groups of {1st set
of
> cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the tab
le
> better. Understand and analyze the entities in question and identify each
of
> the attributes explicitly. Identify the dependencies among these attribute
s
> and consider decomposing them into multiple tables appropriately, if
> required.
> Even, without considering any ill-effects of under normalized schemas, wit
h
> certain assumptions based on the query you posted, you might benefit from
> having your table structured like:
> CREATE TABLE Parts (
> Part_nbr INT NOT NULL PRIMARY KEY,
> ... ) ;
> CREATE TABLE Characteristics (
> Character_id INT NOT NULL ,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Code ...
> Characteristic...
> PRIMARY KEY ( Character_id, Part_nbr ) );
> CREATE TABLE Processes (
> Process_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Process_code ...
> PRIMARY KEY ( Process_id, Part_nbr ) );
> CREATE TABLE Locations (
> Location_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Location_code ...
> PRIMARY KEY ( Location_id, Part_nbr );
> Now the query is as simple as:
> SELECT p1.Part_nbr,...
> c1.code, ...
> p2.process_code,...
> l1.location_code...
> FROM Parts p1
> INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
> INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
> INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
> ...
> This could be your comparable resultset which you might consider using for
a
> variety of purposes. For the specific case you mentioned in your post, you
> can simply pivot this data, either using your client programming language
or
> if done within the server, using the popular pivoting technique detailed i
n
> MSKB ( support.microsoft.com ) : 175574
> --
> Anith
>
>
"Changes not saved to database. Expression Too Complex"
Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
Without diving into the details on the one-to-many 99 column Access (Main)
table , can someone tell me the reason for the error? Is it really the
complexity of the SQL statement? Would SQL server help me? Does SQL Server
allow update() of a queries. StevePlease send us the text of the query you are trying. Based on the little
data we have here, UPDATE() is likely not usable in your situation, since
this is only for use in triggers.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Steve B." <SteveB@.discussions.microsoft.com> wrote in message
news:6284FB15-F8CE-4BDA-9060-B4B09428F4B6@.microsoft.com...
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries. Steve|||hi steve
SQL Server allows updates. there might be an error in the sql string that u
were trying to pass.
please check the query and revert back.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL Serve
r
> allow update() of a queries. Steve|||> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries.
Just off the top of my head, each version of Access has a different maximum
limit on how long an SQL statement is allowed to be. I have actually gotten
that error. That's all I can think of though.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||I hope this is what your looking for (see below). In the mean time, I found
this (please see first few paragraphs):
http://support.microsoft.com/defaul...kb;en-us;192716
I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
thinking of dividing my Main table into 4 or 5 tables indexed on part number
(part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
workaround. What a mess..
The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexed
(dropdown) from their own table in the "Main" table but, every Characteristi
c
field is different (blueprint dimensions, note number, etc) . I really nee
d
30 characteristics not 21. Can't get there from here.
I don't know if a commercial database program like SQL server will help me.
Thats why I'm asking. Comments?
(copied from VS Dataadapter wizard)
SELECT
MainID,
PartNumber,
Nomenclature,
PartStatus,
CoverageBy,
CoverageByDate,
Engine,
Service,
Originator,
RequirementSourceCodes,
PartPointOfContact,
VerificationDate,
CharacteristicCodes1,
ProcessCodes1,
LocationCodes1,
Characteristic1,
CharacteristicCodes2,
LocationCodes2,
ProcessCodes2,
Characteristic2,
CharacteristicCodes3,
LocationCodes3,
ProcessCodes3,
Characteristic3,
CharacteristicCodes4,
LocationCodes4,
ProcessCodes4,
Characteristic4,
CharacteristicCodes5,
LocationCodes5,
ProcessCodes5,
Characteristic5,
CharacteristicCodes6,
LocationCodes6,
ProcessCodes6,
Characteristic6,
CharacteristicCodes7,
LocationCodes7,
ProcessCodes7,
Characteristic7,
CharacteristicCodes8,
LocationCodes8,
ProcessCodes8,
Characteristic8,
CharacteristicCodes9,
LocationCodes9,
ProcessCodes9,
Characteristic9,
CharacteristicCodes10,
LocationCodes10,
ProcessCodes10,
Characteristic10,
CharacteristicCodes11,
LocationCodes11,
ProcessCodes11,
Characteristic11,
CharacteristicCodes12,
LocationCodes12,
ProcessCodes12,
Characteristic12,
CharacteristicCodes13,
LocationCodes13,
ProcessCodes13,
Characteristic13,
CharacteristicCodes14,
LocationCodes14,
ProcessCodes14,
Characteristic14,
CharacteristicCodes15,
LocationCodes15,
ProcessCodes15,
Characteristic15,
CharacteristicCodes16,
LocationCodes16,
ProcessCodes16,
Characteristic16,
CharacteristicCodes17,
LocationCodes17,
ProcessCodes17,
Characteristic17,
CharacteristicCodes18,
LocationCodes18,
ProcessCodes18,
Characteristic18,
CharacteristicCodes19,
LocationCodes19,
ProcessCodes19,
Characteristic19,
CharacteristicCodes20,
LocationCodes20,
ProcessCodes20,
Characteristic20,
CharacteristicCodes21,
LocationCodes21,
ProcessCodes21,
Characteristic21,
Comments,
AnnualCSIConfirmation,
DataRowState
FROM
Main
WHERE
(AnnualCSIConfirmation = 'Yes') AND
(PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None On
Order (Inactive)') ORDER BY MainID
"Chandra" wrote:
> hi steve
> SQL Server allows updates. there might be an error in the sql string that
u
> were trying to pass.
> please check the query and revert back.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Yes!
i agree with you. The message mentions that, the query must be optimized.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Mike Labosh" wrote:
> Just off the top of my head, each version of Access has a different maximu
m
> limit on how long an SQL statement is allowed to be. I have actually gott
en
> that error. That's all I can think of though.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||hi
looks like u need to normalize your table:
something like:
MainID,
Charid,
CharacteristicCodes,
ProcessCodes,
LocationCodes,
Characteristic,
but, this is out of scope now:
just try to remove "ORDER BY MainID" and check, it might speed up the
process and u can escape from the error.
or
where part:
AnnualCSIConfirmation = 'Yes' AND
PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
(buy/make part)', 'None On Order (Inactive)' )
please revert back with the results
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> I hope this is what your looking for (see below). In the mean time, I fou
nd
> this (please see first few paragraphs):
> http://support.microsoft.com/defaul...kb;en-us;192716
> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
> thinking of dividing my Main table into 4 or 5 tables indexed on part numb
er
> (part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
> workaround. What a mess..
> The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexe
d
> (dropdown) from their own table in the "Main" table but, every Characteris
tic
> field is different (blueprint dimensions, note number, etc) . I really n
eed
> 30 characteristics not 21. Can't get there from here.
> I don't know if a commercial database program like SQL server will help me
.
> Thats why I'm asking. Comments?
> (copied from VS Dataadapter wizard)
> SELECT
> MainID,
> PartNumber,
> Nomenclature,
> PartStatus,
> CoverageBy,
> CoverageByDate,
> Engine,
> Service,
> Originator,
> RequirementSourceCodes,
> PartPointOfContact,
> VerificationDate,
> CharacteristicCodes1,
> ProcessCodes1,
> LocationCodes1,
> Characteristic1,
> CharacteristicCodes2,
> LocationCodes2,
> ProcessCodes2,
> Characteristic2,
> CharacteristicCodes3,
> LocationCodes3,
> ProcessCodes3,
> Characteristic3,
> CharacteristicCodes4,
> LocationCodes4,
> ProcessCodes4,
> Characteristic4,
> CharacteristicCodes5,
> LocationCodes5,
> ProcessCodes5,
> Characteristic5,
> CharacteristicCodes6,
> LocationCodes6,
> ProcessCodes6,
> Characteristic6,
> CharacteristicCodes7,
> LocationCodes7,
> ProcessCodes7,
> Characteristic7,
> CharacteristicCodes8,
> LocationCodes8,
> ProcessCodes8,
> Characteristic8,
> CharacteristicCodes9,
> LocationCodes9,
> ProcessCodes9,
> Characteristic9,
> CharacteristicCodes10,
> LocationCodes10,
> ProcessCodes10,
> Characteristic10,
> CharacteristicCodes11,
> LocationCodes11,
> ProcessCodes11,
> Characteristic11,
> CharacteristicCodes12,
> LocationCodes12,
> ProcessCodes12,
> Characteristic12,
> CharacteristicCodes13,
> LocationCodes13,
> ProcessCodes13,
> Characteristic13,
> CharacteristicCodes14,
> LocationCodes14,
> ProcessCodes14,
> Characteristic14,
> CharacteristicCodes15,
> LocationCodes15,
> ProcessCodes15,
> Characteristic15,
> CharacteristicCodes16,
> LocationCodes16,
> ProcessCodes16,
> Characteristic16,
> CharacteristicCodes17,
> LocationCodes17,
> ProcessCodes17,
> Characteristic17,
> CharacteristicCodes18,
> LocationCodes18,
> ProcessCodes18,
> Characteristic18,
> CharacteristicCodes19,
> LocationCodes19,
> ProcessCodes19,
> Characteristic19,
> CharacteristicCodes20,
> LocationCodes20,
> ProcessCodes20,
> Characteristic20,
> CharacteristicCodes21,
> LocationCodes21,
> ProcessCodes21,
> Characteristic21,
> Comments,
> AnnualCSIConfirmation,
> DataRowState
> FROM
> Main
> WHERE
> (AnnualCSIConfirmation = 'Yes') AND
> (PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
> part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None
On
> Order (Inactive)') ORDER BY MainID
>
> "Chandra" wrote:
>|||>> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
You have a poorly designed table. Any shuffling you do without a thorough
analysis of your business model and logic rules, might still leave the
schema unmaintainable and make the queries complex.
Instead of splitting up a single set of attribute into groups of {1st set of
cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the table
better. Understand and analyze the entities in question and identify each of
the attributes explicitly. Identify the dependencies among these attributes
and consider decomposing them into multiple tables appropriately, if
required.
Even, without considering any ill-effects of under normalized schemas, with
certain assumptions based on the query you posted, you might benefit from
having your table structured like:
CREATE TABLE Parts (
Part_nbr INT NOT NULL PRIMARY KEY,
.. ) ;
CREATE TABLE Characteristics (
Character_id INT NOT NULL ,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Code ...
Characteristic...
PRIMARY KEY ( Character_id, Part_nbr ) );
CREATE TABLE Processes (
Process_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Process_code ...
PRIMARY KEY ( Process_id, Part_nbr ) );
CREATE TABLE Locations (
Location_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Location_code ...
PRIMARY KEY ( Location_id, Part_nbr );
Now the query is as simple as:
SELECT p1.Part_nbr,...
c1.code, ...
p2.process_code,...
l1.location_code...
FROM Parts p1
INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
...
This could be your comparable resultset which you might consider using for a
variety of purposes. For the specific case you mentioned in your post, you
can simply pivot this data, either using your client programming language or
if done within the server, using the popular pivoting technique detailed in
MSKB ( support.microsoft.com ) : 175574
Anith|||Thank You Chandra. That was an easy fix but I regret it didn't work. It wa
s
definitly worth a try in order to escape. I agree. Thanks Steve
"Chandra" wrote:
> hi
> looks like u need to normalize your table:
> something like:
> MainID,
> Charid,
> CharacteristicCodes,
> ProcessCodes,
> LocationCodes,
> Characteristic,
> but, this is out of scope now:
> just try to remove "ORDER BY MainID" and check, it might speed up the
> process and u can escape from the error.
> or
> where part:
> AnnualCSIConfirmation = 'Yes' AND
> PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
> (buy/make part)', 'None On Order (Inactive)' )
> please revert back with the results
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Anith,
Thank You for your input. I've sent a copy of your post to a coworker who
understands database design more then I - such is the problem with using
wizards. A few questions so I (we) can better understand the design/context
.
Assumptions:
1. You appear to be dividing the fields into 4 tables – Parts,
CharacteristicsCode (identified in post as Characteristics), Process and
Locations. There is an additional field Characteristic that varies with eac
h
of the other three fields. So for each of the [possible] 30 Characteristic’s
each of them have an associated CharacteristicsCode , Process and Location.
I guess the structure of all 4 tables (except Parts) are exactly the same an
d
doesn’t matter. With that said, the CharacteristicsCode, Process, Locatio
ns
and Characteristic tables will have the exact same table structure and kind
of relationship.
2. It’s assumed the Parts table will contain the part numbers and
associated part data fields like t nomenclatures, etc
3. Does PRIMARY KEY (…) mean you somehow combine the both fields? Is thi
s
the actual relationship, for example, the join from Process_id to Part_nbr ?
4. SQL statement: what’s p1, c1, etc. and/or what’s p1.Part_nbr, c1.cod
e, etc
I think I’m going to have to create it this before I fully understand it.
However, I’m not sure the Visual Studio (VS) Query Builder inside the VS
environment will allow me to create such an Access SQL statement as
described. The DataAdapter might just expand the SQL statement again in the
DataSet so, I might be right back to the beginning again. I can’t use
external Access queries for SELECT, UPDATE, DELETE etc. Please keep the
post open. Thank You.
Steve
"Anith Sen" wrote:
> You have a poorly designed table. Any shuffling you do without a thorough
> analysis of your business model and logic rules, might still leave the
> schema unmaintainable and make the queries complex.
> Instead of splitting up a single set of attribute into groups of {1st set
of
> cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the tab
le
> better. Understand and analyze the entities in question and identify each
of
> the attributes explicitly. Identify the dependencies among these attribute
s
> and consider decomposing them into multiple tables appropriately, if
> required.
> Even, without considering any ill-effects of under normalized schemas, wit
h
> certain assumptions based on the query you posted, you might benefit from
> having your table structured like:
> CREATE TABLE Parts (
> Part_nbr INT NOT NULL PRIMARY KEY,
> ... ) ;
> CREATE TABLE Characteristics (
> Character_id INT NOT NULL ,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Code ...
> Characteristic...
> PRIMARY KEY ( Character_id, Part_nbr ) );
> CREATE TABLE Processes (
> Process_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Process_code ...
> PRIMARY KEY ( Process_id, Part_nbr ) );
> CREATE TABLE Locations (
> Location_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Location_code ...
> PRIMARY KEY ( Location_id, Part_nbr );
> Now the query is as simple as:
> SELECT p1.Part_nbr,...
> c1.code, ...
> p2.process_code,...
> l1.location_code...
> FROM Parts p1
> INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
> INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
> INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
> ...
> This could be your comparable resultset which you might consider using for
a
> variety of purposes. For the specific case you mentioned in your post, you
> can simply pivot this data, either using your client programming language
or
> if done within the server, using the popular pivoting technique detailed i
n
> MSKB ( support.microsoft.com ) : 175574
> --
> Anith
>
>
expression sum and iif help?
Hi All,
SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))
i write this expression inside the group footer row, it give a #error when generating report.
please help
Cheers
Nick
Hi Nick,
The possible reason for this error is that the Sum function can only add fields of the same data type. It is quite possible in this case that the data types of the fields in the TRUE and FALSe part of the If statement is different. Try making the data types same of both the fields by usinf the Conversion functions available.
Hope this helps.
Regards,
Pradeep
|||Hi,
I think ure iif() is returning varying datatypes. 0 being a integer and Fields!tsHours.Value returning some other datatype.
Use a conversion function such as CDbl() on both fields.For eg: CDbl(Fields!tsHours.Value) and CDbl(0).This makes either of the values returned as Double.
eg:
Sum(IIf(Fields!NO.value>=1,CDbl(Fields!tsHours.Value),Cdbl(0)))
Somiya
|||Thanks Chaubey and Somiya,
You guys are right, after i explicitly convert to same type , then it works.
Cheers
Nick
Expression question
I don't know any other way to ask it than to post my code and ask what's wrong w/ it. It looks right to me, but I'm getting an error every time. Any and all suggestions are greatly appreciated!
This one returns a value of False:
=iif( Fields!Appearance.Value=4, Fields!Appearance.Value="Poor", iif( Fields!Appearance.Value=3,Fields!Appearance.Value="Fair", iif( Fields!Appearance.Value=2,Fields!Appearance.Value="Good", iif( Fields!Appearance.Value=1,Fields!Appearance.Value="Excellent", Fields!Appearance.Value="Unknown"))))
This one just errors out:
=Select Case Month(Fields!SubmitDate.Value)
case 1
Month(Fields!SubmitDate.Value)= "January"
case 2
Month(Fields!SubmitDate.Value)= "February"
case 3
Month(Fields!SubmitDate.Value)= "March"
case 4
Month(Fields!SubmitDate.Value)= "April"
case 5
Month(Fields!SubmitDate.Value)= "May"
case 6
Month(Fields!SubmitDate.Value)= "June"
case 7
Month(Fields!SubmitDate.Value)= "July"
case 8
Month(Fields!SubmitDate.Value)= "August"
case 9
Month(Fields!SubmitDate.Value)= "September"
case 10
Month(Fields!SubmitDate.Value)= "October"
case 11
Month(Fields!SubmitDate.Value)= "November"
case 12
Month(Fields!SubmitDate.Value)= "December"
case Else
Month(Fields!SubmitDate.Value)= "Unknown"
I've fixed the first expression...here's the new code:
=iif( Fields!Appearance.Value=4, "Poor", iif( Fields!Appearance.Value=3, "Fair", iif( Fields!Appearance.Value=2, "Good", iif( Fields!Appearance.Value=1, "Excellent","Unknown"))))
Still having trouble w/ the case statement though
|||
This one returns a value of False:
=iif( Fields!Appearance.Value=4, Fields!Appearance.Value="Poor", iif( Fields!Appearance.Value=3,Fields!Appearance.Value="Fair", iif( Fields!Appearance.Value=2,Fields!Appearance.Value="Good", iif( Fields!Appearance.Value=1,Fields!Appearance.Value="Excellent", Fields!Appearance.Value="Unknown"))))
This one just errors out:
=Select Case Month(Fields!SubmitDate.Value)
case 1
Month(Fields!SubmitDate.Value)= "January"
case 2
Month(Fields!SubmitDate.Value)= "February"
case 3
Month(Fields!SubmitDate.Value)= "March"
case 4
Month(Fields!SubmitDate.Value)= "April"
case 5
Month(Fields!SubmitDate.Value)= "May"
case 6
Month(Fields!SubmitDate.Value)= "June"
case 7
Month(Fields!SubmitDate.Value)= "July"
case 8
Month(Fields!SubmitDate.Value)= "August"
case 9
Month(Fields!SubmitDate.Value)= "September"
case 10
Month(Fields!SubmitDate.Value)= "October"
case 11
Month(Fields!SubmitDate.Value)= "November"
case 12
Month(Fields!SubmitDate.Value)= "December"
case Else
Month(Fields!SubmitDate.Value)= "Unknown"
I've fixed the first expression...here's the new code:
=iif( Fields!Appearance.Value=4, "Poor", iif( Fields!Appearance.Value=3, "Fair", iif( Fields!Appearance.Value=2, "Good", iif( Fields!Appearance.Value=1, "Excellent","Unknown"))))
Still having trouble w/ the case statement though
|||
Instead of using a case statement try this:
=MonthName(DatePart("m",Fields!SubmitDate.Value))
Wednesday, March 7, 2012
Expression for Color property
I keep getting a message when setting the expression for the color property.
When I exit the expression builder an error message is displayed showing the
expression and "not a valid color". I have tried numerous IIF but cannot get
past the error.
IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
What am I missing? Thanks in advance...Make sure you put a = sign before IIF
so the expression should be
=IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
Hope this helps
RP
"Stanley Hammer" wrote:
> I keep getting a message when setting the expression for the color property.
> When I exit the expression builder an error message is displayed showing the
> expression and "not a valid color". I have tried numerous IIF but cannot get
> past the error.
> IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
> What am I missing? Thanks in advance...|||Thanks, that worked great.
"RP" wrote:
> Make sure you put a = sign before IIF
> so the expression should be
> =IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
> Hope this helps
> RP
> "Stanley Hammer" wrote:
> > I keep getting a message when setting the expression for the color property.
> > When I exit the expression builder an error message is displayed showing the
> > expression and "not a valid color". I have tried numerous IIF but cannot get
> > past the error.
> >
> > IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
> >
> > What am I missing? Thanks in advance...
When I exit the expression builder an error message is displayed showing the
expression and "not a valid color". I have tried numerous IIF but cannot get
past the error.
IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
What am I missing? Thanks in advance...Make sure you put a = sign before IIF
so the expression should be
=IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
Hope this helps
RP
"Stanley Hammer" wrote:
> I keep getting a message when setting the expression for the color property.
> When I exit the expression builder an error message is displayed showing the
> expression and "not a valid color". I have tried numerous IIF but cannot get
> past the error.
> IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
> What am I missing? Thanks in advance...|||Thanks, that worked great.
"RP" wrote:
> Make sure you put a = sign before IIF
> so the expression should be
> =IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
> Hope this helps
> RP
> "Stanley Hammer" wrote:
> > I keep getting a message when setting the expression for the color property.
> > When I exit the expression builder an error message is displayed showing the
> > expression and "not a valid color". I have tried numerous IIF but cannot get
> > past the error.
> >
> > IIF( Fields!ACCOUNT_STATUS.Value="LOCKED","Red","Green")
> >
> > What am I missing? Thanks in advance...
Expression Error: Attempted to divide by zero.
i'm trying to calculate a percent field with the following expression and
getting the error message above:
=IIF(Fields!valorPrevisto.Value = 0, 0, Fields!valorRealizado.Value /
Fields!valorPrevisto.Value)
what am i doing wrong ?
thanks.Hi levogiro -
Check out this thread. It describes the problem and offers a couple of
solutions.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/481dcece9e84c707/d34656d64a7f39a9?q=divide+by+zero+expression&rnum=4&hl=en#d34656d64a7f39a9
HTH...
--
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 13 Sep 2005 07:08:05 -0700, "levogiro"
<levogiro@.discussions.microsoft.com> wrote:
>i'm trying to calculate a percent field with the following expression and
>getting the error message above:
>=IIF(Fields!valorPrevisto.Value = 0, 0, Fields!valorRealizado.Value /
>Fields!valorPrevisto.Value)
>what am i doing wrong ?
>thanks.
getting the error message above:
=IIF(Fields!valorPrevisto.Value = 0, 0, Fields!valorRealizado.Value /
Fields!valorPrevisto.Value)
what am i doing wrong ?
thanks.Hi levogiro -
Check out this thread. It describes the problem and offers a couple of
solutions.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/481dcece9e84c707/d34656d64a7f39a9?q=divide+by+zero+expression&rnum=4&hl=en#d34656d64a7f39a9
HTH...
--
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 13 Sep 2005 07:08:05 -0700, "levogiro"
<levogiro@.discussions.microsoft.com> wrote:
>i'm trying to calculate a percent field with the following expression and
>getting the error message above:
>=IIF(Fields!valorPrevisto.Value = 0, 0, Fields!valorRealizado.Value /
>Fields!valorPrevisto.Value)
>what am i doing wrong ?
>thanks.
Expression Data Type Conversion
I am getting the following error in my report. I am tring to program the BackgroungColor Expression so the if the value is null display a blue color else leave it white.
Conversion from string "" to type 'Date' is not valid
Here is my code sample:
=Iif(Fields!ReturnTime.Value = "", "AliceBlue", "White")
Hi RayClark,You cannot compare a string with a date.
You can do this in several ways here is one:
=IIF(Len(Fields!ReturnTime.Value)=0, "AliceBlue", "White")
Use the length function to check the length of return time. If it is 0 set it to alice blue else set it to white :).
http://jhermiz.googlepages.com|||Thanks that worked great!!!
Sunday, February 26, 2012
Express SP2 install error
Hi, try to install (not an upgrade) SQLExpressSP2 on WinXPSP2 from SQLEXPR32.exe package.
.NET version 2.0 is already installed however get error below before last step of Server setup ( Excerpt from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_Core(Local) ):
</- START OF Excerpt >
<Func Name='PerformDetections'>
0
<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>
<Func Name='DisplaySCCWizard'>
CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='1603' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/2/14 16:58:23, returned false
Error: Action "InvokeSqlSetupDllAction" failed during execution.
Action "AnalyzeInstallStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "PrepareForChainingStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "SequenceChainingActionsStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "InstallChainedPackagesStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/2/14 16:58:23
Error: Action "ReportChainingResults" threw an exception during execution.
DwLaunchMsiExec() returned : 1603
Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation.
Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults::perform
Source Line Number: 3416
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_.NET Framework 2.0.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/2/14 16:58:23
Message pump returning: 1603
</- END OF Excerpt >
Cud u please advise workaround?
cheers,
Fred
N.B.
Also have tried to register my installation of SQL Express SP2 but the registration web page has a bug and
does not enable me to register at https://profile.microsoft.com/ProductActivation/ProductActivation.aspx?lcid=1033&WizId=0569136c-78c8-454b-bf27-cb7d7863de63
This message appears in "Red' even when legitimate email-address was entered:
"*Required: E-Mail Address"
cheers,
Fred
|||Tyodotnet wrote:
Hi, try to install (not an upgrade) SQLExpressSP2 on WinXPSP2 from SQLEXPR32.exe package.
.NET version 2.0 is already installed however get error below before last step of Server setup ( Excerpt from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_Core(Local) ):
</- START OF Excerpt >
<Func Name='PerformDetections'>
0
<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>
<Func Name='DisplaySCCWizard'>
CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='1603' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/2/14 16:58:23, returned false
Error: Action "InvokeSqlSetupDllAction" failed during execution.
Action "AnalyzeInstallStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "PrepareForChainingStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "SequenceChainingActionsStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "InstallChainedPackagesStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/2/14 16:58:23
Error: Action "ReportChainingResults" threw an exception during execution.
DwLaunchMsiExec() returned : 1603
Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation.Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults::perform
Source Line Number: 3416Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_.NET Framework 2.0.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/2/14 16:58:23
Message pump returning: 1603
</- END OF Excerpt >Cud u please advise workaround?
cheers,
Fred
Not finding much on this one, I'm going to move this to the setup forum so the experts can take a look.
Mike
|||Hi Mike,
Thanks for your assistance.
Meanwhile I have found out the problem i.e:
1. Instead of running SQLEXPR32.EXE I have extracted the contents
into a new folder using the commandline SQLEXPR32.EXE -X
2. I have created an autostart CD from the extracted data.
3. Run the CD, autorun will open splash.hta automatically.
This installation menu will appear:
Prepare
Review hardware and software requirements
Read the release notes
Install
Run the SQL Server Installation Wizard
Run the SQL Native Client Installation Wizard
Other Information
Browse this CD
Visit the SQL Server website
Read the SQL Server license agreement
4. Run the SQL Native Client Installation Wizard, with success,
installation closes all screens.
5. Run CD again and run the SQL Server Installation Wizard
got into error: current user (the Administrator!)
has not enough access rights to write in "C:\Program Files\Microsoft SQL Server"
6. Check and change access control with Explorer "C:\Program Files\Microsoft SQL Server"
Indeed it was totally unwritable by Admin!
7. Run CD again and the SQL Server Installation Wizard is running flawless.
All steps can be finished.
cheers,
Fred
Express SP2 install error
Hi, try to install (not an upgrade) SQLExpressSP2 on WinXPSP2 from SQLEXPR32.exe package.
.NET version 2.0 is already installed however get error below before last step of Server setup ( Excerpt from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_Core(Local) ):
</- START OF Excerpt >
<Func Name='PerformDetections'>
0
<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>
<Func Name='DisplaySCCWizard'>
CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='1603' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/2/14 16:58:23, returned false
Error: Action "InvokeSqlSetupDllAction" failed during execution.
Action "AnalyzeInstallStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "PrepareForChainingStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "SequenceChainingActionsStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "InstallChainedPackagesStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/2/14 16:58:23
Error: Action "ReportChainingResults" threw an exception during execution.
DwLaunchMsiExec() returned : 1603
Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation.
Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults::perform
Source Line Number: 3416
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_.NET Framework 2.0.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/2/14 16:58:23
Message pump returning: 1603
</- END OF Excerpt >
Cud u please advise workaround?
cheers,
Fred
N.B.
Also have tried to register my installation of SQL Express SP2 but the registration web page has a bug and
does not enable me to register at https://profile.microsoft.com/ProductActivation/ProductActivation.aspx?lcid=1033&WizId=0569136c-78c8-454b-bf27-cb7d7863de63
This message appears in "Red' even when legitimate email-address was entered:
"*Required: E-Mail Address"
cheers,
Fred
|||Hi, try to install (not an upgrade) SQLExpressSP2 on WinXPSP2 from SQLEXPR32.exe package.
.NET version 2.0 is already installed however get error below before last step of Server setup ( Excerpt from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_Core(Local) ):
</- START OF Excerpt >
<Func Name='PerformDetections'>
0
<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>
<Func Name='DisplaySCCWizard'>
CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='1603' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/2/14 16:58:23, returned false
Error: Action "InvokeSqlSetupDllAction" failed during execution.
Action "AnalyzeInstallStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "PrepareForChainingStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "SequenceChainingActionsStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "InstallChainedPackagesStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/2/14 16:58:23
Error: Action "ReportChainingResults" threw an exception during execution.
DwLaunchMsiExec() returned : 1603
Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation.Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults::perform
Source Line Number: 3416Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_.NET Framework 2.0.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/2/14 16:58:23
Message pump returning: 1603
</- END OF Excerpt >Cud u please advise workaround?
cheers,
Fred
Not finding much on this one, I'm going to move this to the setup forum so the experts can take a look.
Mike
|||Hi Mike,
Thanks for your assistance.
Meanwhile I have found out the problem i.e:
1. Instead of running SQLEXPR32.EXE I have extracted the contents
into a new folder using the commandline SQLEXPR32.EXE -X
2. I have created an autostart CD from the extracted data.
3. Run the CD, autorun will open splash.hta automatically.
This installation menu will appear:
Prepare
Review hardware and software requirements
Read the release notes
Install
Run the SQL Server Installation Wizard
Run the SQL Native Client Installation Wizard
Other Information
Browse this CD
Visit the SQL Server website
Read the SQL Server license agreement
4. Run the SQL Native Client Installation Wizard, with success,
installation closes all screens.
5. Run CD again and run the SQL Server Installation Wizard
got into error: current user (the Administrator!)
has not enough access rights to write in "C:\Program Files\Microsoft SQL Server"
6. Check and change access control with Explorer "C:\Program Files\Microsoft SQL Server"
Indeed it was totally unwritable by Admin!
7. Run CD again and the SQL Server Installation Wizard is running flawless.
All steps can be finished.
cheers,
Fred
Express installation fails with "invalid installation package"
I get an error when trying to install the SQL Native Client. It says it cannot find the installation package and to use a valid copy of 'sqlncli.msi'. I've downloaded the Express setup twice and get the same error from both of them.
I'm attempting this in a VMWare session setup for Windows 2K, Advanced Server. I am trying to test my installation and setup procedures. This is not a production setup, just install testing and documentation. I have allocated only 272MB to the VMWare session, which is why the system doesn't meet the recommended settings.
Any help would be greatly appreciated.
Below is the Summary.txt output file.
Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows 2000 Advanced Server Service Pack 4 (Build 2195)
Time : Wed May 03 09:54:23 2006
I'm attempting this in a VMWare session setup for Windows 2K, Advanced Server. I am trying to test my installation and setup procedures. This is not a production setup, just install testing and documentation. I have allocated only 272MB to the VMWare session, which is why the system doesn't meet the recommended settings.
Any help would be greatly appreciated.
Below is the Summary.txt output file.
Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows 2000 Advanced Server Service Pack 4 (Build 2195)
Time : Wed May 03 09:54:23 2006
CPCWIN2KVM1 : The current system does not meet recommended hardware requirements for this SQL Server release. For detailed hardware requirements, see the readme file or SQL Server Books Online.
Machine : CPCWIN2KVM1
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.2047.00
Install : Successful
Log File : c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_CPCWIN2KVM1_SQLSupport_1.log
--
Machine : CPCWIN2KVM1
Product : SQL Native Client
Error : An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.
--
Machine : CPCWIN2KVM1
Product : Microsoft SQL Server Native Client
Product Version : 9.00.2047.00
Install : Failed
Log File : c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_CPCWIN2KVM1_SQLNCLI_1.log
Last Action : InstallFinalize
Error String : An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.
Error Number : 1706
--
Machine : CPCWIN2KVM1
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.2047.00
Install : Successful
Log File : c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_CPCWIN2KVM1_SqlWriter_1.log
--
Machine : CPCWIN2KVM1
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Install : Successful
Log File : c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_CPCWIN2KVM1_MSXML6_1.log
--
Remove the Native Client in the control panel/add remove programs and rerun the installation.|||
Let us know if this resolved your issue. Feel free to mark the thread as answered if it did.
If you're still having problems I can move this thread to the Setup group to see if they have some ideas.
Regards,
Mike Wachal
SQL Express team
-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1
I was having the same problem. I removed the Native Client in the control panel and all seems to be working fine now.
Alex
Friday, February 24, 2012
Express and AspNet_RegSql.exe
Can the Asp Net exe sql script be used to add the aspnetdb database to an Sq
l
Express?
I am doing this for running the App Blocks.
The error that comes back is "can not connect".
Thaks,
MarcCheck to see if you need to specify the instance name for the connection. I
know that when I installed a CTP version of 2005, the "standard" installatio
n
created a non-default instance.
"Marc" wrote:
> Can the Asp Net exe sql script be used to add the aspnetdb database to an
Sql
> Express?
> I am doing this for running the App Blocks.
> The error that comes back is "can not connect".
> Thaks,
> Marc
>
l
Express?
I am doing this for running the App Blocks.
The error that comes back is "can not connect".
Thaks,
MarcCheck to see if you need to specify the instance name for the connection. I
know that when I installed a CTP version of 2005, the "standard" installatio
n
created a non-default instance.
"Marc" wrote:
> Can the Asp Net exe sql script be used to add the aspnetdb database to an
Sql
> Express?
> I am doing this for running the App Blocks.
> The error that comes back is "can not connect".
> Thaks,
> Marc
>
Express 2005 - Database Diagram Error
Installed adventure works..and when creating a database diagram giving
a message says that it does not have valid owner.
The adventure works database is in SQL 2005 compatibility mode.Perhaps the database owner doesn't exist? Try below:
SELECT * FROM sys.databases AS db
WHERE NOT EXISTS
(
SELECT * FROM sys.server_principals AS sp
WHERE sp.sid = db.owner_sid
)
If above returns any rows, try changing the database to a valid login using sp_changedbowner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141080017.869972.135450@.j33g2000cwa.googlegroups.com...
> Installed adventure works..and when creating a database diagram giving
> a message says that it does not have valid owner.
> The adventure works database is in SQL 2005 compatibility mode.
>|||The above query returns a row.
Tibor, my login is sysadmin fixed server role. I have right clicked on
the database (properties) on the file groyp it says that owner is my
windows domain login.|||Ok, so if the owner is a valid login, then the problem is somewhere else...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141137171.049927.50760@.p10g2000cwp.googlegroups.com...
> The above query returns a row.
> Tibor, my login is sysadmin fixed server role. I have right clicked on
> the database (properties) on the file groyp it says that owner is my
> windows domain login.
>
a message says that it does not have valid owner.
The adventure works database is in SQL 2005 compatibility mode.Perhaps the database owner doesn't exist? Try below:
SELECT * FROM sys.databases AS db
WHERE NOT EXISTS
(
SELECT * FROM sys.server_principals AS sp
WHERE sp.sid = db.owner_sid
)
If above returns any rows, try changing the database to a valid login using sp_changedbowner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141080017.869972.135450@.j33g2000cwa.googlegroups.com...
> Installed adventure works..and when creating a database diagram giving
> a message says that it does not have valid owner.
> The adventure works database is in SQL 2005 compatibility mode.
>|||The above query returns a row.
Tibor, my login is sysadmin fixed server role. I have right clicked on
the database (properties) on the file groyp it says that owner is my
windows domain login.|||Ok, so if the owner is a valid login, then the problem is somewhere else...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141137171.049927.50760@.p10g2000cwp.googlegroups.com...
> The above query returns a row.
> Tibor, my login is sysadmin fixed server role. I have right clicked on
> the database (properties) on the file groyp it says that owner is my
> windows domain login.
>
Express 2005 - Database Diagram Error
Installed adventure works..and when creating a database diagram giving
a message says that it does not have valid owner.
The adventure works database is in SQL 2005 compatibility mode.
Perhaps the database owner doesn't exist? Try below:
SELECT * FROM sys.databases AS db
WHERE NOT EXISTS
(
SELECT * FROM sys.server_principals AS sp
WHERE sp.sid = db.owner_sid
)
If above returns any rows, try changing the database to a valid login using sp_changedbowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141080017.869972.135450@.j33g2000cwa.googlegr oups.com...
> Installed adventure works..and when creating a database diagram giving
> a message says that it does not have valid owner.
> The adventure works database is in SQL 2005 compatibility mode.
>
|||The above query returns a row.
Tibor, my login is sysadmin fixed server role. I have right clicked on
the database (properties) on the file groyp it says that owner is my
windows domain login.
|||Ok, so if the owner is a valid login, then the problem is somewhere else...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141137171.049927.50760@.p10g2000cwp.googlegro ups.com...
> The above query returns a row.
> Tibor, my login is sysadmin fixed server role. I have right clicked on
> the database (properties) on the file groyp it says that owner is my
> windows domain login.
>
a message says that it does not have valid owner.
The adventure works database is in SQL 2005 compatibility mode.
Perhaps the database owner doesn't exist? Try below:
SELECT * FROM sys.databases AS db
WHERE NOT EXISTS
(
SELECT * FROM sys.server_principals AS sp
WHERE sp.sid = db.owner_sid
)
If above returns any rows, try changing the database to a valid login using sp_changedbowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141080017.869972.135450@.j33g2000cwa.googlegr oups.com...
> Installed adventure works..and when creating a database diagram giving
> a message says that it does not have valid owner.
> The adventure works database is in SQL 2005 compatibility mode.
>
|||The above query returns a row.
Tibor, my login is sysadmin fixed server role. I have right clicked on
the database (properties) on the file groyp it says that owner is my
windows domain login.
|||Ok, so if the owner is a valid login, then the problem is somewhere else...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141137171.049927.50760@.p10g2000cwp.googlegro ups.com...
> The above query returns a row.
> Tibor, my login is sysadmin fixed server role. I have right clicked on
> the database (properties) on the file groyp it says that owner is my
> windows domain login.
>
Express 2005 - Database Diagram Error
Installed adventure works..and when creating a database diagram giving
a message says that it does not have valid owner.
The adventure works database is in SQL 2005 compatibility mode.Perhaps the database owner doesn't exist? Try below:
SELECT * FROM sys.databases AS db
WHERE NOT EXISTS
(
SELECT * FROM sys.server_principals AS sp
WHERE sp.sid = db.owner_sid
)
If above returns any rows, try changing the database to a valid login using
sp_changedbowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141080017.869972.135450@.j33g2000cwa.googlegroups.com...
> Installed adventure works..and when creating a database diagram giving
> a message says that it does not have valid owner.
> The adventure works database is in SQL 2005 compatibility mode.
>|||The above query returns a row.
Tibor, my login is sysadmin fixed server role. I have right clicked on
the database (properties) on the file groyp it says that owner is my
windows domain login.|||Ok, so if the owner is a valid login, then the problem is somewhere else...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141137171.049927.50760@.p10g2000cwp.googlegroups.com...
> The above query returns a row.
> Tibor, my login is sysadmin fixed server role. I have right clicked on
> the database (properties) on the file groyp it says that owner is my
> windows domain login.
>
a message says that it does not have valid owner.
The adventure works database is in SQL 2005 compatibility mode.Perhaps the database owner doesn't exist? Try below:
SELECT * FROM sys.databases AS db
WHERE NOT EXISTS
(
SELECT * FROM sys.server_principals AS sp
WHERE sp.sid = db.owner_sid
)
If above returns any rows, try changing the database to a valid login using
sp_changedbowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141080017.869972.135450@.j33g2000cwa.googlegroups.com...
> Installed adventure works..and when creating a database diagram giving
> a message says that it does not have valid owner.
> The adventure works database is in SQL 2005 compatibility mode.
>|||The above query returns a row.
Tibor, my login is sysadmin fixed server role. I have right clicked on
the database (properties) on the file groyp it says that owner is my
windows domain login.|||Ok, so if the owner is a valid login, then the problem is somewhere else...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1141137171.049927.50760@.p10g2000cwp.googlegroups.com...
> The above query returns a row.
> Tibor, my login is sysadmin fixed server role. I have right clicked on
> the database (properties) on the file groyp it says that owner is my
> windows domain login.
>
Exporting ToExcel file
Hi, I have a SP that adds an Excel file as a linked server, then tries to
send the result of a query into this file.
I get the following error :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
convert(varchar(10),ProductId), ProductName, Convert (varchar(20),UnitPrice)
from Northwind..Products
[OLE/DB provider returned message: Cannot start your application.
The workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
I am executing this on my Laptop(winXP SP2), sql 2000 is on my laptop. So
what authentification is failing?
Thanks in advance
Hi,
Could you please post the exact text of the stored
procedure? You can use sp_helptext.
You may also want to test this script to see if there is
any problem:
sp_dropserver 'EXCELSOURCE', 'droplogins'
go
--Replace 'E:\test.xls' appropriately
sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
@.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
'E:\test.xls' , @.provstr = 'Excel 5.0'
go
Insert ExcelSource...[ExcelTable$] ( A,B,C )
select convert(varchar(10),ProductId), ProductName,
Convert (varchar(20),UnitPrice)
from Northwind..Products
go
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUYosS7NtNk3oRyQvKh7cX1Ypnwdw==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>Subject: Exporting ToExcel file
>Date: Mon, 21 Feb 2005 21:53:01 -0800
>Lines: 18
>Message-ID:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26922
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hi, I have a SP that adds an Excel file as a linked
server, then tries to
>send the result of a query into this file.
>I get the following error :
>--
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. Authentication
>failed.
>Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
>convert(varchar(10),ProductId), ProductName, Convert
(varchar(20),UnitPrice)
>from Northwind..Products
>[OLE/DB provider returned message: Cannot start your
application.
>The workgroup information file is missing or opened
exclusively by another
>user.]
>OLE DB error trace [OLE/DB Provider
'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80040e4d:
Authentication failed.].
>--
>I am executing this on my Laptop(winXP SP2), sql 2000
is on my laptop. So
>what authentification is failing?
>Thanks in advance
>
|||Hello, here is the SP :
CREATE proc sp_write2Excel
(
@.fileName varchar(100),
@.NumOfColumns tinyint,
@.query varchar(200)
)
--Obligation : create an empty Excel file with a fixed name and place on the
server
/*
Usage
exec sp_write2Excel
-- Target Excel file
'c:\temp\NorthProducts.xls' ,
-- Number of columns in result
3,
-- The query to be exported
'select convert(varchar(10),ProductId),
ProductName,
Convert (varchar(20),UnitPrice) from Northwind..Products'
*/
AS
Begin
declare @.dosStmt varchar(200)
declare @.tsqlStmt varchar(500)
declare @.colList varchar(200)
declare @.charInd tinyint
set nocount on
-- construct the columnList A,B,C ...
-- until Num Of columns is reached.
set @.charInd=0
set @.colList = 'A'
while @.charInd < @.NumOfColumns - 1
begin
set @.charInd = @.charInd + 1
set @.colList = @.colList + ',' + char(65 + @.charInd)
end
-- Create an Empty Excel file as the target file name by copying the
template Empty excel File
set @.dosStmt = ' copy E:\Dev\sql\empty.xls ' + @.fileName
exec master..xp_cmdshell @.dosStmt
-- Create a "temporary" linked server to that file in order to
"Export" Data
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', @.fileName, NULL, 'Excel 5.0'
-- construct a T-SQL statement that will actually export the query
results
-- to the Table in the target linked server
set @.tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' + ' ( ' +
@.colList + ' ) '+ @.query
print @.tsqlStmt
-- execute dynamically the TSQL statement
exec (@.tsqlStmt)
-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
set nocount off
End
GO
"William Wang[MSFT]" wrote:
> Hi,
> Could you please post the exact text of the stored
> procedure? You can use sp_helptext.
> You may also want to test this script to see if there is
> any problem:
> sp_dropserver 'EXCELSOURCE', 'droplogins'
> go
> --Replace 'E:\test.xls' appropriately
> sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
> @.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
> 'E:\test.xls' , @.provstr = 'Excel 5.0'
> go
> Insert ExcelSource...[ExcelTable$] ( A,B,C )
> select convert(varchar(10),ProductId), ProductName,
> Convert (varchar(20),UnitPrice)
> from Northwind..Products
> go
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> <eliassal@.online.nospam>
> <532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
> phx.gbl!TK2MSFTNGXA03.phx.gbl
> microsoft.public.sqlserver.tools:26922
> server, then tries to
> error. Authentication
> (varchar(20),UnitPrice)
> application.
> exclusively by another
> 'Microsoft.Jet.OLEDB.4.0'
> Authentication failed.].
> is on my laptop. So
>
|||Hi,
Your script looks good and it works correctly on my test
machine. Based on my research, this issue can occur
because the login used to connect to the SQL Server does
not have enough permission. Please add the following
statement to your SP defination (below EXEC
sp_addlinkedserver):
EXEC sp_addlinkedsrvlogin 'ExcelSource',
'false',NULL,'ADMIN',NULL
then drop the existing SP and create a new SP to test
the problem.
Feel free to let me know if this resolves your problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUZDYE2sZAiZVxlSiqUFE3MWVkzdg==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>References:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
<K8U2ePMGFHA.2840@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Exporting ToExcel file
>Date: Tue, 22 Feb 2005 10:37:04 -0800
>Lines: 168
>Message-ID:
<6D98A325-8651-4FD5-AC3E-ADDE2258B7C1@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT FEED01.
phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26924
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hello, here is the SP :
>--
>CREATE proc sp_write2Excel
>(
>@.fileName varchar(100),
>@.NumOfColumns tinyint,
>@.query varchar(200)
>)
>--Obligation : create an empty Excel file with a fixed
name and place on the
>server
>/*
>Usage
>exec sp_write2Excel
> -- Target Excel file
> 'c:\temp\NorthProducts.xls' ,
> -- Number of columns in result
> 3,
> -- The query to be exported
> 'select convert(varchar(10),ProductId),
> ProductName,
> Convert (varchar(20),UnitPrice) from
Northwind..Products'
>
>*/
>AS
>Begin
> declare @.dosStmt varchar(200)
> declare @.tsqlStmt varchar(500)
> declare @.colList varchar(200)
> declare @.charInd tinyint
> set nocount on
> -- construct the columnList A,B,C ...
> -- until Num Of columns is reached.
> set @.charInd=0
> set @.colList = 'A'
> while @.charInd < @.NumOfColumns - 1
> begin
> set @.charInd = @.charInd + 1
> set @.colList = @.colList + ',' + char(65 +
@.charInd)
> end
> -- Create an Empty Excel file as the target
file name by copying the
>template Empty excel File
> set @.dosStmt = ' copy E:\Dev\sql\empty.xls ' +
@.fileName
> exec master..xp_cmdshell @.dosStmt
> -- Create a "temporary" linked server to that
file in order to
>"Export" Data
> EXEC sp_addlinkedserver 'ExcelSource', 'Jet
4.0',
>'Microsoft.Jet.OLEDB.4.0', @.fileName, NULL, 'Excel 5.0'
> -- construct a T-SQL statement that will
actually export the query
>results
> -- to the Table in the target linked server
> set @.tsqlStmt = 'Insert
ExcelSource...[ExcelTable$] ' + ' ( ' +[vbcol=seagreen]
>@.colList + ' ) '+ @.query
> print @.tsqlStmt
> -- execute dynamically the TSQL statement
> exec (@.tsqlStmt)
> -- drop the linked server
> EXEC sp_dropserver 'ExcelSource'
> set nocount off
>End
>GO
>
>"William Wang[MSFT]" wrote:
is[vbcol=seagreen]
,[vbcol=seagreen]
and[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.[vbcol=seagreen]
an[vbcol=seagreen]
2000
>
send the result of a query into this file.
I get the following error :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
convert(varchar(10),ProductId), ProductName, Convert (varchar(20),UnitPrice)
from Northwind..Products
[OLE/DB provider returned message: Cannot start your application.
The workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
I am executing this on my Laptop(winXP SP2), sql 2000 is on my laptop. So
what authentification is failing?
Thanks in advance
Hi,
Could you please post the exact text of the stored
procedure? You can use sp_helptext.
You may also want to test this script to see if there is
any problem:
sp_dropserver 'EXCELSOURCE', 'droplogins'
go
--Replace 'E:\test.xls' appropriately
sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
@.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
'E:\test.xls' , @.provstr = 'Excel 5.0'
go
Insert ExcelSource...[ExcelTable$] ( A,B,C )
select convert(varchar(10),ProductId), ProductName,
Convert (varchar(20),UnitPrice)
from Northwind..Products
go
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUYosS7NtNk3oRyQvKh7cX1Ypnwdw==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>Subject: Exporting ToExcel file
>Date: Mon, 21 Feb 2005 21:53:01 -0800
>Lines: 18
>Message-ID:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26922
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hi, I have a SP that adds an Excel file as a linked
server, then tries to
>send the result of a query into this file.
>I get the following error :
>--
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. Authentication
>failed.
>Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
>convert(varchar(10),ProductId), ProductName, Convert
(varchar(20),UnitPrice)
>from Northwind..Products
>[OLE/DB provider returned message: Cannot start your
application.
>The workgroup information file is missing or opened
exclusively by another
>user.]
>OLE DB error trace [OLE/DB Provider
'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80040e4d:
Authentication failed.].
>--
>I am executing this on my Laptop(winXP SP2), sql 2000
is on my laptop. So
>what authentification is failing?
>Thanks in advance
>
|||Hello, here is the SP :
CREATE proc sp_write2Excel
(
@.fileName varchar(100),
@.NumOfColumns tinyint,
@.query varchar(200)
)
--Obligation : create an empty Excel file with a fixed name and place on the
server
/*
Usage
exec sp_write2Excel
-- Target Excel file
'c:\temp\NorthProducts.xls' ,
-- Number of columns in result
3,
-- The query to be exported
'select convert(varchar(10),ProductId),
ProductName,
Convert (varchar(20),UnitPrice) from Northwind..Products'
*/
AS
Begin
declare @.dosStmt varchar(200)
declare @.tsqlStmt varchar(500)
declare @.colList varchar(200)
declare @.charInd tinyint
set nocount on
-- construct the columnList A,B,C ...
-- until Num Of columns is reached.
set @.charInd=0
set @.colList = 'A'
while @.charInd < @.NumOfColumns - 1
begin
set @.charInd = @.charInd + 1
set @.colList = @.colList + ',' + char(65 + @.charInd)
end
-- Create an Empty Excel file as the target file name by copying the
template Empty excel File
set @.dosStmt = ' copy E:\Dev\sql\empty.xls ' + @.fileName
exec master..xp_cmdshell @.dosStmt
-- Create a "temporary" linked server to that file in order to
"Export" Data
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', @.fileName, NULL, 'Excel 5.0'
-- construct a T-SQL statement that will actually export the query
results
-- to the Table in the target linked server
set @.tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' + ' ( ' +
@.colList + ' ) '+ @.query
print @.tsqlStmt
-- execute dynamically the TSQL statement
exec (@.tsqlStmt)
-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
set nocount off
End
GO
"William Wang[MSFT]" wrote:
> Hi,
> Could you please post the exact text of the stored
> procedure? You can use sp_helptext.
> You may also want to test this script to see if there is
> any problem:
> sp_dropserver 'EXCELSOURCE', 'droplogins'
> go
> --Replace 'E:\test.xls' appropriately
> sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
> @.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
> 'E:\test.xls' , @.provstr = 'Excel 5.0'
> go
> Insert ExcelSource...[ExcelTable$] ( A,B,C )
> select convert(varchar(10),ProductId), ProductName,
> Convert (varchar(20),UnitPrice)
> from Northwind..Products
> go
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> <eliassal@.online.nospam>
> <532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
> phx.gbl!TK2MSFTNGXA03.phx.gbl
> microsoft.public.sqlserver.tools:26922
> server, then tries to
> error. Authentication
> (varchar(20),UnitPrice)
> application.
> exclusively by another
> 'Microsoft.Jet.OLEDB.4.0'
> Authentication failed.].
> is on my laptop. So
>
|||Hi,
Your script looks good and it works correctly on my test
machine. Based on my research, this issue can occur
because the login used to connect to the SQL Server does
not have enough permission. Please add the following
statement to your SP defination (below EXEC
sp_addlinkedserver):
EXEC sp_addlinkedsrvlogin 'ExcelSource',
'false',NULL,'ADMIN',NULL
then drop the existing SP and create a new SP to test
the problem.
Feel free to let me know if this resolves your problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUZDYE2sZAiZVxlSiqUFE3MWVkzdg==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>References:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
<K8U2ePMGFHA.2840@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Exporting ToExcel file
>Date: Tue, 22 Feb 2005 10:37:04 -0800
>Lines: 168
>Message-ID:
<6D98A325-8651-4FD5-AC3E-ADDE2258B7C1@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT FEED01.
phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26924
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hello, here is the SP :
>--
>CREATE proc sp_write2Excel
>(
>@.fileName varchar(100),
>@.NumOfColumns tinyint,
>@.query varchar(200)
>)
>--Obligation : create an empty Excel file with a fixed
name and place on the
>server
>/*
>Usage
>exec sp_write2Excel
> -- Target Excel file
> 'c:\temp\NorthProducts.xls' ,
> -- Number of columns in result
> 3,
> -- The query to be exported
> 'select convert(varchar(10),ProductId),
> ProductName,
> Convert (varchar(20),UnitPrice) from
Northwind..Products'
>
>*/
>AS
>Begin
> declare @.dosStmt varchar(200)
> declare @.tsqlStmt varchar(500)
> declare @.colList varchar(200)
> declare @.charInd tinyint
> set nocount on
> -- construct the columnList A,B,C ...
> -- until Num Of columns is reached.
> set @.charInd=0
> set @.colList = 'A'
> while @.charInd < @.NumOfColumns - 1
> begin
> set @.charInd = @.charInd + 1
> set @.colList = @.colList + ',' + char(65 +
@.charInd)
> end
> -- Create an Empty Excel file as the target
file name by copying the
>template Empty excel File
> set @.dosStmt = ' copy E:\Dev\sql\empty.xls ' +
@.fileName
> exec master..xp_cmdshell @.dosStmt
> -- Create a "temporary" linked server to that
file in order to
>"Export" Data
> EXEC sp_addlinkedserver 'ExcelSource', 'Jet
4.0',
>'Microsoft.Jet.OLEDB.4.0', @.fileName, NULL, 'Excel 5.0'
> -- construct a T-SQL statement that will
actually export the query
>results
> -- to the Table in the target linked server
> set @.tsqlStmt = 'Insert
ExcelSource...[ExcelTable$] ' + ' ( ' +[vbcol=seagreen]
>@.colList + ' ) '+ @.query
> print @.tsqlStmt
> -- execute dynamically the TSQL statement
> exec (@.tsqlStmt)
> -- drop the linked server
> EXEC sp_dropserver 'ExcelSource'
> set nocount off
>End
>GO
>
>"William Wang[MSFT]" wrote:
is[vbcol=seagreen]
,[vbcol=seagreen]
and[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.[vbcol=seagreen]
an[vbcol=seagreen]
2000
>
Sunday, February 19, 2012
Exporting to Excel Error : (rrRenderingError)
When exporting some reports to excel.
This error appears
-----
Exception of type
microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index
----
The report can be exported as CSV without no issues.
Is there any explanation on what this error means...
Is there fix to the problem ....
Help...Have you installed RS sp1?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Constantine" <Constantine@.discussions.microsoft.com> wrote in message
news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> When exporting some reports to excel.
> This error appears :
> -----
> Exception of type
> microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index was out of range. Must be non-negative and less than the size of the
> collection. Parameter name: index
> ----
> The report can be exported as CSV without no issues.
> Is there any explanation on what this error means...
> Is there fix to the problem ....
> Help...|||"Constantine" <Constantine@.discussions.microsoft.com> wrote in message
news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> When exporting some reports to excel.
> This error appears :
> -----
> Exception of type
> microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index was out of range. Must be non-negative and less than the size of the
> collection. Parameter name: index
> ----
> The report can be exported as CSV without no issues.
> Is there any explanation on what this error means...
> Is there fix to the problem ....
> Help...
I and two others have posted to this board with the same issue in the past
month. To my knowledge no fix or workaround has been posted. If you find
such a workaround please post back.
Good luck,
Bryan|||Hi Mary,
I have installed sp1. I will try to re-install it.
Thanks
"Mary Bray" wrote:
> Have you installed RS sp1?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Constantine" <Constantine@.discussions.microsoft.com> wrote in message
> news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> > When exporting some reports to excel.
> > This error appears :
> > -----
> > Exception of type
> > microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown. (rrRenderingError) Get Online Help
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown.
> > Index was out of range. Must be non-negative and less than the size of the
> > collection. Parameter name: index
> >
> > ----
> >
> > The report can be exported as CSV without no issues.
> >
> > Is there any explanation on what this error means...
> > Is there fix to the problem ....
> >
> > Help...
>
>
This error appears
-----
Exception of type
microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index
----
The report can be exported as CSV without no issues.
Is there any explanation on what this error means...
Is there fix to the problem ....
Help...Have you installed RS sp1?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Constantine" <Constantine@.discussions.microsoft.com> wrote in message
news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> When exporting some reports to excel.
> This error appears :
> -----
> Exception of type
> microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index was out of range. Must be non-negative and less than the size of the
> collection. Parameter name: index
> ----
> The report can be exported as CSV without no issues.
> Is there any explanation on what this error means...
> Is there fix to the problem ....
> Help...|||"Constantine" <Constantine@.discussions.microsoft.com> wrote in message
news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> When exporting some reports to excel.
> This error appears :
> -----
> Exception of type
> microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index was out of range. Must be non-negative and less than the size of the
> collection. Parameter name: index
> ----
> The report can be exported as CSV without no issues.
> Is there any explanation on what this error means...
> Is there fix to the problem ....
> Help...
I and two others have posted to this board with the same issue in the past
month. To my knowledge no fix or workaround has been posted. If you find
such a workaround please post back.
Good luck,
Bryan|||Hi Mary,
I have installed sp1. I will try to re-install it.
Thanks
"Mary Bray" wrote:
> Have you installed RS sp1?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Constantine" <Constantine@.discussions.microsoft.com> wrote in message
> news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> > When exporting some reports to excel.
> > This error appears :
> > -----
> > Exception of type
> > microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown. (rrRenderingError) Get Online Help
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown.
> > Index was out of range. Must be non-negative and less than the size of the
> > collection. Parameter name: index
> >
> > ----
> >
> > The report can be exported as CSV without no issues.
> >
> > Is there any explanation on what this error means...
> > Is there fix to the problem ....
> >
> > Help...
>
>
Exporting to Excel error
I am having a problem exporting a report to Excel. The report has about 1000
records. I can export it to a pdf fine, but not Excel. I get an "Index was
outside the bounds of the array" error message.
thank you for any help possible.Are you still having this issue or it's solved? Can you tell me what you did
to fix the problem? Because I am having the same problem too. Thanks.
"PatNUFC" wrote:
> I am having a problem exporting a report to Excel. The report has about 1000
> records. I can export it to a pdf fine, but not Excel. I get an "Index was
> outside the bounds of the array" error message.
> thank you for any help possible.
records. I can export it to a pdf fine, but not Excel. I get an "Index was
outside the bounds of the array" error message.
thank you for any help possible.Are you still having this issue or it's solved? Can you tell me what you did
to fix the problem? Because I am having the same problem too. Thanks.
"PatNUFC" wrote:
> I am having a problem exporting a report to Excel. The report has about 1000
> records. I can export it to a pdf fine, but not Excel. I get an "Index was
> outside the bounds of the array" error message.
> thank you for any help possible.
Subscribe to:
Posts (Atom)