sqlserver2000 date: update

Showing posts with label update. Show all posts

Wednesday, March 28, 2012

increase aggregations (design storage) programmatically (was "Please help thank

Fact table gets update every day with thousands of records, I been increase the aggregations manually. Is there a way I can increase aggregations (design storage) programmatically . My cub gets process ones in day with VB program. I want to increase aggregations before processing cub through VB.

It's argent

please help me

thanksCan you do a full process? If you're not running into time constraints, then I'd do a full process.|||As fact table's records increases, dont I have to increase the aggregation in the cub?|||No. Aggregations are dimension related, so as long as your not creating new dimensions then you don't have to worry about increasing aggregations. However, if processing time is not an issue, I'd do a full process.|||I am confused according to your statement
When I create cub with single records fact table the aggregations are zero.
When I create cub with thousands of records in fact table the aggregations are 850 or more.|||Here's what I'm trying to say:

The theoretical maximum number of possible aggregations in a cube is the product of the number of levels in each cube dimension. As you add levels and dimensions to a cube, the number of possible aggregations increases exponentially. The higher the number of dimensions and levels in a cube, the greater its complexity. In the example in Figure 2, the Time dimension has four levels, the Customers dimension has five levels, and the Products dimension has five levels. This yields a theoretical maximum number of aggregations of 100 (5 customer levels x 5 product levels x 4 time levels). However, this number increases exponentially as you add dimensions or levels. For example, if you add the Day level to the Time dimension, the theoretical maximum number of aggregations increases to 125 (5 x 5 x 5). Now, suppose you add two more dimensions to this cube, each with three levels. The theoretical maximum number of aggregations increases to 1125 (5 x 5 x 5 x 3 x 3). A cube with nine dimensions containing five levels each yields theoretical maximum number of aggregations of 1,953,125. A cube of this complexity is considered a cube of medium complexity. A cube of high complexity might have 20 dimensions with five levels each and yield a theoretical maximum number of aggregations of approximately 95 trillion. As you can see, you can directly affect the theoretical maximum number of aggregations in a cube by changing the number of dimensions or the number of levels. Having multiple dimensions with deep hierarchies improves the ability of users to perform analysis, but having too many of either can lead to resource problems during querying and processing.

Follow this link for more info...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx|||Yes I understand that

What I did is, my fact table was MT (no records) I added one dummy record and created cub out of it, basically dimensions contains single level. I have 40 dimensions and 35 measures. I created a script out of it and sending the script to user to create the cub in their analysis server. In the script I had prompt for users data source name. Users fact table contains millions of records. When user processes the cub with millions of records the dimensions will have multiple levels. So user has to recreate the aggregations?

Sorry, I know you are trying to clarify my doubts but I am still confused.

Thank you so much.|||I think I remember now. You're sharing an identical cube structure with someone, but you're each pointing to different data sources, correct?

If that is the case, the user would have to re-process. Especially since the dimensions will have multiple levels once your script has run against their data. With the numbers you've provided, I wouldn't do a full process. However, again, aggregations are based on dimensions, so you don't need to programatically increase the number of aggregations as new records are added to the fact. Even if new dimension levels are being created, I wouldn't increase the number of aggregations. Just select a particular "performance gain level" and let AS do the rest (i.e. 30%). Having said that, you should probably look into usage based optimization. I have a similar sized cube as you've described and I know that there are a lot of wasted aggregations in the cube (fully processed). It only gets updated once a month, so it's not a big deal to do a full process. Remember, as your cube gets more complex, it's unlikely that your users are making the most of that complexity. That's why usage base optimization makes sense. I haven't done usage based optimization "programatically", so I'm no help there.

I hope that makes sense.|||Thanks so much

User doesnt know about analysis server.
I want to provide user to click option to design storage.

After crating the cub on user server,

I am looking for VB code to
1. Count dimension members
2. Design storage

So user doesnt have to do it manually .sql

Incorrect syntax when there appears to be no syntax errors.

I keep receiving the following error whenever I try and call this function to update my database.

The code was working before, all I added was an extra field to update.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'

Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)

Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @.strProjectName, " _
& "txtProjectDescription = @.txtProjectDescription, " _
& "strProjectState = @.strProjectState, " _
& "intEstDuration = @.intEstDuration, " _
& "dtmCreationDate = @.dtmCreationDate, " _
& "strCreatedBy = @.strCreatedBy, " _
& "strProjectLead = @.strProjectLead, " _
& "dtmEstCompletionDate = @.dtmEstCompletionDate, " _
& "WHERE intProjectID = @.intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@.strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@.strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@.txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@.txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@.strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@.strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@.intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@.intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@.dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@.dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@.strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@.strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@.strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@.strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@.dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@.dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@.intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@.intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()

End Sub

Thankyou in advance.> cmdSQL.Parameters.Add(new SqlParameter("@.intProjectID", SqlDbType.NChar, 5))

why wouldintProjectID be an NChar? or is that just misleading?|||You have an extra comma.

"dtmEstCompletionDate = @.dtmEstCompletionDate, " _
"WHERE ... "

that would result in:

dtmEstCompletionDate = '1/1/2003', WHERE

there's an extra comma before the WHERE clause. That is causing the syntax error.

Cheers
Ken|||Good one!sql

Incorrect syntax near the keyword 'WHERE'.

Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0

Below is the code I am using for an update to a MS Sql Database.

<%@. Language=VBScript %>
<% Option Explicit %>

<html>
<head>
<title>Sample Script 2 - Part 3 </title>

<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">

<center>
<%

Dim oRS
Dim Conn

Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql

Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open

sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id

set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>

<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>

</form>

</center>
</body>
</html>sql=sql & "PhoneNumber='" & PhoneNumber & "',"

It looks like the syntax error is due to the extraneous comma after the last
column.

I strongly suggest you google 'SQL injection'. Your current code will allow
a hacker can execute any arbitrary SQL statement. The best protection
against injection is to use parameterized SQL statements, stored procedures
and validate user input. Never build a SQL Statement string by
concatenating user input values. The example below uses a parameterized
UPDATE statement via OLEDB:

Const adParamInput = 1
Const adInteger = 3
Const adVarChar = 200

Set Conn = CreateObject("ADODB.Connection")
Set Command = CreateObject("ADODB.Command")

Conn.Open _
"Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=FriendsContactInfo;" & _
"App=AspRunner Professional Application"

Command.ActiveConnection = Conn

Command.CommandText = _
" UPDATE dbo.FPFriends" & _
" SET" & _
" Name=?," & _
" StreetAddress=?," & _
" Ciy=?," & _
" State=?," & _
" Zip=?," & _
" PhoneNumber=?" & _
" WHERE Id=?"

Set parameter = Command.CreateParameter( _
"Name", _
adVarChar, _
adParamInput, _
30)
parameter.Value = Name
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"StreetAddress", _
adVarChar, _
adParamInput, _
30)
parameter.Value = StreetAddress
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"City", _
adVarChar, _
adParamInput, _
30)
parameter.Value = City
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"State", _
adVarChar, _
adParamInput, _
2)
parameter.Value = State
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Zip", _
adVarChar, _
adParamInput, _
5)
parameter.Value = Zip
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"PhoneNumber", _
adVarChar, _
adParamInput, _
15)
parameter.Value = PhoneNumber
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Id", _
adInteger, _
adParamInput)
parameter.Value = Id
Command.Parameters.Append parameter

Command.Execute

Conn.Close

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DaveF" <jeacdf@.excite.comwrote in message
news:1173540573.073247.128620@.t69g2000cwt.googlegr oups.com...

Quote:

Originally Posted by

Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0
>
Below is the code I am using for an update to a MS Sql Database.
>
<%@. Language=VBScript %>
<% Option Explicit %>
>
<html>
<head>
<title>Sample Script 2 - Part 3 </title>

<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">

>
<center>
<%
>
Dim oRS
Dim Conn
>
Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql
>
Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")
>
>
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open
>
>
sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id
>
>
>
set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>

<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>
>
</form>
>
</center>
</body>
</html>
>

Friday, March 23, 2012

Incorrect syntax near '5' where 5 is the beginning of a field name

Hi all
i get the following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'5'. (#170)
when trying to update or delete a record from an Access Linked table. Adding
a record is fine. There is a field called 5Years, but this has not been a
problem before. Only occurred when database was moved to a new server.
SQL server version on current and old server is 2000 sp3a
jw
It's always been my understanding that fields can't start with certain
characters, like punctuation characters and numbers.
I'm surprised this ever worked. It may have been a bug that was fixed in
your recent release?
Perhaps someone else has a more knowledgeable response.

Incorrect syntax near =

Hi there

I'm getting anIncorrect syntax near '='. error when I click update in my gridview.

Any ideas?

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Taskdb%>"
SelectCommand="SELECT [taskid], [taskname], [taskdescription], [taskstatus] FROM [tblTask]"
UpdateCommand="UPDATE [tbltask) set [taskname] = @.taskname, [taskdescription] = @.taskdescription, [taskstatus] = @.taskstatus where [taskid] = @.taskid">
<UpdateParameters>
<asp:Parameter Type="String" Name="taskname" />
<asp:Parameter Type="String" Name="taskdescription" />
<asp:Parameter Type="Int16" Name="taskstatus" />
<asp:Parameter Type="Int16" Name="taskid" />
</UpdateParameters>
</asp:SqlDataSource>

Thanks in advance.

looks like a typo

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Taskdb%>"
SelectCommand="SELECT [taskid], [taskname], [taskdescription], [taskstatus] FROM [tblTask]"
UpdateCommand="UPDATE[tbltask) set [taskname] = @.taskname, [taskdescription] = @.taskdescription, [taskstatus] = @.taskstatus where [taskid] = @.taskid">
<UpdateParameters>
<asp:Parameter Type="String" Name="taskname" />
<asp:Parameter Type="String" Name="taskdescription" />
<asp:Parameter Type="Int16" Name="taskstatus" />
<asp:Parameter Type="Int16" Name="taskid" />
</UpdateParameters>
</asp:SqlDataSource>

change the highlight section above to[tbltask]

|||

Thanks, how did I miss that! I must have looked at the code 100 times over.

Incorrect syntax near [SQL UPDATE COMMAND] > cmd.ExecuteNonQuery()

Please let me know what is wrong with my code below. I keep getting the "Incorrect syntax near 'UpdateInfoByAccountAndFullName'." error when I execute cmd.executenonquery. I highlighted the part that errors out. Thanks a lot.

-------------------------------------

public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

using (cn)
{
cn.Open();
return cmd.ExecuteNonQuery() > 1;
}
}

capture the return value to a variable and return that. You are trying to use a shortcut that doesn't exist.

|||

I added a variable that will hold the INTEGER value of the rows updated, but it still generates the same error.
-----------------------------------------
public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

int rowsAffected;

using (cn)
{
cn.Open();
rowsAffected = cmd.ExecuteNonQuery();
return rowsAffected > 1;
}
}

|||

try this

If (rowsAffected >1){

return true;}

else {

return false;}

|||

No luck... It still generates the same error even with the IF condition in place. Here is a copy of the error trace from the browser:

--------------------------------

Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Source Error:

Line 215: {
Line 216: cn.Open();
Line 217: rowsAffected = cmd.ExecuteNonQuery();
Line 218:
Line 219: if (rowsAffected > 1)

|||

post your stored procedure. This is where your problem is.

|||

I don't see the point of using rowsaffected >1

Why don't you just use return rowsAffected?

Isn't that going to give you the count anyway?

|||

Thanks for the quick reply.:

------------------------------

Create procedure UpdateInfoByAccountAndFullName(
@.newaccount varchar(50),
@.newfullname varchar(100),
@.rep varchar(10),
@.zip varchar(10),
@.comments varchar(2000),
@.oldaccount varchar(50),
@.oldfullname varchar(100)
)
as

update Info
Set ACCOUNT = @.newaccount,
FULL_NAME = @.newfullname,
REP = @.rep,
ZIP = @.zip,
COMMENTS = @.comments
where
ACCOUNT = @.oldaccount and
FULL_NAME = @.oldfullname

|||

Duduvi,

Why don't you just use return rowsAffected? Isn't that going to give you the count anyway? > I could use rowsAffected, but I don't really see the need of taking into account how many records where updated. This is why I used the boolean rowsAffected > 1. I just need to know that more than or 1 record(s) were updated.

|||

I don't see a problem. Try removing the return value. Just try executing the query and see if it works then you can eliminate the stored procedure as the problem.

|||

You are right. I dont see the problem neither. This is what freaks me out too. I already tried running the StoredProc and the SQL String from within SQL2005, and it did update the record. The only time it throws an exception is on my .NET code when I do a ExecuteNonQuery() with or without a return value.

|||

HA! I can't believe i missed it. you forgot to specify that your command is a stored procedure using the commandtype enum.

|||

Yep you're right. I forgot to specify the command type. Thanks again.

Incorrect Syntax Near '-'

Hi,
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
JackHi,
Put the server name in sqare brackets [].
[SQL-SERVER2]
Thanks
Hari
SQL Server MVP
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SP that running every 20 min, the SP will update some tables from
> DB on another SQL server.
> ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> server name in SP, but during test SP, i got error Incorrect Syntax Near
> '-', seems i can't use '-' when refering server, is it normal?
> how could i correct this issue other than rename server?
> Appreicate your help.
> Jack
>|||Brilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a SP that running every 20 min, the SP will update some tables
from
> > DB on another SQL server.
> >
> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> > server name in SP, but during test SP, i got error Incorrect Syntax Near
> > '-', seems i can't use '-' when refering server, is it normal?
> >
> > how could i correct this issue other than rename server?
> >
> > Appreicate your help.
> >
> > Jack
> >
> >
>|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/articles/DatabaseNamingConventions.aspx
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>

Incorrect Syntax Near '-'

Monday, March 12, 2012

Inconsistent Results after Update

I have never seen anything like this, so I am quite baffled:

I have a large (6 million rows) table in a data warehouse. Because of a new user requirement, I ran an update on that table to update two columns changing the value from null to a 'real' value.

I ran the update and it completed in 14 minutes.

Now I query the table searching for a count of the records where the value in one of the columns is null. And I keep getting different answers; the results vary by as much as 100,000 records.

Here are the scripts:

CREATE TABLE TASKTRN (
TASKTRNKEY VARCHAR(10) NOT NULL,
TASKHDRKEY VARCHAR(10) NOT NULL,
TASKDTLKEY VARCHAR(10) NULL,
RECEIPTKEY VARCHAR(20) NULL,
RECEIPTLINE VARCHAR(5) NULL,
TASKTYPE INT NOT NULL
)
GO

ALTER TABLE TASKTRN ADD
CONSTRAINT PK_TASKTRN PRIMARY KEY CLUSTERED (TASKTRNKEY)
GO

CREATE INDEX TASKTRN_TASKHDRKEY ON TASKTRN (TASKHDRKEY)
GO

CREATE INDEX TASKTRN_RECEIPTKEY ON TASKTRN (RECEIPTKEY)
GO

/****** Object: Table [dbo].[TASK_TMP] Script Date: 03/21/2003 12:27:40 ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TASK_TMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TASK_TMP] (
[TASKTRNKEY] [char] (10) NOT NULL ,
[TASKHDRKEY] [char] (10) NULL ,
[RECEIPTKEY] [char] (10) NULL ,
[RECEIPTLINE] [char] (5) NULL
) ON [PRIMARY]
END

Print 'Created Temp table'

CREATE INDEX TASK_TMP_TASKTRNKEY ON TASK_TMP (TASKTRNKEY)

CREATE INDEX TASK_TMP_TASKHDRKEY ON TASK_TMP (TASKHDRKEY)

Print 'created Indexes'

INSERT INTO TASK_TMP
SELECT TASKTRNKEY, TASKHDRKEY, RECEIPTKEY, RECEIPTLINE
FROM TASKTRN
WHERE TASKTYPE = 1 AND RECEIPTKEY IS NULL

Print 'Insert Records'

UPDATE TASK_TMP
SET RECEIPTKEY = B.RECEIPTKEY, RECEIPTLINE = B.RECEIPTLINE
FROM
TASK_TMP A JOIN
(SELECT TASKHDRKEY, RECEIPTKEY, RECEIPTLINE
FROM TASKTRN
WHERE TASKTYPE = 1 AND RECEIPTKEY IS NOT NULL) B ON

A.TASKHDRKEY = B.TASKHDRKEY

Print 'Updated null values in temp table'

UPDATE TASKTRN
SET RECEIPTKEY = B.RECEIPTKEY, RECEIPTLINE = B.RECEIPTLINE
FROM
TASKTRN A JOIN
TASK_TMP B ON
A.TASKTRNKEY = B.TASKTRNKEY

Print 'Updated null values in permanent table'

This is the SQL that generates the disparate results:

select count(tasktrnkey) From tasktrn_roc where tasktype = 1 and receiptkey is null

Does anyone have any idea what may be going on?

Regards,

Hugh Scottbad statistics after the update, maybe?

after the update (or large inserts), try running sp_updatestats and see if it affects your results.

running DBCC INDEXDEFRAG might be a good idea as well

-isaac|||Yep,

I did that and still came up with some funky results. Now the mystery deepens a little further.

I ran a two different queries:

SELECT COUNT(TASKTRNKEY) WHERE RECEIPTKEY IS NULL AND TASKTYPE = 1

SELECT COUNT(TASKTRNKEY) WHERE RECEIPTKEY IS NOT NULL AND TASKTYPE = 1

In theory the sum of these two queries should add up to:

SELECT COUNT(TASKTRNKEY) WHERE TASKTYPE = 1

Didn't work; the sum of the results from the first two queries is slightly less than twice the actual number of records where TASKTYPE = 1.

Finally, I ran this query:

SELECT
CASE
WHEN RECEIPTKEY IS NULL THEN 'Null'
ELSE 'Not Null'
END as 'RECEIPTKEY',
COUNT(TASKTRNKEY)
FROM
TASKTRN
WHERE
TASKTYPE = 1
GROUP BY
CASE
WHEN RECEIPTKEY IS NULL THEN 'Null'
ELSE 'Not Null'
END

This returned what I expected it to return. But I am baffled to explain why or why the other select statements return such bizarre and conflicting results.

Regards,

Hugh Scott

Originally posted by isaacfain
bad statistics after the update, maybe?

after the update (or large inserts), try running sp_updatestats and see if it affects your results.

running DBCC INDEXDEFRAG might be a good idea as well

-isaac

Friday, March 9, 2012

Inconsistent Horrible Performance on UPDATE

We're trying to figure out what is causing an incredible inconsistency in
our query performance on the same update. query
We have a table with ~75 million records, it is indexed on a field which
breaks the table into about 25 chunks of ~2 - 3 million records.
If we run an update on a particular chunk of this table ( WHERE idField = 25000 ) and idField is set up in 2-3 million chunks with idFields from 25000
to 25025 It runs for upwards of 13 hours.
If we select out these 2-3 millions records, run an update on them delete
them from the original table, then insert them back changed it can be done
in under 2 hours.
We've been finding SQL server isn't performing well with recrodsets over 5
million. Fillfactors on tables with lots of writing are often set VERY low
~25%, wasting 30 or so GB for the sake of speed is often the route we must
go.
We suspected page splitting, but found 0 page splits occurring during these
updates, but found the reading and writing of these transactions to be maxed
out.
Also The Enterprise Manager shows the Wait Type as PAGEIOLATCH_EX and _SH.
What is causing this? All the drives are defragmented.
Also with the databases in Simple Recovery mode, why are the transaction
logs filling up?
Thanks for any information on these problems.The transaction log filling up is normal since the update is rather large
and the complete action needs to be logged and will only checkpoint once the
transaction is complete.
Could you provide DDL for the table (include the attached indexes) and the
DML (statments used during large update) plus a brief server description,
Disk. Memory, CPU.
I suspect that the logging and the index updates are what is taking so long
without these specifics though it's tough to give advice.
-Lars
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:e7p#5$pWDHA.2568@.tk2msftngp13.phx.gbl...
> We're trying to figure out what is causing an incredible inconsistency in
> our query performance on the same update. query
> We have a table with ~75 million records, it is indexed on a field which
> breaks the table into about 25 chunks of ~2 - 3 million records.
> If we run an update on a particular chunk of this table ( WHERE idField => 25000 ) and idField is set up in 2-3 million chunks with idFields from
25000
> to 25025 It runs for upwards of 13 hours.
> If we select out these 2-3 millions records, run an update on them delete
> them from the original table, then insert them back changed it can be done
> in under 2 hours.
> We've been finding SQL server isn't performing well with recrodsets over 5
> million. Fillfactors on tables with lots of writing are often set VERY
low
> ~25%, wasting 30 or so GB for the sake of speed is often the route we must
> go.
> We suspected page splitting, but found 0 page splits occurring during
these
> updates, but found the reading and writing of these transactions to be
maxed
> out.
> Also The Enterprise Manager shows the Wait Type as PAGEIOLATCH_EX and _SH.
> What is causing this? All the drives are defragmented.
> Also with the databases in Simple Recovery mode, why are the transaction
> logs filling up?
> Thanks for any information on these problems.
>|||Yes. MS has at least two KB articles (don't have the numbers offhand)
regarding parallelism problems. You might to search for them. The
workaround is to disable parallelism for the affected queries. I have also
avoided it by rewriting the query (avoid outer / self joins involving large
datasets). I've only experienced the problem in SELECTs involving large
resultsets - don't do any mass updates.
Note - using SQL Server 7. I believe that the basic issues apply to SS2K as
well.
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:%23xBZ9%23qWDHA.2444@.tk2msftngp13.phx.gbl...
> Some of these problems we've traced to when the optimizer decides to
> parallelize the process. We believe we've encountered a bug where the
> parallel processing with CXPacket and PAGEIOLATCH waits cause the process
to
> go into a dead lock of some sort but the disk usage and cpu usage for the
> process continues to be used with the disk usage at about an 8:1 ratio
> slowing the entire system dramatically.
> Has Anyone else run into problems with parallelism flaking out on large
> updates?
>
> "Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
> news:e7p%235$pWDHA.2568@.tk2msftngp13.phx.gbl...
> > We're trying to figure out what is causing an incredible inconsistency
in
> > our query performance on the same update. query
> >
> > We have a table with ~75 million records, it is indexed on a field which
> > breaks the table into about 25 chunks of ~2 - 3 million records.
> >
> > If we run an update on a particular chunk of this table ( WHERE idField
=> > 25000 ) and idField is set up in 2-3 million chunks with idFields from
> 25000
> > to 25025 It runs for upwards of 13 hours.
> >
> > If we select out these 2-3 millions records, run an update on them
delete
> > them from the original table, then insert them back changed it can be
done
> > in under 2 hours.
> >
> > We've been finding SQL server isn't performing well with recrodsets over
5
> > million. Fillfactors on tables with lots of writing are often set VERY
> low
> > ~25%, wasting 30 or so GB for the sake of speed is often the route we
must
> > go.
> >
> > We suspected page splitting, but found 0 page splits occurring during
> these
> > updates, but found the reading and writing of these transactions to be
> maxed
> > out.
> >
> > Also The Enterprise Manager shows the Wait Type as PAGEIOLATCH_EX and
_SH.
> > What is causing this? All the drives are defragmented.
> >
> > Also with the databases in Simple Recovery mode, why are the transaction
> > logs filling up?
> >
> > Thanks for any information on these problems.
> >
> >
>|||if you are have trouble with a parallel execution plan,
try it with OPTION(MAXDOP 1) to restrict the plan to 1 CPU.
There are some serious issues with Hyper-threading and
parallel execution plans, if this is applicable, disable
HT.
also, in high row count operations, the plan may spool to
the tempdb, you need to verify a high-performance disk
system for both the main app & tempdb data files, and
separate disk drive for each heavy use log file
>--Original Message--
>Some of these problems we've traced to when the optimizer
decides to
>parallelize the process. We believe we've encountered a
bug where the
>parallel processing with CXPacket and PAGEIOLATCH waits
cause the process to
>go into a dead lock of some sort but the disk usage and
cpu usage for the
>process continues to be used with the disk usage at about
an 8:1 ratio
>slowing the entire system dramatically.
>Has Anyone else run into problems with parallelism
flaking out on large
>updates?
>
>"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
>news:e7p%235$pWDHA.2568@.tk2msftngp13.phx.gbl...
>> We're trying to figure out what is causing an
incredible inconsistency in
>> our query performance on the same update. query
>> We have a table with ~75 million records, it is indexed
on a field which
>> breaks the table into about 25 chunks of ~2 - 3 million
records.
>> If we run an update on a particular chunk of this table
( WHERE idField =>> 25000 ) and idField is set up in 2-3 million chunks
with idFields from
>25000
>> to 25025 It runs for upwards of 13 hours.
>> If we select out these 2-3 millions records, run an
update on them delete
>> them from the original table, then insert them back
changed it can be done
>> in under 2 hours.
>> We've been finding SQL server isn't performing well
with recrodsets over 5
>> million. Fillfactors on tables with lots of writing
are often set VERY
>low
>> ~25%, wasting 30 or so GB for the sake of speed is
often the route we must
>> go.
>> We suspected page splitting, but found 0 page splits
occurring during
>these
>> updates, but found the reading and writing of these
transactions to be
>maxed
>> out.
>> Also The Enterprise Manager shows the Wait Type as
PAGEIOLATCH_EX and _SH.
>> What is causing this? All the drives are defragmented.
>> Also with the databases in Simple Recovery mode, why
are the transaction
>> logs filling up?
>> Thanks for any information on these problems.
>>
>
>.
>|||Well I found the OPTION MAXDOP 1 to alleviate the problems fixing 13 hour
incomplete queries to finish in under a minute! This has been a nightmare
problem causing a lot of problems and very little information on this to
find this work around.
We have every database on it's own 3 disk RAID 0 stripe, including the
system drive, temp DB, and logs. I don't think we could break it down any
more. This is on a 2GB dual Xeon 2.8Ghz with Hyperthreaded to 4. Is it
possible using Standard edition SQL server and the 3GB switch to get SQL
server to use 3GB of memory? I think we'll grab 2 more GB of memory and
see if we can get it to use some of it.
All out updates (and mostly all queries) which run over 500,000 records
which most do and are upwards of 30 or so million), now run more as expected
with the parallelism forced off. Pretty much a waste of dual cpu's with
hyperthreading.
"joe chang" <jchang6@.yahoo.com> wrote in message
news:01a601c35abb$10276fb0$a501280a@.phx.gbl...
> if you are have trouble with a parallel execution plan,
> try it with OPTION(MAXDOP 1) to restrict the plan to 1 CPU.
> There are some serious issues with Hyper-threading and
> parallel execution plans, if this is applicable, disable
> HT.
> also, in high row count operations, the plan may spool to
> the tempdb, you need to verify a high-performance disk
> system for both the main app & tempdb data files, and
> separate disk drive for each heavy use log file
> >--Original Message--
> >Some of these problems we've traced to when the optimizer
> decides to
> >parallelize the process. We believe we've encountered a
> bug where the
> >parallel processing with CXPacket and PAGEIOLATCH waits
> cause the process to
> >go into a dead lock of some sort but the disk usage and
> cpu usage for the
> >process continues to be used with the disk usage at about
> an 8:1 ratio
> >slowing the entire system dramatically.
> >
> >Has Anyone else run into problems with parallelism
> flaking out on large
> >updates?
> >
> >
> >"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
> >news:e7p%235$pWDHA.2568@.tk2msftngp13.phx.gbl...
> >> We're trying to figure out what is causing an
> incredible inconsistency in
> >> our query performance on the same update. query
> >>
> >> We have a table with ~75 million records, it is indexed
> on a field which
> >> breaks the table into about 25 chunks of ~2 - 3 million
> records.
> >>
> >> If we run an update on a particular chunk of this table
> ( WHERE idField => >> 25000 ) and idField is set up in 2-3 million chunks
> with idFields from
> >25000
> >> to 25025 It runs for upwards of 13 hours.
> >>
> >> If we select out these 2-3 millions records, run an
> update on them delete
> >> them from the original table, then insert them back
> changed it can be done
> >> in under 2 hours.
> >>
> >> We've been finding SQL server isn't performing well
> with recrodsets over 5
> >> million. Fillfactors on tables with lots of writing
> are often set VERY
> >low
> >> ~25%, wasting 30 or so GB for the sake of speed is
> often the route we must
> >> go.
> >>
> >> We suspected page splitting, but found 0 page splits
> occurring during
> >these
> >> updates, but found the reading and writing of these
> transactions to be
> >maxed
> >> out.
> >>
> >> Also The Enterprise Manager shows the Wait Type as
> PAGEIOLATCH_EX and _SH.
> >> What is causing this? All the drives are defragmented.
> >>
> >> Also with the databases in Simple Recovery mode, why
> are the transaction
> >> logs filling up?
> >>
> >> Thanks for any information on these problems.
> >>
> >>
> >
> >
> >.
> >

Inconistent time out error during update operation.

Using our ASP.net application we are getting inconsistent results when we are trying to update a table with more than 15 update queries sequentially.

We are using Merge replication in our SQL server database. On updating one row, it invokes a trigger to update another table and one more trigger for the merge replication. At only few
instances, the update operation fails and it reports "Time out expired" error.

We are not able to simulate this error in the Test environment since it does not have replication. Can any one help me in resolving this issue.

Please advise .

Hi Krithi,

Is this SQL 2000 or SQL 2005?
Would the updates be touch a lot of rows? If so, I can understand the timeout because, it seems like there are multiple triggers here and also replication adds its own triggers on the tables. That bing the case, if the number of rows touched are large, timeouts can occur.

And are you doing the update operations through isqlw/Management Studio?
If so, can you increase the timeout value in the options in isqlw/Management Studio and try again?

Friday, February 24, 2012

IN() operator problem

i have a list of ID's (indexed) more than 2500 items.
first i have to select these records from one and then Update in
another table. when i pass the list to sql with IN() operator in
select query it takes too long (about 2 mins) and when i run update
query with same criteria it takes too much time.
i am using VB6 for development.
the field is indexed.

what i should do to improve performance?

Khurram Rao[posted and mailed, please reply in news]

Khurram (khurramanis@.gmail.com) writes:
> i have a list of ID's (indexed) more than 2500 items.
> first i have to select these records from one and then Update in
> another table. when i pass the list to sql with IN() operator in
> select query it takes too long (about 2 mins) and when i run update
> query with same criteria it takes too much time.
> i am using VB6 for development.
> the field is indexed.

Yes, the IN operator with many elements performs badly.

This article discusses different alternatives:
http://www.sommarskog.se/arrays-in-sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I have a list of ID's (indexed) more than 2500 items. First I have
to select these records [sic] from one and then Update in another
table. When I pass the list to SQL with IN() operator in select query
it takes too long (about 2 mins) and when I run update query with same
criteria it takes too much time.

Rows are not like records at all. Please post your code and DDL.
You are describing procedural steps, not a relational approach.|||um - create a temp table with your list, and join to it in the update
statement?

"Khurram" <khurramanis@.gmail.com> wrote in message
news:60ff3d6c.0412040236.6e0466ea@.posting.google.c om...
>i have a list of ID's (indexed) more than 2500 items.
> first i have to select these records from one and then Update in
> another table. when i pass the list to sql with IN() operator in
> select query it takes too long (about 2 mins) and when i run update
> query with same criteria it takes too much time.
> i am using VB6 for development.
> the field is indexed.
> what i should do to improve performance?
> Khurram Rao

Wednesday, March 28, 2012

increase aggregations (design storage) programmatically (was "Please help thank

Incorrect syntax when there appears to be no syntax errors.

Incorrect syntax near the keyword 'WHERE'.

Friday, March 23, 2012

Incorrect syntax near '5' where 5 is the beginning of a field name

Incorrect syntax near =

Incorrect syntax near [SQL UPDATE COMMAND] > cmd.ExecuteNonQuery()

Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Incorrect Syntax Near '-'

Incorrect Syntax Near '-'

Incorrect Syntax Near '-'

Monday, March 12, 2012

Inconsistent Results after Update

Friday, March 9, 2012

Inconsistent Horrible Performance on UPDATE

Inconistent time out error during update operation.

Friday, February 24, 2012

IN() operator problem

sqlserver2000 date

Blog Archive

About Me