replacing nulls

Using the DTS ive successfully transfered data from access to sql - however
i need to replace the <nulls>
with '' - is there an easy way to do this ?
thanks
mark
UPDATE SomeTable
SET col1 = COALESCE(col1, ''),
col2 = COALESCE(col2, ''),
...
WHERE col1 IS NULL
OR col2 IS NULL
...
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IfednUh_ya-M_ALdRVn-gQ@.giganews.com...
> UPDATE SomeTable
> SET col1 = COALESCE(col1, ''),
> col2 = COALESCE(col2, ''),
> ...
> WHERE col1 IS NULL
> OR col2 IS NULL
> ...
> --
> David Portas
> SQL Server MVP
> --
>
thanks for the help!
mark
sql

replacing nulls

Using the DTS ive successfully transfered data from access to sql - however
i need to replace the <nulls>
with '' - is there an easy way to do this ?
thanks
markUPDATE SomeTable
SET col1 = COALESCE(col1, ''),
col2 = COALESCE(col2, ''),
..
WHERE col1 IS NULL
OR col2 IS NULL
..
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IfednUh_ya-M_ALdRVn-gQ@.giganews.com...
> UPDATE SomeTable
> SET col1 = COALESCE(col1, ''),
> col2 = COALESCE(col2, ''),
> ...
> WHERE col1 IS NULL
> OR col2 IS NULL
> ...
> --
> David Portas
> SQL Server MVP
> --
>
thanks for the help!
mark

replacing nulls

Using the DTS ive successfully transfered data from access to sql - however
i need to replace the <nulls>
with '' - is there an easy way to do this ?
thanks
markUPDATE SomeTable
SET col1 = COALESCE(col1, ''),
col2 = COALESCE(col2, ''),
...
WHERE col1 IS NULL
OR col2 IS NULL
...
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IfednUh_ya-M_ALdRVn-gQ@.giganews.com...
> UPDATE SomeTable
> SET col1 = COALESCE(col1, ''),
> col2 = COALESCE(col2, ''),
> ...
> WHERE col1 IS NULL
> OR col2 IS NULL
> ...
> --
> David Portas
> SQL Server MVP
> --
>
thanks for the help!
marksql

replacing Access db with SQL Server db

Hi,
We are currently using a couple Access 2002 databases
that were recently converted from Access 97. The
databases are located on a Windows 2000 server and
accessed from Windows XP Pro workstations. One of our
databases has a loader program that looks in a particular
directory for some .txt files, then uploads them into an
Access table. Since upgrading, a message says that it's
an unrecognized database format. The loader was built to
work with Access 97 and is not working with 2002. The
programmer that created the loader program does not have
the latest versions so he can't help me. Can someone
give me any ideas on how to go about correcting this.
The other option would be to move the database into SQL
Server and use Access as front end, but I would still
need a 'loader' program and would not know how to create
it.
Does anyone have ideas, suggestions? If further
information is needed, I'd be happy to supply it.
Thanks in advance for your expertise.
D.If the loader is itself an Access db, find a consultant that will upgrade it
for you...sounds easier than doing a SQLServer conversion. If you go to SQL
Server, you can likely dump the loader and use SQL's built in DTS utility to
load the txt files
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:a67601c406cd$ee46b490$a501280a@.phx.gbl...
> Hi,
> We are currently using a couple Access 2002 databases
> that were recently converted from Access 97. The
> databases are located on a Windows 2000 server and
> accessed from Windows XP Pro workstations. One of our
> databases has a loader program that looks in a particular
> directory for some .txt files, then uploads them into an
> Access table. Since upgrading, a message says that it's
> an unrecognized database format. The loader was built to
> work with Access 97 and is not working with 2002. The
> programmer that created the loader program does not have
> the latest versions so he can't help me. Can someone
> give me any ideas on how to go about correcting this.
> The other option would be to move the database into SQL
> Server and use Access as front end, but I would still
> need a 'loader' program and would not know how to create
> it.
> Does anyone have ideas, suggestions? If further
> information is needed, I'd be happy to supply it.
> Thanks in advance for your expertise.
> D.

Replacement for Access Forms

Just wondering if anyone has any suggestions for a replacement for Access Forms once I move the tables etc to SQL 2005?

Does SQL 2005 have any form building functionality like Access?

Since you are comfortable with Access forms, you may wish to continue using Access for the client Appication -and use SQL Server for the data.

Check in the Access documentation about Access Data Projects.

Replace password with "******"

How can I replace password in SQL server table column with ******
In Access it can be done by Input mask: password
How can I do it in SQL server?
Thanks for help!
Simon
There is no such thing as an input mask in SQL Server.
What you could do is create a view that converts the password to ********,
and grant access for your users only to the view and not to the underlying
table.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>
|||Access is both a front end and backend tool (questionable about how good
it is, but that's a different discussion).
SQL Server won't visually present data any differently than it is stored
(outside of font choice).
If you want stars to appear, that is something you will have to program
around in your front end application, not the database system.
Simon Worth
Simon Abolnar wrote:
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>
|||I threw together an article on this. No, not just for you; this is a common
enough question. :-)
http://www.aspfaq.com/2536
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>

Replace password with "******"

How can I replace password in SQL server table column with ******
In Access it can be done by Input mask: password
How can I do it in SQL server?
Thanks for help!
SimonThere is no such thing as an input mask in SQL Server.
What you could do is create a view that converts the password to ********,
and grant access for your users only to the view and not to the underlying
table.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>|||Access is both a front end and backend tool (questionable about how good
it is, but that's a different discussion).
SQL Server won't visually present data any differently than it is stored
(outside of font choice).
If you want stars to appear, that is something you will have to program
around in your front end application, not the database system.
Simon Worth
Simon Abolnar wrote:
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>|||I threw together an article on this. No, not just for you; this is a common
enough question. :-)
http://www.aspfaq.com/2536
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>

Replace password with "******"

How can I replace password in SQL server table column with ******
In Access it can be done by Input mask: password
How can I do it in SQL server?
Thanks for help!
SimonThere is no such thing as an input mask in SQL Server.
What you could do is create a view that converts the password to ********,
and grant access for your users only to the view and not to the underlying
table.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>|||Access is both a front end and backend tool (questionable about how good
it is, but that's a different discussion).
SQL Server won't visually present data any differently than it is stored
(outside of font choice).
If you want stars to appear, that is something you will have to program
around in your front end application, not the database system.
Simon Worth
Simon Abolnar wrote:
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>|||I threw together an article on this. No, not just for you; this is a common
enough question. :-)
http://www.aspfaq.com/2536
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>

replace out of range dates

Hi,

I'm migrating data from access 2003 to sql server 2005. An access table contains dates like 5/1/0999. This is a date that is out of the datetime range from sql server.

Now my question is ... How do I replace these dates? I don't find something like IsDate in the expressions?

Ok

I did it with a Derived Column task.
It replaces the date column values who have out of range dates. (It only checks for dates smaller then 1/1/1753)

The expression is like this: [DateToCheck] < (DT_DATE)"1753-1-1" ? (DT_DATE)"1753-1-1" : [DateToCheck]

Replace ODBC Login Prompt in MS Access

I'm controlling the connections to my SQL Server through code. I'm trying to avoid saving the username and password in code however. It's easy for me to display my own login form, accept a username and password, and use those to reconnect my linked tables. However, I'm trying to figure out how to work around the problem of when a user enters an improper username and password. The system would then bring up the standard ODBC username and password dialog. I want to trap this, not display it and return to my form.

I've been told elsewhere that it's possible to tell the ODBC dll to silently fail back to Access or some such thing.

-David

Just for the clarification: how do you use ODBC in MS Access? Do you use ADO or do you do something else?

|||Hi,

you can use the SQL_DRIVER_NOPROMPT which tells the driver not to prompt for a login box. More information about that can be found here:

http://msdn2.microsoft.com/en-us/library/ms131326.aspx

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Replace LinkedServer with Service Broker

Hi,

We currently have an application that primarily sits on SQL server and needs to access some legacy data in a DB2 database. The solution we are using is to use a linked server between the two servers. The problem with this is that complex queries occasionally cause the driver to fetch a complete table and filter that table locally. I wondered whether service broker (which I have never used) could allow me to access the DB2 database using MQSeries and thus speed up the application as our ODBC calls have low priority on our mainframe.

Thanks and sorry if this sounds a stupid use of service broker.

Service Broker supports only its own protocol so it cannot communicate with DB2 or MQSeries. You must have Sql on both ends to use Service Broker.

Rick Negrin

Program Manager

Sql Server Service Brokersql

REPLACE Arguments

Hi,

I'm trying to create a view that shows columns based on user access level. I would like to be able to test for different values without having to add a REPLACE command for each value.

For example,

My UserLevelID can be 1, 2, 3, 4 or 5. 5 is an Officer level so I have the following command:

REPLACE(m.UserLevelID, 5, 'Yes'') AS 'Officer',

The problem is that I want the field to be blank for this column if the value is 1, 2, 3 or 4. I've tried the following, but none work.

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), IN(1,2,3,4), '') AS 'Officer'

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), NOT 5, '') AS 'Officer'

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), < 5, '') AS 'Officer'

Any suggestions? I know I can add a REPLACE function five times, but I have a few more uses for this so I would like to find the easiest method.

Thanks,

Lee

not context or sample DDL or data but how about something like

SELECT

CASE CAST(m.UserLevelID AS varchar(10))

WHEN '5' THEN 'Officer'

ELSE ''

END

|||

If you are going to reuse it, a more compact form is something like:

substring (' Yes', 1+3*(UserLevelId/5), 3)

as in the example:

declare @.test table ( UserLevelId integer )
insert into @.test values (1)
insert into @.test values (2)
insert into @.test values (3)
insert into @.test values (4)
insert into @.test values (5)

select UserLevelId,
substring (' Yes', 1+3*(UserLevelId/5), 3) as Indicator
from @.test

-- UserLevelId Indicator
-- --
-- 1
-- 2
-- 3
-- 4
-- 5 Yes

|||

You could have a small table with the UserLevedID and the replacement values, and then JOIN against that table. If you have several such 'replacements' (or lookups as they are often called), then you could add an additional column to specify the 'group' of replacements.

SET NOCOUNT ON

CREATE TABLE MyTable
( LookupGroup int,
UserLevelID int,
UserLevel varchar(20),
)
GO

A lot better and more robust than attempting to 'hard-code' a potentially changing list of values.

Replace Access with a .NET front end and SQL Backend solution.

My company currently has about 20-25 Ms Access Database that they want to
replace the FE with .net and the BE on SQL.

This will be done using Visual Studio 2005. Once the FE is converted to .net
and the BE is SQL they all will be accessed through our intranet (sharepoint).

I work in Ms Access and intermediate at VBA and just learing SQL through the
ENTERPRISE MANAGER SCREEN.
I am just now looking at what Visual Studio 2005 is, but can some one tell me
how this will all connect?

What is the typical route for this process?

Ms access to SQL - upsizing wizard or SQL importing???

Ms Access FE to .net - summarize how this is done in visual studio (user face
rebuilt) then placed on sharepoint?

Can anyone sum this up?

--
Message posted via http://www.sqlmonster.comAnns via SQLMonster.com (u22580@.uwe) writes:

Quote:

Originally Posted by

I work in Ms Access and intermediate at VBA and just learing SQL through
the ENTERPRISE MANAGER SCREEN.

Enterprise Manager is a tool that exists only in SQL 2000, and I assume
- and hope! - that you will implement your new solution on SQL 2005. The
tool in SQL 2005 is SQL Server Management Studio which replaces both
Enterprise Manager and Query Analyzer. As long as you work with SQL 2000,
QA should be your main tool, because it is from this tool you most easily
can run queries. EM may look a little like Access, so you may feel com-
fortable with that. However, SQL Server is very different from Access,
so best is to use a tool that forces you to unlearn a lot things.

Quote:

Originally Posted by

I am just now looking at what Visual Studio 2005 is, but can some one
tell me how this will all connect?
>
What is the typical route for this process?
>
>
Ms access to SQL - upsizing wizard or SQL importing???

Yes, there is an upsizing wizard. And the word that I have from people
who have worked extensively with both Access and SQL Server is that you
should stay away from it. Access and SQL Server are very different and
build on different mindset. You should more or less redesign your
application for SQL Server and .Net. This may seem like a lot more work
that you had expected, but it is likely to pay off in the long run with
lower maintenance costs, if you do it right from the start.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQL 2005 - SQL Server Managment Studio, thank you, when I said I was
currently learning I am learning unfortunately on 2000 b/c that is the study
material I bought and cannot afford to upgrade right now.

You are right with the SQL 2000 Enterprise edition, it is so user friendly
and that edition is all I know and still learning at best with that.

Yes, my company will be doing this on 2005 but for now, my learning is on
2000.
You see I work in a another department besides IT and between that job/baby
and small buisness, I am trying to get my DBA (2000) certification, I would
like the upgrade (2005) but do not have the funds to upgrade.
I have an opportunity to help with this conversion process and learn in
between, but I am trying to get a better grasp of this before sinking.

Does the 2005 Managment Studio look alot like Enterprise manager?
Answer:

Ms Access to SQL - I am assuming your answer would then be exporting/dts tool
from SQL to pull over all these db's?
Answer:

Erland, tell me once the db's are pull over to SQL, now I have Visual Studio
2005 software open - what steps are taken in this software to rebuilt the
user faces and then how is it then put on SHAREPOINT????
ANSWER:

Erland Sommarskog wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I work in Ms Access and intermediate at VBA and just learing SQL through
>the ENTERPRISE MANAGER SCREEN.

>
>Enterprise Manager is a tool that exists only in SQL 2000, and I assume
>- and hope! - that you will implement your new solution on SQL 2005. The
>tool in SQL 2005 is SQL Server Management Studio which replaces both
>Enterprise Manager and Query Analyzer. As long as you work with SQL 2000,
>QA should be your main tool, because it is from this tool you most easily
>can run queries. EM may look a little like Access, so you may feel com-
>fortable with that. However, SQL Server is very different from Access,
>so best is to use a tool that forces you to unlearn a lot things.
>

Quote:

Originally Posted by

>I am just now looking at what Visual Studio 2005 is, but can some one
>tell me how this will all connect?
>>
>What is the typical route for this process?
>>
>Ms access to SQL - upsizing wizard or SQL importing???

>
>Yes, there is an upsizing wizard. And the word that I have from people
>who have worked extensively with both Access and SQL Server is that you
>should stay away from it. Access and SQL Server are very different and
>build on different mindset. You should more or less redesign your
>application for SQL Server and .Net. This may seem like a lot more work
>that you had expected, but it is likely to pay off in the long run with
>lower maintenance costs, if you do it right from the start.
>

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200607/1|||Anns via SQLMonster.com (u22580@.uwe) writes:

Quote:

Originally Posted by

SQL 2005 - SQL Server Managment Studio, thank you, when I said I was
currently learning I am learning unfortunately on 2000 b/c that is the
study material I bought and cannot afford to upgrade right now.

You can download the Evaluation Edition of SQL 2005 from
http://www.microsoft.com/downloads/...FA7F-C094-49A2-
A050-2D07993566EC&displaylang=en.

Even if your study material only covers SQL 2000, I still think you
should work with SQL 2005.

Quote:

Originally Posted by

You are right with the SQL 2000 Enterprise edition, it is so user friendly
and that edition is all I know and still learning at best with that.

Enterprise Manager != Enterprise Edition. Enterprise Manager is available
in all editions of SQL 2000, except for the desktop version MSDE. The
difference between the editions lies in what features that are available,
and what purpose the edition is licensed for.

Quote:

Originally Posted by

Does the 2005 Managment Studio look alot like Enterprise manager?

It's not completely different, but there are also considerable differences.
In the end, you will only do yourself a disservice by learning a tool
that you will not work with.

But Enterprise Manager and SQL Server Management Studio are just the
tools. These are not what you should focus your learning on. You should
learn how to create databases, tables and indexes, and how to write
queries. You may think that you know SQL from Access, but as I said,
there are considerable differences. In general, SQL Server offers much
more powerful constructs, but there also features from Access that
are completely missing. A classic example is dynamic crosstabs.

Quote:

Originally Posted by

Ms Access to SQL - I am assuming your answer would then be exporting/dts
tool from SQL to pull over all these db's?

Maybe. As I said, I think you should consider a major redesign, at
least in places where you have some quirks in Access, or in places
where SQL Server offers better solutions. Then again, even if you go
for a new schema, it may of course be a good idea bring over the
Access databases to SQL Server, so that you can work with them locally.
And for that end SQL Server Integration Services (which is the successor
to DTS in SQL 2005) may be a good choice. (I've never worked with
neither DTS nor SSIS, so I don't really know.)

Quote:

Originally Posted by

Erland, tell me once the db's are pull over to SQL, now I have Visual
Studio 2005 software open - what steps are taken in this software to
rebuilt the user faces and then how is it then put on
SHAREPOINT????

Since you are migrating from Access to .Net, I would assume that you
more or less rewrite the front-end code entirely, but you should ask in a
..Net forum about that.

Sharepoint? I know there is a product with that name, but I have very
little idea what it's good for. So I cannot answer that question.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||A great walk thru / how to on the MS Access to SQL Server 2005 was
blogged. I tried it and made my life much easier.

http://cfpayne.wordpress.com/2006/0...ql-server-2005/
Precia

Anns via SQLMonster.com wrote:

Quote:

Originally Posted by

My company currently has about 20-25 Ms Access Database that they want to
replace the FE with .net and the BE on SQL.
>
>
This will be done using Visual Studio 2005. Once the FE is converted to .net
and the BE is SQL they all will be accessed through our intranet (sharepoint).
>
>
I work in Ms Access and intermediate at VBA and just learing SQL through the
ENTERPRISE MANAGER SCREEN.
I am just now looking at what Visual Studio 2005 is, but can some one tell me
how this will all connect?
>
What is the typical route for this process?
>
>
Ms access to SQL - upsizing wizard or SQL importing???
>
>
>
Ms Access FE to .net - summarize how this is done in visual studio (user face
rebuilt) then placed on sharepoint?
>
>
Can anyone sum this up?
>
--
Message posted via http://www.sqlmonster.com

Repl failure on <> in data field

We added data to a table with ‘<’ and ‘>’ symbols. The replication failed on
SQLServer with the error “Syntax error or access violation”. We replaced the
'<' and '>' with '(' and ')', which fixed the replication error. Can you not
use brackets in a table that is replicated?
This applies to the following characters: * ,< , > , ? , / , \ , % , | , : ,
" and '
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Is this a known bug?
Is there any fix available?
"Paul Ibison" wrote:

> This applies to the following characters: * ,< , > , ? , / , \ , % , | , : ,
> " and '
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||You could describe it as a limitation, a feature or a bug
There's no fix - apart from removing the relevant characters (see
http://support.microsoft.com/default.aspx?scid=kb;[LN];829746)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry I did not expained well.
The table name doesn't contain special character but a space. (Space is not
in the list of special characters). For example [Title Types]
Replication works fine. Adding new data, updating data gets replicated
successfully. Now, when you add data with any special characters in one of
the nvarchar data fields of replicated table ( i.e. [Title Types]).
Replication fails.
The microsoft article addressing the issue of article name with special
characters but I have issue with data when table name contains space(which is
not special character listed).
I can not stop users to not enter special characters in data. So I think I
have only 2 options:
1. Rename table to TitleTypes (i.e remove space), All special characters
will work in data. (Lot of work as table is getting referenced from many
places)
2. add instead of trigger on table to get rid of special characters. (Extra
load on DB for insert and update due to trigger).
Further suggestions will be helpful.
Thanks
"Paul Ibison" wrote:

> You could describe it as a limitation, a feature or a bug
> There's no fix - apart from removing the relevant characters (see
> http://support.microsoft.com/default.aspx?scid=kb;[LN];829746)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

repeating identity

When using Access - you had to be careful about the autoincrement feature. If you delete a record from a table ( autoincrement id=1000) -then compact/repair - then add a new record to that table - the autoincrement field will say 1000 - if that autoincrement value was used to uniquely identify something - it is no longer unique. This is all background for my question...

Does SQL 2000 do this also ? I must have read somewhere that it doesn't - since I have code that moves records around ( delete from one table - insert into other), but the other night, I was awakened by the thought that SQL2000 does the same as Access - i.e. repeating identity after compact/repair

Do I need to worry ?The only thing you need to worry about is if you happen to use theSET IDENTITY_INSERT ON command when inserting records.

This command will allow you to specify a value for the identity column when inserting a record instead of allowing SQL to assign one. And SQL won't know that your assigned numbers are already used and could attempt to reuse them later. This would only happen if you were sloppy in your assignment -- and it is not likely you would need to ever use the SET IDENTITY_INSERT anyway.

FWIW,
Terri|||thank you everyone : ) After re-reading my original post - I thought maybe I should reword it , just to make sure.

I have a table for service calls that has the identity field used for the service call number.
When the call is closed, I delete it from the open service call table - and insert it into the closed calls table.

If the call was number 1000 - open service calls highest identity will be 999.

when the next new call is created - will SQL assign 1001 or 1000 ( access would set new rec to 1000 - if a compact was done )

thanks again|||I'm not 100% clear...but if I am following you...

If your service call was 1000, and then was closed, and you then deleted that service call record from your table, the next identity assigned would be 1001. SQL Server will not reuse 1000.

With 2 exceptions
-- you explicitly insert a record with the identity field set to 1000
-- you reset the identity seed to 1000

Under normal circumstances without you doing anything funky like that, you are fine. SQL Server does not behave like Access.

Terri|||thanks again

RenderStream fun!

Hi Everyone

I have a tricky problem with Reporting Services 2000. We can't use URLs
to access reports for security reasons, so we have to use the web
service. This means I have to write a viewer control (I can't wait for
SQL 2005 either!).

The problem I have is with rendering images. I can either:

i) render report images by using RenderStream(...) to save them to
the web app file system OR
ii) render them using a different web page

I have successfully implemented both solutions.

The problem with the file system solution is that it is not scalable
for a web farm situation (unless I can do something clever with shared
network directories in a web application). This is because on web
server may get the request for the main report page and other servers
may receive the requests for images from the file system...

The problem with the separate web page solution is that it means the
ReportingService object has to be saved into the session so that the
same object is used to Render(...) the report and RenderStream(...) the
images. In our application, use of Session data is strongly discouraged
because of scalability issues. We have a method of passing session data
using a bespoke system, but this canno be used as the ReportingService
object is no serializable!

Does anyone have any advice on how I could approach this problem so
that I get a scalable solution that does not require the use of
Sessions to save the ReportingService object?

Many thanks,

Kulgan.You might get a better response in
microsoft.public.sqlserver.reportingsvcs

Simon|||Many thanks - I will post there!

Rendering reports on PDA, mobile devices

Hi,

I am using URL access method to render the reports in a browser. But the same URL does not work with PDA devices. Report is not displayed on the PDA device. Any idea how to render a report on PDA device using the URL access.

Thanks.

Kavita

You could try to add the following device info to your existing URL (pointing to http://servername/reportserver/... to render to HTML 3.2 format:
... &rc:Format=HTML3.2

-- Robert