conversion tools for sql7 databases

is there any tool / utility , online service / websites that will help me convert a MSSQL 7 database to be compatible to MSSQL2k ?
i have enterprise manager for 7 , but when i try to connect to server which has sql2000 ... i cant .
thanx .You need to uninstall Client Tools for SQL 7.0 and install Client Tools for 2K.

Conversion of Database into text file

hi All,

Actually i have a project on data minning...n i have to convert the databases into text files so that they can be consolidate ....n when consolidation of the databases(in the form of text files) would be done i have to convert the consolidated one (text file) into database again.

so anyone plz tell me dat how to convert a database (using sql server & C#) into text file...

regards,

Hello.

This is what Sql Server Integration Services was made for. Any reason why you want to create the text-files?

You have many other options.

It looks to me that what you can do with an INSERT queries using linked servers. Have a look at sp_addlinkedserver in TSQL. You might get help from reading http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html (ignore step 1 and 3)

Hope this helps.

Conversion of Beta 2 Databases

How do I convert a Beta2 database to the RTM version?
SQL Scott wrote:
> How do I convert a Beta2 database to the RTM version?
Assuming the internal database design has not changed (I don't know that
it has), you could either do a detach / attach or a backup and restore
of the user database. TO be safe, you might want to export the DDL for
the database and create the database schema separately from exporting
the data.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||SQL Scott (SQL Scott@.discussions.microsoft.com) writes:
> How do I convert a Beta2 database to the RTM version?
If memory serves, the April CTP was the last CTP that was able to read the
format of Beta 2. So you would have to install the April CTP to attach the
databases there, and then move the databases from the April CTP to the RTM
version.
Since the April CTP is no longer available for download, this is not really
a workable way. It may be better to script the database (using the Beta 2
tools) and bulk load the data.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||I'll plan to script out the database and export the data using the Beta 2
tools.
Thanks for your response!

Convenient offline tables copy ?

Hi!

I'm looking for the most convenient way of copying 4 tables between two databases which cannot connect to each other directly (I have to go through a filesystem and ftp).

Most of the time I use the CSV import / export from SSIS, but found out that it can be error-prone (have to carefully pick-up the same culture, text delimiter, and configure the XML source on the other side - I don't have many metadatas here except for the column names, so it leads to truncation errors etc sometimes).

Is there a better way to achieve this under SSIS ? So far I didn't actually invoke bcp, as I was looking for a more 'ssish' way of doing this.

Thanks for any pointer

Thibaut Barrère

Once built your packages should be error free, unless something changes. If this is a feature, then SSIS is not going to ber a good solution. BCP can be better because it is simple enough to just dump a table without knowing structures in advance. Bulk Insert Task offers this as well, but there is no Bulk Export Task. I wrote one for DTS, because I liked the way you did not have to manage the changes, just keep source and destination in synch.

(Where did the Xml Source come from, you mean CSV I assume as that was the export format you said.)

For now I'd use BCP, and probably the raw format as well.

|||

Thibaut Barrère wrote:

Hi!

I'm looking for the most convenient way of copying 4 tables between two databases which cannot connect to each other directly (I have to go through a filesystem and ftp).

Most of the time I use the CSV import / export from SSIS, but found out that it can be error-prone (have to carefully pick-up the same culture, text delimiter, and configure the XML source on the other side - I don't have many metadatas here except for the column names, so it leads to truncation errors etc sometimes).

Is there a better way to achieve this under SSIS ? So far I didn't actually invoke bcp, as I was looking for a more 'ssish' way of doing this.

Thanks for any pointer

Thibaut Barrère

SSIS isn't really a tool for managing objects, only data. Hence I like to rely on SQL scripts for deployig the objects (Very easy, just generate the script in SSMS, change the connection, and hit execute) and use the Import/Exprt wizard to pump data between them.

Or try Darren's method!

-Jamie

|||

This could well be part of a script based deployment scenario, and in that scenario the data Import/Export does not cut it for me. You would have to manually maintain or at least run the Wizard, and in this case twice, since we need to stage in files, as you cannot do direct. Makes sense to allow you to version control it as well.

Using BCP to build the data "script" works rather well here, and is just easier to maintain and faster compared to other methods like the Wizard, or generating scripts.

|||

Hi!

thanks for your answers first. Actually I'm not designing a backup strategy or a deployment of some kind : I have a consolidation process on a production machine, and I want to take benefits of a couple of tables which are handled outside the production site (inhouse tables), to achieve clean-up, lookups etc.

What I'm looking for is the easiest way of using this bunch of tables in production, as data sources for the consolidation process.

Following your advices, I've tried bcp and it works just perfectly to export the data. But when calling it for import, sometimes it doesn't insert some rows, but won't return a non-zero errorresult either (I've googled and saw that it seems to happen to others) ! This is quite embarrassing - did you meet such an issue ?

I've also tried the import/export method - but is it supposed to work if I have relationships and constraints between my source tables ?

cheers

Thibaut

Controlling log file size

What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
--
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>

Controlling Access

Is there a method of only allowing users to access SQL
Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
I am not referring to the "Application Roles" available
within SQL Server, rather I would like the following to
take place: -
Example 1.
User connects to SQL Server Database, by either using
Commercial Software or Bespoke System, using "NT
Authentication".
SQL Server recognises the user and the Application/System
and allows access to the Database.
Example 2.
User connects to SQL Server Database, by linking/Importing
Tables to a new Database Document, using "NT
Authentication".
SQL Server recognises the user but not the
Application/System and denies access to the Database.
I am using SQL Server 7.0, although we will soon be
migrating to 2000.
TIA
Tony C.Unfortunately no. You could monitor for connections after
the fact, e.g. have a job that checks who is connected using
what application and kill processes that are connected using
the non-approved applications. But you can't really control
access based on what application is being used.
-Sue
On Fri, 2 Apr 2004 01:34:33 -0800, "Tony C"
<tony.chorleyRUBBISH@.mcalpineplc.com> wrote:

>Is there a method of only allowing users to access SQL
>Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
>I am not referring to the "Application Roles" available
>within SQL Server, rather I would like the following to
>take place: -
>Example 1.
>User connects to SQL Server Database, by either using
>Commercial Software or Bespoke System, using "NT
>Authentication".
>SQL Server recognises the user and the Application/System
>and allows access to the Database.
>Example 2.
>User connects to SQL Server Database, by linking/Importing
>Tables to a new Database Document, using "NT
>Authentication".
>SQL Server recognises the user but not the
>Application/System and denies access to the Database.
>I am using SQL Server 7.0, although we will soon be
>migrating to 2000.
>TIA
>Tony C.

Control parameter help......

I'm writing a page to do some reporting off of one of our databases, and I'm using 3 control parameters for my sql query that feeds my datagrid. 2 of the ControlParameters are from dropdownlists, and one is from a RadioButtonList. The ControlParameters that reference the dropdownlists are both working well, but the one for the RadioButtonList is not.

The error I am getting is:Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near'@.Booga'. (I changed my ControlParameter name from DateStr to Booga to try to avoid any conflicts with reserved words.)

Any ideas? Here is a snippet of my code:

<

formid="form1"runat="server"><tablewidth="100%"><tr><tdstyle="text-align: center"><strong>DataCenter</strong></td><tdstyle="text-align: center"><strong>Time Scope</strong></td><tdstyle="text-align: center"><strong>Call Status</strong></td></tr><tr><tdstyle="height: 47px; text-align: center"><asp:DropDownListID="DropDownList1"DataSourceID="SqlDataSource2"AutoPostBack="true"DataTextField="LocationName"runat="server"/></td><tdstyle="height: 47px; text-align: center"><asp:RadioButtonListID="RadioButtonList1"runat="server"AutoPostBack="true"Font-Size="Smaller"><asp:ListItemSelected="True"Value=" 1 = 1 ">All</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 1) >= (CAST(GETDATE() AS smalldatetime)))">Last Day</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 7) >= (CAST(GETDATE() AS smalldatetime)))">Last Week</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 30) >= (CAST(GETDATE() AS smalldatetime)))">Last 30 Days</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 120) >= (CAST(GETDATE() AS smalldatetime)))">Last 120 Days</asp:ListItem></asp:RadioButtonList></td><tdstyle="height: 47px; text-align: center"><asp:DropDownListID="DropDownList2"AutoPostBack="true"runat="server"><asp:ListItemSelected="True"Value="Open">Open</asp:ListItem><asp:ListItemValue="Closed">Closed</asp:ListItem></asp:DropDownList></td></tr></table><br/><pstyle="text-align: center"><strong>Displaying All Open Tickets</strong><br/></p><asp:SqlDataSourceID="SqlDataSource2"runat="server"SelectCommand="SELECT DISTINCT [locationname] FROM [profile]"ConnectionString="<%$ ConnectionStrings:Heat %>"/><tablewidth="100%"><trwidth="100%"><tdvalign="top"width="100%"><asp:GridViewID="GridView1"AllowSorting="True"runat="server"DataSourceID="SqlDataSource1"DataKeyNames="CallID"AutoGenerateColumns="False"Font-Size="Smaller"Width="100%"><Columns><asp:CommandField/><asp:BoundFieldDataField="CallID"HeaderText="Call ID"ReadOnly="True"SortExpression="CallID"/><asp:BoundFieldDataField="CustID"HeaderText="Customer ID"ReadOnly="True"SortExpression="CustID"/><asp:BoundFieldDataField="CallType"HeaderText="Call Type"ReadOnly="True"SortExpression="CallType"/><asp:BoundFieldDataField="Priority"HeaderText="Priority"ReadOnly="True"SortExpression="Priority"/><asp:BoundFieldDataField="Cause"HeaderText="Cause"ReadOnly="True"SortExpression="Cause"/><asp:BoundFieldDataField="CallDesc"HeaderText="Call Description"ReadOnly="True"SortExpression="CallDesc"><ItemStyleWidth=40%/></asp:BoundField><asp:BoundFieldDataField="RecvdBy"HeaderText="Received By"ReadOnly="True"SortExpression="RecvdBy"/><asp:BoundFieldDataField="RecvdDate"HeaderText="Call Date"ReadOnly="True"SortExpression="RecvdDate"><ItemStyleWrap="False"/></asp:BoundField><asp:BoundFieldDataField="RecvdTime"HeaderText="Call Time"ReadOnly="True"SortExpression="RecvdTime"><ItemStyleWrap="False"/></asp:BoundField></Columns></asp:GridView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"SelectCommand="SELECT * FROM CallLog INNER JOIN Profile ON CallLog.CustID = Profile.CustID WHERE (Profile.LocationName = @.LocationName) AND (CallLog.CallStatus = @.CallStatus) AND @.Booga "ConnectionString="<%$ ConnectionStrings:Heat %>"><SelectParameters><asp:ControlParameterControlID="DropDownList1"Name="LocationName"PropertyName="SelectedValue"Type="String"/><asp:ControlParameterControlID="DropDownList2"Name="CallStatus"PropertyName="SelectedValue"Type="String"/><asp:ControlParameterControlID="RadioButtonList1"Name="Booga"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource></td></tr></table><br/><br/><br/>

<br />

</form>

Your SQL looks incomplete:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"SelectCommand="SELECT * FROM CallLog INNER JOIN Profile ON CallLog.CustID = Profile.CustID WHERE (Profile.LocationName = @.LocationName) AND (CallLog.CallStatus = @.CallStatus) AND @.Booga "

What is @.Boonga supposed to be related to?

|||

That's where the ControlParameter problem is:

<asp:ControlParameterControlID="RadioButtonList1"Name="Booga"PropertyName="SelectedValue"Type="String"/>

That should be making the SQL command end with one of the date comparisons that are controlled by the radiobuttonlist control.

Continue SP after Database Access Failure

Over night we take a copy of various live SQL databases onto another SQL
server for reporting purposes.
I have a stored procedure that compares the latest live data against the 1
day old copies to ensure that they are up to date.
I connect to the live databases using linked servers.
Here's where the problem is - when one of the external links is down or one
of the live databases is offline the stored procedure has an error and stops
.
How can I test within the stored procedure that the database on the linked
server is available? Then, based on the result, carry out an action?
Even a simple select statement against an unavailable database halts the
whole SP even though I've tried breaking the code down into seperate
transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
fails with "SQL Server does not exist or access denied."
Any advice greatly appreciated.Hi Paula,
Error handling in SQL Server 2000 is "somewhat" problematic as you have
seen.
For these cases, I use the following trick of nesting the execution scopes:
USE tempdb
select * from nonexist
select 'passed after error', @.@.error
go
-- batch was terminated without returning the message
exec ('select * from nonexist')
select 'passed after error', @.@.error
go
-- inner scope was aborted, outer scope continued
create proc p3 as
select * from nonexist
select 'passed after error', @.@.error
go
exec p1
-- batch was terminated without returning the message
create proc p2 as
select * from nonexist
go
create proc p3 as
exec p2
select 'passed after error', @.@.error
go
exec p3
-- inner procedure was aborted, outer procedure continued
This should work for most cases although some errors will stop and rollback
the whole batch including outer scopes.
I have tested it with inaccessible linked servers and it worked fine for me.
See the following thread for more details:
http://groups-beta.google.com/group...f3390d2b34758e2
HTH
Ami
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Perhaps the object_id(<object> ) function can help. For example, if
object_id('mydb..mytable') will return an object id if the database and
table exists, otherwise it will return NULL.
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Works for tables on the local SQL server, but not on Linked Servers, which i
s
where I'm having the problem.
Thanks for the tip anyway.
Paula
"JohnnyAppleseed" wrote:

> Perhaps the object_id(<object> ) function can help. For example, if
> object_id('mydb..mytable') will return an object id if the database and
> table exists, otherwise it will return NULL.
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> one
> stops.
>
>|||Paula
You can check PING Server to make sure that remote server is UP or DOWN
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
'Do something'
END
DROP TABLE #t_ip
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> Works for tables on the local SQL server, but not on Linked Servers, which
is
> where I'm having the problem.
> Thanks for the tip anyway.
> Paula
> "JohnnyAppleseed" wrote:
>
SQL
the 1
or
linked
the
still|||Abolutely great! I was over complicating things for my self instead of
breaking the problem down. I will now be pinging the server using your
helpful code, then testing for the database using another great persons
suggestions from this wonderful resource!
Thanks again
Paula
"Uri Dimant" wrote:

> Paula
> You can check PING Server to make sure that remote server is UP or DOWN
> set nocount on
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> 'Do something'
> END
> DROP TABLE #t_ip
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> is
> SQL
> the 1
> or
> linked
> the
> still
>
>

Context Change and Cursor

I have T-SQL code that is used to run utilities against a particular list of databases that are stored in a table. I have a cursor that gets the list of DBs from the sysdatabases table based on entries in a table in a specific database. The list of databases will vary from server to server, so I need to have it use variables rather than code the db names into the script.

declare @.DatabaseId char(8)

declare DatabaseLoop cursor for
select name from master..sysdatabases where name in
(select <column name> from <db name..table> )

open DatabaseLoop
fetch next from DatabaseLoop into @.DatabaseId
while (@.@.fetch_status <> -1)
begin

...

So far, the context has not mattered, for example - backup @.DatabaseID works fine within the cursor since the context doesn't need to change. The problem is that I want to run particular scripts that require the context to be changed to the database. But, of course, USE @.DatabaseID does not work inside the cursor.

So, is there another way to be able to run my script against multiple variables other than using a cursor?

I am not necessarily looking for you to write my code, but if someone could point me in the right direction, I would appreciate it.

I would suggest that you use dynamic SQL for this and you can use a use in there.

declare @.query varchar(8000) -- I am assuming 2000 since you

--used sysdatabases

set @.query = 'use ' + @.database + ' select * from sysobjects'

exec (@.query)

I know this works in 2005, and am pretty sure it worked in 2000

Edit: Sorry, sent the message from my phone and it did a terrible job with the formatting