Field names must be CLS-compliant identifiers.

While creating a report using 'CreateReport' API method for Report Services
2000, I am getting the following error.
//
A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
--> A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
//
The error occurs due to the space on the computed field name. If I
concatinate the words with '_' (for example) then I do not get an error.
I would like know how can I create report that has field with the name like
â'Count Borrower Nameâ', etc?
Thanks.
-SueThe CLS-compliance rule for field names comes from the fact that in vast
majority of scenarios fields in expressions are referenced using the
following syntax: =Fields!FieldName.Value
If a field name is not CLS-compliant (for example "Field Name") then the
syntax above would result in VB compilation error: =Fields!Field Name.Value
is not a valid VB snippet.
RDL does not support field names with spaces. Although if you data source
returns non-CLS-compliant name you designer will build
<Field name="non_CLS_Compliant_Name">
<DataField>non CLS Compliant Name</DataField>
</Field>
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E82BA30A-FD8B-48E9-BF24-28EA9408301D@.microsoft.com...
> While creating a report using 'CreateReport' API method for Report
Services
> 2000, I am getting the following error.
> //
> A field in the data set 'MainDataSet' has the name 'Count Borrower Name'.
> Field names must be CLS-compliant identifiers.
> --> A field in the data set 'MainDataSet' has the name 'Count Borrower
Name'.
> Field names must be CLS-compliant identifiers.
> //
>
> The error occurs due to the space on the computed field name. If I
> concatinate the words with '_' (for example) then I do not get an error.
> I would like know how can I create report that has field with the name
like
> 'Count Borrower Name', etc?
> Thanks.
> -Sue
>

Field name question

Why does SQL Server change the name of a field named Level to [Level]? Level
is not a reserved word as far as I can tell, and this is creating a problem
when trying to hotsync PDAs to a SQL Server database.Ghost,
Level is an ODBC reserved keyword as well as a potential future SQL Server
keyword. See Reserved Keywords in the SQL Bol.
HTH
Jerry
"Ghost Dog" <caspar@.friendly.com> wrote in message
news:%23X6FJDiuFHA.908@.tk2msftngp13.phx.gbl...
> Why does SQL Server change the name of a field named Level to [Level]?
> Level
> is not a reserved word as far as I can tell, and this is creating a
> problem
> when trying to hotsync PDAs to a SQL Server database.
>|||The name has not changed - []'s are quotes for a quoted identifier.
(useful for using keywords if necessary, or multple words in a name).
The connection (oledb, odbc) might be defined to not use quoted
identifiers. If it's off, try turning it on and see if that solves the
problem.
Ghost Dog wrote:

>Why does SQL Server change the name of a field named Level to [Level]? Level
>is not a reserved word as far as I can tell, and this is creating a problem
>when trying to hotsync PDAs to a SQL Server database.
>
>

Field Description Won't Save in MSDE Table

I've been working with numerous tables (creating them, that is) in
MSDE.
Everything's working fine aside from the fact that I can't seem to get
any descriptive information on any field in any table to save.
I add the descriptive text to the field, click to save the table with
the newly-entered description, the hourglass displays and the table is
being saved, and then when the design view of the table refreshes, the
descriptions I've just entered on the various fields are gone.
Anyone else have problems with this?
Thanks!
Sincerely,
Brad H. McCollum
bmccoll1@.midsouth.rr.com
What application are you using to design the table?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0405070905.9a523cb@.posting.google.co m...
> I've been working with numerous tables (creating them, that is) in
> MSDE.
> Everything's working fine aside from the fact that I can't seem to get
> any descriptive information on any field in any table to save.
> I add the descriptive text to the field, click to save the table with
> the newly-entered description, the hourglass displays and the table is
> being saved, and then when the design view of the table refreshes, the
> descriptions I've just entered on the various fields are gone.
> Anyone else have problems with this?
> Thanks!
> Sincerely,
> Brad H. McCollum
> bmccoll1@.midsouth.rr.com
|||I'm using MSDE Manager by Vale Software and haven't found anything
dealing with this issue on their website.
Thanks for any additional information/assistance/recommendations you
might be able to offer.
Sincerely,
Brad H. McCollum
bmccoll1@.midsouth.rr.com
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#DZOEnFNEHA.808@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> What application are you using to design the table?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
> news:52031869.0405070905.9a523cb@.posting.google.co m...
|||hi Brad,
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> ha scritto nel messaggio
news:52031869.0405100708.c92ddd@.posting.google.com ...
> I'm using MSDE Manager by Vale Software and haven't found anything
> dealing with this issue on their website.
> Thanks for any additional information/assistance/recommendations you
> might be able to offer.
>
coul'd be a "feature" of MSDE Manager, as both Enterprise Manager and my own
management tool, available at the link following my sign., provide the
expectet behaviour...
please contact the application provider...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You should contact the vendor directly. I imagine it's not supported
because column descriptions are kind of superfluous. I tend to shy away
from storing column descriptions in the database, and instead opt for good
documentation on my tables. A user can always look at the documentation but
shouldn't be forced to read a (max 255?) character description by connecting
to the database.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0405100708.c92ddd@.posting.google.com ...
> I'm using MSDE Manager by Vale Software and haven't found anything
> dealing with this issue on their website.
> Thanks for any additional information/assistance/recommendations you
> might be able to offer.
> Sincerely,
> Brad H. McCollum
> bmccoll1@.midsouth.rr.com

Faxing In Sql Reporting Services

I have read a couple of references to creating "Custom Delivery Extensions", which could potentially handle faxing from a sql report. However I havent got any further than that.

Can anyone point me to a link or give me some idea as to how I might go about this.

Thanks,

Efax provides a web service for faxing documents.

http://www.efaxdeveloper.com/developer/twa/page/howWorks

So you could write a delivery extension that interfaces with this web service or you can use the email delivery extension to deliver it to a certain email address and have you app check this mailbox periodically and then fax out any reports it receives.

Please note that I am not endorsing efax or claiming its suitable for your purposes. I am merely suggesting a place to begin your investigations.

Fatal Error 682 and SqlCacheDependency

Hello,

I have tried two ways for executing a query and creating a dependency
on it, one using plain Sql commands, the other using Enterprise
Library wrapped commands.

I keep getting:

Warning: Fatal error 682 occurred at Oct 12 2007 11:01AM. Note the
error and time, and contact your system administrator.

string xml = cmd.ExecuteScalar() as string;

When I execute it. Does anyone know what could cause this.

I have read both of these posts and have not yet been able to
investigate the SQL machine's event viewer though:

http://forums.asp.net/p/959871/1188606.aspx#1188606

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

The "value" column is an XML datatype field.

Thanks,
Josh

public static ContentXmlUtilResult GetContentXml(
string key,
bool createDependency)
{
ContentXmlUtilResult result = new ContentXmlUtilResult();

string connString = Global.Current.ConnectionString;

using (SqlConnection conn =
new SqlConnection(connString))
{
SqlCommand cmd =
new SqlCommand(
"SELECT Value FROM dbo.ContentXml WHERE [Key]
= '" + key + "'",
conn);

if (createDependency)
{
System.Web.Caching.SqlCacheDependency dependency =
new SqlCacheDependency(cmd);

result.Dependency = dependency;
}

conn.Open();

string xml = cmd.ExecuteScalar() as string;

conn.Close();

result.Content = xml;
}

return result;

//SqlCommand cmd = (SqlCommand)
// Global.Current.Database.GetSqlStringCommand(
// //@."SELECT Value FROM dbo.ContentXml WHERE [Key] =
@.Key");
// @."SELECT Value FROM dbo.ContentXml WHERE [Key] = '"
+ key + "'");

////cmd.Parameters.Add("@.Key", SqlDbType.VarChar).Value =
key;

//if (createDependency)
//{
// System.Web.Caching.SqlCacheDependency dependency =
// new System.Web.Caching.SqlCacheDependency(cmd);

// result.Dependency = dependency;
//}

//string xml = Global.Current.Database.ExecuteScalar(cmd)
as string;

//result.Content = xml;

//return result;
}

However bizarre this may sound, I solved this problem by changing the length of the key to 15 charcters. I was using SystemStatusContent as the value, so the SQL statement was:

SELECT Value FROM dbo.ContentXml WHERE [Key] = 'SystemStatusContent'

But, when I changed the key to a smaller value, it now works fine!

SELECT Value FROM dbo.ContentXml WHERE [Key] = 'SystemStatus'

I do not know why this fixed it. I'm just glad it did.

Josh

Fastest way to insert?

Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.developersdex.com ***The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
--
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||And how many are you inserting? How many indexes and how large. How much
time does it need to create indexes? Hardware specs? Columns specs?
It would probably best to drop indexes and recreate them if you can, but I
dont see the problem even if you dont drop them. Only, try to see if you
need to defragment them after that...
MC
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>>Hello,
>>If it is a production server I will suggest you to create the table and
>>indexes and then insert the data into new table using BCP IN or BULK
>>INSERT
>>in batch commit mode with BULK_INSERT recovery model. If you are creating
>>a
>>table with SELECT * INTO and this will create locks in sysobjects table
>>which
>> is not a good idea.
>>Thanks
>>Hari
>>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>BOL has some good info but here are some basics. First off it is no
>>different than FULL recovery until you do an operation that can take
>>advantage of a minimally logged operation. These would be things like:
>>CREATE INDEX
>>SELECT INTO
>>BULK INSERT
>>etc. (See BOL for more details)
>>When you do execute one of these in Bulk_Logged or Simple recovery mode
>>only
>>the ID of the extent that was modified during that operation is logged in
>>the tran log. So if you did a bulk insert of 1 million rows and it filled
>>up
>>1000 extents you would only log the ID's for those 1000 extents not the
>>actual data that would normally be logged in FULL recovey mode.There are
>>several implications of this. One is that you can no longer do point in
>>time
>>recovery until you issue another full backup to start the proper logging
>>again. You can restore the entire log file though. The second is that when
>>you backup the tran log it will go and get all the data for those 1000
>>extents and place them in the log backup file. So the backup will take the
>>hit that normally would have occured if you did not do a minimally logged
>>load.
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
Rob
Rob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
David Gugick
Imceda Software
www.imceda.com
|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
David Gugick
Imceda Software
www.imceda.com
|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>
|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
--
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
--
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how to
>> help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>> David,
>> Thanks for the info. I already can sequence the SubID's in my
>> application. I am trying to find a way to cheat (ie faster) by using SQL.
>> In a related topic is there a way to get the select the ROWID?
>> Rob
>>
>> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
>> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how
>> to help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>>
>

Fan Trap or Multiple one-to-many joins

I'm having a problem creating a view on some data that involves two one-to-many joins like this:

tbl1 m--> tbl2 <--n tbl3

and I need to create a view on the data from all three tables without duplicates from tbl1 and tbl3.

The problem is that tbl1 and tbl3 are not related at all, except that they are linked by data in tbl2.

Think of it like this: you have a project, which can have multiple consultants, and multiple stakeholders, and the data must be returned in such a way that each consultant and each stakeholder appears once in the output (the project name must appear multiple times of course). The issue is that the following two datasets are logically distinct but semantically identical:

proj A, consultant A, stakeholder A
proj A, consultant B, stakeholder B
--
proj A, consultant B, stakeholder A
proj A, consultant A, stakeholder B

but what I'm aiming for is:

proj A, consultant A, stakeholder A
proj A, consultant B, stakeholder B

I've heard this described as a fan trap, but usual solutions involve reorganzing the data so that tbl3 joins tbl1 joins tbl2, but in my case there is no link there

Incidently, the intended platform for this is DB2 and/or SQL Server. Any help appreciated, thanks.your examples are not very clear

you start out by diagramming tbl1, tbl2, tbl3, and then immediately switch to projects, consultants, and shareholders, without showing the actual data in these tables, just some apparent cross join query results

you might wish to show a more comprehensive example, because so far, it's hard to understand what you're asking|||I've heard this described as many things, most of which aren't polite to repeat. ;)

You're trying to figure out how to build a join to show the relationship between consultants and shareholders, to produce a one-to-one join between two tables that explicitly have no relationship. If you figure out how to make this happen, please let me know... I'm sure that I'll be fascinated by the explanation!

You've got a clear relationship between project and shareholder, and another relationship between project and consultant. You don't explicitly state that there must be a shareholder or a consultant for any given project, and at some point in the project's life I can guarantee that there will not be one of either. You don't explicitly state that there must be one shareholder for every consultant. If you think about these requirements, unless at least one of these requirements is false, you can't get the output you want... There ain't no way to git there from here.

You need to rethink either the specifications or the requirements. Something has got to give because using the definitions that you've given, the present problem can't be solved.

-PatP|||ah, so that's what that is -- i had never heard that terminology before

this pdf is a pretty good explanation --
http://support.businessobjects.com/documentation/installation_resources/5i/tips_and_tricks/pdf/universe_design/ut001.pdf

i would solve this problem with a UNION query --

proj A, consultant A
proj A, consultant B
proj A, stakeholder A
proj A, stakeholder B

Failure to create a Report Project within Visual Studio 2003

I'm having a problem creating a Report Project within Visual Studio. I have
VS.NET 2003 installed and I had then installed Sql Reporting Services with
the latest service pack. If I try and then create a reporting project within
a pre-existing solution, it gives me the following error.
Class already exists.
I looked at the knowledge base and it seems that this behaviour is caused by
a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
add-in manager and tested again. The problem still exists.
Any other ideas on what I should try?
Thanks,
Darren.On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
<dmpenner@.discussions.microsoft.com> wrote:
>I'm having a problem creating a Report Project within Visual Studio. I have
>VS.NET 2003 installed and I had then installed Sql Reporting Services with
>the latest service pack. If I try and then create a reporting project within
>a pre-existing solution, it gives me the following error.
>Class already exists.
>I looked at the knowledge base and it seems that this behaviour is caused by
>a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
>add-in manager and tested again. The problem still exists.
>Any other ideas on what I should try?
>Thanks,
>Darren.
Darren,
If you try to create a new project from the File menu (choose the
Wizard option for speed) does that create a working report or not?
Andrew Watt
MVP - InfoPath|||Andrew,
Just tried creating a new report project from the file menu and I still get
the same "Class already exists" message. If I look in my local directory
where I'm trying to create the new project, a folder is created there with a
virtually empty .rptproj file with the following contents.
<?xml version="1.0"?>
<Project xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xmlns:xsd="">http://www.w3.org/2000/10/XMLSchema">
<DataSources />
<Reports />
</Project>
I can't however, see the project in the solution explorer and it doesn't
seem to be added to the solution file. Any other suggestions?
Thanks,
Darren.
"Andrew Watt [MVP - InfoPath]" wrote:
> On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
> <dmpenner@.discussions.microsoft.com> wrote:
> >I'm having a problem creating a Report Project within Visual Studio. I have
> >VS.NET 2003 installed and I had then installed Sql Reporting Services with
> >the latest service pack. If I try and then create a reporting project within
> >a pre-existing solution, it gives me the following error.
> >
> >Class already exists.
> >
> >I looked at the knowledge base and it seems that this behaviour is caused by
> >a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
> >add-in manager and tested again. The problem still exists.
> >
> >Any other ideas on what I should try?
> >
> >Thanks,
> >Darren.
> Darren,
> If you try to create a new project from the File menu (choose the
> Wizard option for speed) does that create a working report or not?
> Andrew Watt
> MVP - InfoPath
>|||Darren,
This isn't something I have seen before so the following questions are
simply trying to identify more clearly your specific
setup/circumstances.
Can you describe your setup in more detail? Which add-ins do you have?
When did you disable them?
Have you ever (I assume not, but want to be clear) created an RS
report successfully on that machine?
Apart from RS is Visual Studio working normally?
Andrew Watt
MVP - InfoPath
On Thu, 7 Apr 2005 08:55:03 -0700, "dmpenner"
<dmpenner@.discussions.microsoft.com> wrote:
>Andrew,
>Just tried creating a new report project from the file menu and I still get
>the same "Class already exists" message. If I look in my local directory
>where I'm trying to create the new project, a folder is created there with a
>virtually empty .rptproj file with the following contents.
><?xml version="1.0"?>
><Project xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
>xmlns:xsd="">http://www.w3.org/2000/10/XMLSchema">
> <DataSources />
> <Reports />
></Project>
>I can't however, see the project in the solution explorer and it doesn't
>seem to be added to the solution file. Any other suggestions?
>Thanks,
>Darren.
>"Andrew Watt [MVP - InfoPath]" wrote:
>> On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
>> <dmpenner@.discussions.microsoft.com> wrote:
>> >I'm having a problem creating a Report Project within Visual Studio. I have
>> >VS.NET 2003 installed and I had then installed Sql Reporting Services with
>> >the latest service pack. If I try and then create a reporting project within
>> >a pre-existing solution, it gives me the following error.
>> >
>> >Class already exists.
>> >
>> >I looked at the knowledge base and it seems that this behaviour is caused by
>> >a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
>> >add-in manager and tested again. The problem still exists.
>> >
>> >Any other ideas on what I should try?
>> >
>> >Thanks,
>> >Darren.
>> Darren,
>> If you try to create a new project from the File menu (choose the
>> Wizard option for speed) does that create a working report or not?
>> Andrew Watt
>> MVP - InfoPath