Attach database SQL 2005

There is an error when i attach the database

"TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

E:\DataBase\Test\MyTest_Data.MDF is not a primary database file. (Microsoft SQL Server, Error: 5171)

"

What I do normally when the log file take much space then i detach the database and delete the log file and attach the database file again. It works in SQL 2000. but generating error in SQL 2005.

Your method of truncating the log file works in SQL2000 but it's not so easy in SQL2005 (like nearly everything in SQL2005 compared to SQL2000). To attach the mdf file after deleting the ldf file, you can use the sp_attach_single_file_db stored procedure. It will build a new log file. I have used it several times when moving DBs to a different server. I did not move the log file but rather rebuilt it on the target server.

However, I suggest changing your setup so that the log file does not grow so big which necessitates manual truncation. There are several ways to do this but let me suggest two:

1. Change the Recovery Model in Database properties in Mgmt Studio to Simple. Then the log file will not grow as transactions occur. The problem with this is that recovery is to the time of the last database backup.

2. If the Recovery Model in Database properties in Mgmt Studio is Full, then transactions remain in the log file until the log file is backed up. That is why it grows so big. Apparently you are not backing it up. A database backup does not truncate the log file. A separate log file backup performs a truncation. We recommend that a maintenance plan be set up to back up the log file at least once daily. This is not the database backup but rather a log file backup. Our standard practice for most of our production DBs is to have the maintenance plan back up the DB at about 11pm each night and back up the log files every 2 hours from 8am to 8pm daily. That way the maximum work that would be potentially lost is 2 hours, which is tolerable in most sites. Believe me, we have used it! Although rare, RAID 5 drives do actually fail.

|||

I guess what u are doing to avoid frequent log full is not the correct way of doing it.

i would suggest

.change the recovery type of database from simple to FULL"

.consider taking frequent Transaction log back ups in between full backups.

otherway..instead of detach the database run the log backup with no_log clause to free up trnsaction log space.

Assigning results of a select query to variables...

Hi,

I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.

So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like
"select height, width, depth from products where id=@.idinput"

OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?

This is what I've got so far...

[code]
cmdSelect = New SqlCommand( "GetProd", connstr )
cmdSelect.CommandType = CommandType.StoredProcedure
dbcon.Open()

dbcon.Close()
[/code]

The main prob is just what to connect this record to in order to access the individual fields.

Thx :)Return it as a datereader, then:

Do while dbreader.read()
var1 = dbreader("field1")
var2 = dbreader("field2")...
Loop

You could also return the values as output parameters. This used to be the much faster way in ADO, but I've read that performance is about the same either way in ADO.net. If it matters that much, try both and test it.|||Thx man, that does just the job. :)

Assigning Passwords to Connection Managers using Expressions

Hi Experts here,

Sorry if this query had been raised earlier. While In DTS Packages we retrieve connection details for all Connections in a Package from a table and then assign them to the following global Variables

gv_Source_User, gv_Source_Pwd, gv_Source_DataSource, gv_Source_InitialCatalog

Finally we reassign these variables to repective Connection Properties using Dynamic Properties Task. After Migrating to SSIS though we are able to assign almost all variables to Properties of Connection Managers via Expression except the Password which we donot find in the drop down list in order to assign gv_Source_Pwd.

Is there any work around to assign passwords dynamically?

Many Thanks

Subhash Subramanyam

Hi, Was also thinking if we can go about using script task to do the same ..

|||No, you can't use script. You can assemble the complete connection string (with password) in an expression-based variable and then assign that to the ConnectionString property through an expression on the connection manager.

See www.connectionstrings.com if you need help building the string.
|||

Many Thanks to JayH.

I already had this in my mind yesterday with a bit of confusion - How to work out the code which uses Different connection Managers (what if it was Oracle). i.e. the Connection string built will be of different format .

A question for you is: Though the security can be compromised from Value set to ConnectionString, Why Password property hasn't been added in the dropdown list?

e.g. SQL OLEDB Connection, Data Source=Serverxyz;User ID=sa;Initial Catalog=PUBS_CATALOG;Provider=SQLOLEDB;Persist Security Info=True;

For Oracle, First Manually provide all the details, so that ConnectionString will be built on Connection Manager Properties. This seems really a burden that we must manually verify every derived connectionstring if it can successfully connect.

Many Thanks once again

Subhash Subramanyam

Asp.Net and SqlServer data

Hi

can anybody tell me that:
1)How can i retrieve a binary field (image field) that stored in SqlServer2000 with Vb.Net and save
it again in my hard disk that i have again that file?
2)can SqlServer itself convert image field to file with its store procedures?
3)can sqlserver run an exe file on local computer from its storeprocedure?

Best RegardsHi,
1. see http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=718011.
2. Not that I know of.
3. Yes, use xp_cmdshell, seeMSDN.

Asp.net & File

Hi

i use this code to retrieve my image field from sql server
(my file isn't picture !!!)

***********
Dim MyData() As Byte
MyData = Ds.Tables(mytable).Rows(0).Item(myfield) 'For Example
Dim K As Long
K = UBound(MyData)

Dim fs As New FileStream("c:\mkh.xml", FileMode.OpenOrCreate, FileAccess.Write) 'in this line get error
fs.Write(MyData, 0, K)
fs.Close()

fs = Nothing
Ds = Nothing
************
but it doesn't work and give me this error in ASP.Net(With Vb)
<<Access to the path "C:\mkh.xml" is denied.>
i get this code from microsoft msdn and alot of furoms !!!!
then why doesn't work??If we start with the error you're getting, the reason is simple. The Account which Asp.Net use to perform different actions (it's called '<MACHINENAME>\ASPNET'), is not allowed access to your XML file by your system. Locate 'C:\mkh.xml', right click it, choose Security & Sharing, locate your ASPNET account, and let this account have the rights to 'Read and Run', 'Read' and 'Write'.

Begin there, and come back later when that is solved and you need more help.