I have built a DTS package in SQL Server 2000 to Delete all rows from a table and then import from another table. I have scheduled it run twice a day and everytime it runs, it does not delete anything but imports all the records from another table. So when its done, I have twice the number of records in my final table. Then I go back to the server and execute the package manually and it works perfect. It deletes all and then imports new. I have enabled package logging and it shows both the steps executed perfectly (even when it executes on scheduled time).
More than 10 times, i validated the package can be executed successfully, if i use a SQL job to run this package. All data and function goes well, however the job result reported failure after i saw the job 100% completed. That 's so weird.
Anyone met the same the issue. Could anyone give me some suggestion or hints?
I have a SSIS Project thats been running fine for months up until yesterday.
There's a master package that calls other packages, and when i run it now in Visual Studio, i get a Message Box after each of it's child packages run. The message states:
TITLE: Microsoft Visual Studio------------------------------The designer window cannot be closed while a package is running.Stop the debugger before attempting to close the window.------------------------------BUTTONS:OK------------------------------
This seems to be causing issues, because VS seems to hang on my machine at some point when running master packages, and i think it might due to a message box.
I have a package that has a FOREACHLOOP container. Inside the container is a SCRIPT task that runs a stored procedure and writes the output of the sp to a file.
That part works fine.
However, if I add a second script task inside the FOREACHLOOP container, I get error messages when running the second script task.
The second script task is identical to the first script task, except for the connection information. That is, it's doing the same thing, but running the sp on another server.
I tried removing the second script task from the FOREACHLOOP container, and putting it in it's own FOREACHLOOP container, but it still gives the same error.
This is the error I'm receiving:
Error 30009: Reference required to assembly 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the implemented interface 'System.Xml.Serialization.IXmlSerializable'. Add one to your project. Line 7 Column 22 through 30
I am so confused. I hope someone can help! I wrote a large stored procedure that is meant to be called from a VB application. The store procedure collects data from several tables and buildsa new table of summary data. It is called with 4 parameters (date ranges and names for the new summary tables to create).
I tested the SP in SQL 7 while developing it, and never had any problems. But when I tried it in 6.5, it complained that I had too many subqueries. So I rewrote the procedure with less nestings, and then when I tried it I didn't get an error but it crashed with a DB Disconnected error.
I rewrote it again and then it finally worked (but only from Enterprise Manager); when I called from a VB application (using ADO), I get an error "protocol error in TDS stream." And looking in the SQL error log I see a whole lot of errors, none of which I understand, but the first mesage is "EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump.
I know that without knowing the contents of the stored procedure, there's no way anybody can tell me exactly what's going wrong, but I'm sure hoping that some people can give me some clues or directions to look. I'm desperate and have been working on this one project for too long already!
When I run this code on a column of the type float or real it's ok, but not if the column is decimal, why?
this produces an error:
SELECT IsNull(column1,'') as column1 FROM mytable
---
this code works:
SELECT IsNull(column1,0) as column1 FROM mytable
---
The problem is that this query is used in a C++ environment to build an insert into another table. The query is build together in a CString. C++ considers a CString to have ended if it encounters a NULL, therefore I need the check on all columns.
The error message I get is:
"Error converting data type varchar to numeric."
Why is it ok to use float or rel, but not decimal??
MY DATABASE FOR SHOPPING CART IS ACTING STRANGE IT HAS AUTOMATICALLY DESTOYED ALL PRIMARY KEYS. AND HAS CREATED 4 DUPLICATE RECORDS FOR EACH RECORD SO AT THIS STAGE I HAVE AROUNG 15000 RECORDS IN EACH TABLE IT MEANS 15 TIMES 4 60000 RECORDS. CAN ANYONE UPDATEME WHY THIS HAS HAPPENED AND WHAT CAN BE DONE TO DELETE EXISTING DUPLICATES. I NEED FAST REPLY IN THIS MATTER AS THIS IS URGENT. IF POSSIBLE KINDLY WRITE QUERIES TRIGGERS FOR FUTURE PROBLEMS AS WELL AS THESE DAYS I AM SICK SO MY BRAIN AINT WORKING IN THIS MATTER. URGENT URGENT URGENT THANKS ALOT I HAVE PREVIOUSLY POSTED AND HAVE GOT A GOOD RESPONSE. AND HOPE A GOOD ONE AGAIN. ONE MORE THING IS THAT I HAVE A BACKUP HERE AT MY OFFICE SYSTEM IT ALSO HAS DONE THE SAMETHING. I MADE A NEW DATABASE AND TRYED TO IMPORT DATA WITH DISTINCT KEYWORD BUUT IT IS NOT COPYING IT AS WELL. URGENT RESPONSE REQUIRED IF ANYONE CAN DO.
I have MSSQL2k SP3a on WIN2k SP4.moved a Date/log files to this server about a week ago from a SQL7serverand attached it to this new Sql2k server.everything works fine for about 24hrs and then it starts timing out !!all I have to so is restart the MSSQL service and works fine againtill the next day !there is only this one database on this server and nothing else isrunning on it.have anyone seen this before ? how could this be fixed !!thanksDon
I wonder if anyone can explain what I am doing wrong here:
I need to transport data between several tables using dataflow components. I need to wrap this in transactions to either succeed or fail completly, I do want to use my own BEGIN TRAN and COMMIT TRAN statements, not the transaction support built into SSIS.
As written in several BLOGs, I should set the "RetainConnection" property to "TRUE" to make sure each dataflow runs on the same connection as the BEGIN and COMMIT statements... and that seems to be random.
Sometimes they do run on the same connection, but I also logged (using SQL Profiler) package executions where the BEGIN TRAN was issued to SPID different than the one used in first dataflow, another SPID again for the second dataflow and finally yet another for the COMMIT... in this case COMMIT fails with "needs an open transaction".
One of my table, dbo.Customer, gives me different values of "row count" everytime I check the properties.
The dbo.Customer have around 12 000 rows but when I check the properties (or open the table) the value always differs. The table row count can have any value from ~9000 - 12000.
Its like the table are trying to load all rows but cant.
Hi,I have a problem with the execution of a vb-procedure that should do 2 updates of databasetables.The procedure runs when the eventhandler registrates that I want make an update within the <asp:detailview..>-controll.But I get the errormessage: End-Of-Command requestedThis is the procedure:Public Sub myDetails_Update(ByVal sender As Object, ByVal e As System.EventArgs) handle DetailsView1.Databound Dim conn As New SQLConnection("Data Source=.SQLEXPRESS;AttachDbFile=C:Pferdeservice KarleApp_dataPferde.mdf;Integrated Security=True Connection Timeout=30;User Instance=True") conn.open() Dim cmd As New SqlCommand() Dim cmd1 As New SqlCommand() cmd.Connection = conn cmd.CommandText = "UPDATE News_Kultur set Header = @Header, Ueberschrift_D = @Ueberschrift_D, Ueberschrift_E = @Ueberschrift_E, Text_D = @Text_D, News_Kultur.Text_E = @Text_E"WHERE (((News_Kultur.ID)=@ArtikelID))" cmd.CommandType = CommandType.Text cmd1.CommandText = "UPDATE News set Datum= @Datum WHERE ID=@NewsID" cmd1.CommandType = CommandType.Text cmd.Parameters.add("@Headers", SqlDbType.nvarchar, 50) cmd.Parameters.add("@Ueberschrift_D", SqlDbType.nvarchar, 50) cmd.Parameters.add("@Ueberschrift_E", SqlDbType.nvarchar, 50) cmd.Parameters.add("@Text_D", SqlDbType.Text) cmd.Parameters.add("@Text_E", SqlDbType.Text) cmd.Parameters.add("@ArtikelID", SqlDbType.Int) cmd1.Parameters.add("@Datum", SqlDbType.Datetime) cmd1.Parameters.add("@NewsID", SqlDbType.Int) cmd.Parameters.add("@Headers").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text cmd.Parameters.add("@Ueberschrift_D").value = DirectCast(DetailsView1.FindControl("Ueberschrift_D"), TextBox).Text cmd.Parameters.add("@Ueberschrift_E").value = DirectCast(DetailsView1.FindControl("Ueberschrift_E"), TextBox).Text cmd.Parameters.add("@Text_D").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text cmd.Parameters.add("@Text_E").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text cmd.Parameters.add("@ArtikelID").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text cmd.ExecuteScalar() cmd1.Parameters.add("@Datum").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text cmd1.Parameters.add("@NewsID").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text cmd1.ExecuteScalar() conn.close()End Sub Regards
I am trying to import some data from csv files. When I try it using bulk insert I get a conversion error. When I use the exact same format file and data file with an openrowset it works fine. I would prefer to use the BULK insert as I can make some generic stored procedures to handle all my imports and not have to code the column names in the SQL. Any suggestions?
BULK Insert stuff
From 'c:projects estdatalist.txt'
with
(FORMATFILE='c:projects estdatamyformat.xml')
insert into stuff (ExternalId, Description, ScheduledDate, SentDate, Name)
select *
from OPENROWSET (BULK 'c:projects estdatalist.txt',
FORMATFILE='c:projects estdatamyformat.xml')
as t1
The destination table has more columns than the data file. The Field IDs represent the ordinal position of the columns in the destination table. Column 1 in the destination table is an int identity. The conversion failure is from trying to convert column 5 to int which makes me think bulk insert is ignoring the name attributes in the XML and just trying to insert the columns into the table in order without skipping.
I am having quesion about attributes acting as both inputs and predictive outputs in a mining models, I mean if we are going to predict the outputs, then I cant actually see the effects of themselves acting as inputs as well?
I dont really see through the points of it.
Could please any experts here shed me any light on it.
I am looking forward to hearing from you shortly and thanks a lot in advance.
In my environment always on is there. Today I observed that primary server fail over to secondary server .now the secondary server acting as primary role.
Can I know when is fail over is happened and who did the fail over. Is there any script to find this?
A user was created with a limited privilege under the USERS group. Once this user loged in the Report Manager he is acting like an Admin and Content Manager, though he is not given even a browser role.
What do u think that this guy is acting like a Super User evenif he is restricted to a browser role on the Report Manager ????????????
I have a dts package that is mysteriously changing back to a previously saved version. Package is saved, and re-opened with saved changes, then moments later opened again to have been reverted back to an older version. Has anyone ran into this before? The only thing that I can think of is that if a version of the package is left open on some other machine (as the older version0, it may be auto-saving, and reverting back.
I run a website, so all of this is done remotely, I do not have access to the servers directly.
On my prior host, everything on the DB worked just fine. Had it optimized and running pretty good for the site by adding indexes, etc.
Then I moved to my new faster server (new host) and at first everything seemed to be just fine. However, some users started complaining about speed in spots.
Sure enough when I checked a given page -- WHAM... the page took about 14 seconds to load. Not exactly fast. :)
So I checked to make sure the indexes copied over and sure enough they did. But it was still slow, even in Query Analyzer/MSE.
So I decided to rerun my view script... on a lark... just to see if that affected something. And sure enough it did. The page started running at 1 second or less. OK, I thought, the view just got corrupted or something.
All was fine until the next night when it happened again. I had added 12 new rows to the table (I do this nightly) and it seemed that adding the new rows slowed down the system. Until the view was rebuilt. With that done again, it's worked fine.
But each night now I have to rerun that view.
What gives??? Is there a DB setting or something causing views/indexes to not be maintained?
The prior server was 2000 or 2003. The new one is 2005.
Again, I cannot get access to the box itself except through Management Studio. The host's tech support group is less than useless, treating everyone as incompetent before they help you.
I'm trying to CAST an integer to a varchar and I keep getting e+006 in my result set. The reason I need to do this is so that I can combine 2 columns to return a string.
I was developing a data-driven website but I got this error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(I have Sql server 2005 also downloaded before) Please help me cause any website that has data can not be executed even the quick start tutorial that ships with the ASP.NET 2.0 What should I do?
Hello and thanks for taking a moment to read this message.I have two colums from which I am taking values. One of which(a bit field), i am running a CASE statement against it to get SQL Server to return a string. The other is just a simple varchar column. My SELECT statement for the columns look like this: SELECT Case tblDisplayProfile.Approved When 0 then 'Not Approved' when 1 then 'Approved' else 'Not looked at' END AS Approved, tblDisplayProfile.DisplayProfileDesc These statements do return me something. Now what I want to do is combine (concantenate) the two fields. This is where I have problems. Any suggestions would be greatly appreciated.
Why does ado.net produce this error when I kow for a fact that there are no connections to any sql server 2005 database involved - this could and probbaly has had people off looking at the wrong connection strings in their web.configs. Note:- The connection string we have is to a sql 200 db I just wanted to make that clear. CheersGegor Error Message:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Stack Trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)
Hi, I moved a SQL 2000 DB to a SQL 2005 server. When I run my .NET 1.1 or 3.5 website to call a stored procedure it returns the wrong results.The SQL stored proc runs fine when I excute it within SQL Manager but gives a different result when called via .NET The funny thing is I have 3 SQL servers 2000 , 2005 , and another 2005. The same data on each of the databases and the same website. Two of the databases work but the second 2005 database my production server decrements the dates some how. The funny thing is the stroed proc is not returning dates as such but a varchar of the name of the day that's getting decremented. My stored proc is SELECT
Case datepart(dw, DT_DATE) When 2 Then 'Mon' When 3 Then 'Tue' When 4 Then 'Wed' When 5 Then 'Thu' When 6 Then 'Fri' End AS [WeekDay], Sum(VL_HOURS) as Total
FROM ...
WHERE ....
GROUP BY datepart(dw, DT_DATE) and My c# .net code is "SqlHelper.ExecuteReader( connectionstring, storedproc, paramaeters);"using MS.ApplicationBlocksWhat happens that from sql, and 2 of my websites is when week day = 4 it returns "Wed" However on my Production server it returns "Tue" ???
i have a form which takes in a lot of input...one of them is a date field..am passing all the values to the stored proc and inserting into a table from there...pretty simple...however when the user does not enter any value in the field...its errors out as...
Hello! MSSQL Server 7.0 SP1 When I open DTS designer for some unknown reason I'm getting error message "Could not create Component Categories manager". Then under Task I don't have any choices. Is there a quick way to fix it?
I was running a DTS package which transfer a complete database, about 2.3GB in size, to another database on the same server. It was necessary for environment change from dev to QA. I baby sat the package till it was rebuilding indexes (90%) complete. This morning I have the following error message on my screen,
[Microsoft][ODBC SQL Server Driver][SQL Server] [Microsoft][ODBC SQL Server Driver][SQL Server]Location: r:SPHINXNTDBMSqueryqeexecqsxchng.inl:749 Expression: (m_cbPageMac + ulPxvarSize) <=m_pxpktdesc->m_cbPageMax SPID: 40 ProcessID: 347
I have no idea at this time what caused it. And as far as I can tell there in r drive mapped wither on the serve or my computer ( I was running the DTS package from my machine).Any help is appreciated
Last night our production server went crazy. The primary (production) database MDF file had size 15Gb and then for some reason the size became 0 (zero) K, So the SQL right away reported the error:
I/O error 38(Reached end of file.) detected during read of page buffer..
Error: 823, Severity: 24, State: 10
And the database became "suspected". Now we restoring it, but have anybody seen such a thing before? I'm breaking my head to figure out what could cause this problem!!!!!
Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o( what maybe the problem?
When I run the command: exec master..xp_cmdshell 'NET USE' from the analyzer the box responds there are no entries in the list.
After that, I run the command: exec master..xp_cmdshell 'NET USE Z: /DELETE' after which the box responds with a "network connection could not be found." and that's all okay.
The weird thing is: exec master..xp_cmdshell 'NET USE Z: \MACHINESHARENAME' results in a "The local device name is already in use.".
The machine in this particular case is the box itself. I have no problem accessing other disks on other systems. I can see the share using the view command. There's no maximum on the share itself and I can connect to the share using another sql box with the same user.
I don't know why it won't budge, worked before like a charm. After six months or so it just stopped. Anyone seen/solved this behaviour?
I have a datetime field with a value of '-28049-03-16 10:01:16.267' in SQl Server 2000. I haven't a clue how or why this value was entered as I did not design or write the database. However, I do have the task of writing a DTS package to export the data to Access. The problem is that the DTS fails when it tries to export the record with the above value into an Access table with a Date/Time field. I thought I could get round this by using a UDF to say if the date equals this value then make it null:
RETURNS DateTime
As BEGIN DECLARE @CheckDate varChar(300) SET @CheckDate = CONVERT(varChar(300),@Date)
IF (@CheckDate = '-28049-03-16 10:01:16.267') BEGIN SET @Date = Null END RETURN (@Date) END
However when I use the UDF it gives me the following error:
Server: Msg 542, Level 16, State 1, Procedure UDFn_CheckDate, Line 23 An invalid datetime value was encountered. Value exceeds the year 9999.
I have tried many variations of Convert, Cast etc. but still can't get it to work.
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP). SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method? :(
INSERT INTO Contact_Info (First,Last,Age) VALUES ('Alex','Strait',19)
Now it seems basic, but there in one more field that you're not supposed to specifiy a value in the SQL, its an int IDENTITY datatype field called num_id. Now this SQL works if it doesnt have the num_id field. But heres the error is gives me, I have never seen it before
Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
I have this stored procedure that works a little odd, some times it do what I want it to do which is....
- Insert orderinformation in tbl_Torderinfo table and then transfer all rows that are associated with a ordernumber from tbl_Ctemp to tbl_Torders
but sometimes it only insert data into the tbl_Orderinfo table and doesn't transfer the order details from tbl_Ctemp to tbl_TOrders even if there is rows to transfer. Any ideas of what might cause this odd behaivior?
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP). SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method?
