we're in a business where customers often ask for a foolproof scheme that even prevents folks with DB privileges from fraudulently inserting, updating or deleting data. The scheme must be so air tight that a judge can be convinced of its reliability.
What are the most effective schemes out there?
Hi,
I've having some baffling problems with a java applicaiton!
I have an application server called WA01 which used to access two tables on an MS SQL 2000 server via a scheme login. The two tables are:
_PurchaseOrderInterface
_PurchaseInvoiceInterface
Both tables were owned by the scheme user, with enough permissions to read & write. The java application on server WA01 could happily read the data within the tables and write a bit flag back to each row.
The MS database has been moved to a new server which no longer allows the java application on server WA01 to access the tables via the scheme login, the only way the java app can view and update the tables is by changing the owner of the tables to dbo. The new server is still MS SQL 2000, with comparible security settings.
The java app keeps complaing of an unknown source when trying to access via scheme, is this a domain trust issue between the two servers? Any ideas would be welcomed. I'm not an SQL expert but have a good grasp of the security structure etc.
Regards,
Alan
I m using sql server 2005
i have got one request ,to apply page level locking on database
can nyone how it is done
i can do that for a single script and for session(transaction isolation level)
but dont know about database level locking scheme
thanks in advance
This morning I found that during the night someone hacked into my remote hosted SQL server 2005 database. Every record in a description field in each of three tables had everything after the first sentence replaced with the same URL. The three pages that used these tables badly infected anyone who viewed them in IE.I had my host restore the 12/30 backup, and I changed all the database and ftp passwords, now I want to be sure my code isn't giving anyone access. I'm using strongly typed datasets. This is my code behind code.Dim sCategory = Request.QueryString("cat")Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapterDim dF As Data.DataTable = ResourceAdapter.GetDataByCategory(sCategory)Dim numRows = dF.Rows.CountLabelCat.Text = sCategoryRepeater1.DataSource = dFRepeater1.DataBind() Is there anything I can do to better secure this? Oh, my connect string is in the web.config file only, but it isn't encrypted. I will be encrypting it today.Diane
View 9 Replies View RelatedHi all.. I've been scouring the forums for about 6 hours to no
avail. This is a really simple question. I'm trying to have
a registration page that lets the user input name, email, desired
username, and password. I want to check the username and email
fields to make sure ppl cannot sign up twice. So from what I've
gathered I have a couple of options:
1) i can set up a unique constraint on the database columns,
2) i can run a select statement before inserting,
3) i can store the whole database column in a variable then search through it.
My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
Hi
I have a problem. Every time I select the first Item of a dropdownlist, it prevents the Insert to the database. There are 8 droplists on the page but the insert is only effected by the First Item of lstTheme selcetion.
My Insert code is here: Any ideas??
Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command
Me.SqlCommandThemeTest.Connection = Me.SqlConnection1
Dim Name As String
Dim values As String
Name = "Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager, date"
values = "lstTheme, lstGuideName, lstGuidePage, lstPageType, lstChangeCategory, lstChangeFrom, txtChangeFrom, lstChangeTo, txtChangeTo, DropDownList1, txtDate"
SqlCommandThemeTest.CommandText = "INSERT INTO dbo.ChangesReport (Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager, date) VALUES (@themeValue, @guideValue, @guidepageValue, @pagetypeValue, @changecategoryValue, @changefromValue, @changefromtextValue, @changetoValue, @changetotextValue, @contentmanagerValue, @dateValue)"
SqlCommandThemeTest.Parameters.Add("@themeValue", lstTheme.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@guideValue", lstGuideName.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@guidepageValue", lstGuidePage.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@pagetypeValue", lstPageType.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changecategoryValue", lstChangeCategory.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changefromValue", lstChangeFrom.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changefromtextValue", txtChangeFrom.Text)
SqlCommandThemeTest.Parameters.Add("@changetoValue", lstChangeTo.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changetotextValue", txtChangeTo.Text)
SqlCommandThemeTest.Parameters.Add("@contentmanagerValue", DropDownList1.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@dateValue", txtDate.Text)
Try
Me.SqlConnection1.Open()
Me.SqlCommandThemeTest.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try
Response.Redirect("WebForm3.aspx")
End Sub
The database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
View 1 Replies View RelatedI have a user AD account SOPUSJSmith for example. I have given this account security admin at the SQL Server level and dbo at the database level. Is there any way to prevent them from using ODBC to pull data into an Access DB or an Excel spreadsheet?
Thanks in Advance - SOX is killing me!
I am trying to deploy a SQL Server Express database to a remote SQL Server 2005 host. I was planning to copy and paste the data from my local tables to the remote tables using Management Studio Express but my ISP (NTL) appears to be blocking port 1433 and preventing me from getting a connection to the remote database.
Will an upgrade to SQL Server 2005 Developer Edition help me? I understand that it has a Backup & Restore Wizard but how does this work? Does it rely on a remote connection and will it fall foul of the NTL port blocking? Or, can the file that the Backup stage produces be copied into the web-based MSSQL Manager, that my web host provides, to restore to the remote database?
Any help would be much appreciated.
I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.
View 2 Replies View RelatedWe are running SQL Server 2014 Web Edition (64-bit). On three occasions now we have had two of our live secondary log shipping databases become unusable through the log shipping process. We ship logs to these databases every five minutes, and generally the process works fine. However, on three occasions in as many months we have suddenly received alerts warning us that the transaction log restores could not be performed on the secondary databases. The SQL Server Error Log revealed the following:
Date,Source,Severity,Message
10/13/2015 04:01:16,spid52,Unknown,Setting database option SINGLE_USER to ON for database 'ObfuscatedName'.
10/13/2015 04:01:17,spid52,Unknown,Starting up database 'ObfuscatedName'.
10/13/2015 04:01:17,spid52,Unknown,Recovery is writing a checkpoint in database 'ObfuscatedName' (8). This is an informational message only.
[code].....
An error occurred during recovery, preventing the database 'ObfuscatedName' (8:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support."
The extract above shows two previously successful restores to the secondary database. Suddenly, at 04:11:16, the database can no longer be started up or restored to. This is despite CHECKDB giving a clean bill of health only five minutes earlier. The result is that the last three lines are then repeated ad infinitum.
The only way we have managed to get around this issue so far is to replace the secondary database with a recent backup of the primary. Once this is in place, any outstanding transaction logs are successfully applied when the log shipping restore job next runs. However, this seems rather drastic.It is rather concerning that our warm-standby databases continue to become corrupted without any apparent explanation from SQL Server.
I can't seem to find a way to do the following:create table part_table (col1 int,col2 datetime) on psX (datename(week,col2))I want to partition based on the week number of a date field.So if I enter in data like the following in my part_table:(1, 1/1/2007) should go into partition 1 for week #1(52, 12/21/2007) should go into partition 52 for week #52 of the yearI tried adding in a computed column, but it says its nondeterministic.
View 4 Replies View RelatedHi,
I'm wondering if SSIS can capture the table schema structure, including the primary keys, foreign keys and indexes applied from the source table to the destination table? My source tables will be coming from AS400/DB2 and I'm using OLEDB Provider for DB2. I would like to automatically generate the table schema of my destination table in SQL Server 2005 (thru SSIS) as similar as my source table from AS400.
Thanks,
May Lanie
I am interested in adding a new row to a table 'Table05' that exists in a SQL Server 2005 database whenever a table 'Table00' in another SQL Server 2000 database has a row added to it. Can someone tell me a way to implement the above solution?
I wanted to confirm that when using Database Mirroring that DDL Updates such as table and stored procedure updates on the principle server are replicated to the mirror server.
I thought I read that non-logged operations would not be replicated to the mirror server. What would be some examples of that. At the moment...you would think every entry in the the database would be saved into a table and that would be replicated.
...cordell...
I have an automated system, which keeps on collecting data and put them in a datafile with data delimited by tabs. This datafile will be sent to OPENROWSET and data gets inserted into the database. Recently I observed a situation in which a character(é) gets inserted as two characters(é) adn that is creating lot of problems. I was able to observe, the character é in UTF-8 gets changed to é in UTF-7 format.
This strange situation comes while inserted data into an nvarchar field. In the format(.fmt) file, which will be sent as one of the arguments to OPENROWSET, this field is specified as "SQLCHAR" datatype with collation SQL_Latin1_General_CP1_CI_AS.
My questions are,
1) In which codeset OPENROWSET reads the datafile? Is there any way to specify to OPENROWSET to read in a specific format(i.e. as Unicode data or non-unicode data etc.)?
2) Can we specify datatype as "SQLCHAR" and collation type as "SQL_Latin1_General_CP1_CI_AS", in format file, for unicode characters? If this is wrong,what is the alternate for Unicode characters?
3) Is there any other place the problem can be?
Thanks in advance.
Hi ,
I had a table which is going to burst, and of course performance issue is come in to place. and now we thinking to apply to partition method into this table.
So is that possible to create a partition scheme and against the existing table? and how is the T-SQL statement will be look like.
Thanks for anyone for giving some clue...
No error is thrown, but the update is not made?  Possibly due to the dreaded inline sql being used?  The data structure for these 2 servers is horrific (but that is a story in itself).  The current structure (which needs immediate change) is each employee all 10 of them, have their own database.  If the userid is  6 or higher they are on server2 if the userid is 5 or below they are on server1.  Then they each have their own table that stores the data.  (A story for another day, but def needs overhaul).  However, here is the sql -- what needs to be altered to work with the current data structure and still get the updates needed?  Or we can scratch the update statements entirely if we can get the correct counts needed.
Declare
@employeeID varchar(50)
,@managerID varchar(50)
,@sql varchar(Max)
,@employee varchar(max)
,@itemsold varchar(max)
[Code] ....
MS-SQL 2005, C# 2.0
I will have many client databases that will be updated. When they are updated I need to transfer some of that data to a central Server database somewhere on the Internet. Note, schemas do not necessarily match.
Transfering the data (Web Services, Remoting) is not a problem.
What I am looking for is a cool, correct, advisable way, for the client database to notify me of an Insert, Update or Delete. I can then initiate the connection to the Server and 'push' the data (maybe pull some back from the Server too).
Obviously Triggers may be a place to start... But I need to know 'external to the database' that an update has happened or can I send the data from within the SQL Server assembly?
Anyone have any ideas or a technique, maybe something new in SQL Server 2005 (all databases will be 2005), for acheiving this? Just so I don't go down the 'wrong' path...
There are some BLOB's involved (1-2 page PDF's), if that makes a difference.
I envisage that the process transfering the data will be a Windows Service running on each client. The connection may not always be available, so some kind of 'to do' list of outstanding data to be transfered is required.
I'm just starting on this, so any pointers would be great, I'm sure it's all been done before ;)
Thanks
Rob.
How to add some more ranges to existing partition schema and function?
Already My table partitioned on date ranges,
6 partitions , each partition contains 6 moths data, so total data is 3 years.
i.e. 1 partition data- from jan2012 to Jun2012
2 partition data- from july2012 to dec2012
3 partition data- from jan2013 to Jun2013
4 partition data- from july2013 to dec2013
5 partition data- from jan2014 to Jun2014
6 partition data- from july2014 to dec2014
After Jan2015 data will go to Primary file group(Default)
Now customer wants to add two more file groups with these partitions ranges, i.e. jan2015 to jun15 and Jul15 to dec15.
File group and ndf file adding is OK, But
how to alter partition scheme and partition function with these additional ranges to existing partition function and scheme?
partitioned table size is 200 GB.
I'm trying to work out a database design to make it quicker for my clientprogram to read and display updates to the data set. Currently it reads inthe entire data set again after each change, which was acceptable when thedata set was small but now it's large enough to start causing noticabledelays. I've come up with a possible solution but am looking for others'input on its suitability to the problem.Here is the DDL for one of the tables:create table epl_packages(customer varchar(8) not null, -- package_type char not null, -- primary keypackage_no int not null, -- /dimensions varchar(50) not null default(0),weight_kg int not null,despatch_id int, -- filled in on despatchloaded bit not null default(0),item_count int not null default(0))alter table epl_packagesadd constraint pk_epl_packagesprimary key (customer, package_type, package_no)My first thought was to add a datetime column to each table to record thetime of the last change, but that would only work for inserts and updates.So I figured that a separate table for deletions would make this complete.DDL would be something like:create table epl_packages(customer varchar(8) not null,package_type char not null,package_no int not null,dimensions varchar(50) not null default(0),weight_kg int not null,despatch_id int,loaded bit not null default(0),item_count int not null default(0),last_update_time datetime default(getdate()) -- new column)alter table epl_packagesadd constraint pk_epl_packagesprimary key (customer, package_type, package_no)create table epl_packages_deletions(delete_time datetime,customer varchar(8) not null,package_type char not null,package_no int not null)And then these triggers on update and delete (insert is handled automaticallyby the default constraint on last_update_time):create trigger tr_upd_epl_packageson epl_packagesfor updateas-- check for primary key changeif (columns_updated() & 1792) > 0 -- first three columns: 256+512+1024insert epl_packages_deletionsselectgetdate(),customer,package_type,package_nofrom deletedupdate Aset last_update_time = getdate()from epl_packages Ajoin inserted Bon A.customer = B.customer andA.package_type = B.package_type andA.package_no = B.package_nogocreate trigger tr_del_epl_packageson epl_packagesfor deleteasinsert epl_packages_deletionsselectgetdate(),customer,package_type,package_nofrom deletedgoThe client program would then do the initial read as follows:select getdate()selectcustomer,package_type,package_no,dimensions,weight_kg,despatch_id,loaded,item_countfrom epl_packageswherecustomer = {current customer}order bycustomer,package_type,package_noIt would store the output of getdate() to be used in subsequent updates,which would be read from the server as follows:select getdate()selectcustomer,package_type,package_no,dimensions,weight_kg,despatch_id,loaded,item_countfrom epl_packageswherecustomer = {current customer} andlast_update_time > {output of getdate() from previous read}order bycustomer,package_type,package_noselectcustomer,package_type,package_nofrom epl_packages_deletionswherecustomer = {current customer} anddelete_time > {output of getdate() from previous read}The client program will then apply the deletions and the updated/insertedrows, in that order. This would be done for each table displayed in theclient.Any critical comments on this approach and any improvements that couldbe made would be much appreciated!
View 4 Replies View RelatedHi all,
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
V
Normally we create a new table for tabler partitioning, like:
Code Snippet
CREATE TABLE [dbo].[Sales]
(
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[SalesDate] [datetime] NOT NULL,
[col_01] varchar(50) NULL,
[col_02] varchar(50) NULL,
..
) ON [ps_Sales](SalesDate)
GO
with
Code SnippetCREATE PARTITION SCHEME [ps_Sales]
AS
PARTITION PFN_Sales TO ([FG_20080501], [FG_20080516], [FG_20080601], [FG_20080616],
[FG_20080701], [FG_20080716], [FG_20080801], [FG_20080816],
[FG_20080901], [FG_20080916], [FG_20081001], [FG_20081016])
But what if I have [dbo].[Sales] existed in db, that have attached to [PRIMARY]?
Alter table?
or I need to create a temp table first?
HiI have have two linked SQL Servers and I am trying to get remote writesworking correctly (fast).I have configured the DB link on both machines to:Point at each others DB.I have security set up to map each others server loginsand Server Options: Collation Compatible, Data Access, RPC, RPC Out, UseRemote Collation all checkedMy problem is that when a SP performsBegin TransactionUpdate Local TableUpdate Remote TableCommit TranIt takes several seconds to complete. (about 7 seconds not acceptable tous)This is due to the remote update - how can I improve the response time?example of a stored procedures that takes timewhere ACSMSM is a remote (linked) SQL Server.procedure [psm].ams_Update_VFE@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strVFEID varchar(16),@strDescription varchar(64),@strVFEVirtualRoot varchar(255),@strVFEPhysicalRoot varchar(255),@strAuditPath varchar(255),@strDefaultBranding varchar(16),@strIPAddress varchar(23)asdeclare @strStep varchar(32)declare @trancount intSet XACT_ABORT ONset @trancount = @@trancountset @strStep = 'Start of Stored Proc'if (@trancount = 0)BEGIN TRANSACTION mytranelsesave tran mytran/* start insert sp code here */set @strStep = 'Write VFE to MSM'updateACSMSM.msmprim.msm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID;set @strStep = 'Write VFE to PSM'updateACSPSM.psmprim.psm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID/* end insert sp code here */if (@@error <> 0)beginrollback tran mytranset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Errorreturn -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''end-- commit tran if we started itif (@trancount = 0)commit tranreturn 0
View 2 Replies View RelatedHello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish
View 3 Replies View RelatedHi all,
This managed application was written to run on a Symbol 3090 Win CE 5.0 scanning device. We are using the symbol provided classes to access the scanning interface, and SQL Compact database on the device to collect the scanned data, and then using merge replication to synchronize scanned data when the device is docked. The problem we have experienced seems to be releated to the performance when inserting and updating records in the database.
We have tested some randomly generated 1000 records and inserting/updatating into a database. At first the time to commit a record increases when the database is flushing into the memory (The flush interval in the connection string property is 10 seconds by default). and then as the database size grows increasing the time to commit every single record which is causing the application to perform slowly as they scan items into the database. However, the device program memory remains consistant as they are scan items. From our tests, I found the time to execute either a update/insert command on 2MB sqlMobile database (upto 10000 records, depending on the size of the columns) is taking nearly 2 to 2 and half seconds to complete. Below is the only code I am executing,
If Not sqlObj.UpdateItem(1061022, itemNo, 1) Then
sqlObj.InsertResultSet(1061022, itemNo, itemObj.Style, itemObj.Color, itemObj.Size, itemObj.Description, 0, 1)
End If
For the notes, I am using prepared updated command and resultset.insert methods to perform update and insert commands into the database.
Any help on this issue is highly appreciated.
Thanks
Ravi.
I have a stored procedure that does the following
BEGIN TRANSACTION OUTERTXN
BEGIN TRANSACTION
Copy records from live to archive
END TRANSACTION with commit or rollback
execute sproc to write audit log with success or fail
IF transaction was committed
BEGIN TRANSACTION
Delete records from live the archive
END TRANSACTION with commit or rollback
execute sproc to write audit log with success or fail
End IF
END TRANSACTION OUTERTXN with commit if both inner transactions were successful or rollback if either failed
If either inner transaction rolled back execute sproc to write audit log saying whole process is rolling back End IfMy problem is that if the outer transaction rolls back then I am losing the two audit records because they are part of the transaction scope. I want these executes to commit even if the master transaction fails.
I have a sql 2005 express database uploaded to my website with important information in it.
Now, I had to make some table change and need to update the online database.
I am not sure if the 'Copy Website' function in Visual Studio 2005 will update the database structure and data or will simply overwrite it.
Does anybody know the answer? If it overwrites it, would you please point me to information on how can I update the database structure and data without ruining it?
Thanks.
Hello,
I will be getting data in either Excel or Access form on a daily basis. I would like to automate the process of converting this (excel or access) data to a table in an existing SQL database. Since this conversion needs to performed on a daily basis, note that I need to update the table that contains data from the day before.
Is it possible to do this and if it is possible, can someone tell me how to do it.
Thanks in advance,
Joe green
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?
I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.
My code:
declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName
Is failing:
Incorrect syntax near '@fileGroupName'.
Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?
Q: If using a variable is not possible, is there another way to specify the default filegroup?
I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)
BEGIN TRANSACTION
CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
(
[ID]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID])
DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
COMMIT TRANSACTION
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen