Do I Have To Reinitialize Snapshot If A New Table Added?
May 2, 2007
Hello,
After creating a publication for Merge Replication, if I need to add or drop a simple table, do I have to reinitialize the snapshot all over again?
Thanks
Ekrem Önsoy
View 1 Replies
ADVERTISEMENT
Dec 20, 2006
I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:
1. The article is dropped from the publication
2. the object is then changed
3. The article is added back to the publication
My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.
Thanks,
Bill
View 8 Replies
View Related
Aug 19, 2006
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies
View Related
Jan 4, 2006
How many triggers can be added in a table?
View 2 Replies
View Related
May 15, 2007
I ran the code below that adds a column to an SQL table.
I refreshed Data Sources, but, the new column does not appear.
I refreshed Server Explorer and the new column does not appear.
I tried running the code again and I got:
"Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once."
so the column did actually get added, but, I can't see it.
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Try
Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Patient.mdf;Integrated Security=True;User Instance=True")
Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)"
Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection)
sqlConnection.Open()
command.ExecuteNonQuery()
sqlConnection.Close()
'Catch
'End Try
Close()
End Sub
End Class
View 5 Replies
View Related
Nov 4, 2005
Is it possible to find out when a particular column was added to any table?
Thanks
View 1 Replies
View Related
Apr 11, 2006
i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05
my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one
1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.
DROP TABLE aMgmt.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT'
GO
any help?
View 2 Replies
View Related
Jul 20, 2005
Can a new field be added to a table that already exists.Thanks in advance.
View 2 Replies
View Related
Feb 15, 2007
Hello,
We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."
This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.
Can someone point me in the right direction?
Thanks,
BCB
View 1 Replies
View Related
Dec 6, 2014
I store files in db in sql server 2008 by filestream. But when a column would be added to table which have filestream, properties of table would be changed. by every things change on table, retrieve files will faced to error. but store process work probably.
and filestream filegroup at following address will be empty. why?
Right click on table --> properties --> storage --> filestream filegroup
View 0 Replies
View Related
Sep 11, 2007
SQL Server 2005 SP2 Error: Script failed for Table 'dbo.TableName'
I keep getting this error when trying to generate a snapshot for transact replication.
Here is the publishing table schema:
CREATE TABLE [dbo].[viMediaPlaylist](
[MemberID] [bigint] NOT NULL,
[xmlPlaylist] [xml](CONTENT [dbo].[viMediaPlaylistCollectionSchema]) NOT NULL,
PRIMARY KEY CLUSTERED
(
[MemberID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
I tried the following:
#1)Create table schema at the subscriber manually, and change package to Keep object as is. Got the same error during snapshot.
#2Filtered replication to NOT include XML column. This worked, and no error was generated, replication was up and running.
So my question is, what is the problem with XML column being replicated?
Any ideas how i can make this work? At this point i'm not sure what the problem is with the xml column, but it seems like ti tries to run schema script, but i'm not asking it to create the schema i already did it myself. Thanks!
Here is a log dump from the distributor:
2007-09-11 16:40:14.56 SQL Command dump
2007-09-11 16:40:14.56 ================
2007-09-11 16:40:14.56 Server: SQL02
2007-09-11 16:40:14.56 Database: Video
2007-09-11 16:40:14.56 Command Text: sys.sp_releaseapplock
2007-09-11 16:40:14.56 Parameters:
2007-09-11 16:40:14.56 @Resource = SQL02-Video_viMediaPlaylis-71
2007-09-11 16:40:14.56 @LockOwner = Session
2007-09-11 16:40:14.56 @DbPrincipal = db_owner
2007-09-11 16:40:15.17 [0%] The replication agent had encountered an exception.
2007-09-11 16:40:15.17 Source: Unknown
2007-09-11 16:40:15.17 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException
2007-09-11 16:40:15.17 Exception Message: Script failed for Table 'dbo.viMediaPlaylist'.
2007-09-11 16:40:15.17 Message Code: Not Applicable
2007-09-11 16:40:15.17
2007-09-11 16:40:15.17 Call Stack:
2007-09-11 16:40:15.17 Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Table 'dbo.viMediaPlaylist'. ---> Microsoft.SqlServer.Management.Smo.UnsupportedVersionException: Either the object or one of its properties is not supported on the target server version.
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.GetTypeDefinitionScript(ScriptingOptions so, SqlSmoObject oObj, String sTypeNameProperty, Boolean bSquareBraketsForNative)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.AppendScriptTypeDefinition(StringBuilder sb, ScriptingOptions so, SqlSmoObject oObj, SqlDataType sqlDataType)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 --- End of inner exception stack trace ---
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.AgentCore.Run()
View 6 Replies
View Related
Jun 7, 2015
I am getting continuous errors in publisher/distributor server
Background: Transactional replication (Push) with one publication (above 30 articles (tables)) and 1 subscriber. Publisher/Distributor is 2008r2 SP2 & Subscriber is 2008r2 sp1
Current Problem:
Repl-Distribution
Message
Replication-Replication Distribution Subsystem: agent PubServername-Production-ProductionReplication-Subscriberservername scheduled for retry (One of the replication job). Query timeout expired.
When I open the replication monitor..In the first tab it is showing performance excellent but when I double click on the publiscation, it is showing errors under distributor to subscriber tab and there are some undistributed commands 5873507, estimated time to apply these commands, based on last performance is 1:06:05:05
Question 1: Do I need to reinitialize the subscriptions? How to find out the problem what went wrong with replication
Question 2: If I want to do any schema changes @publisher do I need to stop the log reader agent and start after the deployment? In my case both publisher/distributor are on the same server (Transactional Replication)
View 0 Replies
View Related
May 8, 2007
How can you tell if a merge subscription is set to reinitialize?
View 2 Replies
View Related
Jan 23, 2008
Hi members!
I was wondering how to reintialize one of many subscriptions under a publication...
I know I can use sp_startpublication_snapshot to reinitialize all the subscriptions but how do you only reinitialize one subscription????
Thanks for your help!!! very appreciated!
thanks,
View 1 Replies
View Related
Jan 17, 2008
We have several sites using SQLCE to merge sync. with an SQL 2005 server, and recently a large change was made at the server end. The sites now only seems to upload their changes, but are unable to download any server changes, and the sync fails with the following message (after about half an hour):
A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)
The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write.
HRESULT 0x8004501E (0)
I would like to reinitialize the subscriptions using the option to upload any subscriber changes first, but I don't know exactly what this will do. Will it simply re-download the entire database, after uploading any recent changes? This is what I want it to do, but am unable to find any help telling me what exactly 'reinitialize subscription' does.
Many thanks in advance!
View 1 Replies
View Related
Mar 27, 2004
I have a few databases that I replicate between NY and Greece. We utilize merge replication on all SQL 2000 servers. My question, is there a quick way to reinitialize replication besides pushing the initial snapshot? I would like to avoid the initial snapshot in the event of a failure due to the amount of time it takes to transfer over our 3mb ATM circuit.
Any help is greatly appreciated.
Thanks,
Michael
View 2 Replies
View Related
Oct 24, 2007
Hi,
I'm setting up Transaction Replication b/w SQL Server 2K and SQL Server 2K5.
I have published Tables, Views and SPs as articles.
When I try to modify the published Stored procedure, the changes are not replicated.
When I Reinitialize the Subscription and start the Snapshot agent, it is copying the changes
made. But all articles are reinitialized again, So it takes huge time to do this.
Is there any way to just reinitialize only the changed article?
Or Is there any work around for this problem?
Than
View 1 Replies
View Related
Jun 20, 2007
Hi
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
View 1 Replies
View Related
Feb 27, 2004
If I have one table in one publication in transactional replication
replication between primary and replicate is broken because subscription is marked as inactive.
If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken
If I do
Reinitialize subscriptions
start the snapshot agent
start the distribution agent
Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.
Any help is appreciated
View 1 Replies
View Related
Oct 24, 2006
Hi
I have a web sync merge publication with multiple anonymous subscribers. Unfortunately two of the subscribers have experienced errors as follows.
###############################################
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199401)
###############################################
Now they successfully sync'd within the retention period so I am baffled as to why the cleanup has occured. Unfortunately I can't seem to find a way to reinitialize just a single subscriber. It seems that the only reinitialize option is across all subscribers, which I do not wish to do as they would loose any changes since last sync.
Can anyone shed any light on this.
Cheers
Rab
View 3 Replies
View Related
Nov 18, 2006
I've been trying to reinitialize the membership database, but something stops me.
I have tried deleting aspnetdb.mdf from the App_Data folder. This works fine, and then a new one is created when I use the asp.net adminstration tool. I can use the server explorer to view the table content (all null).
Although I can open the asp.net configuration page, the problem occurs when I select security. Then I get the error
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: Database 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727asp.netwebadminfilesApp_DataASPNETDB.MDF' already exists. Could not attach file 'C:Documents and SettingsJeff LeeseMy Documentsworldmy workcurrentcounsellorApp_DataASPNETDB.MDF' as database 'ASPNETDB_devjeffleese'.
All this seems to have happened after I modified my connection info in the web.config file:
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;
 AttachDbFilename=|DataDirectory|ASPNETDB.MDF;
 user instance=true;
 Integrated Security=True;
 Initial Catalog=ASPNETDB_devjeffleese;"
providerName="System.Data.SqlClient" />
</connectionStrings>
So, two questions:
1) What are the 
 sections about? They seem to have been inserted automatically at some point, and I wonder if its just formatting info or should I remove them?
2) How can I get security going again. How do I deal with the 'already exists' problem that prevents 'attaching' ?
Would greatly appreciate guidance in how to start over...
View 1 Replies
View Related
Feb 11, 2015
When trying to reinitialize a transactional replication subscription I am unable to select the "Generate the new snapshot now" checkbox. This seems to be happening only with SSMS 2014. When I connect to the same server from SSMS 2008 R2 I am able to select this checkbox.
View 0 Replies
View Related
Feb 6, 2015
I am very new to SQL coming from a SAS background.
I work at a College where I am trying to create a table that basically stores application data so that I can record how many applications we had on a set day.
For example I want to run it say bi-weekly and create a date/time stamp for each row and then the next time it runs it appends to the original table but with the new date/time stamp.
I have managed to create the table and the date/time stamp but I'm not sure how to do the next stage.
This is not the best practice as the table will get "big" quite quickly (although we're only dealing in the hundreds in terms of rows). Ideally I'd have a more transactional database that only appends a record if there is a change i.e. the status of the application has changed from "Received" to "Interview" or similar.
I'm not sure how to copy and paste the code dynamically in this forum but this is what I have got so far (see below).
SELECT
CC.cc_name as Academy,
SUBSTRING(CC2.cc_reference,1,4) as Cost_Centre,
MD2.m_reference as Course,
MD2.m_name as Course_Name,
CAST(MD2.m_start AS DATE) as Start_Date,
[Code] ...
View 4 Replies
View Related
Aug 28, 2014
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
View 5 Replies
View Related
Feb 14, 2006
I need help from you data warehouse / SSIS experts out there! I have a Transaction Fact Table with dollar amounts as the measurements. The grain is one row per transaction. I want to roll this up into a Monthly Periodic Snapshot based on 5 keys. I am having no problem where there is transaction data for each month.
However, the problem I am having is - how do I gracefully insert the Monthly rows for the five keys where there was no activity in the transaction fact table - I am sure there is a slick way to do this with SSIS but I am definitely having a mental block on how to accomplish this. Any help would be appreciated!
View 2 Replies
View Related
Nov 29, 2007
Hi,we are executing the following query in a stored procedure using snapshot isolation level:DELETE FROM tBackgroundProcessProgressReportFROM tBackgroundProcessProgressReport LEFT OUTER JOIN tBackgroundProcess ON
tBackgroundProcess.BackgroundProcessProgressReportID =
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFT
OUTER JOIN tBackgroundProcessProgressReportItem ON
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID =
tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportIDWHERE (tBackgroundProcess.BackgroundProcessID IS NULL) AND (tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.However, for some reasone we get the following exception:System.Data.SqlClient.SqlException:
Snapshot isolation transaction aborted due to update conflict. You
cannot use snapshot isolation to access table 'dbo.tBackgroundProcess'
directly or indirectly in database 'RHSS_PRD_PT_Engine' to update,
delete, or insert the row that has been modified or deleted by another
transaction. Retry the transaction or change the isolation level for
the update/delete statement.The exception specifies that we are
not allowed to update/delete/insert records in tBackgroundProcess, but
the query indeed deletes records from tBackgroundProcessProgressReport,
not from the table in the exception.Is the exception raised because of the join?Has someone encountered this issue before?Thanks,Yani
View 1 Replies
View Related
Apr 25, 2015
Regarding Snapshot replication....
If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....
Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?
View 2 Replies
View Related
Mar 12, 2015
We are facing a weird scenario in which the snapshot is getting corrupted after insertupdate few million records in to a table .
SQL Server 2012
windows server 2008 R2
service pack 1
64-bit OS
View 1 Replies
View Related
Nov 22, 2006
Hi! could you please check my code on adding record on my DB.. Im using SQL Server included on VWD.Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim con As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Request.mdf;Integrated Security=True;User Instance=True" Dim sqlcon As New Data.SqlClient.SqlConnection(con) Dim sqlcmd As New Data.SqlClient.SqlCommand("Insert into uRequest (eticket, ename, edept, edetails, ejobpend, edate) values (@eticket,@ename,@edept,@edetails,@jobpend,@edate)") sqlcon.Open() sqlcmd.Parameters.Add("@eticket", Data.SqlDbType.Char, 10).Value = "11-0010-06" sqlcmd.Parameters.Add("@ename", Data.SqlDbType.Char, 100).Value = User.Identity.Name sqlcmd.Parameters.Add("@edept", Data.SqlDbType.Char, 50).Value = DropDownList1.Text sqlcmd.Parameters.Add("@edate", Data.SqlDbType.DateTime).Value = Date.Now sqlcmd.Parameters.Add("@edetails", Data.SqlDbType.Text).Value = TextBox1.Text sqlcmd.Parameters.Add("@ejobpend", Data.SqlDbType.Text).Value = TextBox2.Text sqlcon.Close() End Sub I've check the Data on my DB but nothing has been added. nwy, how can u create a confirmation message if the record has been successfully added.Thanks and sorry for the trouble.
View 1 Replies
View Related
Dec 14, 2004
Not 100% sure how to do this so I would appreciate some directions. I have 2 tables, Systems and Contacts. A system can have 1 to infinite contacts and contact can have 1 to infinite systems. So I use a 3rd table Sys_Con to add the contact needed for each system.
Now my question is once I add the System and contact how do I know for sure which ContactID to add in my Sys_Con table? Because while I am doing this operation maybe someone can add an other contact. So is there a way for my store proc Add_Contact to return the contactID needed for my store proc Add_Sys_Con ?
TABLE Sys_Con
SystemID int
ContactID int
Table Contact
ContactID int Identity
ContactName
...
Table System
SystemID
...
View 3 Replies
View Related
Jul 23, 2005
hi i have a question how can i delete last added row. I have 2 tables .source and destination . I take a 1 row from source table , do someoperation on it and save to destination table . after succesfull written Iwant to delete added row from source table.. i'm using a coursors. the mainproblem is : is there any function to check which row was last added. Now Iam doing it using select * from destionation where (and necessaryconditions). but if destination table will be 100000000 rows for example ittakes too much time... Is there another possibility to do it ???please helpMarcin Wolkuwolku
View 2 Replies
View Related
Sep 27, 2006
In developing a VWDE project I added 2 columns to the User table in the ASPNETDB.mdf database. I can see the columns in the Data Definition and I can see the values I added when I Show Table Data but I cannot access them with a SQLDataSource control? The SQLDataSource shows all of the columns in that table except the ones I added. Any suggestions? Thanks.
View 2 Replies
View Related
Dec 12, 2003
I would like to retreive the identity field value of a record that was just added to the table.
In other words, I have a form and on submission, if it is a new record, I would like to know the identity value that the SQL Server has assigned it.
This may be overkill, but here is my code to process the form:
Protected Sub processForm(ByVal thisID As String, ByVal myAction As String)
Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
sqlConn.Open()
Dim sql As String
Select Case myAction
Case "save"
If thisID > 0 Then
sql = "update INCIDENT set " & _
"RegionID = @RegionID, " & _
"DistrictID = @DistrictID, " & _
"DateReported = @DateReported, " & _
..CODE...
"WHERE IncidentID = " & myIncidentID
Else
sql = "insert into INCIDENT(" & _
"RegionID, " & _
"DistrictID, " & _
"DateReported, " & _
...CODE...
") " & _
"values(" & _
"@RegionID, " & _
"@DistrictID, " & _
"@DateReported, " & _
...CODE...
")"
End If
Case "delete"
sql = "delete from INCIDENT where IncidentID = " & myIncidentID
Case Else
End Select
Dim sqlComm As New SqlCommand(sql, sqlConn)
sqlComm.Parameters.Add(New SqlParameter("@RegionID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@DistrictID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@DateReported", SqlDbType.NVarChar))
...CODE...
sqlComm.Parameters.Item("@RegionID").Value = ddRegionID.SelectedValue
sqlComm.Parameters.Item("@DistrictID").Value = ddDistrictID.SelectedValue
sqlComm.Parameters.Item("@DateReported").Value = db.handleDate(txtDateReported.SelectedDate)
...CODE...
Dim myError As Int16 = sqlComm.ExecuteNonQuery
'Response.Redirect("incident.aspx?id=" & )
End Sub
The response.redirect at the end of the sub is where I would like to put the identity field value.
View 6 Replies
View Related