Copy Database Fails in SSMS - Invalid Object Name

This is a carry on from my earlier post about generating scripts to recreate
a database.
Instead of generating a database script, I just tried to copy it, using the
wizard in SSMS. It fails in the same way my generated script did earlier.
I have discovered that if I send the log output to a text file instead of
the windows event log I get much more detailed information (I wish it would
say that on the screen, given that event log is the default; it would saved
me hours). I also realise now that Copy Database just scripts the database
and then executes it, which is why I am getting the same problem here as I
was when I scripted the database separately and then ran the script.
The output log says 'invalid object name' when creating a view in the
script, and it picks out an object - another view, for example - that the
first view is dependent upon. Initially, it would complain about views named
'dbo.MyView1', so I removed the 'dbo.' and it fell over on another view. So,
I naturally thought that it didn't like the 'dbo.' prefix. However, once I
had removed all the prefixes, it started to complain about MyView1 again, so
that obviously wasn't the real problem.
The strange thing is that this used to work. I have copied this database
before (perhaps a month ago) without problem, but I can't begin to think
what might have changed to cause it to fall over now. Could it be something
to do with permissions, or context? I connect using the database owner's
login, but I have tried others (all of which are in the sysadmin group), but
to no avail.
Can anyone suggest what could be going on, or how I can get closer to the
real cause?
TIA
CharlesHi
Could it be that you try to create an object (view) that contains another
object (another view) that does not exist yet?
"Charles Law" <blank@.nowhere.com> wrote in message
news:eYgkq4MCIHA.2004@.TK2MSFTNGP06.phx.gbl...
> This is a carry on from my earlier post about generating scripts to
> recreate a database.
> Instead of generating a database script, I just tried to copy it, using
> the wizard in SSMS. It fails in the same way my generated script did
> earlier.
> I have discovered that if I send the log output to a text file instead of
> the windows event log I get much more detailed information (I wish it
> would say that on the screen, given that event log is the default; it
> would saved me hours). I also realise now that Copy Database just scripts
> the database and then executes it, which is why I am getting the same
> problem here as I was when I scripted the database separately and then ran
> the script.
> The output log says 'invalid object name' when creating a view in the
> script, and it picks out an object - another view, for example - that the
> first view is dependent upon. Initially, it would complain about views
> named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another
> view. So, I naturally thought that it didn't like the 'dbo.' prefix.
> However, once I had removed all the prefixes, it started to complain about
> MyView1 again, so that obviously wasn't the real problem.
> The strange thing is that this used to work. I have copied this database
> before (perhaps a month ago) without problem, but I can't begin to think
> what might have changed to cause it to fall over now. Could it be
> something to do with permissions, or context? I connect using the database
> owner's login, but I have tried others (all of which are in the sysadmin
> group), but to no avail.
> Can anyone suggest what could be going on, or how I can get closer to the
> real cause?
> TIA
> Charles
>|||Hi Uri
Well that was what I thought originally, but now I am not so sure. It will
frequently be the case that a view uses another view or another object.
Therefore, the Copy Database operation in SSMS must be able to create
objects in the correct order. Either that, or it mustn't care if an object
does not exist yet. Either way, Copy Database would be useless if it
couldn't work out how to copy a database without hitting this problem,
wouldn't you say?
Charles
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O4nelCNCIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Hi
> Could it be that you try to create an object (view) that contains another
> object (another view) that does not exist yet?
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:eYgkq4MCIHA.2004@.TK2MSFTNGP06.phx.gbl...
>> This is a carry on from my earlier post about generating scripts to
>> recreate a database.
>> Instead of generating a database script, I just tried to copy it, using
>> the wizard in SSMS. It fails in the same way my generated script did
>> earlier.
>> I have discovered that if I send the log output to a text file instead of
>> the windows event log I get much more detailed information (I wish it
>> would say that on the screen, given that event log is the default; it
>> would saved me hours). I also realise now that Copy Database just scripts
>> the database and then executes it, which is why I am getting the same
>> problem here as I was when I scripted the database separately and then
>> ran the script.
>> The output log says 'invalid object name' when creating a view in the
>> script, and it picks out an object - another view, for example - that the
>> first view is dependent upon. Initially, it would complain about views
>> named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another
>> view. So, I naturally thought that it didn't like the 'dbo.' prefix.
>> However, once I had removed all the prefixes, it started to complain
>> about MyView1 again, so that obviously wasn't the real problem.
>> The strange thing is that this used to work. I have copied this database
>> before (perhaps a month ago) without problem, but I can't begin to think
>> what might have changed to cause it to fall over now. Could it be
>> something to do with permissions, or context? I connect using the
>> database owner's login, but I have tried others (all of which are in the
>> sysadmin group), but to no avail.
>> Can anyone suggest what could be going on, or how I can get closer to the
>> real cause?
>> TIA
>> Charles
>>
>|||Charles Law (blank@.nowhere.com) writes:
> The output log says 'invalid object name' when creating a view in the
> script, and it picks out an object - another view, for example - that
> the first view is dependent upon. Initially, it would complain about
> views named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on
> another view. So, I naturally thought that it didn't like the 'dbo.'
> prefix. However, once I had removed all the prefixes, it started to
> complain about MyView1 again, so that obviously wasn't the real
> problem.
> The strange thing is that this used to work. I have copied this database
> before (perhaps a month ago) without problem, but I can't begin to think
> what might have changed to cause it to fall over now. Could it be
> something to do with permissions, or context? I connect using the
> database owner's login, but I have tried others (all of which are in the
> sysadmin group), but to no avail.
The tools can only do as good as they can given the input. And in the input
in this case is the view sys.sql_dependencies, which by its design cannot
be wholly reliable. To wit, references are stored by object_id. So guess
what happens if you try:
create view a as select n = 12
go
create view b as select n from a
go
exec sp_depends b
go
drop view a
go
create view a as select n = 123
go
exec sp_depends b
go
drop view a, b
By dropping a and recreating it, the information that b depends on a is
lost.
One way out is to create all views WITH SCHEMABINDING. Had b in the example
above been defined WITH SCHEMABINDING, it would not be possible to drop or
alter a without first dropping b. Then again, this measure may be to
harsh for you.
If you are on SQl 2005 SP2, there is a way out for you: you can use
sp_refreshsqlmodule to refresh the dependency information for a view
or a procedure. You would have to run it for all your views before you
script.
Another option is to simply maintain a build-script the loads the views in
the right order. Note that this script would not be an SQL script - it
would be a script in VB-script, Perl, C# or even a BAT file that reads the
files with the view definition and load them to SQL Server. The files
would of course be retrieved from the version-control system.
In SQL 2008, currently in beta, they have a new style for reference
information, so that information is held by name and not by object id.
Thereby dependencies are not lost when an object id is dropped. I have
not tested whether the tools actually make use of this features.
Personally, I find that the new feature has several shortcomings compared
to the old reference information, particularly with regards to information
on column level.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

copy crystalreport(.pdf) into sql database

Edited by SomeNewKid. Please post code between<code> and</code> tags.

---

Hi, i'm doing a project on generating a crystalreport and viewing it in a pdf format. I'm using vb.net and sql server.

i managed to general the crystalreport, save it in local directory then view it. but on the other hand i would also like to save the pdf file into my sql database. this is the difficult part and also the part which i'm stuck now. follows are all my code. hope there will be someone who can help me! thanks!

 Dim myConnection As New SqlClient.SqlConnection()
 myConnection.ConnectionString = "server=(local);database=st;Trusted_Connection=yes;timeout =45"
 Dim MyCommand As New SqlClient.SqlCommand()
 MyCommand.Connection = myConnection
 Dim CommandStr As String
 CommandStr = "execute usp_rpt_note @.note_id= '" & Label_NoteID.Text & "'"
 MyCommand.CommandText = CommandStr
 MyCommand.CommandType = CommandType.Text
 Dim MyDA As New SqlClient.SqlDataAdapter()
 MyDA.SelectCommand = MyCommand
 Dim myDS As New Note_DataSet()
 MyDA.Fill(myDS, "usp_rpt_note")
 Dim oRpt As New Note_CrystalReport1()
 oRpt.SetDataSource(myDS)
 oRpt.ResourceName = "Note_CrystalReport1.rpt"
 Dim DiskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = New CrystalDecisions.Shared.DiskFileDestinationOptions()
 oRpt.ExportOptions.ExportDestinationType = CrystalDecisions.[Shared].ExportDestinationType.DiskFile
 oRpt.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.PortableDocFormat
 Dim filename, filenamepdf, filenamexls As String
 filename = UCase(Logon_Name(Request.ServerVariables("LOGON_user"))) & CStr(Year(Now())) & CStr(Month(Now())) & CStr(Day(Now())) & CStr(Hour(Now())) & CStr(Minute(Now())) & CStr(Second(Now())) & CStr(CInt(Int((100000 * Rnd(200)) + 1)))
 filename = filename
 filenamepdf = "D:\Inetpub\wwwroot\report\temp\" & filename & ".pdf"
 DiskOpts.DiskFileName = filenamepdf
 oRpt.ExportOptions.DestinationOptions = DiskOpts
 oRpt.Export()
 ' Database operation variables
 Dim connect As New SqlClient.SqlConnection("Initial Catalog=st;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=True;")
 Dim cmd As New SqlClient.SqlCommand("usp_copy_frm_directory", connect)
 Dim p As SqlClient.SqlParameter
 Dim uploaded_file As String
 Dim strFileUpload As String
 'uploaded_file = Request.QueryString("strFileName")
 strFileUpload = "d:\Inetpub\wwwroot\report\temp\" & filename & ".pdf"
 filename = strFileUpload
 'File/data variables
 Dim imageStream As New IO.FileStream(filename, IO.FileMode.Open)
 'Dim imagestream As New IO.FileStream(Server.MapPath("ToBeUpload.doc"), IO.FileMode.Open)
 Dim imageData(imageStream.Length) As Byte
 Dim FileSize As Integer = CType(imageStream.Length, Integer)
 Dim UploadDate As DateTime = DateTime.Now
 'Dim sContentType As String = imageStream.GetType.ToString 'File1.PostedFile.ContentType
 Dim sContentType As String = "Application/pdf"
 'Dim sContentType As System.IO.Stream = File1.PostedFile.InputStream
 ' Read the image data and close the stream
 imageStream.Read(imageData, 0, imageStream.Length)
 imageStream.Close()
 ' Save the image
 cmd.CommandType = CommandType.StoredProcedure
 p = New SqlClient.SqlParameter("@.ImageData", SqlDbType.Image)
 p.Value = imageData
 cmd.Parameters.Add(p)
 p = New SqlClient.SqlParameter("@.ContentType", SqlDbType.NVarChar)
 p.Value = sContentType
 cmd.Parameters.Add(p)
 connect.Open()
 cmd.ExecuteNonQuery()
 connect.Close()
 cmd.Dispose()
 connect.Dispose()
 Dim strPDF As String
 strPDF = "./temp/" & filename & ".pdf"
 Response.Redirect(strPDF)

Somehow it didnt work! i'm calling the above codes as a function in my pageload.

Hope help is given! thanks!!You seem to be taking the correct approach -- loading the file stream into a byte array and using that byte array as an image parameter for a stored procedure that presumably performs an insert.

Are you receiving any errors at all?

The PageLoad does not seem like the correct place for that code -- the examples I've seen/used put the code in a submit button's click event.

Terri

copy "maint-plans" & paste on prod server ?

again
i created and tested my maintenance plans
on the dev server
now, i must carry them to the prod server ?
are there any way ?
generating scripts, or copy&paste ?
or perhaps, executing some insert
on a system db ?
thanks again
--
atte,
Hernán** now, i must carry them to the prod server ?
( excuse me
the first question is not a question
but an assert )
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:%23CHuydKlGHA.4244@.TK2MSFTNGP02.phx.gbl...
| again
| i created and tested my maintenance plans
| on the dev server
| now, i must carry them to the prod server ?
|
| are there any way ?
|
| generating scripts, or copy&paste ?
|
| or perhaps, executing some insert
| on a system db ?
|
| thanks again
|
| --
| atte,
| Hernán
|
|