Database Discussion Boards

First Prev Next

Re: Problems with CREATE DATABASE

Frederick S Jones11-Mar-04 2:08

Frederick S Jones

11-Mar-04 2:08

Your suggestion worked - for some dumb reason I tried to do this while connected to the databases...all of them. D'Oh! | :doh:

But when I tried to create the new db while only being connected to the instance, it worked. Yeah, I'm new at this...

Thanks for your help Smile | :)

Frederick S Jones
"Get that finger out of your ear! You don't know where that finger's been!"

Generating SQL script

Asad Hussain9-Mar-04 2:51

Asad Hussain

9-Mar-04 2:51

I want to create a script the database that I will be deploying with my application. The problem is that when I generate a SQL script it only scripts the database schema/structure but not the data. There is some data in the database that I wish to include with my script.

How can I script the database so that it includes the entire database including the data?

Thanks.

Re: Generating SQL script

Colin Angus Mackay9-Mar-04 5:34

Colin Angus Mackay

9-Mar-04 5:34

Some suggestions:

You can write the INSERT statements in a script. It can be time consuming if there is a lot of data.

You can create an XML file that contains the data and your deployment package can load that into the database.

You can skip the scripting of the database schema and have the deployment install pre-filled database files and just script the connection of these files to the Master database (I'm assuming your using SQL Server - other databases may differ)

EuroCPian Spring 2004 Get Together[^]
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Oracle Notification Services??

Chen Venkataraman8-Mar-04 9:43

Chen Venkataraman

8-Mar-04 9:43

Does anyone know what's the equivalent of SQL Server Notification Services in Oracle?

I'm primarily looking to maintain the consistency of the data caches obtained from reading the database to be sync'ed with any changes made to the database. i.e, need a way for apps to register interest & be notified when a row changes for e.g. Are there any classes/add-ons in the .NET framework that works with the Oracle database? Any pointers would be appreciated.

Chen Venkataraman

Find right Sql Collation Problem !!

MasudM8-Mar-04 5:49

MasudM

8-Mar-04 5:49

Hi,
I want to save Armenian UniCode Characters in my tables any body knows which collation should I choose for my DataBase ?!!

Thanks in advance .

Hi, everyone. I need help about SQL 2000 server.

jlizardo8-Mar-04 4:01

jlizardo

8-Mar-04 4:01

Hi, everyone. I need help to SQL 2000 server in relation to obtain a backup for any database. I have some trouble using the backup of SQL 2000 server because the SQL agent must be stop first before start the backup. How I can automaticly start a backup without user interaction with the server?

Johnny Lizardo

Re: Hi, everyone. I need help about SQL 2000 server.

Rob Graham8-Mar-04 8:56

Rob Graham

8-Mar-04 8:56

What you want to do is add a scheduled task for SQLServerAgent, and let it do the backup on a scheduled basis. If you have enterprise manager, use the database amintenace wizared to create the backup jobs. If not the TSQL code below will create a weekkly backup schedule, rotating the backup file name and overwiting the oldest. (there is a better way to do this, sql can manage the retention for you, but this is all I had handy)

BEGIN
  BEGIN TRANSACTION
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
  IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
	  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id FROM   msdb.dbo.sysjobs WHERE (name = N'MyDatabase backup1')
  IF (@JobID IS NOT NULL) BEGIN
-- Check if the job is a multi-server job
	IF (EXISTS (SELECT  *
          FROM    msdb.dbo.sysjobservers
          WHERE   (job_id = @JobID) AND (server_id <> 0)))
	BEGIN
	-- There is, so abort the script
		RAISERROR (N'Unable to import job ''MyDatabase backup1'' since there is already a multi-server job with this name.', 16, 1)
		GOTO QuitWithRollback
	END
	ELSE
	-- Delete the [local] job
		EXECUTE msdb.dbo.sp_delete_job @job_name = N'MyDatabase backup1'
		SELECT @JobID = NULL
	END

BEGIN

	-- Add the job
	EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'MyDatabase backup1', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	-- Add the job steps
	EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'sp_Hmx_BackupDb ''MyDatabase'',''D:\Database\DbBackups'',''MyDatabase1.BAK''', @database_name = N'MyDatabase', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	DECLARE @stDate as int	-- default job start date is today
	SET @stDate=0
	DECLARE @today as datetime
	set @today = DATEADD(day,0,GETDATE())		-- get start date
	set @stDate = DATEPART(yy,@today)*10000 + DATEPART(mm,@today)*100 + DATEPART(day,@today)
	-- Add the job schedules
	EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'backup1', @enabled = 1, @freq_type = 4, @active_start_date = @stDate, @active_start_time = 010000, @freq_interval = 14, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	-- Add the Target Servers
	EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  END
  COMMIT TRANSACTION
  GOTO   EndSave
  QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  EndSave:
END
GO

-- Job Script generated by Cruncher - job |MyDatabase backup2|
-- Do Not Edit

BEGIN
  BEGIN TRANSACTION
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
  IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
	  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id FROM   msdb.dbo.sysjobs WHERE (name = N'MyDatabase backup2')
  IF (@JobID IS NOT NULL) BEGIN
-- Check if the job is a multi-server job
	IF (EXISTS (SELECT  *
          FROM    msdb.dbo.sysjobservers
          WHERE   (job_id = @JobID) AND (server_id <> 0)))
	BEGIN
	-- There is, so abort the script
		RAISERROR (N'Unable to import job ''MyDatabase backup2'' since there is already a multi-server job with this name.', 16, 1)
		GOTO QuitWithRollback
	END
	ELSE
	-- Delete the [local] job
		EXECUTE msdb.dbo.sp_delete_job @job_name = N'MyDatabase backup2'
		SELECT @JobID = NULL
	END

BEGIN

	-- Add the job
	EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'MyDatabase backup2', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	-- Add the job steps
	EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'sp_Hmx_BackupDb ''MyDatabase'',''D:\Database\DbBackups'',''MyDatabase2.BAK''', @database_name = N'MyDatabase', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	DECLARE @stDate as int	-- default job start date is today
	SET @stDate=0
	DECLARE @today as datetime
	set @today = DATEADD(day,7,GETDATE())		-- get start date
	set @stDate = DATEPART(yy,@today)*10000 + DATEPART(mm,@today)*100 + DATEPART(day,@today)
	-- Add the job schedules
	EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'backup2', @enabled = 1, @freq_type = 4, @active_start_date = @stDate, @active_start_time = 010000, @freq_interval = 14, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	-- Add the Target Servers
	EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  END
  COMMIT TRANSACTION
  GOTO   EndSave
  QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  EndSave:
END
GO

Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf

Connection Pooling

Mazdak7-Mar-04 21:03

Mazdak

7-Mar-04 21:03

If I set my pool size is to 10,then 10 client connect to my database , what happend if 11th client request to connect to SQLSrever? Should it wait so one of 10th disconnect from databse or new pool create with size 10?

Mazy

"A bank is a place that will lend you money if you can prove that you don't need it." - Bob Hope

Re: Connection Pooling

Rob Graham8-Mar-04 3:00

Rob Graham

8-Mar-04 3:00

It will just create a new connection and add it to the pool. The 11th client will take the full overhead for connection creation.

Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf

Re: Connection Pooling

Mazdak8-Mar-04 3:32

Mazdak

8-Mar-04 3:32

Sorry, I can't get it. New pool create or it will wait?

Mazy

"A bank is a place that will lend you money if you can prove that you don't need it." - Bob Hope

Re: Connection Pooling

Rob Graham8-Mar-04 6:23

Rob Graham

8-Mar-04 6:23

This[^] explains it better than I can.
brief summary: the 11th client in a 10 connection pool will wait for a connection to become free. If the wait exceeds the ConnectionTimeout property, an exception will be thrown.

Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf

Re: Connection Pooling

Mazdak8-Mar-04 7:59

Mazdak

8-Mar-04 7:59

Thanks.

Mazy

"A bank is a place that will lend you money if you can prove that you don't need it." - Bob Hope

unknown extension file

aguest6-Mar-04 10:59

aguest

6-Mar-04 10:59

i is there someone who know a database with the extension ".DTA" ,and which tools
can i open it ????

Re: unknown extension file

josefg6-Mar-04 13:44

josefg

6-Mar-04 13:44

I hope this[^] helps

Josef

.Data Provider for MySQL

josefg6-Mar-04 4:35

josefg

6-Mar-04 4:35

Has anyone ever used the .Data Provider Library[^] by ByteFX[^]? What are your opinions of it?

Thanks,
Josef

Re: .Data Provider for MySQL

Anonymous6-Mar-04 8:51

Anonymous

6-Mar-04 8:51

I tried using it. It only has partial support for VS so don't expect to be able to drag and drop it onto your form, you will be creating the objects by hand in your own code. If you need to support more than 5 connections then I guess that it is a good alternative if the end user does not want to ponyup the money for a decent backend.

The database that I ended up being more interested in was the MaxDB from the MySql folks that supports triggers and stored procedures. I don't beleive that this .Data Provider supports MaxDB as MaxDB is *not* MySql but rather a open source database developed by the SAP people. MaxDB may be distributed free of charge *if* your application is being distrubuted as open source. Otherwise, it has a healty price tag.

Re: .Data Provider for MySQL

josefg6-Mar-04 23:21

josefg

6-Mar-04 23:21

Thanks for the reply mate.

Also has anyone ever used MySQLDriverCS[^]?

Thanks
Josef

Slightly off topic... choosing db server software

Verdant1235-Mar-04 19:18

Verdant123

5-Mar-04 19:18

does anyone have any links or comments on how to choose a database server product?

i have fairly large database in mysql on linux (os could cange) currently, but it is being bogged down, on some pretty serious hardware, and i think doing some optimized stored procedures,perhaps a full text index, etc would help... i have used a lot of the various databases, but haven't done much performance analysis... any thoughts?

it does a lot of transactions, updates, inserts and selects...
but the biggest slowdown is the relatively low percentage of searches.

Re: Slightly off topic... choosing db server software

Andy Harman9-Mar-04 10:23

Andy Harman

9-Mar-04 10:23

Could you give us an example of one slow search, and what indexes you have in place.

Importing Data in Bulk from SQL to Access

Adnan5625-Mar-04 19:07

Adnan562

5-Mar-04 19:07

I imported data in VC 6.0 using DTSPackage from SQL to Access but importing larger data made the proccess slower. Is there anyway that this could be made faster.

I aslo tried Queries, SELECT and INSERT... SELECT was good but INSERT could not paste the data in bulk... can someone help me plzzzzz

Thnx in Advance

AD

Importing Data in Bulk from SQL to Access

Adnan5627-Mar-04 19:34

Adnan562

7-Mar-04 19:34

Y DONT U PEOPLE HELP ME

Cry | :((

PLZZZZZZZZZZZZZZZZZZZZ

HELPPPPPPPPPPPPPPPPPPP

MEEEEEEEEEEEEEEEEEEEEE

AD

using LIMIT within a SELECT statment

Matt Newman5-Mar-04 7:36

Matt Newman

5-Mar-04 7:36

I am trying to limit the number of items returned from a SQL Server (SQL Server 2000). However when I do something like this ASP.NET gets mad at me:

SELECT * FROM SomeWhere WHERE Something = 'SomeCondition' LIMIT 5

Basically I only want the first 5 results returned. This worked in my experience in MySQL. Could anyone show me how to do it in SQL Server 2000?

^Matt Newman

Re: using LIMIT within a SELECT statment

Henrik Stuart5-Mar-04 7:49

Henrik Stuart

5-Mar-04 7:49

SELECT TOP 5 * FROM SomeWhere WHERE Something = 'SomeCondition'

MSSQL doesn't support "LIMIT x, y" but it can be emulated through a somewhat... icky... nested subselection.

--
Henrik Stuart (http://www.unprompted.com/hstuart/)

Re: using LIMIT within a SELECT statment

Matt Newman5-Mar-04 8:02

Matt Newman

5-Mar-04 8:02

Thanks, thats exactly what I needed.

^Matt Newman

Re: using LIMIT within a SELECT statment

Lee Fuller5-Mar-04 9:18

Lee Fuller

5-Mar-04 9:18

Also consider set rowcount n to stop processing of the result set after n rows (and set rowcount 0 to reset). The set rowcount is especially useful in a stored proc where you want to pass n (the # of rows) as a variable from your components/application.

Cheers,

Lee.

Lee H Fuller
lee@fullerdata.com

Last Visit: 31-Dec-99 18:00 Last Update: 29-Apr-24 23:14

Refresh

ᐊ Prev 1...2275 2276 2277 227822792280 2281 2282 2283 2284 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.