i have started developing on DTS recently. and i have a few question about "How-To" issues.
Hope you guys can help
1) I noticed, whenever i want to "redirect row" whenever an error happen, i need to set my AccessMode to OpenRowSet... This mode as i noticed, cant be used when i am tranferring data into a sql table which has primary key set
e.g. Ms Access which has identity -> SQL table with PK set
Is there anyway, to capture the row error in my situation?
2) Another question is, in a data flow... can i have a source which has multiple output arrow.
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
I have some tasks that I need to accomplish within T-SQL but cannot find a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using the BULK INSERT command which works fine but it does not allow the following..
I am tying to call BCP to output the contents of a table to a text file from with in a stored procedure. The procedure will be called from an ASP page ... My question is were does the file get created ??? I want to create the text file on server ONE and SQL server is running on server TWO and IIS is running on server THREE... do i have to have a drive letter mapped to server ONE and if so is it mapped on the SQl server or the IIS server ....
ie: exec master..xp_cmdshell bcp db..table out h:est.out -Uxx -Pxx -Sx
I have some tasks that I need to accomplish within T-SQL but cannot find a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using the BULK INSERT command which works fine but it does not allow the following..
I have two questions, 1) Could anyone please point me in the right direction concerning information pertaining to NT Server Enterprise Edition verses NT Server Workstations. We are having problems running SQL Server 7.0 and the Enterprise Edition together on the same machine and was wanting to find information about compatability issues, if there are any, 2) I, on a SQL Server 6.5 database shrunk it by 2 Gb. When I looked to see if SQL Server released those 2 Gb back to the hard drive, I was amazed that it didn't! Did I miss something or will SQL Server 6.5 not release the space because of the initial set-up. And why did SQL Server 6.5 automatically take the space from the Transaction logs when neither of the devices were specified? Is the Transaction Log the default area for shrinkage?
Thanks in Advance! Daimon Russell daimon_r@hotmail.com
1. When we create DTS in SQL Server through DTS designer, where are they stored physically? 2. What would be the best way to modify a DTS without using DTS designer? 3. Is there any other way to create DTS apart from DTS designer and Visual Basic? 4. Is there any website which has detailed information for DTS? (which has more FAQs like above?)
In our production environment, we keep changing the servers frequently, and everytime that happens, I have to change the connection properties in all the DTS going to them one by one.
I am not too familiar with SQL Server, but my supervisor gave me the task of finding out the difference between SQL Enterprise and SQL Standard. He also asked me to research the difference between processor licences and client access licences. I will use the Internet as a resource, but I would also like to hear the opinions of someone who uses these programs or is knowledgable about them. So please any suggestions or any useful links would be very helpful.
I have two questions. 1) If a database is suspect we can have that trace from sysdatabases.There is a column named status.My question is in case of suspect datatbase what will be value in the field status of sysdatabases? 2) The password of an user login(created by using sp_addlogin stored procedure or any other way) is stored in the table sysxlogins of master database.The password is stored in a varbinary format.How can I get the actual password(means in a char format)?I mean how can I convert the varbinary value to a readable format?
Hi All, I am new in SQL SERVER 2000.I have few questions - 1) WHAT WILL I DO TO TRUNCATE THE SIZE OF A TRANSACTION LOG? 2) WHAT WILL BE THE STEPS OF BUILDING THE MASTER DATABASE? 3) WHAT WOULD BE THE PLAN OF ACTION WHEN SQL DOES NOT STARTS UP? 4) WHAT WOULD BE MY PLAN OF ACTION WHEN SQL DB GETS CORUPTED OR STARTS IN A SUSPECT MODE?
Could I do periodicity backups to another computer(mediaserver) using VDI??
I mean , Could I config a Virtual Device so that I can do backups like disk or tape, I can use 'backup database ...to virtual_device='...' ' to do backup to another computer(mediaserver)? suppose that I have finished the interface of mediaserver.
If this is impossible,how can I do periodicity backups to another computer??
I have finished a program using VDI that can do backup to another computer,and I know how to do periodicity backups to disk or tape. but I am puzzled about the periodicity backup using VDI.
I am putting together a proposal for my church, the current DB software (Access) has been outgrown. One proponent of a no name brand software insists that to implement SQL could take a year and a team of programmers, is this true??
Hi Could any one tell me the answers for these questions.
1. how do we troubleshoot a datbase if it is in suspect mode.What is the reason for a database to be in suspect mode. 2. how can we move a file from C drive to D drive, so that the file location in C drive is completely moved . 3.In a particular primary file group there are many objects. How can we move some of the objects from this primary file group in to another file group. 4. can we install a sql server on a remote server so that it will not ask any inputs like domain name, authentication modes. . 5.If we want to implement clustering , can we use the virtual ip address on clustering as the ip address of our system or we have to use another ip address for clustering 6. In 2000 we have DTs package.can we run the same DTS package in 2005.
Hi Im relatively new to ASP/SQL and have been thrown into the deep end by work. Ive got courses to go on, but not for another 3-6months.
Ive got an ASP file calling a database using SQL. Once you hit the "go" button, it puts this data into an Excel file, under a new window (still showing the asp file in the address bar).
It currently shows:
1) Item numbers that end in "0" i.e. 3.10, 12.20 appear as 3.1 and 12.2 respectively in the Bill of Materials … i.e. being treated as decimals … I need them to show-up as text. ************************************************** ******************* 2) When saving the spreadsheet … it would be good to get "X" to set the default file name to: <Quote ID>_<Customer>_<Platform>_<version>.[xls|pdf] ************************************************** ******************* 3) When a spreadsheet is displayed, it is in an editable Excel format. Is it possible to lock the file automatically when its opened or password protect it? ************************************************** ******************* 4) If a spreadsheet window is open and you try and open a new one, the old one pops-up/is still there. Need to be able to close the old one and re-open a new one automatically.
If necessary i will post the code. Any takers? Thanks
I need help with a few sql questions, but you need to look at picture containing the tables and the relationships between them. How do I post or upload the picture first?
Hi all,I'd like to know if it's possible to sort twice in a same SQL query.I use SQL for retrieving Data into an Excel Spreadsheet.(Excel 2000 or XP) and to paste the queries results into an Excelspreadsheet.1. I tried to sort by date and the only way was to use the serail numberinstead of the litteral nameI tried SELECT * FROM [Sheet1$] Where [Date] <= 07/20/2003"but this does not work.I then tried SELECT * FROM [Sheet1$] Where [Date] <= 37822" and itworks.Do you know a way to use 07/20/2003?2. I need to sort my data twice, first by name and second by date. Is itpossible to sort twice in one query?Something like:"SELECT * FROM [Sheet1$] Where [Date] <= 07/20/2003 ORDER BY [NAME] ASC,ORDER BY [DATE] DESC"3. Even if the first row of my Excel sheet includes the headers, I cannot perform a query other than SELECT * FROM, for exemple, SELECT [NAME]FROM, does not work. do you know how to do that?Thanks in advance for sharing your experience,Phil*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
1. If the TempDB Database is deleted from MS-SQL Server what willhappen..?2. How to insert a not null column in an existing table withrecords..?3. If a table is deleted, what will happen for the Stored proceduresand Views, which that table reffered..?
Hi All, I was asked couple of questions yesterday and I thought I had to still learn a lot. Questions sound very normal but as for as I am concerned, don't know the answers hence this forum. Please advise. Here the questions asked to me 1) A simple insert into the table (could be from .net application or query analyzer) takes 10 minutes and times out, does not do any thing. What could be the reason and how would you identify and resolve the problem.
2) A simple select from table taking 10 minutes( assuming there are only there columns in the table,those are, id, name, description) id being identity column and has got index as well. What could be the reason and how would you identify and resolve the problem?
3) How would you change the identity column value or can we change the identity value and how?
4) How would you use the shared, update, exclusive locks in the sql statement.?
The <Protect Sensitive Data Using Encryption in SQL Server 2005> said : If regenerate service master key with "FORCE" option, the keys protected by old service master key are lost forever along with the data encrypted by them.
I create a master key in database and create a certificate protected by it.Then i use the certificate to encrypt some data.
The master key also exists and the encrypted data can by decrypt after i regenertate the SMK with force option. So i'm puzzled,I want to know what are the keys which protected by SMK , are they database master key?
All of a sudden MS-VSA window does not come up after hitting the Design Script... button. Work around: Get out and get back.
What is the work around? Regarding SQLDMO:
Trying to add a reference to SQLDMO using the References node of a Script Task brings up a dialog with just one tab for the .NET. Is there a way to reference COM?
1) When I make changes to a stored proc (drop and recreate ) I have to reissue EXEC permission to users. Is there a keyword that helps to avoid that?
2) In a SP I have multiple select statements. I'm only interested in the results of the last statement. When I run the SP from Query Analyzer it returns a result set for each select statement and When I call it from ASP.net I get .rowcount = 0. What am I doing wrong?
3) Is there a way to return a result set from a SP to another SP that avoids cursors? I'm using a temp table in the outer proc and filling it in the inner proc.
In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach? Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts. You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ...." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return3. Should my data source class return data objects like1st approach[DataObject(True)]pubic class TopicDataSource{ public static Topic[] GetTopicList() { }}or should it return DataSet / DataTable / DataReader like2nd approach [DataObject(True)]public class TopicDataSource{ public static DataTable GetTopicList() {}}Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.4. If I go with approach 1, how should I control which property of my data objects is read-only after it's has been inserted/created? Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)? Or do I set it and the page level and write a code to throw an exception in the rare case the application / user try to change it's value? Or else?Please help. These questions slow me down for days now.If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.I will be extremely grateful to anyone who answer any of my questions.Thanks a lot.PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.
say i have this file (below) and want to enter it into a table i've made. why cant i do this in query analyzer? and whats the easiest/best way to do itinsert into myTablevalues(1,"StaticHtml","StaticHtmlPageControl"2,"Questions","QuestionsPageControl"3,"Login","LoginPageControl"4,"SubmitSurvey","SubmitSurveyPageControl"5,"Registration","RegistrationPageControl"7,"SubmitUserAttributesSurvey","SubmitUserAttributesSurveyControl"10,"RepeatPageGroup","RepeatPage"11,"ThankYouPage","ThankYouPageControl") alsowhats the easiest way to make a copy of a database with and without the data - on the same server cheers
Hi all,I've been able to successfully use SQLCacheDependency and my test application is using only one database table to test SQLCacheDependency. Now, I was reading "Programming Microsoft ASP.NET 2.0" by Dino Esposito and I've couple of questions.a)-SQLCache will expire on Insert, Update and Delete queries on the database table? Am I right? b)-Right now I'm using SQLCacheDependency on one table, can I use it on more than one table ? If I can't , can you please give me some ideas on how to use SQLCacheDependency on more than one table? c)-Lastly, If large number of clients are using my application, what about the performance gains/losses if I use SQLCacheDependency? I will really appreciate your help. Thanks,Oltmans
Hi All,I've successfully implemented an example of SQLCacheDependency on one database table. I was reading "Programming Microsoft ASP.NET 2.0" by Dino Esposito and I've couple of questions. I will really appreciate your help.a)-Can I use SQLCacheDependency on more than one database tables? If I can't then I will appreciate if you can give me couple of hints as to how to do this.b)-As I understand SQLCache on a table will expire if any Insert and Update SQL operations happen on that table.c)-Our software is being used by many clients. Do you think SQLCacheDependency will have some sort of performance hits?I will really appreciate any help.Thanks,Oltmans
HELP PLEASE! 1) Is it true that INSERT, UPDATE, and DELETE queries can be carried out on only ONE table at a time?2) I have a newly created database which is empty, and has four tables related with FK's. How can I begin to enter data, if i can only do an INSERT or UPDATE on one table. When i do a SELECT query, i don't even get an empty results set to modify. But i think that's an old fashion technique anyway. And if i can only INSERT VALUES into one table at a time, that violates the relational integrity. PLEASE HELP!DanThe only thing I can think of is the de-enforce the Relationships Constraint... Then, one at a time, fill each table's 1st record with fictious data, and then enforce the Relationships again, hoping that the identity columns will match up.
I developed an ASP.NET 3.5 site in VWD 2008 Express (with SQL Server 2005 Express) on Vista Home Basic (no IIS). So I then moved my source over to an XP Pro machine with IIS 5.1 and installed .NET 3.5 and SQL Server 2005 Express. Brought up the site, but it failed when I attempted to login. What do I have to do to get this to work? Do I need to attach the ASPNETDB.MDF in my APP_DATA directory to my SQLServerExpress? Was this taken care of by the ASP.NET config tool when I set up users/roles on the Vista machine?Do I need to set some permissions? Like I said this was working only on through the built in webserver in VWD 2008 Express.ThanksEric
