Are there any know performance problems with using the command SET DATEFORMAT dmy in SQL Server 2000 stored procedures? The reason I ask is because a particular stored procedure is taking 750ms to execute, yet when I remove 'SET DATEFORMAT dmy' line the execution times only takes 20ms (as in twenty - no typo). I mean that's a massive difference, why does SET DATEFORMAT command do this, is there a faster alternative?
As for the sProc itself it does a DateTime comparison on a VarChar field that stores a date time value (yes, I know it's not very well normalised/typed), eg.
In one Data Flow Task (running by itself) I simply have a Raw File Source pushing rows to an OLE DB Command. This command executes an UPDATE command (UPDATE table SET field = ?, anotherfield = ? WHERE thisfield = ?) and performs extremely slow. It's possible to have 62K+ rows needing to be updated and it typically takes this task around 25 - 30 minutes to run.
Is there anything I can do to increase performance? Are there any options other than the OLE DB Command to perform updates?
SQL 2000 Connection String:user id=MyUserName;password=MyPassword;initial catalog=MyDB;server=MyServer;Connect Timeout=30 This SELECT statement returns its 10 results nearly instantly:SELECT * FROM MyTableDitto from above, but completes in 30-40 seconds:SELECT * FROM [dbo].[MyTable]Ditto from above, but completes nearly instantly: SELECT TOP 1000 * FROM [dbo].[MyTable] Obviously I have stopped using the [dbo] syntax in my SqlCommand's (SELECT's and EXECUTE's) but still would like to know why this is.vr, Rich
MY Problem is that i want retrieve date from sql in dd/mm/yyyy format. i know that through convert fn , it is possible. but i want to retrieve date in specific format untill the connection is made with sql-server.
I tried by using set dateformat dmy then i fired query like select getdate() but i am not geting required output
I posted 01/08/07 and it went into my database like this: 08/01/2007 then I posted 15/08/07 and it went in like: 15/08/07
I have put SET DATEFORMAT dmy in the INSERT INTO and SELECT SQL statements
now i get: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. /pubspace/events.asp, line 96
line 96: SET DATEFORMAT dmy SELECT * FROM members INNER JOIN (pubs INNER JOIN tEvents ON pubs.PubID = tEvents.PubID) ON members.UserID = tEvents.UserID WHERE Start_Date = '" & dDate & "' ORDER BY eventTime "
Hello,I receive a file containing some character fields along with a date.The date values in the file are formatted as "dd/mm/yy", that is2-digit day, 2-digit month, and 2-digit year. The separator could beslash or a dash ("-"). The file is in a proprietary format, and bcp isnot an option.So, I decided to load the file using a prepared statement. I open acursor with an INSERT statement, read from the file, parse out values,and put it in the database using the cursor. All is OK; except thatthe date values are mangled. This is despite the fact that I am issuinga "set dateformat dmy" before running the INSERT statement.It seems that the "set dateformat dmy" is not being accepted, or it isbeing ignored. I set it at the beginning right after opening aconnection to the database. From what I understand, it should work.Am I doing something wrong? Any suggestions on how to get this towork?Thanks!
Hi, I have a date field in the report, I want to convert its format to string like this: 22-May-08 I tried to use FormatDateTime function but it doesn't have much option.
This stored procedure is not able to accept the value from the parameter, because the application is passing it, in the format ymd and sql server is specting it in ydm.
I tried to set the dateformat = ymd in the store procedure, thing that did not work. I have to set it before exec my-stored procedure. But I cant from my application.
When I write a query getdate() the aviliable date format is yy-mm-dd But I need in dd-mm-yy, so how can I get this. Am getting in all the ways i.e, mm-dd-yy, yy-dd-mm and yy-mm-dd except dd-mm-yy. pl send the ans ASAP.
I want to change the default date format from 'mdy' to 'dmy'. I can do this for session by using SET DATEFORMAT 'dmy' but I want to this permanent on Database level (preferred) or SQL instance level how can I do this?
i have a report with date formats,numbers, percentages,when runnig the preview it successfully those are visible in preview, but problem is when iam importing report into excel , those date formats,numbers,percentages are not visible in excel sheet. instead of date formats, numbers, percentages, cells are empty and cells not contain any data like dateformat , number,percentage.
so please help me because i have only one day for delivering repport,
i hope any body save me this problem, iam giving advanced thanks to our forum members.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
Hi,I'm using the sql TOP command to retrieve the top N number of results where N is a value passed into the stored proc...eg: select TOP(@N) table.*from table...etc..if @N is not passed into the stored proc then by default i want it to select every row from the table. e.g to achieve something similar to...select table.*from table...how can i do this with with as few lines of code possible? thanks!
Hello, i have this sql command: sqlcommand2.CommandText = "Select Count(UserIP) From InboundTraffic Where InboundURL Contains('" & SiteDomain(i).ToString & "') and DateTimeReceived > #" & Last30Days & "#" My problem is that it is counting every field in the coulmn UserIp even though every field under Inboundurl currently contains 'a' and SiteDomain has a value of something like google.com. Should it not be returning nothing? Thanks!
Hi! Suppose a company has ten branches and a total employees of 10,000 ones. At the employees' table, how may I calculate the difference between every employee's salary to the average salary of own branch and write to the other field of table, just with a SQL Command?
Employee: ID | Branch | Salary | DifferenceToAverage
I am using the bcp command to export a bulk text file into the database, bcp elearning.dbo.BulkData in mobile.txt -c -t, -SZOHL-02 -Usa -P1234567890 -E I have 6 fields in the table to which i am exporting data.One field is numeric and i have to set the identity to yes,It gives me an error string data trucncated. When i remove the identity field, i am able to export data.So, how do i tackle this prob?I used the -E attribute to keep the identity .But still i get the error. The text file has comma seperated fields.I am using sql server 2000
Hello, i'm a junior progammer, I must use the BCP command for create a file that is needed to be used by another program.I have my template to use EX.:
