Re: date pblm in mssql server - Database Discussion Boards

Salil Khedkar2-Feb-06 1:54

2-Feb-06 1:54

I am writing a stored proc. Currently doing a "SELECT INTO" to copy data from one table to a new one. But eventually
a. I want to do it most optimally as the data is gonna be huge
b. Also it should be able to insert data in an existing table.

What SQL stmt/function should I use? D'Oh! | :doh:

Salil Khedkar [^]

Re: Is SELECT INTO optimal to copy data?

Mike Dimmick2-Feb-06 2:32

Mike Dimmick

2-Feb-06 2:32

INSERT INTO [table]
SELECT [col1], [col2], [col3], ...
FROM ...

Stability. What an interesting concept. -- Chris Maunder

-- modified at 8:32 Thursday 2nd February, 2006

Help!!Compacting Database MSDE/SQL

jfk_lili1-Feb-06 22:35

jfk_lili

1-Feb-06 22:35

Hi Guys,

I have not known so much about the SQL Server and MSDE, so just rush to here to get help.

Can any one tell me When records are deleted, is the space immediately compacted or when is it compacted?
and Is the compacting configurable? How to set it up? Since I am using the SQL buddy

For large number of records deleted (eg. Archival), is the database compacted immediately!

Many thanks in advance! I am really appreciate your help.

Re: Help!!Compacting Database MSDE/SQL

Mike Dimmick2-Feb-06 2:38

Mike Dimmick

2-Feb-06 2:38

SQL Server does not generally compact free space. To do so, use the Shrink feature (in T-SQL, DBCC SHRINKDATABASE).

To have this occur automatically, set the 'Auto Shrink' option. You can do this in T-SQL with ALTER DATABASE db SET AUTO_SHRINK ON. MSDE has this option turned on by default.

If Auto Shrink is set, the shrinking operation occurs when there is more than 25% free space in the file. The resulting file will have 25% space free or be the size it was when originally created, if that is larger.

Stability. What an interesting concept. -- Chris Maunder

Re: Help!!Compacting Database MSDE/SQL

jfk_lili2-Feb-06 17:02

jfk_lili

2-Feb-06 17:02

Hi Mike,

Thanks very much for your reply. So the compacting is a default option which already be turned on in MSDE? How about SQL Server, the option is also turned on by default?

Is there any way I can configure this option so that it can be performed when there is 10% free space?

Many thanks for your help!!

Problems with Backup/Restore of SQL server database

nicsquare1-Feb-06 20:44

nicsquare

1-Feb-06 20:44

I have a problem in restoring sql server database in asp.net
I have first written code for full backup and differential backup of database, it is working properly.

For full backup code is :
-----------------
-----------------
objBackUp.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
objBackUp.Database = "tempbakup"
objBackUp.Devices = ""
objBackUp.Files = Server.MapPath("tempbakupFull.zip")
objBackUp.MediaName = Server.MapPath("tempbakupFull.zip")
objBackUp.MediaDescription = "TempBakup Database Backup Full" & Now
-------------------
------------------

For Differential backup :
objBackUp.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Differential
objBackUp.Database = "tempbakup"
objBackUp.Devices = ""
objBackUp.Files = Server.MapPath("tempbakupDiff.zip")
objBackUp.MediaName = Server.MapPath("tempbakupDiff.zip")
objBackUp.MediaDescription = "TempBakup Database Backup Differential" & Now

But when I used wrote following code for restore databse, I got problem

"The volume on device 'c:\inetpub\wwwroot\TestApp\tempbakupfull.zip' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set."

CODE is

oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
oRestore.Database = "TempBakUp"
oRestore.Files = Server.MapPath("tempbakupfull.zip,tempbakupdiff.zip")
oRestore.FileNumber = 1
oRestore.ReplaceDatabase = True
oRestore.LastRestore = False
oRestore.SQLRestore(srv)

oRestore.FileNumber = 2
oRestore.LastRestore = true
oRestore.SQLRestore(srv)

date pblm in mssql server

Vipin.d1-Feb-06 17:27

Vipin.d

1-Feb-06 17:27

hi all,
i want to select all the jobcode from jobmaster

my query is
SELECT txtjcode,convert(smalldatetime,dtlastdate)as last ,getdate()as tday FROM jobmast
it will work fine
it select entries like

test 2006-01-25 00:00:00 2006-02-01 09:51:05.107
US001 2006-01-25 00:00:00 2006-02-01 09:51:05.107
US002 2006-02-10 00:00:00 2006-02-01 09:51:05.107

but when use the following querry,

SELECT txtjcode,convert(smalldatetime,dtlastdate)as last ,getdate()as tday FROM jobmast WHERE convert(smalldatetime,dtlastdate)>= getdate()

it will select only this data
US002 2006-02-10 00:00:00 2006-02-01 09:51:05.107

and didnt select

US001 2006-01-25 00:00:00 2006-02-01 09:51:05.107

actually my query select both
why happens this so?
any soln to this

"i want to select all data where last date >= today date"
plz help urgent Frown | :(

CrazySanker

All I ever wanted is what others have....

Re: date pblm in mssql server

Rana Muhammad Javed Khan1-Feb-06 20:00

Rana Muhammad Javed Khan

1-Feb-06 20:00

One solution is that u should only save DATE portion in the datetime field u can send it as
Format(dtPicker.Value,"mm-dd-yyyy")
then ur query will work fine.

Regards,

Javed

Re: date pblm in mssql server

Colin Angus Mackay1-Feb-06 23:26

Colin Angus Mackay

1-Feb-06 23:26

What type is dtlastdate and why are you converting it? Shouldn't it already be in a datatime (or smalldatetime) format already?

crazysanker wrote:
WHERE convert(smalldatetime,dtlastdate)>= getdate()

It didn't select the row with dtlastdate as 2006-01-25 because that is before now. You asked to select rows with a dtlastdate after now.

>= Greater than or equal to: WHERE dtlastdate is greater than or equal to getdate()

The query is working correctly.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Copy diagram from on db to another on same db

bubberz1-Feb-06 12:02

bubberz

1-Feb-06 12:02

I know you can view the diagram icon, highlight it and click Cut or Copy, but if I go to another database with the same exact schema, how do I "paste" that diagram?

Thanks!

Re: Copy diagram from on db to another on same db

bubberz2-Feb-06 3:37

bubberz

2-Feb-06 3:37

Here's how:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320125

generating database

D.N.1-Feb-06 4:09

D.N.

1-Feb-06 4:09

I am new in database, somebody has sent me a small database via mail its a file, how can i generate a database using it.

i just know that i need to create a database through Enterprize Manager but how to fill it using some file

please guide me step by step

Re: generating database

Colin Angus Mackay1-Feb-06 5:18

Colin Angus Mackay

1-Feb-06 5:18

It depends on what they've sent. If it is a backup file then just restore it. If it is an actual detached database then just attach it.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: generating database

D.N.1-Feb-06 5:49

D.N.

1-Feb-06 5:49

how can i attach it

Re: generating database

Colin Angus Mackay1-Feb-06 5:50

Colin Angus Mackay

1-Feb-06 5:50

In Enterprise Manager right-click the server, All Tasks, Attach Database... (or somesuch menu option - I don't have EM up right now so I can't check)

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

date acessing problm in msssql server 2000

Vipin.d31-Jan-06 18:50

Vipin.d

31-Jan-06 18:50

date acessing pblm

SELECT txtjcode,dtlastdate,getdate() FROM jobmast

it will select

test 2006-01-25 00:00:00 2006-01-25 11:00:35.903
US001 2006-02-01 00:00:00 2006-01-25 11:00:35.903

when i query

SELECT txtjcode,dtlastdate,getdate() FROM jobmast WHERE intstatus=1 and dtlastdate <= getdate()

US001 2006-02-01 00:00:00 2006-01-25 11:00:35.903

that do not select txtjcode "test"

actually time difference is there. ineed to select all upto todays date independend of todays time

what i do

Smile | :)

CrazySanker

All I ever wanted is what others have....

Re: date acessing problm in msssql server 2000

Colin Angus Mackay31-Jan-06 20:27

Colin Angus Mackay

31-Jan-06 20:27

What is the value of intstatus for the test row? You don't select that, but you do filter on it.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: date acessing problm in msssql server 2000

idonthavename31-Jan-06 22:37

idonthavename

31-Jan-06 22:37

The following link may give u the solution. http://www.intellectualbrains.com/DiscView.aspx?mid=163&forum_id=6&[^]

kum

Databinding 2 Datagridviews

elraton31-Jan-06 18:37

elraton

31-Jan-06 18:37

Hi.

I posted this on the C# forum, but probablly this is a better place.

I'm having a little trouble getting this thing to work.

I have two tables Houses and People

Every "Person" belongs to a House, but only one per house is the "OwnerOfHouse".
So I made a colum named "HouseOwner" in the "House" table, which has the PersonID. And every person has a "HouseID_FK" which tells them to which house they belog.

SO here is the thing. I'm working with ADO.NET 2.0, and using BindingSource. So I have a BindingSource that has my Houses in a nice DataGridView, and when I click on any of them, I have another DataGridView which displays the people that live there.
That works Fine.

Now, when I chose one of the inhabitants, and press a button, His ID is updated on the Houses table, and I have a reference that tells me he is the Owner. So far so good.

What I want to do is that when I choose a House in my first DataGridView, The Owner is selected automaticlly in the People DataGridView. So far I've tried everything I could thinf of, but it allways selects the first element (the first person on the list).

Thanks in advance.

SQL Procedures & SourceSafe

Len Miller31-Jan-06 11:36

Len Miller

31-Jan-06 11:36

** HELP **

When one wants to version control their stored procedures, they can do this fairly easily through the VS.NET IDE.

As an FYI - If you care - Here is an old sample of how to do this
http://weblogs.asp.net/fmarguerie/archive/2003/02/24/2880.aspx

OK, all is good, now, if one MOVES the VSS database (in this case, to a different server), everything seems to go haywire.
(Using VS .NET 2003, SourceSafe version 6.0d, SQL Server 2000)

The problem seems to be rooted in the fact that there is no place to tell .NET IDE the new location of your srcsafe.ini file!

The FIRST time around, (after some preliminary tasks), in server explorer window, you simply right-click on the "+-Stored Procedures" entry and select "Add To Source Control". A nice dialog comes up and you enter the location of your SS database, and your project name, etc.
All SP's are added to VSS and bam! you are good to go.
When you go here to check in and out, refresh, you are asked for a SourceSafe login, etc.

Now, the VSS database has moved. So, when you try to log in to VSS it fails (just keeps popping up the login dialog until you click cancel). When you right click, Add and Remove from source control are disabled.

I have tried clearing out the database, unchecking and re-checking "Enable Version Control"
in Tools->Options->Database Tools->Server Explorer... nothing works!

Somewhere, the OLD location of this VSS database is being stored and I need to change it.

Any Help?????

Thanks,
-Len Miller

"If I had eight hours to chop down a tree, I'd spend six sharpening my axe."
-Abraham Lincoln

Re: SQL Procedures & SourceSafe

S Douglas1-Feb-06 0:27

S Douglas

1-Feb-06 0:27

Have you looked in the registry?

Looks like this tool might help unlink the source safe lock so you can reconnect to the new db.

A small tool to remove SCC information of a VS2003.NET project file[^]

Re: SQL Procedures & SourceSafe

Len Miller1-Feb-06 3:24

Len Miller

1-Feb-06 3:24

Thanks for the reply "S".

I did search the registry (I looked for srcsafe.ini & part of my VSS db path) nothing found except sone history entries.

The article & tool link you provided is cool, but it will not help. It only removes VSS references from a particular VS solution. The Server Explorer is independent of any solution. I do not have a particular solution that is at issue, it is when I navigate to "Stored Procedures" under my server explorer window.

Thanks again.

Thanks,
-Len Miller

"If I had eight hours to chop down a tree, I'd spend six sharpening my axe."
-Abraham Lincoln

Specifying DateTime value in a query

Luis Alonso Ramos31-Jan-06 7:37

Luis Alonso Ramos

31-Jan-06 7:37

Hello,

I'm trying to run this code in SQL Server 2005:

DECLARE @UserId int
EXEC User_Insert @UserId = @UserId OUTPUT, @FirstName = N'Luis Alonso', @LastName = N'Ramos',
    @PhoneNumber = N'whatever, @Email = whatever', @UserName = N'luisr',
    @UserType = 5, @BranchId = NULL, @CreationUserId = 0, @CreationDate = N'31/01/2006'

EXEC User_SetPassword @UserId = @UserId, @PasswordHash = N'356A192B7913B04C54574D18C28D46E6395428AB'

and I'm getting this error:

Msg 8114, Level 16, State 1, Procedure User_Insert, Line 0<br />
Error converting data type nvarchar to datetime.

The problem is with the last parameter, CreationDate. I don't know how to pass a literal date value (it works fine when called from code using parameterized queries.) If I use SQL Management Studio to generate the query for me, it's generated as above, and it won't work.

Even using @CreationDate = GETDATE() won't work, but with a different error.

Ideas?

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

-- modified at 13:37 Tuesday 31st January, 2006

Re: Specifying DateTime value in a query

Farhan Noor Qureshi31-Jan-06 8:16

Farhan Noor Qureshi

31-Jan-06 8:16

Try this.

<br />
DECLARE @UserId int<br />
DECLARE @dtNow DateTime<br />
Set @dtNow = GetDate()<br />
<br />
EXEC User_Insert @UserId = @UserId OUTPUT, @FirstName = N'Luis Alonso', @LastName = N'Ramos',    @PhoneNumber = N'whatever, @Email = whatever', @UserName = N'luisr',    @UserType = 5, @BranchId = NULL, @CreationUserId = 0, @CreationDate = @dtNow<br />
<br />
EXEC User_SetPassword @UserId = @UserId, @PasswordHash = N'356A192B7913B04C54574D18C28D46E6395428AB'<br />

Farhan Noor Qureshi

^{if (this == this) thow this;}

Embedded sql Visual c++.net and mysql and ODBC

JerryMcguire31-Jan-06 7:02

JerryMcguire

31-Jan-06 7:02

Hi,

I have an c++application where the user enters the value of a"primary key" in a
Table via embedded sql. Is there a way of checking if the key already exists in the table. Thus I could show a message in the application to user that the value already exists. There for he would have to enter another one ?

Foxpro or mySql

Gerry

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