I have created several DTS packages that are exectued by a COM application on receiving the text source files from the data source. The problem I am having is that if the source file is empty I receive the error "Error creating datafile mapping: The volume for a file has been externally altered so that the opened file is no longer valid". If the package fails then the calling application returns an error too. I am wondering if it is possible to have a dts package succeed if it receives an empty file, or if I can create an activeX step at the beginning of the package to check the size.
Hi every one I am new meber of this community with an urgent new problem with SQL server DTS process:) pls HELP! PROCESS DETAILS:
My data base(db) is on main frame(flat file with hierarchical db format) I used vb script to get that db and split it in to three files so processing is easy after this I use dts PROCESS WITH EXECUTE SQL TASK in combination of some bulk insert scripts to pick these files from server directory and import it in to sql server tables. I know I could have use bulk insert task in dts but my formate file was giving me a problem while I try to develop it through that process so I ended up using tsql bulk insert statment with formate file in the EXECUTE SQL TASK .
PROBLEM : My problem is I have to manually activate VB script where as I want this to be part of my DTS packet so it can be automated.
I know there is Active x scripting task in DTS but have not used it can some one please tell me if this script can be put in to Active X scripting task with or with out any modifications and also what are the modifications I need to do so it can run before other DTS task (which picks the file and insert it in three different tables after format file application.
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
'Set SourceFile = fso.OpenTextFile("C:SourceWGEE.NONX.GMM220D.UNLOAD.G00178888V00", ForReading) Set SourceFile = fso.OpenTextFile("C:SourceDataNotepad.txt", ForReading)
set l1 = fso.OpenTextFile("C:SourceLevel1.txt", ForWriting,True) set l2 = fso.OpenTextFile("C:SourceLevel2.txt", ForWriting,True) set l3 = fso.OpenTextFile("C:SourceLevel3.txt", ForWriting,True)
Do While SourceFile.AtEndOfStream <> True retstring = SourceFile.ReadLine
if Instr(1,retstring,"PACM",0) > 0 then Level1No = Level1No + 1 Level1Str = Rpad(Level1No," ",10) l1.WriteLine(Level1Str & retstring) End if
if Instr(1,retstring,"PACPRS",0) > 0 then Level2No = Level2No + 1 Level2Str = Rpad(Level2No," ",10) l2.WriteLine(Level1Str & Level2Str & retstring) End if
if Instr(1,retstring,"PACACC",0) > 0 then Level3No = Level3No + 1 Level3Str = Rpad(Level3No," ",10) l3.WriteLine(Level1Str & Level2Str & Level3Str & retstring) End if
Loop
Function Rpad (MyValue, MyPadChar, MyPaddedLength) Rpad = MyValue & string(MyPaddedLength - Len(MyValue), MyPadChar) End Function
if i try to delete data via MFC from Microsofts SQL Server 2000 i get following message: Data can be read only.. Weird i can write and read already. do i have to setup anything in Micrsofts SQL Server 2000 ??
Hi!I have a large project that is due to complete this week. In order tocomplete it I need SQL Server 2000 installed on a remote server. Mydisk is corrupt and to order another media disk would damage mydeadline. I have the licence and serial key, but now need good installfiles. I am even ready to buy another retail box, if I can find asupplier that would give me a download site for the media, while I waitfor the shipment!Please PLEASE help!Regards,Barry
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1 -- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
Im new to stored procedure, but here's what I want, if someone can get me started and provide this as an example it would be VERY welcome!I have the following, a SP with parameter IsMale. This parameter may be empty. If it's not empty I want to add some text to my selection query: AND IsMale=paramvalueHere's the SP so far:ALTER PROCEDURE [dbo].[spFindUsersAdvanced] --declare parameters here@IsMale bitASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DISTINCT BirthDate,IsMale, FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID WHERE tblUserData.Username<>''IF @IsMale THENBEGIN AND IsMale='True'ENDELSEBEGIN AND IsMale='False'ENDEND IFEND1. How do I check if the parameter is empty?2. How do I add the text to my selection query?
Hi all,I am trying to write a stored procedure, which has two insert statements.the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?thanks for your help!
Hi plz read below is my scenarioI have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.Thanks in advance.bye bye.happy coding!!!
I've few questions regarding SQL Server. Appreciated, If any one out there help me with it.
I would like to do T-SQL Programing and would like to know some good book. I'll mostly be doing Stored procedures,Triggers and views programming. Any advice?
On my sql server I've 10 logins. But, I cannot view these logins from the Enterprise GUI. But, When i run a query against the syslogin table, those all 10 logins shows up. Why is that?
I'm trying to construct the following SQL statement but am missing something somewhere.
I get a syntax error with the 1st FROM highlighted.
If i construct a query out of the bit that works and then construct a query on that query then i can get it to work no problem but how to mimic that in SQL ? I can't simply view the SQL of the queries because the query names replace the SQL statements.
I'm trying to SUM the 5 volumes that get returned by the TOP 5 SQL statement. The VotingUniverse_TotalVolumeByType query returns the TotalVolume which will enable me to divide the SUMmed 5 volumes by the TotalVolume to obtain a %age.
SELECT Type, Sum(Volume) AS Volume, TotalVolume FROM >>>>>>>>>>this following bit works............ (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Type, VotingUniverse_TopFirmsVolumeByType.Volume FROM VotingUniverse_TopFirmsVolumeByType WHERE (((VotingUniverse_TopFirmsVolumeByType.Volume) In (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Volume FROM VotingUniverse_TopFirmsVolumeByType ORDER BY Volume DESC))) ORDER BY VotingUniverse_TopFirmsVolumeByType.Volume;) >>>>>>>>>>>end of bit that works INNER JOIN VotingUniverse_TotalVolumeByType ON Type = VotingUniverse_TotalVolumeByType.Type GROUP BY Type, TotalVolume;
I have a xxx.exe running on SQL 2000 server as a sql job every hour. For some reason this job sometime just hung in the middle and never finish. But if I stop it and rerun it, it will be down within 10 seconds. I am wondering if we can set up something so that the job aborts automatically if it ran more than 10 minutes.
We are running Sql 6.5 sp5 on a Pentium II 350 Mhz with 512 Mb. RAM, 1 ScSi 4 Gb hd, one 8Gb ScSi hd and 2 IDE hd (13,6 Gb and 8Gb) computer running windows NT 4.0 sp 6 without any problems. Now have bought a new computer. The new one is a Pentium III 733 Mhz 512 Mb Ram with 2 ScSi ultra wide 2 18Gb each and one 40 Gb Ide Hd. We have installed windows NT 4.0 sp 6 and Sql 6.5 sp 5. We have restored our database to make some tests to it. The database is 7 Gb big. The results of our test are that the old computer is between 20% and 30% faster than the new one when doing sql instructions and using the database even knowing that the hd´s of the new computers are 4 times faster than the old ones.
Would you please give us any help or clue that would make the new computer go faster? We know that upgrading to sql 7 will work but we need first to make this computer work better with the 6.5. Thank you.
I often use DTS to move databases between the servers. When you choose "Transform" data and “Column Mappings and Transformation” window opens, the default option is “Append rows to destination table”. Question: Is there a way to change the default option to “Delete rows in destination table”, so I wouldn’t have to go thru the transformation of dozens of tables every time I move databases around.
P.S. I know that I can save DTS package with my settings and use it later. The problem is that every time it might be different database or objects might change. So, it is not a solution for me.
I have a client requirement. We are integrating two applications. So the problem is that, whenever some updates (meaning to say "create" , "delete" , "update") happens in the SQL DB Tables, i needed to track that data and send it to a different system.
What I have thought abt is that if SQL could generate a file with all the values deleted/updated/created in the DB, then I could take those values and do the needful. Please help me..
Hello guys, I'm new to the forum and to MS SQL 2K. I'm trying to a merge similar rows in a table into a single row and put them in a new table.
Example:- This is my input table TableA ID A B C ------------------------ 1 jk kl bj 2 sd we op 3 io po kl 1 ui gh ew 2 kl re op 1 qw kj nn
My output table should look like this TableB ID A1 B1 C1 A2 B2 C2 A3 B3 C3 ----------------------------------------------------- 1 jk kl bj ui gh ew qw kj nn 2 sd we op kl re op 3 io po kl
Please help me on how to create my output. Thanks in advance,
Columns PartnerId: int NativeId: int FirstName: nvarchar(50) LastName:nvarchar(50)
1)I should get the records with a minimum native id for a particular PartnerId, 2) if duplicates exists in the above condition i should select top 1 (first record)
Hi, I have been SQL developer for past 2 yrs, want to get into Administration(DBA) can any one suggest me some good articles on net and good books for this.
Hey all, I need some help to build some data bases and maybe some morestuff. Please reply only if you can do it volunteerly or very cheap please;but there is incentives in time..TIA
I have a tableCreate Table Payments {paymentid int,customerid int,amount int,date datetime}What I want is the sum of the amounts of the last payments of all customers.Now the last payment of a customer is not necessarily the one with thehighest paymentid for that customer BUT it is the one with the highestpaymentid on the MOST RECENT date. We dont keep the time part just the dateso if there are more than 1 payments of a customer on a date ( and there aremany such cases ) only then the paymentid decides which is the last payment.Further the last payment may be the last as of today but I may want to findthe sum of all the last payments upto say March 1, 2003or any date. My own solution is too slow even it is correct.SELECT SUM( AMOUNT )FROM PAYMENTS AS P1WHERE PAYMENTID =( SELECT MAX( PAYMENTID ) FROM PAYMENTS AS P2 WHERE P1.CUSTOMERID =P2.CUSTOMERID AND DATE =( SELECT MAX(DATE) FROM PAYMENS AS P3 WHERE P3.CUSTOMERID = P2.CUSTOMERIDAND DATE < #9/8/03# ))What would be the most efficient solution to this.Both in SQL Server and in Access 2000thx in advance
I am using the following T-SQL to pull records from a given table that have a start time between 6:59:59PM and 7:00:00AM. However, it's not working. The SQL I'm using is listed here. What am I doing wrong?
select RecNum, convert(varchar(8), StartTime, 8) as Start_Time from TableA
where convert(varchar(8), StartTime, 8) between '18:59:59' and '07:00:00'
By the way, there are a multitude of records that I'm looking for in the table.
I need some Help with VSA in SSIS . Whenever i open VSA for scripting , it shows the following validation errors.
Warning 1 The dependency 'EnvDTE' could not be found. Warning 3 The dependency 'Microsoft.SqlServer.DTSRuntimeWrap' could not be found. Warning 5 The dependency 'Microsoft.SqlServer.msxml6_interop' could not be found. Warning 4 The dependency 'Microsoft.SqlServer.PipelineHost' could not be found. Warning 6 The dependency 'Microsoft.SqlServer.SqlTDiagM' could not be found. Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found. Warning 7 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
I am able to locate these dependencies in the c: programfilesmicrosoftsqlserver90SDKAssemblies.
APPLICATION SUMMARY * Online only application. * Trust url parameter is set. ERROR SUMMARY Below is a summary of the errors, details of these errors are listed later in the log. * Activation of C:UserspmohanDesktopReportBuilder(2).application resulted in exception. Following failure messages were detected: + Downloading file:///C:/Users/pmohan/Desktop/ReportBuilder.exe.manifest did not succeed. + Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. + Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. + Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'.
COMPONENT STORE TRANSACTION FAILURE SUMMARY No transaction error was detected.
WARNINGS There were no warnings during this operation.
OPERATION PROGRESS STATUS * [4/3/2008 3:25:51 PM] : Activation of C:UserspmohanDesktopReportBuilder(2).application has started. * [4/3/2008 3:25:51 PM] : Processing of deployment manifest has successfully completed. * [4/3/2008 3:25:51 PM] : Installation of the application has started.
ERROR DETAILS Following errors were detected during this operation. * [4/3/2008 3:25:51 PM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype) - Downloading file:///C:/Users/pmohan/Desktop/ReportBuilder.exe.manifest did not succeed. - Source: System.Deployment - Stack trace: at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next) at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles() at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState) at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation) at System.Deployment.Application.DownloadManager.DownloadApplicationManifest(AssemblyManifest deploymentManifest, String targetDir, Uri deploymentUri, IDownloadNotification notification, DownloadOptions options, Uri& appSourceUri, String& appManifestPath) at System.Deployment.Application.ApplicationActivator.DownloadApplication(SubscriptionState subState, ActivationDescription actDesc, Int64 transactionId, TempDirectory& downloadTemp) at System.Deployment.Application.ApplicationActivator.InstallApplication(SubscriptionState& subState, ActivationDescription actDesc) at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut, String textualSubId, String deploymentProviderUrlFromExtension) at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state) --- Inner Exception --- System.Net.WebException - Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. - Source: System - Stack trace: at System.Net.FileWebRequest.EndGetResponse(IAsyncResult asyncResult) at System.Net.FileWebRequest.GetResponse() at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next) --- Inner Exception --- System.Net.WebException - Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. - Source: System - Stack trace: at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint) at System.Net.FileWebRequest.GetResponseCallback(Object state) --- Inner Exception --- System.IO.FileNotFoundException - Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. - Source: mscorlib - Stack trace: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, Boolean useAsync) at System.Net.FileWebStream..ctor(FileWebRequest request, String path, FileMode mode, FileAccess access, FileShare sharing, Int32 length, Boolean async) at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint)
COMPONENT STORE TRANSACTION DETAILS No transaction information is available.
into a 1-column recordset containing 2 rows with the values 'Martin' and 'Cyril'
Here's the nearest I have got:
Code Block declare @pElementListXML XML set @pElementListXML = ' <ElementList> <Element>Martin</Element> <Element>Cyril</Element> </ElementList>
' ;WITH nodes_cte as ( SELECT [c].[value]('/', 'NVARCHAR(40)') Element FROM @pElementListXML.nodes('/ElementList/Element') T(c) ) SELECT * FROM nodes_cte
Hi everyone, I€™m pursuing a fast deployment for SSIS. I mean, we€™ve got ten ASP/VB6 developers which often doing some DTS stuff by mean, of course, Enterprise Manager.
Now, they€™d have to handle the same but with SSIS. I don€™t have very clear whether only is necessary install them SSIS component or it€™d requires something else. Framework 2.0 too??
We want to create an Internet sealing system. The web server where the site is located isn€™t owned by our company however the SQL server that contains reservation data is in our Local Area Network. In the beginning, due to security reasons, we considered to set another SQL Server in our DMZ and update our internal SQL Server through Replication. We€™ve been highly recommended to use Web Services instead of Replication. Replication has been let out; however, I€™m thinking of setting a SQL Server in the DMZ which only has stored procedures that update our Internal database (The database which contains reservation data). What€™s your opinion about this? I know that Web Services is the best choice but I would like to know if it€™s a good alternative.
I'm a newbie to SQL server..... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user?
The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom.
Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P.
We want to create an Internet sealing system. The web server where the site is located isn€™t owned by our company however the SQL server that contains reservation data is in our Local Area Network. In the beginning, due to security reasons, we considered to set another SQL Server 2000 in our DMZ and update our internal SQL Server through Replication. We€™ve been highly recommended to use Web Services instead of Replication. Replication has been let out; however, I€™m thinking of setting a SQL Server in the DMZ which only has stored procedures that update our Internal database (The database which contains reservation data). What€™s your opinion about this? I know that Web Services is the best choice but I would like to know if it€™s a good alternative.
Hello geeks, I am new to database thing, wanted to know that does MS reporting services 2005 works with SQL 2000..? if yes than can any one please give me reference link. I appreciate for the efforts to be taken by you people.
Hi I'm a newbie to SQL server..... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user? What about the case where I reaches let's say 1000 users in the one user per database case, it would be extremly difficult to have an overview of the databases or what? The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom. Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P. Regards Joachim