I need to set up a job to allow users to restore their databases, on SQL Server 2000 SP3. The idea is that a user inserts a record into a table, identifying the dump they want to load. (They can only restore their own account.) A job picks up this record, restores the database, and notifies the user as appropriate.
My part of this is writing the procedure that the job executes, including the dump restore. Part of that is getting each dump's file groups (data, index, and log) into the proper locations for this server and this user.
Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?
I have a page with over 20 Textbox and DDL controls and an upload in various Divs and Panels (Ajax enabled) that are used for gathering user data. Some of the fields are mandatory and some optional. In the Code behind (VB- I am a complete novice) On the submit button click event, I iterate through the controls in the page and build an array with information from the controls that have data in them, (filtering out the non-filled textboxes, and DDLs). All this works well, and I get an array called 'myInfo' with the columns with the control ID, and control values 'rvalue' (as string), with the number of rows equal to the filled textboxes and DDLs. I then step through the array and build a string with 'name=values' of all the rows in the myinfo array and email this as a message: ThisMessage = "" NoOfControls = myInfo.GetLength(0) For i = 0 To NoOfControls - 1 ThisMessage = ThisMessage & myInfo(i).ID.ToString & "=" & myInfo(i).rvalue.ToString & "; " Next SendMail(email address, ThisMessage) I also want to add this information to a database, appended by the IP address and datetime.now. Dim evdoDataSource As New SqlDataSource() evdoDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("SQLConnectionStringCK").ToString evdoDataSource.InsertCommandType = SqlDataSourceCommandType.Text Dim InsertMessage As String = """INSERT INTO evdoData (" NoOfControls = myInfo.GetLength(0) Dim k As Integer For k = 0 To NoOfControls - 1 InsertMessage = InsertMessage & myInfo(k).ID.ToString & ", " Next InsertMessage = InsertMessage & "IPNo, DateEntered) VALUES (" For k = 0 To NoOfControls - 1 InsertMessage = InsertMessage & "@" & myInfo(k).ID.ToString & ", " Next InsertMessage = InsertMessage & ", @IPNo, @DateEntered" & ")""" evdoDataSource.InsertCommand = InsertMessage I then similarly iterate through and do the insertparameters. Now here is the rub- (My all too often DUH moment!) When I look at the insertmessage in debug (and to be sure- I also show the insert string on a temporary debug label on the page), The insertmessage looks fine: "INSERT INTO EvdData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@FirstName, @Age, @Email, @Phone, @Country, @City, , @IPNo, @DateEntered)" However when the above code(evdoDataSource.InsertCommand = InsertMessage) is run, I get an error - the message with the error is: The identifier that starts with 'INSERT INTO ModelData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@FirsteName, @Age, @Email, @Phone,' is too long. Maximum length is 128 EH? When I actually copy the InsertMessage from the debug window and paste it manually after the command "evdoDataSource.InsertCommand = " It works, and I get the data inserted into the table.. -It would seem that I am probably missing something obvious in my complete "noviceness" . HELP! (oh and thanks a bunch in anticipation) _____________________________________________________________________________________________________________________ Its Easy --------------------------When you know How. Meanwhile Aaaaaaaaah .
I am creating an ad-hoc reporting module for my website. The user may select a list of columns and specify the WHERE for the report. I build the select statement and the where statement and pass the info to a stored procedure. The stored procedure accepts 3 select parameters and a 3 wherestmt parameters. I finally got the SQLDatasource to work by setting the parameter values in the Selecting method of the SQLDatasource. Binding it to a gridview shows me all my values. Now, I am trying to programmatically call the select method of the SQLDatasource so I can get a dataview or datareader to manipulate further. I am getting an null back when I call the select method. Any ideas why? or a workaround? <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssetMgmtRWConnectionString %>"SelectCommand="getAdhocRptData" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"> <SelectParameters> <asp:Parameter Name="strSelect" Type="String" /> <asp:Parameter Name="strSelect2" Type="String" /> <asp:Parameter Name="strSelect3" Type="String" /> <asp:Parameter Name="strWhere" Type="String" /> <asp:Parameter Name="strWhere2" Type="String" /> <asp:Parameter Name="strWhere3" Type="String" /> <asp:Parameter Name="bitDebug" Type="Boolean" /> </SelectParameters> </asp:SqlDataSource>
<VB Code>Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Getting an error that the object is null? why is the above code not returning any data? 'Tried using a datareader and datasource.Dim rptview As IDataReader = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), IDataReader) If rptview.Read Then lblMsg.Text = "Got Rows" End If End Sub Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting 'For this sp, need to set each paramater even if it is not being used. Setting it in the wizard 'does not work. Need to be set programmatically. e.Command.Parameters("@strSelect").Value = "p.field1, p.field2 " e.Command.Parameters("@strSelect2").Value = "s.field1, s.field2" e.Command.Parameters("@strSelect3").Value = "a.field1, a.field2, a.field4, a.field5" e.Command.Parameters("@strWhere").Value = "p.field3 is not null and a.field5 is null" e.Command.Parameters("@strWhere2").Value = "'" e.Command.Parameters("@strWhere").Value = "" e.Command.Parameters("@bitdebug").Value = False End Sub
I'm developing custom SSIS task (control flow component) which offers usert to choose ADO.NET connection.
I want to use this connection in my code and access SQL server.
There is no any problem unless connection is with IntegratedSecurity = true.
But, when user chooses to set username and password, I can not access password because it is not present in the connection string as sensitive information.
Package.DTSProtectionLevel is set to EncryptAllWithUserKey
I need to programmaticaly access to password, but I don't know which class to use from DTS (SSIS) object model.
I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.
The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.
I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.
I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?
// Get the design time instance of the component and initialize the component CManagedComponentWrapper instance = convert.Instantiate(); instance.ProvideComponentProperties();
// Reinitialize the metadata. instance.AcquireConnections(null); instance.ReinitializeMetaData(); instance.ReleaseConnections();
// Iterate through the inputs of the component. IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput(); foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection) { IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY); //instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1); }
Hi, Is there a way to programmatically access the results of an SQLDataSource control select at the time it is binding to a DetailView? There are some fields in the data results I do not want to render to the page, but I still need for other stuff. I know I can programmatically invoke a select method, but since it is selecting anyway for binding to the DetailView it would be better to get all the data in one swoop. Thanks in advance for any assistance you could provide. Best Regards, Brett
I'm trying to create an SSIS package that will do a straight data copy between databases. The problem is that the underlying schema of the origin may change and the requirement is that the transfer be table driven. i.e. the tables that are copied are listed in a table and there should be no human intervention when the schema changes.
I'm moving data between SQL Server and SQL Azure, so backup and restore doesn't work. Has to be an SSIS package.
What's the best way to deal with a changing schema in an SSIS package? Can I delete and rewrite the underlying XML for any tables that change? Do I need to do it programmatically with C#? Do I need to create the package from scratch each time?
I have reviewed the BOL documentation on how to configure Peer-to-Peer replication via T-SQL and how to use the Replication Wizard to implement replication.
What I would like to find out is how do I configure the peer-to-peer replication process to use an existing column on a table that contains a GUID instead of creating an extra column with a uniqueidentifier GUID value. When you use the Wizard each table article has this extra column added to it.
I don't seem to be able to find it in the books-on-line. Can some one point me to the correct article or BOL page.
Hi, I have added a new SQL Server database to my project and I was wondering how to do generate a SQL dump of the database, tables, and queries? I couldn't find any utilities in Visual Studio 2008 Pro. I also couldn't figure out how to create indexes using VS. Thanks!
1. I need to create a replication enviroment. In that i want to snapshot the database as and when i want from my Java program, so that selective tables are updated with latest data. Is there anyway i can do with out creating native replication methods.
2. Is there any way with out creating a snapshot or replication. I need to copy the data from my master database to child database(my reporting database) with out straining much my master database
I get the following error when doing a dump on a 6.5 database. *** Page '697569' of database 'FOREX_UPLOAD' in the database has the PG_DMPTRLR bit set. Page '698121' of database 'FOREX_UPLOAD' in the database has the PG_DMPTRLR bit set. Page '698233' of database 'FOREX_UPLOAD' in the database has the PG_DMPTRLR bit set. *** I have run SP5 as suggested,and can see that more of the db has been dumped than before but still I get the error I get no errors when doing dbcc's. Any ideas???
When I use "DUMP DATABASE", it will help me to backup both database and transaction log. However, it will not help me to clean up the transaction log. Therefore, I am worry that will the following happen?
1. DUMP DATABASE Start backup database ( there are transactions during the backup process ) Finish backup database Start backup transaction Finish backup transaction
2. DUMP TRANSACTION Start backup transaction and clean up the transaction log
So, will the transactions been backuped twice? If yes, will it cause those transaction do twice when I restore the database then restore the transaction?
I need to dump transaction log every night. Before I do that I'd like to check those open transactions and kill the ones not part of sa or SQL server dba's activity. How can I achieve this?
After Dumping a database device, I still find the xxx.DAT files inthe /Data directory. Can these be 'safely' removed/backed up ? They are taking up WAY too much space.
Question 2: After installation and after creating devices & databases, is there any way to reassign the /Data directory on another drive? Either for future devices/databases or existing?
I understand from the beta sql 7 newsgroups that 7 can't restore a 6.5 dump. This is extremely frustrating that MS wouldn't put this type of backword compatibility into 7.
Has anyone found another way to accomplish this? I can't use transfer manager as the 2 servers are on seperate, non-connected boxes. I ftp'd the dump from 6.5 server to a middle-man machine, then copied it to the 7.0 server.
Am I looking at bcp'ing out all 203 tables in my 1GB db then bcp'ing them back in?
Is there a way to dump sql in Access like in Transact SQL or mysqldump? I've gotta convert an Access Database into a MySQL database, it is a small database, but I'd not want to put it in by hand .
If not, I'll just make a PHP script to rip it from a textfile.
I am trying to import a database from a dump file. The dump file is from a sql server db.
I have created the db on another server and want to import the data from the dump file.
I use the bcp like this :
bcp nnfdb.sa.users in x -Usa -P
nnfdb is the name of the database users is the name of a table x is the name of the dump file.
But I get this error : SQLState = S0002, NativeError = 208 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'nnfdb.sa.users'.
also I am sure if I have to run the bcp for each table or if I can import all tables in one bcp run.
****************************************************************************** * * BEGIN STACK DUMP: * 04/25/07 08:32:19 spid 22 * * Exception Address = 00412373 (CTableIsolation::FNeedLockClass + 14) * Exception Code = c0000005 E * Access Violation occurred reading address 00000014 * Input Buffer 132 bytes - * S E L E C T S u p F r o m , S u p T o F R O M S u p p o r t P r * o f i l e s w h e r e S u p P r o f i l e = 3 0 4 5 * * * MODULE BASE END SIZE * sqlservr 00400000 008d2fff 004d3000 * ntdll 77f60000 77fbefff 0005f000 * KERNEL32 77f00000 77f5efff 0005f000 * ADVAPI32 77dc0000 77dfefff 0003f000 * USER32 77e70000 77ec1fff 00052000 * GDI32 78140000 78174fff 00035000 * RPCRT4 77e10000 77e66fff 00057000 * ole32 77b20000 77bd0fff 000b1000 * OLEAUT32 65340000 653dafff 0009b000 * VERSION 77a90000 77a9afff 0000b000 * SHELL32 77c40000 77d7afff 0013b000 * COMCTL32 71710000 71793fff 00084000 * LZ32 779c0000 779c7fff 00008000 * opends60 41060000 41085fff 00026000 * ums 41090000 4109cfff 0000d000 * MSVCRT 78000000 78045fff 00046000 * sqlsort 04000000 0408efff 0008f000 * MSVCIRT 780a0000 780b1fff 00012000 * sqlevn70 410a0000 410a6fff 00007000 * rpcltc1 77bf0000 77bf6fff 00007000 * COMNEVNT 410b0000 410fefff 0004f000 * ODBC32 1f7d0000 1f803fff 00034000 * comdlg32 77d80000 77db1fff 00032000 * SQLWOA 41100000 4110bfff 0000c000 * odbcint 1f8c0000 1f8d5fff 00016000 * NDDEAPI 75a80000 75a86fff 00007000 * WINSPOOL 77c00000 77c17fff 00018000 * SQLTrace 41130000 4117dfff 0004e000 * NETAPI32 4ca00000 4ca40fff 00041000 * NETRAP 77840000 77848fff 00009000 * SAMLIB 777e0000 777ecfff 0000d000 * WSOCK32 776d0000 776d7fff 00008000 * WS2_32 776b0000 776c3fff 00014000 * WS2HELP 776a0000 776a6fff 00007000 * WLDAP32 77950000 77978fff 00029000 * SQLFTQRY 41020000 4103afff 0001b000 * SSNMPN70 41190000 41195fff 00006000 * SSMSSO70 411a0000 411aafff 0000b000 * SSMSRP70 411b0000 411b7fff 00008000 * ENUDTC 74e20000 74e36fff 00017000 * XOLEHLP 74e40000 74e48fff 00009000 * MTXCLU 74e50000 74e5cfff 0000d000 * ADME 74e60000 74e72fff 00013000 * DTCUtil 74e80000 74e89fff 0000a000 * DTCTRACE 74e90000 74e96fff 00007000 * CLUSAPI 7f230000 7f23cfff 0000d000 * RESUTILS 7f250000 7f259fff 0000a000 * MSDTCPRX 74ea0000 74ed0fff 00031000 * DTCCM 74ee0000 74efdfff 0001e000 * msafd 77660000 7766efff 0000f000 * wshtcpip 77690000 77698fff 00009000 * rpclts1 77e00000 77e05fff 00006000 * RpcLtScm 755b0000 755bafff 0000b000 * MSWSOCK 77670000 77684fff 00015000 * rnr20 75600000 7560dfff 0000e000 * RpcLtCcm 756d0000 756defff 0000f000 * security 76e70000 76e81fff 00012000 * msapsspc 756e0000 756f3fff 00014000 * MSVCRT40 779d0000 779e4fff 00015000 * schannel 77400000 7741dfff 0001e000 * MSOSS 75710000 75734fff 00025000 * CRYPT32 75740000 757b4fff 00075000 * MSASN1 757c0000 757cffff 00010000 * msnsspc 757d0000 757eefff 0001f000 * digest 757f0000 757fffff 00010000 * SQLRGSTR 01b60000 01b64fff 00005000 * xpsqlbot 01bf0000 01bf5fff 00006000 * sqlboot 01c00000 01c07fff 00008000 * EntApi 0a000000 0a011fff 00012000 * PSAPI 76bf0000 76bfafff 0000b000 * WININET 01db0000 01e45fff 00096000 * SHLWAPI 01e50000 01eb8fff 00069000 * xpstar 02440000 02470fff 00031000 * SQLWID 02480000 02485fff 00006000 * SQLSVC 02490000 024a8fff 00019000 * odbcbcp 024b0000 024b5fff 00006000 * SQLRESLD 024c0000 024c5fff 00006000 * W95SCM 024d0000 024d7fff 00008000 * SQLSVC 024e0000 024e5fff 00006000 * sqlimage 02590000 025bcfff 0002d000 * * Edi: 00100C70: 000a0100 00030008 00000000 00000000 00100e70 00100e50 * Esi: 00000000: * Eax: 00000000: * Ebx: 00000004: * Ecx: 00000000: * Edx: 00000004: * Eip: 00412373: 007ea6e2 05f61577 01f88318 468b0013 d49f850f 00147e83 * Ebp: 01C8F864: 00000001 00000000 2149a1e4 00000000 00412336 01c8f898 * SegCs: 0000001B: * EFlags: 00010246: 003d0052 00490044 005f004c 00410043 004f004c 005f0053 * Esp: 01C8F854: 00412336 01c8f898 00000000 00000004 2149a1e4 00000000 * SegSs: 00000023: *******************************************************************************
SqlDumpExceptionHandler: Process 22 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Using 'sqlimage.dll' version '4.0.5' Stack Dump being sent to C:MSSQL7logSQL00026.dmp
Sunrise406, creation date and time: 2001/11/16(14:34:00), first LSN: 647599:6555:1, last LSN: 647633:8427:1, striped: 0, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQL7BACKUPSunrise406_tlog_200704250000.TRN'}).
Using 'xpstar.dll' version '2000.28.09' to execute extended stored procedure 'xp_regread'.
Database backed up with following information: Database: Sunrise406, creation date and time: 2001/11/16(14:34:00), pages dumped: 47456, first LSN: 647633:8425:1, last LSN: 647633:8427:1, sort order: 52, striped: 0, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQL7ackupSunriseDailyBackup9pm'}).
I have a sql 2000 database on my local machine that I need to get to a faraway server so he can make one like it on his server. The admin tells me Ineed to make a dump of it and send it to him. I just don't know how to dothat. Can someone help me out?Thanks,Jerry
Tried googling, but I guess I have the wrong terminology for MSproducts.Using PostgreSQL, or MySQL, if I wanted to tranfer a database from onemachine to another, I'd do a "dump" with a single command, such as:mysqldump [databasename] > [path/filename]Then I'd take the file generated and dump it back onto a new machine.What's the process with MS SQL Server? Can someone point me in theright direction to a webpage dealing with this?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
I am having problems and can€™t seem to get a grip as to what is happening. My dump files are filing up fast and I have to go and delete them because they take up too much space.
We started playing with SharePoint 2007 search and think it is the culprit.
Any advise would be great.
Here is a little info form the dump file
BugCheck Dump ===================================================================== This file is generated by Microsoft SQL Server version 9.00.1399.06 upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request. Computer type is AT/AT COMPATIBLE. Bios Version is DELL - 1 Phoenix ROM BIOS PLUS Version 1.10 A04 Current time is 15:10:02 04/24/07. 4 Intel x86 level 15, 3400 Mhz processor (s). Windows NT 5.2 Build 3790 CSD Service Pack 1. Memory MemoryLoad = 57% Total Physical = 4095 MB Available Physical = 1741 MB Total Page File = 5976 MB Available Page File = 3709 MB Total Virtual = 2047 MB Available Virtual = 290 MB ***Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump6891.txt SqlDumpExceptionHandler: Process 100 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I need to make a gigantic collection of excel sheets searchable from a web interface.I need entire rows fetched into a webpage depending on the users query.Mind you I'm not searching the names (filenames) of these excel files, butthe contents inside the excel file. E.g. in a file test.xls, if I search for the word"test1" from the web interface, the entire row in the excel file containingthe word test1 should be displayed.One way out is to dump the contents of the excel sheets into a database, andsearch the database via ASP.For this, can anyone tell me how to efficiently dump the contents of an excelsheet into SQL server?Or if anyone can suggest an alternate strategy for searching this mammothcollection of excel files' contents, if would be great.Thanks a lot.
I would like to export the database(with table schema and existing data). My database is a mdf file and i know that SQL Server 2005 Express Edition cannot do this. I would like to know if there are any FREE tools can export the table schema and existing data to a .sql file. OR SQL Server 2005 can do this? Thank you
I hv worked in 6.5. There we take backup through the command 'Dump Database'(e.g---> "Dump database master to masdump WITH INIT." ). As for as I know, in 7.0 we are taking backup using the command "Backup Database". Is it possible to use "Dump Database" command in 7.0.
When I backup a my transaction log and then run DBCC SQLPERF(logspace) the log shows 12% space used. The backup was run at night long after any transactions had been executed so there should be no active transactions. In SQL 6.5 you could run DBCC CHECKTABLE(syslogs) to clear up the usage inaccuracy is there a similar command in 7.0 to fix the space usage inaccuracy?
My tempdb keeps filling up. A look at sysindexes indicates that all of the space used is in syslogs. Running dump tran (no_log, truncate_only), dump database, and checkpoint give no relief. No transactions are open. Log and data are on shared devices (2MB on master device, 1024MB on a dedicated tempdb device). Why won't my tempdb log dump off? Thanks, Chris
