New to SQL - Please help

Please excues my ignorance, but I am in no way a SQL guy; so here goes:
I have an accounting package that uses SQL 2000. SQL is creating a backup
file that is getting HUGE (about 2 GB)
The actual data is around a few hundred MB.
Is there a way to reduce the size of the backup file by:
Compacting?
Performaing a backup (that SQL knows about)?
Is there a way to move that file?
Can any of this be done on-line?
Thanks for any help.
Regards;
PBWelcome to SQL
Try doing the following at a time when the database has
few or no users.
Right Click on the Database and select Properties
Select Options Tab
Under Recovery - Model if its not set to simple change it
to simple.
Press OK
Right Click the database and select All Tasks -> Shrink
Database
Select the check box 'Move Pages to beginning...'
Press ok
Go make yourself a drink whilst your waiting ;)
And after if finishes change your 'Recovery - Model' back
to what it was prior to your change.
Peter
>--Original Message--
>Please excues my ignorance, but I am in no way a SQL guy;
so here goes:
>I have an accounting package that uses SQL 2000. SQL is
creating a backup
>file that is getting HUGE (about 2 GB)
>The actual data is around a few hundred MB.
>Is there a way to reduce the size of the backup file by:
>Compacting?
>Performaing a backup (that SQL knows about)?
>Is there a way to move that file?
>Can any of this be done on-line?
>Thanks for any help.
>Regards;
>PB
>
>.
>|||Please forgive my ignorance, but:
Where is the database? It appears that the app that uses SQL is pointing to
a file share on another server. The backup file (BAK) is local, and it is
killing me. As I said, it is 2 GB and growing.
If it is the BAK file that is huge; will that fix my problem? The database
itself is only 132 MB.
TIA;
PB
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:44e901c47fb8$3fa1b9d0$a401280a@.phx.gbl...
> Welcome to SQL
> Try doing the following at a time when the database has
> few or no users.
> Right Click on the Database and select Properties
> Select Options Tab
> Under Recovery - Model if its not set to simple change it
> to simple.
> Press OK
> Right Click the database and select All Tasks -> Shrink
> Database
> Select the check box 'Move Pages to beginning...'
> Press ok
> Go make yourself a drink whilst your waiting ;)
> And after if finishes change your 'Recovery - Model' back
> to what it was prior to your change.
> Peter
>
>
> >--Original Message--
> >Please excues my ignorance, but I am in no way a SQL guy;
> so here goes:
> >
> >I have an accounting package that uses SQL 2000. SQL is
> creating a backup
> >file that is getting HUGE (about 2 GB)
> >
> >The actual data is around a few hundred MB.
> >
> >Is there a way to reduce the size of the backup file by:
> >
> >Compacting?
> >Performaing a backup (that SQL knows about)?
> >
> >Is there a way to move that file?
> >
> >Can any of this be done on-line?
> >
> >Thanks for any help.
> >
> >Regards;
> >
> >PB
> >
> >
> >
> >.
> >|||When releasing space SQL Server doesn't actually free
disk space, its thinking is if its needed it before, it
needs it now, so if you say delete half your database you
will see the same disk space.
The problem though is probably to do with your log file
that will grows a heck of a lot.
To work out where your files are click onto your database
then at the top menu selecy view 'Something (I'm away
from SQL) pad'
It will give you the location and size of your data files
and log files.
These files can be anywhere on your Network.
As for your backup files (.BAK) its considered good
practice to store them on a different server than the one
that SQL is on.
And yes, performing the steps will solve the problem as
the steps will release the unused disk space and so the
backups will not be fooled into thinking there is more to
the files than there actually is.
Anyway its 22:15 here and I am off to do something else.
Good luck
Peter
Peter
>--Original Message--
>Please forgive my ignorance, but:
>Where is the database? It appears that the app that uses
SQL is pointing to
>a file share on another server. The backup file (BAK) is
local, and it is
>killing me. As I said, it is 2 GB and growing.
>If it is the BAK file that is huge; will that fix my
problem? The database
>itself is only 132 MB.
>TIA;
>PB
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:44e901c47fb8$3fa1b9d0$a401280a@.phx.gbl...
>> Welcome to SQL
>> Try doing the following at a time when the database has
>> few or no users.
>> Right Click on the Database and select Properties
>> Select Options Tab
>> Under Recovery - Model if its not set to simple change
it
>> to simple.
>> Press OK
>> Right Click the database and select All Tasks -> Shrink
>> Database
>> Select the check box 'Move Pages to beginning...'
>> Press ok
>> Go make yourself a drink whilst your waiting ;)
>> And after if finishes change your 'Recovery - Model'
back
>> to what it was prior to your change.
>> Peter
>>
>>
>> >--Original Message--
>> >Please excues my ignorance, but I am in no way a SQL
guy;
>> so here goes:
>> >
>> >I have an accounting package that uses SQL 2000. SQL
is
>> creating a backup
>> >file that is getting HUGE (about 2 GB)
>> >
>> >The actual data is around a few hundred MB.
>> >
>> >Is there a way to reduce the size of the backup file
by:
>> >
>> >Compacting?
>> >Performaing a backup (that SQL knows about)?
>> >
>> >Is there a way to move that file?
>> >
>> >Can any of this be done on-line?
>> >
>> >Thanks for any help.
>> >
>> >Regards;
>> >
>> >PB
>> >
>> >
>> >
>> >.
>> >
>
>.
>|||Are you appending each backup to your existing backup set?
If so then your backup file will continue to grow.
If you are using Enterprise Manager to do the backup,
check this:
R-click the database you want to back up.
Select 'All Tasks-->Backup Database' and in the 'General'
tab have a look at the 'Append to media' option - if this
is set then change it to check the 'Overwrite Existing
media' option instead.
Doing this will leave you with only the current backup so
you might want to set up a database maintenance plan
instead to handle your backups.
HTH
John
>--Original Message--
>Please excues my ignorance, but I am in no way a SQL guy;
so here goes:
>I have an accounting package that uses SQL 2000. SQL is
creating a backup
>file that is getting HUGE (about 2 GB)
>The actual data is around a few hundred MB.
>Is there a way to reduce the size of the backup file by:
>Compacting?
>Performaing a backup (that SQL knows about)?
>Is there a way to move that file?
>Can any of this be done on-line?
>Thanks for any help.
>Regards;
>PB
>
>.
>

New to SQL - Please help

Please excues my ignorance, but I am in no way a SQL guy; so here goes:
I have an accounting package that uses SQL 2000. SQL is creating a backup
file that is getting HUGE (about 2 GB)
The actual data is around a few hundred MB.
Is there a way to reduce the size of the backup file by:
Compacting?
Performaing a backup (that SQL knows about)?
Is there a way to move that file?
Can any of this be done on-line?
Thanks for any help.
Regards;
PBPlease forgive my ignorance, but:
Where is the database? It appears that the app that uses SQL is pointing to
a file share on another server. The backup file (BAK) is local, and it is
killing me. As I said, it is 2 GB and growing.
If it is the BAK file that is huge; will that fix my problem? The database
itself is only 132 MB.
TIA;
PB
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:44e901c47fb8$3fa1b9d0$a401280a@.phx.gbl...[vbcol=seagreen]
> Welcome to SQL
> Try doing the following at a time when the database has
> few or no users.
> Right Click on the Database and select Properties
> Select Options Tab
> Under Recovery - Model if its not set to simple change it
> to simple.
> Press OK
> Right Click the database and select All Tasks -> Shrink
> Database
> Select the check box 'Move Pages to beginning...'
> Press ok
> Go make yourself a drink whilst your waiting ;)
> And after if finishes change your 'Recovery - Model' back
> to what it was prior to your change.
> Peter
>
>
> so here goes:
> creating a backup|||When releasing space SQL Server doesn't actually free
disk space, its thinking is if its needed it before, it
needs it now, so if you say delete half your database you
will see the same disk space.
The problem though is probably to do with your log file
that will grows a heck of a lot.
To work out where your files are click onto your database
then at the top menu selecy view 'Something (I'm away
from SQL) pad'
It will give you the location and size of your data files
and log files.
These files can be anywhere on your Network.
As for your backup files (.BAK) its considered good
practice to store them on a different server than the one
that SQL is on.
And yes, performing the steps will solve the problem as
the steps will release the unused disk space and so the
backups will not be fooled into thinking there is more to
the files than there actually is.
Anyway its 22:15 here and I am off to do something else.
Good luck
Peter
Peter

>--Original Message--
>Please forgive my ignorance, but:
>Where is the database? It appears that the app that uses
SQL is pointing to
>a file share on another server. The backup file (BAK) is
local, and it is
>killing me. As I said, it is 2 GB and growing.
>If it is the BAK file that is huge; will that fix my
problem? The database
>itself is only 132 MB.
>TIA;
>PB
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:44e901c47fb8$3fa1b9d0$a401280a@.phx.gbl...
it[vbcol=seagreen]
back[vbcol=seagreen]
guy;[vbcol=seagreen]
is[vbcol=seagreen]
by:[vbcol=seagreen]
>
>.
>

New Id

I am using Microsoft Visual Studios 2005 Pro creating a web form in asp.net with a vb.net code page. I am trying to get the id of a newly inserted record. I am using the sqldatasource insert command to insert the record. Does anybody know how to return the new id. during the insert? Thanks, Any help is appreciated

there are basically 2 ways of doing that.

1. Create ID before you do insert using something like SELECT Max(id) +1 FROM ... Use this approach if you ID field is not set as a identity column

2. If your id field is an identity field then you can write a stored procedure that inserts the record and then you do something like

SET @.ID = scope_identity()

Where @.ID is your output parameter or you can return it as a return value.

|||

Here is a modified sample from Quickstarts tutorial with inline SQL for insert (ContactID is the Identity field of the table):

ProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)

Response.Write("Record Inserted: " + Server.HtmlEncode(e.Command.Parameters("@.ContactID").Value.ToString()) +"<br/>")

EndSub

<asp:GridViewAutoGenerateColumns="False"DataKeyNames="ContactID"DataSourceID="SqlDataSource1"

ID="GridView1"runat="server">

<Columns>

<asp:CommandFieldShowDeleteButton="True"/>

<asp:BoundFieldDataField="ContactID"HeaderText="ContactID"InsertVisible="False"

ReadOnly="True"SortExpression="ContactID"/>

<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>

</Columns>

</asp:GridView>

<asp:SqlDataSourceConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"ID="SqlDataSource1"

runat="server"SelectCommand="select ContactID, ContactName from Contacts"

InsertCommand="INSERT INTO Contacts (ContactName) VALUES (@.contactName);SELECT @.contactID = Scope_Identity()"

OnInserted="SqlDataSource1_Inserted"DeleteCommand="DELETE FROM Contacts WHERE ContactID = @.ContactID">

<InsertParameters>

<asp:ParameterName="contactName"Type="String"/>

<asp:ParameterDirection="Output"Name="contactID"Type="Int32"/>

</InsertParameters>

<DeleteParameters>

<asp:ParameterName="ContactID"/>

</DeleteParameters>

</asp:SqlDataSource>

<br/>

<asp:DetailsViewAutoGenerateRows="False"DataKeyNames="ContactID"DataSourceID="SqlDataSource1"

DefaultMode="Insert"HeaderText="Insert New Contact"Height="50px"ID="DetailsView1"

runat="server"Width="125px">

<Fields>

<asp:BoundFieldDataField="ContactID"HeaderText="ContactID"InsertVisible="False"

ReadOnly="True"SortExpression="ContactID"/>

<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>

<asp:CommandFieldShowInsertButton="True"/>

</Fields>

</asp:DetailsView>

|||

orka777:

there are basically 2 ways of doing that.

1. Create ID before you do insert using something like SELECT Max(id) +1 FROM ... Use this approach if you ID field is not set as a identity column

2. If your id field is an identity field then you can write a stored procedure that inserts the record and then you do something like

SET @.ID = scope_identity()

Where @.ID is your output parameter or you can return it as a return value.

For Number 1, is correct but might no be that good even it will do the job for you.

For Number 2, the code should be like:

1SET @.MyIdentity =@.@.IDENTITY

Where @.MyIdentity in an output parameter of type int.

Here I prefere to not return the identity of the inserted record as an output parameter, I perefere to return it using the Return statment in the stored procedure., like:

1CREATE PROCEDURE MySP23AS45SET NOCOUNT ON67declare MyIdentityint-- as a normal variable in the stored procedure body89Insert Into MyTable (col1, col2)values ('A','B')10SET @.MyIdentity =ISNULL(@.@.IDENTITY,-1)-- if no record is inserted then you will get -1111213Return @.MyIdentity1415SET NOCOUNT OFF16

Good luck.|||

I am trying to do this through the sqldatasource1.insert statement that I have built using the querybuilder. When I try to put in the @.ID as an output or as a return value It says I need to declare the scalar variable. I have already added in the parameters area and it is set to output with the control being the textbox that I want the Id in. But when I add the line "SET @.NewID = scope_identity()" or "SET @.NewID = @.@.identity" then I get an error on both @.NewId and @.SN which is another scalar variable which normally works fine and works fine again after I pull that line out. But I'm not doing it as a stored procedure. Could that be the problem? I don't know about stored procedures. How do I create them?

|||

Carehalf:

I don't know about stored procedures. How do I create them?

Just check my last post, it is a complete running fine example.

Anyway.. here is a very simple example:

1CREATE PROCEDURE MyStoredProcedureName2@.MyInputParameterint,-- input parameter of type integer3@.MyOutputParameterint OUTPUT-- output parameter of type integer45AS67-- write you statments here8-- and here9-- and here as well :)1011-- how to set a value for ourput parameter12SET @.MyOutputParameter = 100-- just for example1314-- how to increment the input parameter by 1 (for example)15SET @.MyInputParameter = @.MyInputParameter + 1161718/* At the end you the @.MyOutputParameter output parameter value19 will be 100 (as per the logic in this simple example20 Did you see how easy it is to created a stored procedure? */21

Good luck.

|||

I understand the stored procedure from looking at your last statement and this one, but I am not sure where to create it

|||

Carehalf:

I understand the stored procedure from looking at your last statement and this one, but I am not sure where to create it

Create you stored procedures in your application/project database.

Example:

USE MyDatabase
GO

Then the CREATE PROCEDURE statment as in my last two posts.

Good luck.

|||

Carehalf:

Create a table in your database and copy the code sample I posted (change the connection string in the code) and check the syntax to work for you if you don't want to create the Stored Procedure.

Here is the table definition:

CREATETABLE [dbo].[Contacts](

[ContactID] [int]IDENTITY(1,1)NOTNULL,

[ContactName] [nvarchar](50)

)

New Dimension

Hi!
I am a newbie, creating new Dimensions, Dimension structures, getting following error running a process; How would I fix this...
"the Dim_Salesperson was not found in the DW database on the server "

In Browser following sentence:
"Either the user, name, does not have access to the DW database or database does not exist."Sorry sorry... problem solved... :-)

new database, set userid and password

I am creating a new application and just created a new database

Application: VB.net 2005/ ASP.net

Database: Sql Server 2005 with four tables

I need to set the userid and password on the database. How do I do that?

I want to be able to create a SQL connection object in my code and I have something like the following:

<CODE>

Dim objconAsNew SqlConnection("server=serverName;uid=;pwd=;database=SomeDatabase")

</CODE>

but the "uid" and the "pwd" are not set on my database. what is an easy way to do this? thanks

This site has complete connection strings in all pop db systems:http://www.connectionstrings.com/

Both Windows Auth and SqlServer Auth has examples in the site.

|||

I recommend that you use windows integrated authentication

Simply make sure that the user identify of the application pool in which your application lives has login rights to your database. Then you don't have to store the password in your config file or your code.

Check this outhttp://weblogs.asp.net/achang/archive/2004/04/15/113866.aspx

Matt

|||

Hi

Yes Matt you are right.

By windows authentication we can achieve this.

First use windows authentication, create user, give him neccessary rights, and mapped this user with your newly created database.

and now in your webconfig provide this username and password to connect this database.

I hope this will helpful u...

|||

Preferably you do not put the username and password in the web.config

If we are using windows integrated authentication, then you want to give permission to the windows account that your application runs under on the database. Then you do not have to worry about password in the config file.

New Data Source showing only default instance

I have two instances in SQL on my machine. When creating a New Data Source in BIDS, I can only see the default instance. I have checked the SAC manager to make sure the second instance allows remote connections and the setting are exactly the same as the default. Any ideas? Thanks.Yeah, but does the second instance run on a different port? Can you type in the second instance's name and have it work?|||I checked the Config Mgr and the port number was blank for the second instance. When I changed the port number to the same as the first instance, I was unable to restart the second instance until I removed the port number. Should I use a different #?|||After changing the ports and changing them back to get the second instance to start again, the test connection in BIDS was successful this time. Go figure. Thanks.