Problems inserting records into non dbo schema

I have a basic data flow which tries to insert data from an excel spreadsheet to a loading table (sql server 2005). I have created this table in a non dbo schema. I have used the schema owner as the sql server login for this loading step.

The problem is SSIS seems to throw a strange error when I do this:

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

When I create this table in the dbo it seems to work ok. I have tried giving the schema owner sa rights on the sql server and it still doesnt work. Im wondering if this is a known bug in ssis.

Does anyone have any ideas?This turned out to be a conflict between using Nvarchar(max) and Nvarchar(255). Even though the data would fit ssis didnt seem to like it.|||

Of course not. they're two different data types!

If I understand correctly varchar(max) is not the same as an infinitely long "normal" varchar. Or it helps to not think about it that way anyway.

I'm sure you'd get a better answer on the T-SQL forum

-Jamie

sql

Problems exporting to Excel

I am having a problem exporting a linked SQL server table from Access 2003
to
Excel. The file is about 2,000 records with several columns. The problem
is that when the table is imported into excel some of the records are not
coming across with the correct data and when numeric fields are summed the
totals are wrong.
When I export directly to Excel from the SQL server using DTS, I don't have
this problem. Has anyone else had this problem? Anyone have ideas of how to
fix it? I need to be able to allow the users to create queries in access
and export the data they need, so setting up a DTS as a solution is not
really workable (or at least I don't think it is).Hi Jim,
Being that this works fine from SQL Server but you are
having problems from MS Access, it's likely an MS Access
issue that you may want to post in one of the MS Access
newsgroups. Maybe try the following group:
microsoft.public.access.externaldata
-Sue
On Mon, 21 Aug 2006 14:52:39 GMT, "JIM" <jcrisp1@.kc.rr.com>
wrote:

>I am having a problem exporting a linked SQL server table from Access 2003
>to
>Excel. The file is about 2,000 records with several columns. The problem
>is that when the table is imported into excel some of the records are not
>coming across with the correct data and when numeric fields are summed the
>totals are wrong.
>When I export directly to Excel from the SQL server using DTS, I don't have
>this problem. Has anyone else had this problem? Anyone have ideas of how t
o
>fix it? I need to be able to allow the users to create queries in access
>and export the data they need, so setting up a DTS as a solution is not
>really workable (or at least I don't think it is).
>

problems adding records to linked tables

Hi everyone,

I just used the upsize wizard in Access to create a Access front end and SQL server 2000 backend with linked tables, but I am receiving an error that I can't figure out.

When I am trying to add a record into my 'Cases' linked table in the SQL server, I get a error saying: "Invalid object name" with -2147217865 as the error message.

Here is my code:

Dim testrecord As ADODB.Recordset
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb.1;" & _
"DSN=intelitrak;" & _
"UID=geir;PWD=test"
conn.Mode = adModeReadWrite
conn.Open

Set testrecord = New ADODB.Recordset
testrecord.Open "Cases", conn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect -- the ERROR occurs here!!!

With testrecord
.AddNew
If Forms(stDocName)!NewReopen = "New" Then
!MasterID = NewID
IsReOpen = False
Else

....etc.

I looked all around for an answer, and I partially found a answer at Microsoft's website: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/9/95.asp&NoWebContent=1

However, I do not know how to make this work. I tried to create a stored procedure, and test if that made any differance, but it still said it the it was a invalid object.

Can anyone help me?

GeirJust a shot in the dark...

What if you add dbo_ to the front of Cases|||That was not it.

I tried to put 'dbo_Cases' in my VBA code, but the same error happens.

Geir|||Sorry...mayb a post in the VB forum...

Do you have other code that connects?|||none of my code that involves adding new records to the database is working. The code that views the records, however, works perfectly.

Geir

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin
That is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin
|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
David Portas
SQL Server MVP
|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>
|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>
|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers[vbcol=seagreen]
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> application
not[vbcol=seagreen]
> marked
> The
key
> the
>
|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>
|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> / :
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
LixinThat is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
--
David Portas
SQL Server MVP
--|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> > Many applications retrieve information from the same table. Each
> application
> > needs to remember the records it has retrieved before, so that it does
not
> > retrieve these records in the following run any more.
> >
> > The current solution is to put the different "flag" field for each
> > application on the table. The flag field for each application will be
> marked
> > by the application after it retrieves the records.
> >
> > The problem is that the table becomes extremely wide and keep widening.
> The
> > table needs to be changed each time a new appliction comes up.
> >
> > I am thinking about keep a list for each application which stores the
key
> > values of the records the application has retrieved, instead of marking
> the
> > original table.
> >
> > Is there any better way to do it? What is the workaround you recommend?
> > Thanks,
> >
> > Lixin
> >
> >
>|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
LixinThat is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
David Portas
SQL Server MVP
--|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> application
not[vbcol=seagreen]
> marked
> The
key[vbcol=seagreen]
> the
>|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> / :
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: mssql locks several records in a specific table, while no one is editing them

Hi,
I have an Access application with linked tables via ODBC to MSSQL
server 2000.

Having a weird problem, probably something i've done while not being
aware of (kinda newbie).

the last 20 records (and growing)of a specific table are locked - cant
change them - ("another user is editing these records ... ").

I know for a fact that no one is editing records and yet no user can
edit these last records in the MDB - including the administrator -
while able to add new records.

Administrator able to edit records in the ADP (mssql server) where the
tables are stored.

Please help, the application is renedred inert .

Thanks for reading,
Oren.Hi

I can't say that I am an expert on access, but this sounds like the
recordset may need refreshing i.e someone has changed the record and access
knows the local copy is out of date.

John

"Oren" <orentini@.hotmail.com> wrote in message
news:b1368c93.0409160909.128bae81@.posting.google.c om...
> Hi,
> I have an Access application with linked tables via ODBC to MSSQL
> server 2000.
> Having a weird problem, probably something i've done while not being
> aware of (kinda newbie).
> the last 20 records (and growing)of a specific table are locked - cant
> change them - ("another user is editing these records ... ").
> I know for a fact that no one is editing records and yet no user can
> edit these last records in the MDB - including the administrator -
> while able to add new records.
> Administrator able to edit records in the ADP (mssql server) where the
> tables are stored.
> Please help, the application is renedred inert .
> Thanks for reading,
> Oren.|||Hi,
thanks for replying.

There is no local and remote copy of the table, the table is only
stored in the sql server and linked to the access MDB.

Simple refresh wont do (tried it, via the access menubars).

have any more suggestions?

Thanks,

Oren,

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<414b53f6$0$27296$afc38c87@.news.easynet.co.uk>...
> Hi
> I can't say that I am an expert on access, but this sounds like the
> recordset may need refreshing i.e someone has changed the record and access
> knows the local copy is out of date.
> John
> "Oren" <orentini@.hotmail.com> wrote in message
> news:b1368c93.0409160909.128bae81@.posting.google.c om...
> > Hi,
> > I have an Access application with linked tables via ODBC to MSSQL
> > server 2000.
> > Having a weird problem, probably something i've done while not being
> > aware of (kinda newbie).
> > the last 20 records (and growing)of a specific table are locked - cant
> > change them - ("another user is editing these records ... ").
> > I know for a fact that no one is editing records and yet no user can
> > edit these last records in the MDB - including the administrator -
> > while able to add new records.
> > Administrator able to edit records in the ADP (mssql server) where the
> > tables are stored.
> > Please help, the application is renedred inert .
> > Thanks for reading,
> > Oren.|||Hi

You may want to post this is an access news group!

John

"Oren" <orentini@.hotmail.com> wrote in message
news:b1368c93.0409180015.718465e6@.posting.google.c om...
> Hi,
> thanks for replying.
> There is no local and remote copy of the table, the table is only
> stored in the sql server and linked to the access MDB.
> Simple refresh wont do (tried it, via the access menubars).
> have any more suggestions?
> Thanks,
> Oren,
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<414b53f6$0$27296$afc38c87@.news.easynet.co.uk>...
> > Hi
> > I can't say that I am an expert on access, but this sounds like the
> > recordset may need refreshing i.e someone has changed the record and
access
> > knows the local copy is out of date.
> > John
> > "Oren" <orentini@.hotmail.com> wrote in message
> > news:b1368c93.0409160909.128bae81@.posting.google.c om...
> > > Hi,
> > > I have an Access application with linked tables via ODBC to MSSQL
> > > server 2000.
> > > > Having a weird problem, probably something i've done while not being
> > > aware of (kinda newbie).
> > > > the last 20 records (and growing)of a specific table are locked - cant
> > > change them - ("another user is editing these records ... ").
> > > > I know for a fact that no one is editing records and yet no user can
> > > edit these last records in the MDB - including the administrator -
> > > while able to add new records.
> > > > Administrator able to edit records in the ADP (mssql server) where the
> > > tables are stored.
> > > > Please help, the application is renedred inert .
> > > > Thanks for reading,
> > > Oren.

Problem: Identity seed

I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX
You can use IDENTITY_INSERT ON, load your data, turn it OFF and then reset
the seed to the max, using DBCC CHECKIDENT.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX
|||No. What you're trying to do is in violation of the use of an identity
column. Its sole purpose is to increment a value defined by the seed and
step. Identity_insert allows you to insert into an identity column, but the
system will do a check of the newly inserted and reset the seed if the new
value is larger than the current seed.
Bol has this thoroughly documented under 'set identity_insert'.
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> I have an Identity field in a table with many records. Since the database
is
> corrupt, I recreated the table and loaded the original data back into the
> new table.
> I want to make sure the Identity field keep incrementing its value from
the
> max value instead of starting from 1 again.
> Here is what I did:
> Before I loading the data, I set the IDENTITY_INSERT ON and set it off
when
> the data was loaded. However, the new records' identity field value still
> start
> from the 1. :-(
> I don't want to change the identity seed (it must be "1"). Is there any
> other way I can achieve the goal?
> Thanks a lot,
> LX
>

Problem: Identity seed

I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LXYou can use IDENTITY_INSERT ON, load your data, turn it OFF and then reset
the seed to the max, using DBCC CHECKIDENT.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX|||No. What you're trying to do is in violation of the use of an identity
column. Its sole purpose is to increment a value defined by the seed and
step. Identity_insert allows you to insert into an identity column, but the
system will do a check of the newly inserted and reset the seed if the new
value is larger than the current seed.
Bol has this thoroughly documented under 'set identity_insert'.
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> I have an Identity field in a table with many records. Since the database
is
> corrupt, I recreated the table and loaded the original data back into the
> new table.
> I want to make sure the Identity field keep incrementing its value from
the
> max value instead of starting from 1 again.
> Here is what I did:
> Before I loading the data, I set the IDENTITY_INSERT ON and set it off
when
> the data was loaded. However, the new records' identity field value still
> start
> from the 1. :-(
> I don't want to change the identity seed (it must be "1"). Is there any
> other way I can achieve the goal?
> Thanks a lot,
> LX
>

Problem: Identity seed

I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LXYou can use IDENTITY_INSERT ON, load your data, turn it OFF and then reset
the seed to the max, using DBCC CHECKIDENT.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX|||No. What you're trying to do is in violation of the use of an identity
column. Its sole purpose is to increment a value defined by the seed and
step. Identity_insert allows you to insert into an identity column, but the
system will do a check of the newly inserted and reset the seed if the new
value is larger than the current seed.
Bol has this thoroughly documented under 'set identity_insert'.
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> I have an Identity field in a table with many records. Since the database
is
> corrupt, I recreated the table and loaded the original data back into the
> new table.
> I want to make sure the Identity field keep incrementing its value from
the
> max value instead of starting from 1 again.
> Here is what I did:
> Before I loading the data, I set the IDENTITY_INSERT ON and set it off
when
> the data was loaded. However, the new records' identity field value still
> start
> from the 1. :-(
> I don't want to change the identity seed (it must be "1"). Is there any
> other way I can achieve the goal?
> Thanks a lot,
> LX
>

Problem: Trigger and multiple updates to the table

Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.

UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20

Error I am getting is :

Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

What is the best possible way to make it work? Thank you.Figure out which trigger is blowing up, and fix it? Knowing that there is more code than we can see, I can't for the life of me figure out a way to help you find out where the problem is, much less how to fix it.

-PatP|||I guess this is the problematic trigger. Should I use cursor to handle multiple inserts/updates?

CREATE trigger t_thickupdate on dbo.sched_lot_hdr
for update
as

-- declare local variables
declare
@.lotno int,
@.charthick varchar(10),
@.decthick decimal(6,5)

-- populate local variables
set @.lotno = (select lotno from inserted)
set @.charthick = (select matcharthick from inserted)
set @.decthick = (select matthick from inserted)

-- determine which was updated: character thickness or decimal thickness
if update(matthick)

-- decimal thickness was updated
begin
set @.charthick = (select thkfrac from prod_thk_stds where thkdec = @.decthick)
update sched_lot_hdr set matcharthick = @.charthick where lotno = @.lotno
end
else

-- character thickness was updated
begin
set @.decthick = (select thkdec from prod_thk_stds where thkfrac = @.charthick)
update sched_lot_hdr set matthick = @.decthick where lotno = @.lotno
end|||A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based...|||Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense...|||I'd suggest using:CREATE TRIGGER t_thickupdate
ON dbo.sched_lot_hdr
FOR UPDATE
AS

-- determine which was updated: character thickness or decimal thickness
IF Update(matthick) -- decimal thickness was updated
UPDATE sched_lot_hdr
SET matcharthick = s.thkfrac
FROM inserted AS i
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
JOIN prod_thk_stds AS s
ON (s.thkdec = i.matthick)
ELSE -- character thickness was updated
update sched_lot_hdr
set matthick = s.thkdec
FROM inserted AS i
JOIN prod_thk_stds AS s
ON (s.thkfrac = i.matcharthick)
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)

RETURNNote that you need to test this up one side and down the other before you put it into production, this was a quick paste up job with no opportunity for me to test it!

-PatP|||yeah :(

This already been developed. I came to this project recently and working on some enhancements. Thanks for the help. I think I understand the problem, I will resolve it.

Update: thats fast. Thanks Pat, I will test it.|||Originally posted by Brett Kaiser
A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based... Yea vous! I'm with you on that!
Originally posted by Brett Kaiser
Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense... Nah, that happens all the time when folks convert from what I call "unit-record" code to a database. They find ways to get the database to "clean up" data coming from disparate sources that would have required application changes when the only thing that could reach the data was their application.

-PatP

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

Problem with user prompt

I want to prompt the user for a specific ID number or use a * for all
records at the time of running the report
So in my criteria for the employee ID field I put "= @.empid"
In the report the input box is there. If I put in an ID number it
works great. However if I put in an asterisk "*" nothing shows up.
Any ideas?Your where clause should be written like this:
where
(isnull(@.EmpID, '*') = '*')
or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
this lets your users enter a value for EmpID (then the second part of the
where clause will deal with it)
or leave the parameter field empty - then all their records will be selected
You could also replace the '*' with a '' (empty string) - this way, if the
users leave the parameter field blank, it will behave like a '*'.
Andrei.
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>I want to prompt the user for a specific ID number or use a * for all
> records at the time of running the report
> So in my criteria for the employee ID field I put "= @.empid"
> In the report the input box is there. If I put in an ID number it
> works great. However if I put in an asterisk "*" nothing shows up.
> Any ideas?
>|||Ok you lost me a little bit.
Here is my current where clause.
WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =@.ClockNo)
The "@.clockno" section is where I prompt them for a clock number.
If I put a * it doesn't work. If I put a good number in it works fine.
Where does your 'where' clause fit into this?
Andrei wrote:
> Your where clause should be written like this:
> where
> (isnull(@.EmpID, '*') = '*')
> or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
> this lets your users enter a value for EmpID (then the second part of the
> where clause will deal with it)
> or leave the parameter field empty - then all their records will be selected
> You could also replace the '*' with a '' (empty string) - this way, if the
> users leave the parameter field blank, it will behave like a '*'.
> Andrei.
>
> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> >I want to prompt the user for a specific ID number or use a * for all
> > records at the time of running the report
> >
> > So in my criteria for the employee ID field I put "= @.empid"
> >
> > In the report the input box is there. If I put in an ID number it
> > works great. However if I put in an asterisk "*" nothing shows up.
> >
> > Any ideas?
> >|||I'd write an IIF statement (either in a stored procedure, or in the
Report Query):
IF @.clockno = '*'
BEGIN
select ...
from ...
where (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
END
ELSE
BEGIN
select ..
from ..
where (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =@.ClockNo)
END
Bruce Lawrence wrote:
> Ok you lost me a little bit.
> Here is my current where clause.
> WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
> (pshstj.trn = 'RO1' OR
> pshstj.trn = 'WO1' OR
> pshstj.trn = 'XXX')) AND (pshstj.empid => @.ClockNo)
> The "@.clockno" section is where I prompt them for a clock number.
> If I put a * it doesn't work. If I put a good number in it works fine.
> Where does your 'where' clause fit into this?
>
> Andrei wrote:
> > Your where clause should be written like this:
> >
> > where
> > (isnull(@.EmpID, '*') = '*')
> > or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
> >
> > this lets your users enter a value for EmpID (then the second part of the
> > where clause will deal with it)
> > or leave the parameter field empty - then all their records will be selected
> >
> > You could also replace the '*' with a '' (empty string) - this way, if the
> > users leave the parameter field blank, it will behave like a '*'.
> >
> > Andrei.
> >
> >
> > "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> > news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> > >I want to prompt the user for a specific ID number or use a * for all
> > > records at the time of running the report
> > >
> > > So in my criteria for the employee ID field I put "= @.empid"
> > >
> > > In the report the input box is there. If I put in an ID number it
> > > works great. However if I put in an asterisk "*" nothing shows up.
> > >
> > > Any ideas?
> > >|||WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate)
AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
AND
(
(isnull(@.ClockNo, '*') = '*')
or
( (isnull(@.ClockNo, '*') <> '*') and (pshstj.empid =lockNo) )
)
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164828482.158109.84050@.j44g2000cwa.googlegroups.com...
> Ok you lost me a little bit.
> Here is my current where clause.
> WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
> (pshstj.trn = 'RO1' OR
> pshstj.trn = 'WO1' OR
> pshstj.trn = 'XXX')) AND (pshstj.empid => @.ClockNo)
> The "@.clockno" section is where I prompt them for a clock number.
> If I put a * it doesn't work. If I put a good number in it works fine.
> Where does your 'where' clause fit into this?
>
> Andrei wrote:
>> Your where clause should be written like this:
>> where
>> (isnull(@.EmpID, '*') = '*')
>> or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
>> this lets your users enter a value for EmpID (then the second part of the
>> where clause will deal with it)
>> or leave the parameter field empty - then all their records will be
>> selected
>> You could also replace the '*' with a '' (empty string) - this way, if
>> the
>> users leave the parameter field blank, it will behave like a '*'.
>> Andrei.
>>
>> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
>> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>> >I want to prompt the user for a specific ID number or use a * for all
>> > records at the time of running the report
>> >
>> > So in my criteria for the employee ID field I put "= @.empid"
>> >
>> > In the report the input box is there. If I put in an ID number it
>> > works great. However if I put in an asterisk "*" nothing shows up.
>> >
>> > Any ideas?
>> >
>|||Andrei,
I'm not sure how... but it works.
I love you