Before I start let me say I know this is a silly way to go about
things, but it is one of those "my company made me do it" things.
My company is in the process of ( finishing ) migrating from foxpro to
sqlserver.
They have a foxpro program that does a lot of various updates. I
would like to write a tsql script to replace it.
The trick is that the tsql script would have to have some procedural
programming artifacts that I don't think it has.
I/my company would like the script to generate output messages that
the user can see while the script is running. Everytime I have ran a
script in query analyzer I usually don't see the output ("print"
statemetns, etc ) until all of the work is
done. Is there a way to run thing so I can see the output statements
as the work is being done?
Can print statements work from within a tsql loop?
The other thing is that I/my company wants the tsql script to write
output messages to a log file while it is operating ( for error
checking and other logging).
Hi, I'm relatively new to SQL and ASP.NET and I'm after some assistance to what I hope is an easily achievable goal. I need to upload some files (.pdf, .doc, .jpg - whatever) to a directory on my webserver. Once this has been done, I'd like to write some data to an SQL Database Table that will contain information about these files in a datagrid (or something similar) along with a link to download them. E.g I upload car.jpg which is located at /images/secure/car.jpg and is a photo of car. When a user logs in, they will be shown a datagrid which pulls information from said SQL database and in this datagrid will be something like: Filename Description Link car.jpg A pic of a car Download
If anyone is able to help me out with regards to linking to a file from an SQL dB it would be greatly appreciated, and if you require any further information just let me know :) Ben.
Hi guys,I have a stored procedure that I will subsequently post below thismessage. What the stored procedure does is, it reads some specifictables in a database and created XML off it.The problem comes in when the data is bigger than a few hundred/thousand characters. The data is truncated and the XML file is notfully made.Now i have been reading some posts by some people and tried using TEXT/NTEXT/ IMAGE type to write the files but they give me errors which,again, I am adding to this email.STORED PROC:****************************CREATE PROCEDURE usrp_sp_makexmlfile@str varchar(50),@templatefile varchar(255),@ReturnValue as image OUTASSET NOCOUNT ONDECLARE @strVar as varchar(8000)/*DECLARE @ReturnValue as varchar(255)*/Set @strVar = 'Select * from ' + @str + ' FOR XML AUTO'Set @templatefile = 'c: emplate.tpl'EXEC (@strVar)SELECT @ReturnValueGOTHIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:************************************************** *******An unhandled exception of type 'System.InvalidOperationException'occurred in system.data.dllAdditional information: Parameter 2: '@ReturnValue' of type: Byte[],the property Size has an invalid size: 0Also, if i try to add an integer value to the image/text/ntext like8000 or something less than that, it tells me that the result has to bediscarded because the current process has had some error.Can someone give me a suggestion on how to resolve this or an examplethat would illustrate the solution? Thank you all in advance.Pi.
Hi guys,I have been trying to write data from a database to XML files andalthough i have been successful in doing that, the file(s) that are madeare not of the form that I would want.I am using:SQLDataSet.WriteXml(strFileName, XmlWriteMode.WriteSchema);to generate the files where strFileName is the name of the file. Thefiles when viewed with XML Editor are seen as lines of data instead ofthe regularly generated XML where each row is one below the other. Whenviewed with WordPad/Notepad i see elements />< betweeen the dataand somehow I think this has to do something with the problem.Can someone help me with this?? Any help or suggestion would be greatlyappreciated.Thanks.*** Sent via Developersdex http://www.developersdex.com ***
I need to write text files of the size more than 1GB! First thought of doing this with BCP but then fetching data from stored proc was taking too long (more than one hour) and so BCP did not seem to be the right option as we have less flexiblity in terms - handling errors, knowing the status as to what point the writing has been complete etc. Most importantly getting such a huge amount of data did not seem to be a one time job! So I am now thinking about writing a CLR procedure which fetches smaller amount of data at a time and writing to file. Something like - open connection, get data, close connection, write to file and continue in cycles of this till whole file is written. Does this makes sense considering the amount of data to be fetched?
Any suggestion/comment on this will be much helpful!
I am having a scenario where all the stored procedures are stored in a folder (one sql file per sproc). Stored procedure does not have 'IF Exists .... DROP Procedure' in the script so before creating them we have to drop all sproc manually.
Can anyone help me writing a script / SSIS process to loop through each file in folder and write "IF EXISTS ... DROP PROCEDURE" with the procedure name in it ??
I can create a package that loop through each file in FOR each Loop task but dont know how to write in file using .net
We have a package that is using a ForEach loop container to access files on a network drive. For some reason I am getting a message that the ForEach enumerator is empty and did not find any files that matched the pattern. For the pattern I left the default *.* for testing purposes. I have specified the file folder as \remoteserverfilesharesubfolder and also as \remoteserverc$filesharesubfolder and have gotten the same message. However when I map a network drive and set the file folder to the network drive it finds the files. Is this a permissions issue?
After I finish processing the file I want to move it to a new directory. Once this is deployed in production, the package will not be running under a domain account and probably won't have access to the network folder. Is there any way to specifiy in the connection manager itself that it should use a specific account to access the folder?
Is there a quick way to extract a full dump of 50 tables to 50 corresponding text files?
i.e.
table_a has to be extracted to table_a.txt
table_b has to be extracted to table_b.txt
table_c has to be extracted to table_c.txt etc.
I don't want to have to add each one separately by hand in the DTSX package designer. I can't see any way to do it in a loop (because you have to do the field mapping). I can't seem to get the DTS Wizard to help - it only seems to be able to handle one table-to-text extract at any one time. And I've tried editing the DTXS file directly (in XML) but it looks like it's going to be rather complex, even if I only do it to define the connection managers. Feel free to suggest any better way to do this, though the specification has already been agreed, so I'm unlikely to be able to change it. Thanks
I want to write a batch file that will do just that. The problem is bcp or bulkcopy never works with my code, it doesn't recognize it. Maybe I'm going the wrong way to do this, but I could use some help. Ideally I'll make the batch to do the importing via bulkcopy or something like it of a text file into a table and use windows scheduler to automate it. Be as specific as you can please, I'm very new to sql server.
Okay, here's my thing: I have a file that looks (sort of) like this:
A1a A2b A3c A4d A1e A2f A3g A4h A5i A1j A1k . . .
What I want to do is split these apart. A1 should be the first record in every new file. The first file would have four records, the second one would have five, the third one would have one, etc. How do I specify more than one output? Is there a way to do, e.g. "Output001.txt", "Output002.txt", etc.?
In SQL2005 (SP2) I have a standard user (User1) who owns some jobs. He has been granted the "SQLAgentUserRole" in the MSDB database so he can see his jobs and run them. He has all the normal permissions (Select, Update, Insert, Execute, Delete, View References) to the schemas in the database he works in.
Here is my problem. When a developer who is logged in as User1, tires to edit a job step that has an output file (which was initially moved from another server by a DBA with SA rights) he gets the following error message.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Only a sysadmin can specify '@output_file_name' parameter for a jobstep. (Microsoft SQL Server, Error: 14582) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3156&EvtSrc=MSSQLServer&EvtID=14582&LinkId=20476
I have tired all kinds of permissions (like granting the user SQLAgentOperatorRole) but it is still a no go. If I (as the DBA) remove the job step's output file, then he can edit it. He can also edit other job steps in the job, as long as they do not have output files associated with them.
How can I let the job owner use output files with their job steps and not make them SA. It just does not make sense.
I've just started using SQL Server 2005 Integration Services and I've come up against a situation where I need to name output files dynamically (i.e. based on a timestmap). Looking through this newsgroup, and other web resources it looks like my only option is to use a Script Task/ActiveX Script Task to rename the file after it has been created with a generic file name. I was wondering if there was a different approach or if there was a way to pass in a variable to the file connection manager that I could append to the file name at run-time.
I'm using isqlw to generate delimited text files from scripts. There are several SET options available so that you can restrict the output to just the data in the script, for example SET NOCOUNT ON.
I can't find a similar SET option to suppress the column headings, although there is a tickbox that allows you to do this in Query Analyzer (Query -> Current Connection Options -> Advanced -> Print Headers)
DTS packages appear to support this, yet I can't find the setting from a dts file.
i have a weird situation here, i tried to load a unicode file with a flat file source component, one of file lines has data like any other line but also contains the character "ÿ" which i can't see or find it and replace it with empty string, the source component parses the line correctly but if there is a data type error in this line, the error output for that line gives me this character "ÿ" instead of the original line.
simply, the error output of flat file source component fail to get the original line when the line contains hidden "ÿ".
I am trying to get a result like below without using the cursor.
col1 col2 col3 col4
1111 date uniquenumber 6 1111 date uniquenumber 5 1111 date uniquenumber 4 1111 date uniquenumber 3 1111 date uniquenumber 2 1111 date uniquenumber 1 2222 date uniquenumber 4 2222 date uniquenumber 3 2222 date uniquenumber 2 2222 date uniquenumber 1 3333 date uniquenumber 2 3333 date uniquenumber 1
the column that say unique number is unique and is not duplicated and date column might have duplicates
Please advise whether it is possible to write a query without cursor.
I am looking to create a incremental value based on the resulting insert that I am using. There already is another field being used as an identity field. I have a beginning value that I just want to add the row number to for the insert.
insert into lineitem select substring(group_id,4,len(ltrim(rtrim(group_id)))-3) as co_code, 0,0,(case when enddate < cast(month(getdate()) as varchar(10))+cast(day(getdate()) as varchar(10)) then 'Prior' else 'Current' end ), left(acct_type,2) as bene_type, convert(smalldatetime,left(ltrim(rtrim(eff_date)), 8)), 0,trans_amt,0,0,convert(smalldatetime,left(ltrim(r trim(sett_date)),8)), ltrim(rtrim(b.fname)) + ' ' + ltrim(rtrim(b.lname)) as payee,0,0,a.ssn,'Y',999+count(*), (case when isnull(b.location,'') = '' then '' else b.location end) as location from mbi_tran_temp a left join enrollees b on a.ssn = b.ssn and ltrim(rtrim(a.group_id)) = ltrim(rtrim(b.mbicode))
The '999+count(*)' is where I would like to have the incremental value.
Hi Friends please let me know how can we incrementally load a destination table with source table. bearing in mind that we need to track that there are no duplicates in the destination table. I need to load only changed or new data in the final load. Please give me some examples also. I am tryin this from last 2 days as I am totally new to SSIS.
I have done an bulk upload and I would like to start doing incremental uploads. I just want to upload only the new records that have been added to my data source ( free foxpro tables ). Can anybody point me to an example or info to accomplish this.
Dear all,I have an SQL table where I am doing insert from an asp.net web project.I have a primary Key, set to be incremented by 1on each insert.I have another column, Col1 that should be incremented on each insert, How can I do this?I need to have 2 incremental columns? do u have any idea on how to do this?I am doing this right now by calling a select statement in the Add stored procedure on this table, I read the last value of col1 and then I increment it by 1, this work fine on development environement, but when many users are accessing this website, I will have wrong values for col1. Any idea??Thanks.
hi, i need to create two instances of db and transfer incremental uploads from one db to another without having to transfer the entire table of data again and again. how should i go about it? what commands should i use?
i have a catalog and add directory which has 10,000 documents and all are index but if i add 1000 documents to that directory and i don't want those 1000 documents to be indexed. i want only previous 10,000 index document and don't want to new document to be indexed. is there any way can stop the new document to be indexed, please let me it's bit urgent. Thanking you in anticipation
I've created an SSIS package that loads data from source to destination, using Lookup and conditional Split to check New rows and changed rows for one table.
Now I want to take this father by loading data for multible table more that 100. I did it in T-SQL using dynamic sql and cursor.
