Replacing Nulls

I have a query that has serveral Left Joins. Obviously when there are no
results in the right tables a value of NULL is returned. When creating an
MS Reporting Services report, the tables seem to ignore the NULL values
(because they aren't recognized as real values I assume). How do I alter my
query to replace a NULL value with an actually value, such as 999 so I can
count the "NULL" results?
I appreciate any help.
Thankstry ISNULL function
ISNULL(<fieldname>, 999) AS SomeThing
"Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>I have a query that has serveral Left Joins. Obviously when there are no
>results in the right tables a value of NULL is returned. When creating an
>MS Reporting Services report, the tables seem to ignore the NULL values
>(because they aren't recognized as real values I assume). How do I alter
>my query to replace a NULL value with an actually value, such as 999 so I
>can count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Thanks for the reply SharkSpeed. That makes sense, but where would I put
it?
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:eqqHn8e9FHA.1020@.TK2MSFTNGP15.phx.gbl...
> try ISNULL function
> ISNULL(<fieldname>, 999) AS SomeThing
>
> "Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
> yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>|||Hi Amon
Have you tried the ISNULL([ColumnName], [NewValue]) function?
Lucas
"Amon Borland" wrote:

> I have a query that has serveral Left Joins. Obviously when there are no
> results in the right tables a value of NULL is returned. When creating an
> MS Reporting Services report, the tables seem to ignore the NULL values
> (because they aren't recognized as real values I assume). How do I alter
my
> query to replace a NULL value with an actually value, such as 999 so I can
> count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Lucas, where would I use this at. In the Select or after the table in the
join?
Thanks
"Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote in
message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
> Hi Amon
> Have you tried the ISNULL([ColumnName], [NewValue]) function?
> Lucas
> "Amon Borland" wrote:
>|||in the Select I assume..
SELECT a, b, n FROM table LEFT JOIN SELECT x, y, z FROM table2 ON ...
becomes
SELECT a, b, ISNULL(n, 999) AS n FROM table LEFT JOIN SELECT x, y, ISNULL(z,
999) FROM table2 ...
"Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
news:eZMs3Mf9FHA.220@.TK2MSFTNGP14.phx.gbl...
> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
> in message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>|||You can use it on the Select part of your SQL Statement. For example:
SELECT [ColumnName1], ISNULL([ColumnName2], [NewValue])
FROM [TableName]
Lucas
"Amon Borland" wrote:

> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
in
> message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>
>

Replacing NULL value in multiple columns in a table

Hi,

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.

Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL

But because there are lots of columns this is pretty much work, also
there are multiple tables.

Is there an easy way to replace all NULL values in all columns in a
table?

Thanks in Advance
BobBF (bob@.faessen.net) writes:

Quote:

Originally Posted by

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.
>
Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL
>
But because there are lots of columns this is pretty much work, also
there are multiple tables.
>
Is there an easy way to replace all NULL values in all columns in a
table?

First of all, that operation would only be possible with columns
that hold character data. For numeric and datetime columns there
is rarely any good replacement for NULL values. So, unless, your
database only has nullable character columns, you need to fix the
application to handle NULL values anyway.

No, there is no direct function for setting many columns to NULL. You
need to have an UPDATE statement for each table, and one that lists
all columns that should be set to NULL. The good news is that you
can generate the statements:

SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
c.name + ' IS NULL'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')

--
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|||Great,

Thanks a lot, with the query I can create the new script much and much
easier.

I replaced the o.name with the tables which I new they had the problem
and now I have have all columns to replace the values.

Thanks a lot.

Regards, Bob

Erland Sommarskog schreef:

Quote:

Originally Posted by

BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.

Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL

But because there are lots of columns this is pretty much work, also
there are multiple tables.

Is there an easy way to replace all NULL values in all columns in a
table?

>
First of all, that operation would only be possible with columns
that hold character data. For numeric and datetime columns there
is rarely any good replacement for NULL values. So, unless, your
database only has nullable character columns, you need to fix the
application to handle NULL values anyway.
>
No, there is no direct function for setting many columns to NULL. You
need to have an UPDATE statement for each table, and one that lists
all columns that should be set to NULL. The good news is that you
can generate the statements:
>
SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
c.name + ' IS NULL'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')
>
>
>
--
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

Replacing existing records in a table

I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?updates?

The message I have entered is too short.|||can UPDATE do multiple column update?

actually the purpose is for rerun of query. If i were to use UPDATE, i need to check if the record exist first. That's not efficient. I am looking for direct replacement.|||can UPDATE do multiple column update?

yes

actually the purpose is for rerun of query. If i were to use UPDATE, i need to check if the record exist first.

maybe. maybe not.

That's not efficient. I am looking for direct replacement.

really? that is all in how you skin the cat.

why don't you read Brett Kaiser's sticky at the top of this board, follow the directions, and save everyone a lot of time.

Replacing Database Template values

I have been working on creating templates to have for when creating
databases, tables, views, etc.
When I do a replace template values on this particular template all values
get replaced except for the drop database line. The strange thing is if I
replace the values and then do replace values again it works?
Any suggestions on why this is happening and what I can do to resolve it.
/ ****************************************
********************************/
/* Name : <Database_Name ,VCHAR,'SP_Create_Database'>
*/
/* Version : <Version ,VCHAR,'1.0'>
*/
/* Author : <Author ,VCHAR,'Joseph Pruiett'>
*/
/* Initials : <Author_INT ,VCHAR,'JEP'>
*/
/* Date : <Date , date ,'04/29/2005'>
*/
/* Description : <Desc , CHAR,'TEST'>
*/
/ ****************************************
********************************/
/* <Date , date ,'04/29/2005'> : <Version ,VCHAR,'1.0'>: <Author
,VCHAR,'Joseph Pruiett'>: Description <Desc , CHAR,'TEST'> */
/ ****************************************
********************************/
/ ****************************************
********************************/
/* @.d_name -- Change data value for this to the name of Database */
/ ****************************************
********************************/
--Check to see if database exist
IF EXISTS (SELECT name
FROM master..sysdatabases
WHERE name = N'<@.d_name, sysname, new_db>')
BEGIN
DROP DATABASE <@.d_name, sysname, new_db>
END
GO
--Create database
CREATE DATABASE <@.d_name, sysname, new_db>
ON PRIMARY
(
NAME = <logical_file_name_1, , new_db_file_1>,
FILENAME = N'<os_file_name_1, , c:\program files\microsoft sql
server\mssql\data\new_db.mdf>',
SIZE = 1MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%
),
(
NAME = <logical_file_name_2, , new_db_file_2>,
FILENAME = N'<os_file_name_2, , c:\program files\microsoft sql
server\mssql\data\new_db.ndf>',
SIZE = 1MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%
),
LOG ON
(
NAME = <logical_log_file_name_1, , new_db_log_file_1>,
FILENAME = N'<os_log_file_name_1, , c:\program files\microsoft sql
server\mssql\data\new_db_log_1.ldf>',
SIZE = 1MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%
)
GOYou can try to use a decent text editor to see if there are any hidden
characters. Textpad is a good one to try.
-oj
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:0FB8C772-DA23-4856-BD31-619C118D8664@.microsoft.com...
>I have been working on creating templates to have for when creating
> databases, tables, views, etc.
> When I do a replace template values on this particular template all values
> get replaced except for the drop database line. The strange thing is if I
> replace the values and then do replace values again it works?
> Any suggestions on why this is happening and what I can do to resolve it.
> / ****************************************
********************************/
> /* Name : <Database_Name ,VCHAR,'SP_Create_Database'>
> */
> /* Version : <Version ,VCHAR,'1.0'>
> */
> /* Author : <Author ,VCHAR,'Joseph Pruiett'>
> */
> /* Initials : <Author_INT ,VCHAR,'JEP'>
> */
> /* Date : <Date , date ,'04/29/2005'>
> */
> /* Description : <Desc , CHAR,'TEST'>
> */
> / ****************************************
********************************/
> /* <Date , date ,'04/29/2005'> : <Version ,VCHAR,'1.0'>: <Author
> ,VCHAR,'Joseph Pruiett'>: Description <Desc , CHAR,'TEST'> */
> / ****************************************
********************************/
> / ****************************************
********************************/
> /* @.d_name -- Change data value for this to the name of Database */
> / ****************************************
********************************/
> --Check to see if database exist
> IF EXISTS (SELECT name
> FROM master..sysdatabases
> WHERE name = N'<@.d_name, sysname, new_db>')
> BEGIN
> DROP DATABASE <@.d_name, sysname, new_db>
> END
> GO
> --Create database
> CREATE DATABASE <@.d_name, sysname, new_db>
> ON PRIMARY
> (
> NAME = <logical_file_name_1, , new_db_file_1>,
> FILENAME = N'<os_file_name_1, , c:\program files\microsoft sql
> server\mssql\data\new_db.mdf>',
> SIZE = 1MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%
> ),
> (
> NAME = <logical_file_name_2, , new_db_file_2>,
> FILENAME = N'<os_file_name_2, , c:\program files\microsoft sql
> server\mssql\data\new_db.ndf>',
> SIZE = 1MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%
> ),
> LOG ON
> (
> NAME = <logical_log_file_name_1, , new_db_log_file_1>,
> FILENAME = N'<os_log_file_name_1, , c:\program files\microsoft sql
> server\mssql\data\new_db_log_1.ldf>',
> SIZE = 1MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%
> )
> GO
>

Replacing data from another database

Hello all,
I have a seems to be simple task but haven't figured out the best way to
approach it.
I have 2 address tables: Address and CleanedAddresses
Address has all the address fields with additional fields such as type,
description, etc. CleanedAddresses has only address info, they both have
the same ID. On Address its an identity on CleanedAddresses it's an INT.
I want to cycle through Address and update the addresses on it with the
addresses on CleanedAddresses - simple enough huh. I tried writing an ASP
page to do so and it never finished, I think due to connectivity issues.
There are about 12,000 addresses. Do you have any suggestions on how I
should do this task? Thanks ahead of time.
ShawnShawn,
How about this?
UPDATE Address
SET AddressLine1 = CleanedAddresses.AddressLine1
-- More Columns as needed
FROM CleanedAddresses
WHERE Address.ID = CleanedAddresses.ID
RLF
<programmingcodeATjards.com> wrote in message
news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I have a seems to be simple task but haven't figured out the best way to
> approach it.
> I have 2 address tables: Address and CleanedAddresses
> Address has all the address fields with additional fields such as type,
> description, etc. CleanedAddresses has only address info, they both have
> the same ID. On Address its an identity on CleanedAddresses it's an INT.
> I want to cycle through Address and update the addresses on it with the
> addresses on CleanedAddresses - simple enough huh. I tried writing an ASP
> page to do so and it never finished, I think due to connectivity issues.
> There are about 12,000 addresses. Do you have any suggestions on how I
> should do this task? Thanks ahead of time.
> Shawn
>|||Give unto Caesar that which is Caesar's.
Write it in T-SQL and run it through SQLagent.
<programmingcodeATjards.com> wrote in message
news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I have a seems to be simple task but haven't figured out the best way to
> approach it.
> I have 2 address tables: Address and CleanedAddresses
> Address has all the address fields with additional fields such as type,
> description, etc. CleanedAddresses has only address info, they both have
> the same ID. On Address its an identity on CleanedAddresses it's an INT.
> I want to cycle through Address and update the addresses on it with the
> addresses on CleanedAddresses - simple enough huh. I tried writing an ASP
> page to do so and it never finished, I think due to connectivity issues.
> There are about 12,000 addresses. Do you have any suggestions on how I
> should do this task? Thanks ahead of time.
> Shawn
>|||What do you mean? Take it through SQLAgent to do what? How about looping
through every record?
"Jay" <spam@.nospam.org> wrote in message
news:OQ%23wtN5%23HHA.4956@.TK2MSFTNGP06.phx.gbl...
> Give unto Caesar that which is Caesar's.
> Write it in T-SQL and run it through SQLagent.
>
> <programmingcodeATjards.com> wrote in message
> news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
>> Hello all,
>> I have a seems to be simple task but haven't figured out the best way to
>> approach it.
>> I have 2 address tables: Address and CleanedAddresses
>> Address has all the address fields with additional fields such as type,
>> description, etc. CleanedAddresses has only address info, they both have
>> the same ID. On Address its an identity on CleanedAddresses it's an INT.
>> I want to cycle through Address and update the addresses on it with the
>> addresses on CleanedAddresses - simple enough huh. I tried writing an
>> ASP page to do so and it never finished, I think due to connectivity
>> issues. There are about 12,000 addresses. Do you have any suggestions on
>> how I should do this task? Thanks ahead of time.
>> Shawn
>|||Thanks,
How do you loop through all the address records? From the beginning of the
file to the end. I didn't know you could access the other table by using
tablename.fieldname, thanks for the insight.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%236Nf$K5%23HHA.5464@.TK2MSFTNGP02.phx.gbl...
> Shawn,
> How about this?
> UPDATE Address
> SET AddressLine1 = CleanedAddresses.AddressLine1
> -- More Columns as needed
> FROM CleanedAddresses
> WHERE Address.ID = CleanedAddresses.ID
> RLF
> <programmingcodeATjards.com> wrote in message
> news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
>> Hello all,
>> I have a seems to be simple task but haven't figured out the best way to
>> approach it.
>> I have 2 address tables: Address and CleanedAddresses
>> Address has all the address fields with additional fields such as type,
>> description, etc. CleanedAddresses has only address info, they both have
>> the same ID. On Address its an identity on CleanedAddresses it's an INT.
>> I want to cycle through Address and update the addresses on it with the
>> addresses on CleanedAddresses - simple enough huh. I tried writing an
>> ASP page to do so and it never finished, I think due to connectivity
>> issues. There are about 12,000 addresses. Do you have any suggestions on
>> how I should do this task? Thanks ahead of time.
>> Shawn
>|||Shawn,
This does not loop through at all. Instead of row-at-a-time processing it
processes the entire set of qualifying rows.
It joins the Address and CleanedAddresses tables for the row where the ID
matches, which you defined as the case. Therefore, this one statement
updates every Address row that has a matching CleanedAddresses row.
RLF
<programmingcodeATjards.com> wrote in message
news:%238RaHf5%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> How do you loop through all the address records? From the beginning of
> the file to the end. I didn't know you could access the other table by
> using tablename.fieldname, thanks for the insight.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%236Nf$K5%23HHA.5464@.TK2MSFTNGP02.phx.gbl...
>> Shawn,
>> How about this?
>> UPDATE Address
>> SET AddressLine1 = CleanedAddresses.AddressLine1
>> -- More Columns as needed
>> FROM CleanedAddresses
>> WHERE Address.ID = CleanedAddresses.ID
>> RLF
>> <programmingcodeATjards.com> wrote in message
>> news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
>> Hello all,
>> I have a seems to be simple task but haven't figured out the best way
>> to approach it.
>> I have 2 address tables: Address and CleanedAddresses
>> Address has all the address fields with additional fields such as type,
>> description, etc. CleanedAddresses has only address info, they both
>> have the same ID. On Address its an identity on CleanedAddresses it's
>> an INT.
>> I want to cycle through Address and update the addresses on it with the
>> addresses on CleanedAddresses - simple enough huh. I tried writing an
>> ASP page to do so and it never finished, I think due to connectivity
>> issues. There are about 12,000 addresses. Do you have any suggestions
>> on how I should do this task? Thanks ahead of time.
>> Shawn
>>
>|||easiest way to do this would just be with a simple T-sql statement...
update address
set address.field1 = cleanedaddress.field1,
address.field2 = cleanedaddress.field2, etc, etc
from address
inner join cleanedaddress
on address.id = cleanedaddress.id
is there some reason that you couldn't do it like that?
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
<programmingcodeATjards.com> wrote in message
news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I have a seems to be simple task but haven't figured out the best way to
> approach it.
> I have 2 address tables: Address and CleanedAddresses
> Address has all the address fields with additional fields such as type,
> description, etc. CleanedAddresses has only address info, they both have
> the same ID. On Address its an identity on CleanedAddresses it's an INT.
> I want to cycle through Address and update the addresses on it with the
> addresses on CleanedAddresses - simple enough huh. I tried writing an ASP
> page to do so and it never finished, I think due to connectivity issues.
> There are about 12,000 addresses. Do you have any suggestions on how I
> should do this task? Thanks ahead of time.
> Shawn
>|||What I mean is: you are attempting to do database maintenance in an ASP
page. This is a bad idea. The ASP page will have a heavier footprint on your
overall infrastructure and your code will be limited.
Since what you need is SQL, just write a procedure to do what you want, thus
keeping everything inside the database, where it will work betrter anyway.
Then, if you want to schedule the task to run repeatably, schedule the job
in SQLagent.
<programmingcodeATjards.com> wrote in message
news:usygie5%23HHA.700@.TK2MSFTNGP05.phx.gbl...
> What do you mean? Take it through SQLAgent to do what? How about
> looping through every record?
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OQ%23wtN5%23HHA.4956@.TK2MSFTNGP06.phx.gbl...
>> Give unto Caesar that which is Caesar's.
>> Write it in T-SQL and run it through SQLagent.
>>
>> <programmingcodeATjards.com> wrote in message
>> news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
>> Hello all,
>> I have a seems to be simple task but haven't figured out the best way
>> to approach it.
>> I have 2 address tables: Address and CleanedAddresses
>> Address has all the address fields with additional fields such as type,
>> description, etc. CleanedAddresses has only address info, they both
>> have the same ID. On Address its an identity on CleanedAddresses it's
>> an INT.
>> I want to cycle through Address and update the addresses on it with the
>> addresses on CleanedAddresses - simple enough huh. I tried writing an
>> ASP page to do so and it never finished, I think due to connectivity
>> issues. There are about 12,000 addresses. Do you have any suggestions
>> on how I should do this task? Thanks ahead of time.
>> Shawn
>>
>

Replacing columnn name programmatically

Hi,

The following script does not return any resultset against a test db
while I know for a fact tables with letter "aaa" has columns that
contains "ccc".
What's wrong? the the inner cursor?

Thanks.

-- get all tbls with letter aaa
declare @.tbl varchar(8000)
declare tblCursor cursor for
SELECT name
FROM sysobjects
WHERE xtype = 'U'
AND name LIKE '%aaa%'

open tblCursor
fetch next from tblCursor
into @.tbl

while (@.@.fetch_status = 0)
begin

-- get all columns with letter ccc and replace it with nothing /
remove it
declare @.tbuffer varchar(4000)
declare @.cbuffer varchar(8000)

declare abnormal_cols cursor for
SELECT o.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
and o.id = object_id('+@.tbl')
-- ORDER BY c.name

open abnormal_cols
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer

while (@.@.fetch_status = 0)
begin
-- EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
'COLUMN';
-- test
print @.tbuffer + ', ' + @.cbuffer;
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer
end

close abnormal_cols
deallocate abnormal_cols;

fetch next from tblCursor
into @.tbl

end
close tblCursor
deallocate tblCursor;Hi

I can't see why there are two cursors here, try:

SELECT o.name, Replace(c.name,'ccc','') as NewName, c.name as OldName
FROM sysobjects o JOIN syscolumns c ON o.id = c.id
JOIN syscolumns a ON o.id = a.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
AND a.name LIKE '%aaa%'

John

"Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
news:fc254714.0310211451.2f59f9c4@.posting.google.c om...
> Hi,
> The following script does not return any resultset against a test db
> while I know for a fact tables with letter "aaa" has columns that
> contains "ccc".
> What's wrong? the the inner cursor?
> Thanks.
>
> -- get all tbls with letter aaa
> declare @.tbl varchar(8000)
> declare tblCursor cursor for
> SELECT name
> FROM sysobjects
> WHERE xtype = 'U'
> AND name LIKE '%aaa%'
> open tblCursor
> fetch next from tblCursor
> into @.tbl
> while (@.@.fetch_status = 0)
> begin
> -- get all columns with letter ccc and replace it with nothing /
> remove it
> declare @.tbuffer varchar(4000)
> declare @.cbuffer varchar(8000)
> declare abnormal_cols cursor for
> SELECT o.name, c.name
> FROM sysobjects o
> JOIN syscolumns c ON o.id = c.id
> WHERE o.xtype = 'U'
> AND c.name LIKE '%ccc%'
> and o.id = object_id('+@.tbl')
> -- ORDER BY c.name
> open abnormal_cols
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> while (@.@.fetch_status = 0)
> begin
> -- EXEC sp_rename
'+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
> 'COLUMN';
> -- test
> print @.tbuffer + ', ' + @.cbuffer;
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> end
> close abnormal_cols
> deallocate abnormal_cols;
> fetch next from tblCursor
> into @.tbl
> end
> close tblCursor
> deallocate tblCursor;

Replacement for my LIKE Clause

Dear all,

I need some help from all my Transact SQL Guru friends out there..

Here is the scenario in its most simplified form.. ..

I have two tables.. A(Lookup table) and B(Transaction Table)

TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...

TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)

I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...

Therefore, this is what I had wrote initially..

update B
set B.EmployeeLocationID =A.EmployeeLocationID

Quote:

Originally Posted by

>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +

'%'
where B.EmployeeLocationID is null

This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...

For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...

Therefore this is the expected match for my examples based on
requirement..

"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A

Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..

Looking forward to hearing from someone ASAP.. Please help..

Best regards,

VM...Interesting. Maybe this will give you and angle to try.

UPDATE B
SET EmployeeLocationID =
(SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL

Roy Harvey
Beacon Falls, CT

On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:

Quote:

Originally Posted by

>Dear all,
>
>I need some help from all my Transact SQL Guru friends out there..
>
>Here is the scenario in its most simplified form.. ..
>
>I have two tables.. A(Lookup table) and B(Transaction Table)
>
>TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>
>TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
>first time)
EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>
>Therefore, this is what I had wrote initially..
>
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID

Quote:

Originally Posted by

>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +

>'%'
>where B.EmployeeLocationID is null
>
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>
>Therefore this is the expected match for my examples based on
>requirement..
>
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>
>Looking forward to hearing from someone ASAP.. Please help..
>
>Best regards,
>
>VM...

|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.

SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?

Quote:

Originally Posted by

Quote:

Originally Posted by

>A(Lookup table) and B(Transaction Table) <<

Why did you avoid clear names?

CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??

-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.

Can two prefixes belong to the same SAN? No specs given.

Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?

CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);

The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I hope you get where I am leading this to, from my examples.. <<

No. Clear specs would have been nice, along with real DDL.

Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:

INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,

(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),

Etc.);

You will need error handling code for prefixes that do not match.|||Roy,

Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..

Celko,

Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...

I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...

I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...

Thanks once again...

VM

--CELKO-- wrote:

Quote:

Originally Posted by

Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
>
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>
>

Quote:

Originally Posted by

Quote:

Originally Posted by

A(Lookup table) and B(Transaction Table) <<

>
Why did you avoid clear names?
>
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??
>
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
>
Can two prefixes belong to the same SAN? No specs given.
>
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
>
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
>
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

I hope you get where I am leading this to, from my examples.. <<

>
No. Clear specs would have been nice, along with real DDL.
>
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
>
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
>
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
>
Etc.);
>
You will need error handling code for prefixes that do not match.

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 View with Join or SubQuery

My application uses a View stored in a database. I have queries that join
this view with other tables. I'd like to eliminate the view. For example, if
the view was defined by:
CREATE VIEW dbo.AcctBalance
AS
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
My VB code (using ADO) creates this T-SQL query:
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
All my attempts to replace the View have failed so far. Can someone provide
guidance?
Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
RichardRichard
Why do you want to eliminate the VIEW? Any reasons?
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN
(
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
) AS AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
> My application uses a View stored in a database. I have queries that join
> this view with other tables. I'd like to eliminate the view. For example,
if
> the view was defined by:
> CREATE VIEW dbo.AcctBalance
> AS
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> My VB code (using ADO) creates this T-SQL query:
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
> All my attempts to replace the View have failed so far. Can someone
provide
> guidance?
> Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
> --
> Richard
>|||The database does not belong to me, but to the customer. I'm trying to get
my code out of the customer's database. Also, if I need to revise the View,
I must code a utility to modify the View in the customer's database. Any
other change can be implemented by building a new dll. I understand that
it's partly a philosophical thing.
Your post indicates that I can join a table that is created in the
parenthesis. I like that idea and will try it. Thanks a lot.
Richard
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Richard
> Why do you want to eliminate the VIEW? Any reasons?
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN
> (
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> ) AS AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
>
> "Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
> message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
join
example,
> if
> provide
dbo.Sales.
>|||Hi,
Just to confirm, your code works perfectly for me. Thanks again.
Richard
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:O8NbIERFFHA.1392@.tk2msftngp13.phx.gbl...
> The database does not belong to me, but to the customer. I'm trying to get
> my code out of the customer's database. Also, if I need to revise the
View,
> I must code a utility to modify the View in the customer's database. Any
> other change can be implemented by building a new dll. I understand that
> it's partly a philosophical thing.
> Your post indicates that I can join a table that is created in the
> parenthesis. I like that idea and will try it. Thanks a lot.
> Richard
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> join
> example,
> dbo.Sales.
>

Replace temp table with inline table-value function

PREFACE: We are getting rid of the temporary tables so I don't need to be
convinced not to use them.
In our current system we have a pattern where a temporary table is created
in one or more "calling" procedures and populated with selected keys of a
table and in the "called" procedure, those keys (from the temporary table)
are joined to a set of tables to produce a detail result set. Multiple
"calling" procedures exist that populate the temp key table based on various
criteria, but they all call the same "called" procedure which centralizes
the logic for pulling together the details.
This method causes concurrency problems because the "called" procedure is
re-compiled every time because it references a temporary table defined in
another procedure.
The method I have come up with to get rid of the temporary tables but to
still centralize and re-use the detail logic is as follows:
I have created an inline table-value function that replaces the common
"called" procedure in the above scenario. Now in the "calling" procedures,
instead of populating a temp table with keys and calling the "called"
procedure, they simply join the criteria with the user defined function,
selecting the needed fields from the results. Looking at the query plan,
this seems very optimal because it appears that the whole query (the key
criteria and the user-defined function statements) are merged together and
an execution plan is generated for them as a whole (instead of as 2 discrete
statements), giving me the best of both worlds: centralized, re-usable
logic, and a good execution plan.
My question is: Is there anything inherently non-scalable about using SQL
Server 2000's inline table-value function that will burn me under heavy
load?
Thanks,
Mike Jansen
(Abbreviated DDL follows)
OLD WAY
---
CREATE TABLE dbo.Entities
(
entity_pk int IDENTITY(100, 1) NOT NULL CONSTRAINT pk_Entities PRIMARY
KEY,
blah
blah
)
GO
CREATE PROCEDURE dbo.spGetEntityDetails
AS
SELECT
E.entity_pk, E.blah, E.blah, D.blah, D.blah
FROM
#EntityList E
INNER JOIN EntityDetails D ON E.entity_pk = D.entity_pk
GO
CREATE PROCEDURE dbo.spSeeOneGroupOfEntities
AS
CREATE TABLE #EntityList (entity_pk int NOT NULL PRIMARY KEY)
INSERT #EntityList (entity_pk)
SELECT E.entity_pk
FROM Entities E INNER JOIN .....
WHERE E.blah = 'one kind'
EXEC dbo.spGetEntityDetails
DROP TABLE #EntityList
GO
CREATE PROCEDURE dbo.spSeeAnotherGroupOfEntities
AS
CREATE TABLE #EntityList (entity_pk int NOT NULL PRIMARY KEY)
INSERT #EntityList (entity_pk)
SELECT E.entity_pk
FROM Entities E INNER JOIN .....
WHERE E.blah = 'another kind' AND ...
EXEC dbo.spGetEntityDetails
DROP TABLE #EntityList
GO
NEW WAY
---
CREATE FUNCTION dbo.fnGetEntityDetails()
RETURNS TABLE
RETURN
(
SELECT D.entity_pk, D.blah, D.blah, D2.blah, D2.blah
FROM EntityDetails D INNER JOIN EntityDetails2 D2 ON ....
)
GO
CREATE PROCEDURE dbo.spSeeOneGroupOfEntities
AS
SELECT
E.entity_pk, D.blah, D.blah
FROM
Entities E INNER JOIN dbo.fnGetEntityDetails() D ON E.entity_pk =
D.entity_pk
WHERE
E.blah = 'one criteria' AND ...
GO
CREATE PROCEDURE dbo.spSeeAnotherGroupOfEntities
AS
SELECT
E.entity_pk, D.blah, D.blah
FROM
Entities E INNER JOIN dbo.fnGetEntityDetails() D ON E.entity_pk =
D.entity_pk
WHERE
E.blah = 'another criteria' AND ...Noting to self that my descriptions can be a little long (and hence take too
long to read...), here's my question succinctly:
Is there anything inherently non-scalable about using SQL Server 2000's
inline table-value function that will burn me under heavy
load?
Thanks,
Mike|||> Is there anything inherently non-scalable about using SQL Server 2000's inline table-valu
e
> function that will burn me under heavy
> load?
Not that I know of. I haven been told that they are optimized and used in th
e same way as views (and
they were called parametized views during early stages of development of SQL
Server 2000). I can't
offer proof or similar, I'm afraid, but that are my experiences and what I h
ave been told.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O19%23vR5kFHA.1044@.tk2msftngp13.phx.gbl...
> Noting to self that my descriptions can be a little long (and hence take t
oo long to read...),
> here's my question succinctly:
> Is there anything inherently non-scalable about using SQL Server 2000's in
line table-value
> function that will burn me under heavy
> load?
> Thanks,
> Mike
>|||Mike Jansen wrote:
> Noting to self that my descriptions can be a little long (and hence take t
oo
> long to read...), here's my question succinctly:
> Is there anything inherently non-scalable about using SQL Server 2000's
> inline table-value function that will burn me under heavy
> load?
I agree with Tibor.
A couple of years ago, I did a good bit of tuning work on a system that
made heavy use of udf's. My experience was positive with inline
table-valued functions. The plans produced looked to me like the
optimizer treats them as it would a view or a derived table. It can
"see inside" them and optimize to the base table level. I think I saw
this behavior even when nesting functions.
Multistatement table-valued functions, on the other hand, seemed to be
a black box to the optimizer. That makes sense. How could he (the
optimizer) evaluate the logic that could be inside a multi-statement
function. Instead, it uses a table scan of whatever table variable is
returned.
Best of luck
Payson

> Thanks,
> Mike

replace table with different datasource in dsv

HI all,

I've used test db which is the small portion of production db and now I'm trying to replace all tables with production db in dsv. But it gives an OLE DB error now. (but I checked it and nothing wrong with it.)

What should I check when replacing table with differenct db?

Do I check the data source references in ds?

Please give me some comments.

Thanks in advance.

If the table names and schema names (on which you built dimensions, cubes and partitions) are the same in the test and production databases, the only thing you need to change is the connection string of the DataSource object, to use production database as the default catalog.

Adrian Dumitrascu

Replace OpenDatabase with?

I have a MDB that consists largely of a number of linked tables to
external data files, which contain various activity data. I then open
this db from my ADP project using OpenDatabase and OpenRecordset,
which I believe are based on DAO (are they?).
I am in the process of attempting to remove all DAO code from the ADP.
I have never used ADODB to open an MDB, however, and I was wondering
if anyone has a few pointers? It's pretty simple code...
'open the file via the linked table in the mdb, and check the date
inside it
Set db = OpenDatabase("L:\Tools\Reconciliation
\Reconciliation.mdb")
Set act = db.OpenRecordset("Activity")
startDate = CDate(act.fields(1))
I'm hoping this can be converted to something using ADOBD and
connection strings. I'm pretty familiar with these, but they seem to
be extremely fragile so any advice to start would be great!
MauryDim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
'Do some stuff here
cnn.Close
Set cnn = Nothing
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
>I have a MDB that consists largely of a number of linked tables to
> external data files, which contain various activity data. I then open
> this db from my ADP project using OpenDatabase and OpenRecordset,
> which I believe are based on DAO (are they?).
> I am in the process of attempting to remove all DAO code from the ADP.
> I have never used ADODB to open an MDB, however, and I was wondering
> if anyone has a few pointers? It's pretty simple code...
> 'open the file via the linked table in the mdb, and check the date
> inside it
> Set db = OpenDatabase("L:\Tools\Reconciliation
> \Reconciliation.mdb")
> Set act = db.OpenRecordset("Activity")
> startDate = CDate(act.fields(1))
> I'm hoping this can be converted to something using ADOBD and
> connection strings. I'm pretty familiar with these, but they seem to
> be extremely fragile so any advice to start would be great!
> Maury|||On Mar 25, 10:27=A0pm, "Dave Patrick" <DSPatr...@.nospam.gmail.com>
wrote:
> =A0 =A0Dim cnn As ADODB.Connection
> =A0 =A0Set cnn =3D CurrentProject.Connection
> =A0 =A0'Do some stuff here
> =A0 =A0cnn.Close
> =A0 =A0Set cnn =3D Nothing
Ummm, no.
Maury|||That's very descriptive!
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
Ummm, no.
Maury

Replace OpenDatabase with?

I have a MDB that consists largely of a number of linked tables to
external data files, which contain various activity data. I then open
this db from my ADP project using OpenDatabase and OpenRecordset,
which I believe are based on DAO (are they?).
I am in the process of attempting to remove all DAO code from the ADP.
I have never used ADODB to open an MDB, however, and I was wondering
if anyone has a few pointers? It's pretty simple code...
'open the file via the linked table in the mdb, and check the date
inside it
Set db = OpenDatabase("L:\Tools\Reconciliation
\Reconciliation.mdb")
Set act = db.OpenRecordset("Activity")
startDate = CDate(act.fields(1))
I'm hoping this can be converted to something using ADOBD and
connection strings. I'm pretty familiar with these, but they seem to
be extremely fragile so any advice to start would be great!
Maury
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
'Do some stuff here
cnn.Close
Set cnn = Nothing

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
>I have a MDB that consists largely of a number of linked tables to
> external data files, which contain various activity data. I then open
> this db from my ADP project using OpenDatabase and OpenRecordset,
> which I believe are based on DAO (are they?).
> I am in the process of attempting to remove all DAO code from the ADP.
> I have never used ADODB to open an MDB, however, and I was wondering
> if anyone has a few pointers? It's pretty simple code...
> 'open the file via the linked table in the mdb, and check the date
> inside it
> Set db = OpenDatabase("L:\Tools\Reconciliation
> \Reconciliation.mdb")
> Set act = db.OpenRecordset("Activity")
> startDate = CDate(act.fields(1))
> I'm hoping this can be converted to something using ADOBD and
> connection strings. I'm pretty familiar with these, but they seem to
> be extremely fragile so any advice to start would be great!
> Maury
|||On Mar 25, 10:27Xpm, "Dave Patrick" <DSPatr...@.nospam.gmail.com>
wrote:
> X XDim cnn As ADODB.Connection
> X XSet cnn = CurrentProject.Connection
> X X'Do some stuff here
> X Xcnn.Close
> X XSet cnn = Nothing
Ummm, no.
Maury
|||That's very descriptive!

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
Ummm, no.
Maury

Repl problem with Identity Ranges?

I've given the automatic range on one of the tables for replication and I
entered
1000000 for publisher and 1000000 for subscriber.
When the table is replicated, the id column starts with 278418. Why does it
do this? does SQL know not to count? or am i missing something in
understanding?
I'm sorry. After some research, I found out that i missed something. Sorry
for the post.

Rephrase my Issue with Pivot Table

I have two tables (definitions follow) one called Product, the other called
Traveler_Step. The PK-FK link is the Traveler_Step_#. Each product can
only be at one step at a time. If the product is done processing the
Traveler_Step_# is 0. Not all steps have a traveler at them. What I need
is a query that will return all the steps from the Traveler_Step table that
have a value of -1 in the WIPStep field. The query needs to order the steps
by WIP_Status_Order. Also, there are many different Traveler_Step_# numbers
for different travelers that have the same WIPStep for them. Running the
following query against the two tables:
SELECT
Product.Ingot_Number,
Product.Traveler_Number,
MIN(Traveler_Step.Step) AS Step,
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description,
COUNT(Product.Product_#) AS PieceCount
FROM
Product
RIGHT OUTER JOIN
Traveler_Step ON Product.Traveler_Step_# = Traveler_Step.Traveler_Step_#
GROUP BY
Product.Ingot_Number,
Product.Traveler_Number,
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description
HAVING
Traveler_Step.WIPStep < 0
ORDER BY
Product.Traveler_Number,
Traveler_Step.WIP_Status_Order
I get the following results:
NULL NULL 10 -1 1 Weigh/Record 0
NULL NULL 23 -1 3 Hard Pickle 0
NULL NULL 55 -1 4 Hot Roll Intermediate 0
NULL NULL 15 -1 5 UT Test 0
NULL NULL 20 -1 6 Beta Quench 0
NULL NULL 25 -1 7 Grit Blast 0
NULL NULL 30 -1 8 Pickle 0
NULL NULL 40 -1 9 Air Anneal 0
NULL NULL 45 -1 10 Grit Blast 0
NULL NULL 50 -1 11 Pickle 0
NULL NULL 60 -1 12 Cold Roll Intermediate 0
NULL NULL 80 -1 13 Vacuum Anneal Hang 0
NULL NULL 90 -1 14 Cold Roll Final 0
NULL NULL 115 -1 15 Vacuum Anneal F/P 0
NULL NULL 130 -1 16 Plane Width 0
NULL NULL 140 -1 17 Machine Shape 0
NULL NULL 150 -1 18 Machine Grooves 0
NULL NULL 165 -1 19 Shear Final Length 0
U06436L 53086A 15 -1 5 UT Test 5
U06436L 53086B 15 -1 5 UT Test 4
U06450L 53223J 140 -1 17 Machine Shape 2
U06450L 53223L 140 -1 17 Machine Shape 16
U06460L 53236A 140 -1 17 Machine Shape 5
U06460L 53237K 40 -1 9 Air Anneal 4
U06460L 53237M 40 -1 9 Air Anneal 4
U06460L 53237N 40 -1 9 Air Anneal 3
U06494L 53248G 20 -1 2 Grit Blast 1 *
U06494L 53307A 20 -1 6 Beta Quench 4
U06494L 53307B 20 -1 6 Beta Quench 3
I noticed that step 2 does not appear in the first fields with null values.
But one operation is at step 2 (marked with a star (*)). I need to get all
the steps to appear on the left of the matrix, then list all the items in
the corresponding row of the matrix depending on the
Traveler_Step.WIP_Status_Order field. I can get the steps to appear but as
you can see in the query results the right join is not returning all the
traveler_steps with null values. So I would like to see
Traveler 12345 Traveler 67899 Traveler
23343 Traveler 223344
Step 1
Step 2
x
Step 3
Step 4
Step 5 x
x
Step 6 x
Step 7
...
Step 19
Where the traveler numbers are the columns and the X corresponds to the
step in the rows. Any help?
john
CREATE TABLE [dbo].[Product](
[Product_#] [int] IDENTITY(1,1) NOT NULL,
[Traveler_#] [int] NOT NULL,
[Type_#] [int] NOT NULL,
[Part_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Date_Shipped] [datetime] NULL,
[Operator_#] [int] NOT NULL,
[Create_Date] [datetime] NOT NULL,
[Shop_Order_#] [int] NOT NULL,
[Date_Closed] [datetime] NULL,
[Ingot_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status_#] [int] NULL,
[Anneal_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MRT_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Double_Single_Length] [int] NULL,
[Carton_#] [int] NULL,
[Weight] [int] NULL,
[Sister_Piece_#] [int] NULL,
[Slab_Traveler_#] [int] NULL,
[Chemistry_#] [int] NULL,
[Traveler_Number] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Plate_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinalID] [bit] NULL CONSTRAINT [DF_Product_FinalID] DEFAULT (0),
[Traveler_Step_#] [int] NULL,
[Marked] [int] NULL CONSTRAINT [DF_Product_Marked] DEFAULT (0),
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Product_#] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Traveler_Step](
[Traveler_Step_#] [int] IDENTITY(1,1) NOT NULL,
[Traveler_#] [int] NOT NULL,
[Step] [int] NULL,
[Step_Description] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Station_#] [int] NOT NULL,
[Sub_Station_#] [int] NULL CONSTRAINT [DF_Traveler_Step_Sub_Station_#]
DEFAULT (0),
[Kanban_Point] [bit] NULL,
[Dependant_Traveler_Step_#] [int] NULL,
[Work_Standard_#] [int] NULL,
[Kanban_Notify_Station_#] [int] NULL,
[WIPStep] [int] NULL,
[Enabled] [bit] NULL,
[WIP_Status_Order] [int] NULL,
CONSTRAINT [PK_Traveler_Step] PRIMARY KEY CLUSTERED
(
[Traveler_Step_#] ASC
) ON [PRIMARY]
) ON [PRIMARY]I didn't see this before answering your next post.
You SQL query should probably be:
SELECT
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description,
Product.Ingot_Number,
Product.Traveler_Number,
MIN(Traveler_Step.Step) AS Step,
COUNT(Product.Product_#) AS PieceCount
FROM
Traveler_Step
LEFT OUTER JOIN Product
ON Product.Traveler_Step_# = Traveler_Step.Traveler_Step_#
GROUP BY
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description,
Product.Ingot_Number,
Product.Traveler_Number
HAVING
Traveler_Step.WIPStep < 0 /* - make sure this is removing
rows you want */
ORDER BY
Traveler_Step.WIP_Status_Order,
Product.Traveler_Number

repeatwith property

I have tried for days to get certain textboxes and tables in page body

to repeat on multiple page reports

Any help is appreciated.

Hello,

The repeatwith property is not available on tables and is not supported by physical paginated renderes.

However, the RepeateOnNewPage property of table&group headers and footers is supported by all renderers.

Thank you,

Nico

|||

How to transfer parameter to Header and display it on next page.

I reference one report textbox to header textbox "= ReportItems!textbox1.Value "

But it didn't display on second page....

What can I do?

|||

Hello,

Could you provide the rdl so I can understand what you are trying to achieve?

Thanks,

Nico

|||

How to use static query in my header textbox

= "SELECT [Contractor Name] FROM tblvendor WHERE vendorID=" & Parameters!vendorID.Value

It display the text "SELECT [Contractor Name] FROM tblvendor WHERE vendorID=354" not the query result

Repeating a task

OK gentlemen, here's another one.

How do I repeat a task a few times over until the condition is met.

I have two tables(table1 and table2). Table 1 consists of an ID column and a number of user data columns. Table2 consists of one ID coloumn, one table1ID column and one user data column.

What I need to do is to take the data fields lying horizontally in table1, and stack them up vertically into table2 consecutively, while keeping their association with the appropriate id in table1.

It would look something like this:

table1
-------------------
id data1 data2 data3 data4
-------------------
1 appple orange melon kiwi
2 green red blue yellow
3 ford honda bmw mazeratti
4 Mary stacy Jane Sharon

table2
------------
id table1id data
------------
1 1 apple
2 1 orange
3 1 melon
4 1 kiwi
5 2 green
6 2 red
7 2 blue
. . .
. . .
. . .
15 4 Jane
16 4 Sharon

Any ideas about how to do that?

ThanksWell, the tables didn't come out the way I intended. But I hope you get the idea. :)|||table1
-------------------
id data1 data2 data3 data4
-------------------
1 appple orange melon kiwi
2 green red blue yellow
3 ford honda bmw mazeratti
4 Mary stacy Jane Sharon

table2
------------
id table1id data
------------
1 1 apple
2 1 orange
3 1 melon
4 1 kiwi

I didn't test it but something like this whould work :

Insert Into Table2 (table1id,data)
(Select Id, Data1 From Table1
Union
Select Id, Data2 From Table1
Union
Select Id, Data3 From Table1
Union
Select Id, Data4 From Table1
Order by 1)

Id form Table2 should be an auto-incremental id|||Insert Into Table2 (table1id,data)
(Select Id, Data1 From Table1
Union
Select Id, Data2 From Table1
Union
Select Id, Data3 From Table1
Union
Select Id, Data4 From Table1
)

Without the "order by" it works
but the data is not inserted in the good order
so if it matters you could dump the ordered data into a temp table
and then insert the rows from that temp table into Table2

Repeating "starting up database DBtest" message in sql log

In my sql log I see the 'starting up database DBtest' repeating every few
seconds. The database seems fine and I can retreive data from tables of the
database. I've included a short clip of my log. I think something is wrong
here. Anybody have any clues. My first take is to terminate the spid65 and
see if all is well but this is a production system so I wanted to see if
anyone else has seen this before and had any suggestions.
Thanks, Edie
7/20/20051:13:27spid63Starting up database 'DBTest'.
7/20/20051:14:27backupDatabase backed up: Database: DBTest, creation
7/21/20051:13:27spid63Starting up database 'DBTest'.
7/21/20051:14:22backupDatabase backed up: Database: DBTest, creation
7/22/20051:14:19backupDatabase backed up: Database: DBTest, creation
7/23/20051:14:21backupDatabase backed up: Database: DBTest, creation
7/24/20051:14:20backupDatabase backed up: Database: DBTest, creation
7/24/200521:00:07backupDatabase backed up: Database: master, creation
7/24/200521:00:10backupDatabase backed up: Database: model, creation
7/24/200521:00:12backupDatabase backed up: Database: msdb, creation
7/25/20051:13:27spid63Starting up database 'DBTest'.
7/25/20051:14:20backupDatabase backed up: Database: DBTest, creation
7/25/200511:06:18spid65Starting up database 'DBTest'.
7/25/200511:06:18spid65Starting up database 'DBTest'.
7/25/200511:06:18spid65Starting up database 'DBTest'.
7/25/200511:06:19spid65Starting up database 'DBTest'.
7/25/200511:06:19spid65Starting up database 'DBTest'.
7/25/200511:06:19spid65Starting up database 'DBTest'.
Hi,
It seems you have enabled the database option "AUTOCLOSE". THis will close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
or from Query analyzer execute the below command to turn off:-
sp_dboption <dbname>, 'autoclose', False
This will ensure that database will never closed as soon as last user logs
off.
Thanks
Hari
SQL Server MVP
"Edie Richardson" <Edie Richardson @.discussions.microsoft.com> wrote in
message news:D91B95AE-C74C-483A-9A7F-243B879A7636@.microsoft.com...
> In my sql log I see the 'starting up database DBtest' repeating every few
> seconds. The database seems fine and I can retreive data from tables of
> the
> database. I've included a short clip of my log. I think something is
> wrong
> here. Anybody have any clues. My first take is to terminate the spid65
> and
> see if all is well but this is a production system so I wanted to see if
> anyone else has seen this before and had any suggestions.
> Thanks, Edie
> 7/20/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/20/2005 1:14:27 backup Database backed up: Database: DBTest, creation
> 7/21/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/21/2005 1:14:22 backup Database backed up: Database: DBTest, creation
> 7/22/2005 1:14:19 backup Database backed up: Database: DBTest, creation
> 7/23/2005 1:14:21 backup Database backed up: Database: DBTest, creation
> 7/24/2005 1:14:20 backup Database backed up: Database: DBTest, creation
> 7/24/2005 21:00:07 backup Database backed up: Database: master, creation
> 7/24/2005 21:00:10 backup Database backed up: Database: model, creation
> 7/24/2005 21:00:12 backup Database backed up: Database: msdb, creation
> 7/25/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/25/2005 1:14:20 backup Database backed up: Database: DBTest, creation
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
>
|||Switch off "Auto Close" on the DB (Database Property)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Edie Richardson" <Edie Richardson @.discussions.microsoft.com> wrote in
message news:D91B95AE-C74C-483A-9A7F-243B879A7636@.microsoft.com...
> In my sql log I see the 'starting up database DBtest' repeating every few
> seconds. The database seems fine and I can retreive data from tables of
> the
> database. I've included a short clip of my log. I think something is
> wrong
> here. Anybody have any clues. My first take is to terminate the spid65
> and
> see if all is well but this is a production system so I wanted to see if
> anyone else has seen this before and had any suggestions.
> Thanks, Edie
> 7/20/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/20/2005 1:14:27 backup Database backed up: Database: DBTest, creation
> 7/21/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/21/2005 1:14:22 backup Database backed up: Database: DBTest, creation
> 7/22/2005 1:14:19 backup Database backed up: Database: DBTest, creation
> 7/23/2005 1:14:21 backup Database backed up: Database: DBTest, creation
> 7/24/2005 1:14:20 backup Database backed up: Database: DBTest, creation
> 7/24/2005 21:00:07 backup Database backed up: Database: master, creation
> 7/24/2005 21:00:10 backup Database backed up: Database: model, creation
> 7/24/2005 21:00:12 backup Database backed up: Database: msdb, creation
> 7/25/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/25/2005 1:14:20 backup Database backed up: Database: DBTest, creation
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
>
|||thanks! The autoclose option was checked.
"Hari Prasad" wrote:

> Hi,
> It seems you have enabled the database option "AUTOCLOSE". THis will close
> the MDF and LDF as soon as the last user logs of the database. Again the MDF
> and LDF will be opened once a user logins to the database.
> How to check this option is checked:-
>
> 1. Enterprise manager -- Databases -- Select the database
> 2. Right click and select properties -- Choose options
> 3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
> or from Query analyzer execute the below command to turn off:-
> sp_dboption <dbname>, 'autoclose', False
> This will ensure that database will never closed as soon as last user logs
> off.
> Thanks
> Hari
> SQL Server MVP
>
> "Edie Richardson" <Edie Richardson @.discussions.microsoft.com> wrote in
> message news:D91B95AE-C74C-483A-9A7F-243B879A7636@.microsoft.com...
>
>

repeated posts

I have a database where I collect student information from three different tables.
When I write a select case to see all the student information, the problem is that a student can have more than onte contact person from ex AF. How can I see all this information as one record?

I wrote like this:

Select distinct Studieinfo.PersNR, Elev.Fornamn + ' ' + Elev.Efternamn AS Namn, Studieinfo.Startvecka, Studieinfo.slutvecka,
Studieinfo.startdatum, Studieinfo.slutdatum, Studieinfo.Kursort, Studieinfo.Studietid, Studieinfo.Forlangning,
Studieinfo.beraknad_studietid, Studieinfo.mal, Studieinfo.delrapport, Studieinfo.moduler,
KontaktPersoner_FK.Fornamn + ' ' + KontaktPersoner_FK.Efternamn AS KontaktFK, KontaktPersoner_AF.Fornamn + ' ' +KontaktPersoner_AF.Efternamn AS KontaktAF
From Studieinfo, KontaktPersoner_FK, Kontakt_FK, KontaktPersoner_AF, Kontakt_AF, Elev
WHERE Elev.PersNR=Kontakt_FK.PersNR
and Elev.PersNR=Kontakt_AF.PersNR
and Elev.PersNR=Studieinfo.PersNR
and Elev.PersNR='691215-3638'
and Kontakt_FK.KontaktNR_FK=KontaktPersoner_FK.Kontakt NR_FK
and Kontakt_AF.KontaktNR_AF=KontaktPersoner_AF.Kontakt NR_AF
goIf you have defined contact types (Mothe, Father, Guardian, ParoleOfficer...) then you can write a CROSSTAB query to do this. Look it up in Books Online for instructions. Otherwise you may need to use a cursor to loop through related records and concatenate multiple contact records into a single character string. A user-defined function would be ideal for this.

I'd also say that this type of formatting (which is purely for the sake of appearance) is often best delegated to the reporting interface (crystal, VB, Excel, Access, ect...). In a sense, when you try to formulate a query like this you are asking a relational database to be non-relational.

blindman|||Thanks for your advice. I was actually thinking of correcting it within the asp on the page. Like you suggested but was just wondering if it was possible to do with the sql.

repairing replicated tables

Hi all,
I have a sql2005 merge replication running nightly between SQl 2005 standard
and SQLexpress. (The subscription is on the SQLexpress) last night it began
failing. I ran a DBCC checkDB and found one table has 3 inconsistancies in
it and the lowest level of repair is repair with loss. This box is on its
way out and i need to band-aide it until the new one arrives and is setup.
My question is, do I need to drop the replication before putting the DB in
single user mode? Is there a way to repair the table without running DBCC
checktable with the repair option?
TIA,
Joe
Is the problem related to some indexes and do you get a RID ID error?
If so this is related to some indexes (on system tables IIRC) and you
can drop them and recreate them.
Run CHECKDB again note the object ID which is experiencing the error
and evaluate whether it is an index or not. Script out the index, drop
it and recreate it. There will be no data loss associated with this.
If it is table related you will have to bcp the data out noting where
failure occurs and then work around that/those rows using the firstrow
and lastrow options. You can use DBCC Page to look at the problem
pages if you need to be so granular in your data retrieval.
On Jan 15, 2:16 pm, jaylou <jay...@.discussions.microsoft.com> wrote:
> Hi all,
> I have a sql2005 merge replication running nightly between SQl 2005 standard
> and SQLexpress. (The subscription is on the SQLexpress) last night it began
> failing. I ran a DBCC checkDB and found one table has 3 inconsistancies in
> it and the lowest level of repair is repair with loss. This box is on its
> way out and i need to band-aide it until the new one arrives and is setup.
> My question is, do I need to drop the replication before putting the DB in
> single user mode? Is there a way to repair the table without running DBCC
> checktable with the repair option?
> TIA,
> Joe
|||Thank you, it was an index issue and droping and recreating the Indexes did
the trick.
Thanks again.
"Hilary Cotter" wrote:

> Is the problem related to some indexes and do you get a RID ID error?
> If so this is related to some indexes (on system tables IIRC) and you
> can drop them and recreate them.
> Run CHECKDB again note the object ID which is experiencing the error
> and evaluate whether it is an index or not. Script out the index, drop
> it and recreate it. There will be no data loss associated with this.
> If it is table related you will have to bcp the data out noting where
> failure occurs and then work around that/those rows using the firstrow
> and lastrow options. You can use DBCC Page to look at the problem
> pages if you need to be so granular in your data retrieval.
> On Jan 15, 2:16 pm, jaylou <jay...@.discussions.microsoft.com> wrote:
>