How to remove blank spaces in records?

We imported approximately 2.9 million records from our mainframe serverinto our SQL Server but have run into a problem. The data in afew of the fields contains both leading and trailing spaces. Anexample of the data would be like this, using periods to representspaces:
What we have:
..1A02938....
What we need:
1A02938 (no spaces)
Is there some sort of algorithm I can run on the data to removethose spaces? The problem is coming up when trying to perform aSELECT query. We try something like:
SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938" but we get zeroresults because of the spaces in the database. The datatype ofthe filed is char(20) because we need some flexibility on the size ofthe data stored.
Any assistance would be greatly appreciated.
Try this link for packages and code to use to clean the data before you import it into SQL Server. Hope this helps.
http://www.sqldts.com|||

To select and insert the data you can use LTRIM(RTRIM(Pano20)) = "1A02938"

What you might want to do is run an update on the data.
UPDATE PCPIPTo SET PANO20 = LTRIM(RTRIM(PAN020))
This way, you dont have to trim the field each time and get the performance hit.

Nick

|||nick,
Thanks for the response. It took us about 3 hours toimport the data from the AS/400 so if we can just change the data whileit's in the table it would make life a lot easier. I'll try thatupdate query and see how it works.
Thanks!
|||If 1A03928 is indicative of the data stored in that column, I suggestchanging the datatype from char(20) to varchar(20) as well.|||nick,
Thanks for the help. That did the trick! I didn'tneed to do the RTRIM, just the LTRIM. But that makes my life alot easier.

how to reduce the size of transaction file

i've run "dbcc shrinkfile ('employ')"
The size of the file does not reduced.Win
It could be because the LOG file has an actve portions of transaction. Did
you run BACKUP LOG prior a SHRINKING?
select DB_ID('dbname')
Run DBCC LOGINFO(7)
If you see at the bottom transactions with status=2 that means SQL Server
'needs' them and you could not shrink.
However you can run dump inserting to moce those transaction at the
beginning of the LOG
"Win" <aaa@.aaa.com> wrote in message
news:%23Pgz2NkUGHA.328@.TK2MSFTNGP11.phx.gbl...
> i've run "dbcc shrinkfile ('employ')"
> The size of the file does not reduced.
>|||You'll find some information about why it might not shrink in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Win" <aaa@.aaa.com> wrote in message news:%23Pgz2NkUGHA.328@.TK2MSFTNGP11.phx.gbl...arkred">
> i've run "dbcc shrinkfile ('employ')"
> The size of the file does not reduced.
>

How to recreate replication system objects?

Does anyone know what script I need to run to recreate the
replication system objects in MSSQL?
I believe it's in the Install Directory after installing SQL?
Will it destroy any existing publications/subscriptions on the server?
I have 2 other publications that are running I want to keep.
Thanks
RClick Replication component/ Generate SQL Script.
"Chris" <chriswinstone@.aol.com> wrote in message
news:1125399221.478160.216810@.o13g2000cwo.googlegr oups.com...
> Does anyone know what script I need to run to recreate the
> replication system objects in MSSQL?
> I believe it's in the Install Directory after installing SQL?
> Will it destroy any existing publications/subscriptions on the server?
> I have 2 other publications that are running I want to keep.
> Thanks
>
|||I'm guessing here, but did you have a failed installation? If so, then
reapplying sp4 should do the trick.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
No I didn't have a failed installation as such. The replication was working
fine for about 18 months then all of a sudden I started getting errors from
the merge agent after executing sp_MSfetchidentityrange saying it could not
retrieve identity range resource for the table [TABLENAME].
A post from Hilary Cotter pointed me in the direction of the
msrepl_identity_range tables, however I decided to script the publication
for backup and try removing it. So I ran sp_removedreplication (probably too
hasty on that one!) then tried to run the puplication gen script thats when
I started getting the invalid object name 'syspublications' This is where I
am at at the moment. no replication at all! I can't even remove replication
manually - documented in Q324401!
I'm really stuck! Can't go forward or back! So I thought maybe recreating
the replication system objects would help me go in at least one direction!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uyQXnTZrFHA.464@.TK2MSFTNGP15.phx.gbl...
> I'm guessing here, but did you have a failed installation? If so, then
> reapplying sp4 should do the trick.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Chris,
what about if you disable replication then reenable, then execute your
scripts?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
How do you do this? In QA or EM?
Thanks
Paul Ibison wrote:
> Chris,
> what about if you disable replication then reenable, then execute your
> scripts?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Chris,
in EM - 'Tools, Replication, Disable Publishing'.
HTH,
Paul Ibison
|||Thanks Paul
Thats worked!
I now have a problem when creating my scripted publication again! See later
post.
Thanks again!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236JW0YjrFHA.3060@.TK2MSFTNGP09.phx.gbl...
> Chris,
> in EM - 'Tools, Replication, Disable Publishing'.
> HTH,
> Paul Ibison
>

How to recover deleted data from master database

Hi all,
I made a horrible thing this week. I have an script that delete all data from all user tables, and I run it in the master database.
After this I couldn't access the metadata from the tables of my databases using a JDBC connection.
My script runs over all sysobject that are different from dtproperties.
I don't have a back up of master table. I have tried to copy the data from another master database (from another machine) but I did not work. I tried to copy from a xls, but it did'nt work too. I try do recover the master database (as it is explained in the online books) but it did not work either.

Someone know how can I recover this data?Here is the script:

declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)

declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor

-- ligando os vnculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

deallocate table_name_cursor

And here is the names of the tables of master database that this script had deleted all the containing data:
spt_monitor
spt_values
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_provider_types
spt_datatype_info_ext
MSreplication_options
spt_datatype_info
spt_server_info
spt_server_info

How to reclaim DB space?

Hi
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
JonoI tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a6
01280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono

How to reclaim DB space?

Hi
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
Jono
I tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a601280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono

How to read the content of DTS local package?

I create a local package in DTS a few months ago. And set a schedule to run daily. Now i need to modify. How to read the content of DTS local package? i try to right click the package in job, and edit, but i didn't see the content. How can i see it and modify. Why mine can't be read? Thanks.you need to go under :

Data Transformation Services ->Local Packages in Enterprise manager.

You'll see your package there.|||I can find the package, But i want to see the content. As this is a package that import some of data from another database. So i can't read the content that means the query. I forgot, but i need to modify. Thanks.|||YYU I think SQL SERVER 2005 has a utility that allows you to SEE/EDIT your DTS file, regarless of the version used to create this files.

You can also try to monitor the activty on your SQL SERVER using the PROFILER TOOL, wich comes with SQL SERVER 2000+, and run your DTS files. Your SQL QUERY should appear in your TRACE FILE.

BTW this forum ROCKS!!

BIGGGG UP!!|||Thanks. It is great to have sql server 2005. We will have later. Let's see.

Thanks again!

How to read block of rows from database tables

have created a Database Application in Java and display all the records in tabular format of one Table. This table have Millions of Rows, If I run Select * from Table, then my Machine not responding, so Now I wants to add paging of 1000 rows at one time.

Is there are any option/query to read block of rows at one time and then query again for next page ?

i.e In MYSQL have LIMIT clause with Select Statement

Please let me know..

Database : SQL Server 2000/2005,

Thanks in Advance
Laxmilal

Whenever you use Select statement you must use WHERE condition to limit the rows.

Eg.

Select col1,col2.... From Tablename WHERE someid between 1 and 1000

Madhu

|||

You 'Machine not responding' is most likely due to waiting for millions of rows to come back from the server.

You really need to limit the quantity of data that you are requesting from SQL Server. Without WHERE clause criteria that enforces limits, you are unnecessarily wasting bandwidth (and time) as more data than you need for the operation at hand is being transported to the client application.

With SQL 2005, you may wish to explore using the new ROW_NUMBER() function to assist in retrieving specific 'blocks' of rows.

Referring to Books Online, Topic: ROW_NUMBER()

Example:

USE AdventureWorks; GO

WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber' FROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60;(You should be able to adapt this concept for your needs.)

how to query for text containing parens?

I have an SQL database with rows that have parens in the data.

If I run a select statement such as:

SELECT SongName
FROM Songs
WHERE SongName = 'John Jacob (Jingleheimer Schmidt)'

It returns zero rows. This also:

SELECT SongName
FROM Songs
WHERE SongName LIKE '%John Jacob (Jingleheimer Schmidt)%'

returns zero rows.

If I change it to this:

SELECT SongName
FROM Songs
WHERE SongName LIKE '%John Jacob%'

Then I get the row returned.

Is there a way to use the first query example above and return the row?
I'm guessing it has something to do with the parenthesis...

*** Sent via Developersdex http://www.developersdex.com ***I figured out what the problem is, now how to come up with a solution.

The problem seems to be that the last [space] is being represented with
A0 instead of 20 in the database.

How can I take that into account in my queries and return the data
regardless of the byte value used for [space]?

*** Sent via Developersdex http://www.developersdex.com ***|||On 29 Sep 2006 20:31:59 GMT, Terry Olsen wrote:

Quote:

Originally Posted by

>I figured out what the problem is, now how to come up with a solution.
>
>The problem seems to be that the last [space] is being represented with
>A0 instead of 20 in the database.
>
>How can I take that into account in my queries and return the data
>regardless of the byte value used for [space]?

Hi Terry,

Short-term solution:

WHERE REPLACE (SongName, CHAR(160), ' ') = 'John Jacob (Jingleheimer
Schmidt)'

Downside is that an index on the SongName column (if there is any) can't
be used as effectively.

Long-term solution: fix the front end or the stored proc that handles
data entry to convert char(A0) to space (= fixing the leak), then run an
update to convert existing data (= mopping up the floor).

--
Hugo Kornelis, SQL Server MVP

How to query a remote server from sql 2005

Ok,,im stuck.
I can access a remote 2000 sql server from sql 2005. I connect directly to
the 2000 database ok. I can run queries on it all day.
I can do the same thing with another remote ODBC server with SQL2005.
THe question is how do you login to SQL2000 from sql 2005 and yet still be
able to run queries on the other remote ODBC server?
--
HarryTry using opendatasource which will accept a connection string to access the
remote server.
--
"Harry" wrote:

> Ok,,im stuck.
> I can access a remote 2000 sql server from sql 2005. I connect directly t
o
> the 2000 database ok. I can run queries on it all day.
> I can do the same thing with another remote ODBC server with SQL2005.
> THe question is how do you login to SQL2000 from sql 2005 and yet still be
> able to run queries on the other remote ODBC server?
> --
> Harry|||Create linked servers and use openquery() method.
"Omnibuzz" wrote:
> Try using opendatasource which will accept a connection string to access t
he
> remote server.
> --
>
>
> "Harry" wrote:
>|||Create linked servers and use OpenQuery() method
"Harry" wrote:

> Ok,,im stuck.
> I can access a remote 2000 sql server from sql 2005. I connect directly t
o
> the 2000 database ok. I can run queries on it all day.
> I can do the same thing with another remote ODBC server with SQL2005.
> THe question is how do you login to SQL2000 from sql 2005 and yet still be
> able to run queries on the other remote ODBC server?
> --
> Harry

How to Query a Project Server DB

I am new at this - so please forgive me. I understand the basics of writing
SQL code, but am not familiar with where the code should go or how to run it
.
I simply am trying to write a query to extract some data from Project Server
database.
I have SQL Server 2000 loaded. Where should the code for this query go, and
how do I execute it?SQL Server ships with what are called "Client TOols" , one of which is query
analyzer. This is probably your best bet top get started. As firsts step,
You will need to "Connect" to the database server softwre to do anything.
Generaly that requiresthat you know the Machine (Called "Server"), the
database on the server, what userID or Login, and the Users password...
"Diane" wrote:

> I am new at this - so please forgive me. I understand the basics of writi
ng
> SQL code, but am not familiar with where the code should go or how to run
it.
> I simply am trying to write a query to extract some data from Project Ser
ver
> database.
> I have SQL Server 2000 loaded. Where should the code for this query go, a
nd
> how do I execute it?