Hope this makes sense!
Thanks in advance
Rudy, we are investigating the same process for our cubes. We would like to apply partitioning by day to the SSAS cube via an ETL job that runs after the load (or when appropriate, I'm not a pro at partitioning).
Were you successful with this task and do you have an example or better yet an example package that I could base our process on? Thanks for your time!
Romeo
Hope this makes sense!
Thanks in advance
Rudy, we are investigating the same process for our cubes. We would like to apply partitioning by day to the SSAS cube via an ETL job that runs after the load (or when appropriate, I'm not a pro at partitioning).
Were you successful with this task and do you have an example or better yet an example package that I could base our process on? Thanks for your time!
Romeo
Check out the sysprocesses table:
select * from master..sysprocesses
where dbid = db_id('master')
You might also want to look at @.@.SPID and sp_who in Books Online.
Simon
I have a data file that I am trying to BCP IN into a table. The table has an IDENTITY column as PK.
If the BCP process fails in between (there would be about 500,000 records in the data file) and I restart the process, would the records be overwritten into the table or deleted and re-isnerted if the record already exists? I noticed that it does not create duplicates. So either its over writing the existing records or ignoring them and inserting the new records. I did not find any documenttion regarding this in BOL.
Thanks.
I did a test, found that BCP always tries to append data from file to table. You can use-bbatch_size to specifies the number of rows per batch of data copied. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is copied in one batch.
And there is a -E switch, which decides whether using IDENTITY values from file, or generate a new unique IDENTITY. So when you use BCP with -E option to import data from file that may bring duplicate key values, an error will be raised saying vilation primary key constraint. You can take a look at this SQL SDK article:
http://msdn.microsoft.com/library/en-us/coprompt/cp_bcp_61et.asp?frame=true
|||I do use the -E option. I have a stored proc that I run from multiple Query Analyzer windows parallely to transfer the data faster.
Here's how the command builds up:
SET @.str = 'bcp ' + @.db + '.dbo.' + @.table + ' in "' + @.Fileloc + '" -f"C:\mount\backup21\DocPhrase.fmt" -S' + @.server + ' -T -E '
When I run multiple processes, occassionally one of them fails with locking issue. So I re-run the stored proc. I noticed it doesnt complain about existing records. And there are no duplicates too if I run the bcp in multiple times. So I was wondering whether BCP ignores if the record already exists in the table or overwrites it.
|||
ndinakar:
When I run multiple processes, occassionally one of them fails with locking issue. So I re-run the stored proc. I noticed it doesnt complain about existing records. And there are no duplicates too if I run the bcp in multiple times. So I was wondering whether BCP ignores if the record already exists in the table or overwrites it.
Really strange, in my testing BCP always tried to append rows, not ignore, nor overwritel; and if run same bcp multiple times with -E option, the duplicates vilation error will be raised. I use Profiler to trace SQL server, and found actually BCP calls 'insert bulk' command, not update.
Are you sure your identify column is primary key of the table? How about add a hint to the bcp command as:
-h CHECK_CONSTRAINT
Or you canget the Profiler trace to see what happens why the BCP runs to your SQL server.
|||Yes you are right. It does throw an error if I try to BCP in the same file again if the BCP in was successful the first time.
I believe what was happening in mi situation was, since the entire bcp in is treated as one transaction, if for some locking reason it fails none of the records are inserted. That is why when I re run the process, it does not complain and nicely inserts the records. The records were never in in the first place.
|||It should be as you say, something related to sql transaction in BCP. And there is a-bbatch_size option that can be used to specifies the number of rows per batch of data copied. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. I'm trying to retrieve an image from my ms sql server 2005, and i'm using VS2005...however, i have the following error during the compilation process
Code in webform2.aspx.vb:
Partial Class webform2
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class
System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Incorrect syntax near '='."
Number=102
Procedure=""
Server="DCPRJ007\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at webform2.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite7\webform2.aspx.vb:line 10
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Are you sure you are getting value in the Request.QueryString("id") ? If not, your select statement will have an incorrect syntax and so is the exception. keep a break point and debug to find out.
Thanks
||| Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class
i found that...this line is highlighted during debugging, what is problem with this statement?
error message is : Incorrect syntax near '='.
thx a lot!!
Are you sure, you have some value in Request.QueryString("id") ?
Thanks
|||
gaze:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class
i found that...this line is highlighted during debugging, what is problem with this statement?
error message is : Incorrect syntax near '='.
thx a lot!!
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
try
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id='" & Request.QueryString("id") & "'", cnn)
means ... use single cote before and after the your id.
If you think this post helped you marked as read.
--
CREATE TABLE agls1
(fyear char(4) NULL ,
fprefix char(3) NULL ,
fvcno char(20) NULL ,
fdate datetime NULL ,
fid char(15) NULL ,
fiddate datetime NULL ,
fdesc char(60) NULL ,
facc char(12) NULL ,
fval decimal(18, 2) NULL ,
fcrc char(5) NULL ,
fsub char(1) NULL ,
fmaster char(9) NULL ,
fcode char(15) NULL )
CREATE CLUSTERED INDEX a ON agls1 (fyear, fprefix,
fdate, fvcno)
CREATE INDEX b ON agls1 (fyear, facc, fdate,
fprefix, fvcno)
CREATE INDEX c ON agls1(fyear,fsub, fmaster, fcode)
insert into agls1
( fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
values
( '2004','A','B','123','inv','20040101','01','111' )
--query-1
select * from agls1
where fyear = '2004' and fprefix = 'inv' and
fdate = '20040101' and fvcno = '01'
--query-2
select * from agls1
where fyear = '2004' and facc = '111' and
fdate = '20040101' and fprefix = 'inv' and fvcno = '01'
--query-3
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'
--
The execution plan shows that the index a
is always used for all 3 select queries above.
I have 3 questions for you :
a. Why does SQL not choose index b for query-2 ?
Why does SQL not choose index c for query-3 ?
b. Is it right that query-2 does not benefit from
index b and query-3 does not benefit from index c ?
c. How does SQL choose an index for a process ?
Could anyone help me
Thanks in advance
Anita Hery
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Anita (anonymous@.devdex.com) writes:
> The execution plan shows that the index a
> is always used for all 3 select queries above.
> I have 3 questions for you :
> a. Why does SQL not choose index b for query-2 ?
> Why does SQL not choose index c for query-3 ?
There is only one row in the table, that makes the test somewhat
meaningless.
But the general is that there is always a tradeoff whether to use a
non-clustered index or not. When SQL Server finds row through a non-
clustered index, it has to go to the data page and get data requested
in the query which is not present in the index. This means that it
can be more expensive to use the index than to scan table, if the
optimizer estimates that the index will find many rows.
> b. Is it right that query-2 does not benefit from
> index b and query-3 does not benefit from index c ?
No, that depends on how the data looks like. Let's first take query/index
c. Say that there is over a million rows with year = 2004. In this case,
without the index, query c would have to scan all those rows in the
clustered index, whereas with the non-clustered index can find the
matching rows faster. But if there are say, 10000 rows that matches
query c, I would execpt SQL Server to use the clustered index.
As for index b, there are situations where this index could help, but
in this case, there must be many duplicates in the clustered index,
so that you actually make the query significantly more precise by adding
that extra column.
> c. How does SQL choose an index for a process ?
SQL Server uses a cost-based optimizer which makes its decisions from
statistics about the table column. Therefore the same query can get
different query plans with different data.
There is material in Books Online you can study. I can also recommend
Kalen Delaney's "Inside SQL Server 2000", which covers this topic
in detail.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,
Thanks a lot for the lucid explanation.
Very helpful.
I will continue testing using minimum amount of rows
to see that SQL Server uses index c for query-3.
The data must easily force SQL Server to use
index c. If you do not mind, could you advice me
how data looks like that I should create.
Anita Hery
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
Anita wrote:
> I have just tested 3 queries using QA. The complete
> test information :
> --
> CREATE TABLE agls1
> (fyear char(4) NULL ,
> fprefix char(3) NULL ,
> fvcno char(20) NULL ,
> fdate datetime NULL ,
> fid char(15) NULL ,
> fiddate datetime NULL ,
> fdesc char(60) NULL ,
> facc char(12) NULL ,
> fval decimal(18, 2) NULL ,
> fcrc char(5) NULL ,
> fsub char(1) NULL ,
> fmaster char(9) NULL ,
> fcode char(15) NULL )
> CREATE CLUSTERED INDEX a ON agls1 (fyear, fprefix,
> fdate, fvcno)
> CREATE INDEX b ON agls1 (fyear, facc, fdate,
> fprefix, fvcno)
> CREATE INDEX c ON agls1(fyear,fsub, fmaster, fcode)
> insert into agls1
> ( fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
> values
> ( '2004','A','B','123','inv','20040101','01','111' )
> --query-1
> select * from agls1
> where fyear = '2004' and fprefix = 'inv' and
> fdate = '20040101' and fvcno = '01'
> --query-2
> select * from agls1
> where fyear = '2004' and facc = '111' and
> fdate = '20040101' and fprefix = 'inv' and fvcno = '01'
> --query-3
> select * from agls1
> where fyear = '2004' and fsub = 'A' and fmaster = 'B'
> and fcode = '123'
> --
> The execution plan shows that the index a
> is always used for all 3 select queries above.
> I have 3 questions for you :
> a. Why does SQL not choose index b for query-2 ?
> Why does SQL not choose index c for query-3 ?
> b. Is it right that query-2 does not benefit from
> index b and query-3 does not benefit from index c ?
> c. How does SQL choose an index for a process ?
The last question in particular has lots of book chapters on it.
The fact is that the table is so small that no index can really
help much. A blind table-scan is fastest with a one-row table.
To see more intuitive index use, you should probably test with
a table having thousands of well-distributed rows.
Joe Weinstein at BEA
> Could anyone help me
> Thanks in advance
> Anita Hery
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> I will continue testing using minimum amount of rows
> to see that SQL Server uses index c for query-3.
> The data must easily force SQL Server to use
> index c. If you do not mind, could you advice me
> how data looks like that I should create.
You can always use an index hint to convince SQL Server to use an index:
SELECT * FROM tbl WITH (INDEX = c)
To make it simple you should have fyear = 2004 in all rows you create.
But the values in fsub, fmastser and fcode should vary.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949BF26CFB825Yazorman@.127.0.0.1...
> Anita (anonymous@.devdex.com) writes:
> > I will continue testing using minimum amount of rows
> > to see that SQL Server uses index c for query-3.
> > The data must easily force SQL Server to use
> > index c. If you do not mind, could you advice me
> > how data looks like that I should create.
> You can always use an index hint to convince SQL Server to use an index:
> SELECT * FROM tbl WITH (INDEX = c)
Just to jump in, you can of course do that. However (and this is to Anita,
not Erland since I know he's aware of this), it's generally a fairly bad
idea to force an index hint, since your data may later change in such a way
to make the index less useful.
I'd recommend finding some of the papers Kalen Delany has written on this
subject as it may help.
> To make it simple you should have fyear = 2004 in all rows you create.
> But the values in fsub, fmastser and fcode should vary.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I have inserted 810 rows by following Erland's advice.
With these rows, SQL Server uses index c when executes
query :
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'
Thanks again to all of you that sent the replies
Anita Hery
Note :
Below is my insert test :
declare @.sub as int, @.master as int, @.code as int
set @.sub = 0 --max 15
set @.master = 0 --max 6
set @.code = 0 --max 15 digit
lsub:
set @.sub = @.sub + 1
set @.master = 0
set @.code = 0
lmaster:
set @.master = @.master + 1
set @.code = 0
lcode:
set @.code = @.code + 1
insert into agls1
(fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
values
('2004',str(@.sub,1),str(@.master,9),
str(@.code,15),'inv','20040101','01','111')
if @.code < 15 goto lcode
if @.master < 6 goto lmaster
if @.sub < 9 goto lsub
select * from agls1
where fyear = '2004' and fsub = 'A' and
fmaster = 'B' and fcode = '123'
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Using formview control, I'm trying to insert a record. In the process of inserting, I want to save the categoryid from a shared class. The code runs fine but categoryid gets saved as null...Any pointers?? [When I display, it shows the value]
thanks
protected void savebutton_click(object sender, EventArgs e)
{
//this statement runs fine under debug...but values do not get saved?
SqlDataSource1.InsertParameters.Add("@.category", SharedValues.category.ToString());
SqlDataSource1.Insert();
}
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="itemid" DefaultMode="edit" OnDataBound="FormView1_DataBound">
<EditItemTemplate>
Title:
<asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>'>
</asp:TextBox><br/>
Description:
<asp:TextBox ID="descriptionTextBox" runat="server" Text='<%# Bind("description") %>' Rows="10" TextMode="MultiLine" Width="500px" Height="166px">
</asp:TextBox><br/>
<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
Text="Update">
</asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</EditItemTemplate>
<InsertItemTemplate>
Title:
<asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>'>
</asp:TextBox><br/>
Description:
<asp:TextBox ID="descriptionTextBox" runat="server" Text='<%# Bind("description") %>' Rows="10" TextMode="MultiLine" Width="500px" Height="166px">
</asp:TextBox><br/>
<asp:label ID="categoryLbl" runat="server" Text='<%# SharedValues.category %>'></asp:label>
<br/>
<div class="actionbuttons">
<Club:RolloverButton ID="GreenRolloverButton3" CommandName="Insert" Text="Save"
runat="server" OnClick="savebutton_click" />
<Club:RolloverLink ID="GreenRolloverLink2" Text="Cancel" runat="server" NavigateURL="Classifieds.aspx" />
</div>
</InsertItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"
InsertCommand="insert into tads (title,[description], categoryid) values (@.title,@.description, @.category)"
SelectCommand="Select title, [description], categoryid from tads whereitemid=@.itemid"
UpdateCommand="update tads set title = @.title, [description] = @.description where itemid = @.itemid" >
<SelectParameters>
<asp:QueryStringParameter Name="itemid" QueryStringField="itemid" Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="title" Type="String" />
<asp:Parameter Name="description" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="title" Type="String" />
<asp:Parameter Name="description" Type="String" />
<asp:Parameter Name="category" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
