I am interested in hearing from those who are using SQL7 in production. Please include size of database, number of users, implementation date and experiences to date - good bad or indifferent. Thank-you, Leo
View 1 RepliesHi folks, I have implemented this technique to simplify SCD loads and also to maintain consistent units of work during update/insert of a single row. Wanted to get your feedback on this technique: performance, transaction issues, etc.
I send all rows to an OLE DB Command that performs both update and insert for a single row in a single command:
Code Snippet
UPDATE PROPERTY SET ORD_TERM_DT = ? WHERE ACCOUNT_NBR = ? AND ORD_TERM_DT = '9999-12-31 23:59:59';
INSERT INTO PROPERTY (
ACCOUNT_NBR
, APPRAISAL_COMPANY_CD
, .....
, ORD_TERM_DT
) VALUES (?, ...,?);
This way I can guarantee that if the termination (update) of an old row (say, row 10) succeeds, but insert of the new row 10 fails, that it will roll back. Otherwise, row 10 will get terminated without being replaced with a current record...
Performance: load of 7,734 changed records into a table of 6.8M existing records was roughly 8 seconds. The data flow task container TransactionOption = Required.
We€™re in the process of assessing the content that exists to help people learn and use SQL Server Express. This includes demos, tutorials, whitepapers, Webcasts, Starter Kits, and any other type of content you could imagine. So this is a great chance to TELL US WHAT YOU WANT! We€™ll integrate your feedback and make sure that we try and address the most requested areas of content. Please be as specific as you can.
View 6 Replies View RelatedJust a note to the MS guys...
I'm fully supportive of the product Feedback Center initiative and the subsequent withdrawal of sqlwish.
Problem is, if nobody ever gives us feedback on the things that we submit then it simply is a glorified version of sqlwish with no added value.
I apologise if there are plans afoot to address to offer feedback to the things we put up there but I (and others) have been freely submitting bugs and suggestions for a few months now without hearing anything back and I'm beginnign to wonder why we bother.
Even a simple "This is a good idea and will be considered for Katmai" or "This is a terrible idea now go and stick your head back in the sand" would be better than a cut and pasted response which is just about all I've seen so far.
Comments???
-Jamie
Dear friends,
I'm thinking to take this exams soon... anyone has documents, exams, links or other to help me on it?
Thanks!!
Might be purchasing the SQL Spotlight product, any experience/input from current users would be appreciated. Looks like good product, little worried about the overhead
View 3 Replies View RelatedI haven't touched MSSS in a looong time
When a SELECT returns say 3 rows what does the feedback say ?
(eg Oracle/sqlplus says "3 rows selected")
What if "no data found", what is the feedback ?
thanks
hi. ive just written my first complex SP for SQLServer 2005. id be REALLY grateful for any feedback please. if there's anything i'm doing wrong then it's better that i know sooner rather than later! im wondering if its over complex or just plain wrong. please bear in mind that i have at least made an attempt at this, this is my first proper SP.
the SP that I have written is to insert an Account and a User. The SP should do the following.
1. Start a transaction.
2. Insert a user by calling a nested SP. if the return value is greater than zero then the user has been successfully inserted.
3. only attempt to insert an account afterwards if the user has been inserted successfully.
4. if the user or account insert is not successful then rollback. otherwise commit the transaction
5. return an error code from the SP as such: 0 = success. 1 = failure, a user already exists with the given username. 2 = an unknown error occurred whilst inserting the user. 3 = an unknown error occurred when inserting the Account.
here is my code:
CREATE PROCEDURE sp_Account_I
@Id_Package smallint,
@Amount money,
@Username varchar(16),
@Password varchar(88),
@FirstName varchar(32),
@Surname varchar(32),
@DateBirth datetime,
@Email varchar(64),
@Id_Country int,
@Id_Account int OUTPUT,
@Id_User int OUTPUT
AS
DECLARE @ErrorCode int;
BEGIN
SET NOCOUNT ON;
SELECT @Id_Account = -1;
BEGIN TRANSACTION
/* Insert the user. */
EXEC @ErrorCode = sp_User_I1
@Username,
@Password,
@FirstName,
@Surname,
@DateBirth,
@Email,
@Id_Country,
@Id_User OUTPUT
IF @ErrorCode > 0
BEGIN TRY
INSERT INTO Accounts(
Id_Package,
Id_UserMain,
Amount)
VALUES(
@Id_Package,
@Id_User,
@Amount)
SELECT @Id_Account = SCOPE_IDENTITY();
END TRY
BEGIN CATCH
SELECT @ErrorCode = 3; /* An unknown error occurred. */
END CATCH;
IF @ErrorCode = 0
COMMIT TRANSACTION
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN @ErrorCode;
END
END
I have some long running scripts which I fire at my database using osql.(These are big files and mostly doing inserts but some also do a few otherthings.) It would be nice to have some activity indication (other than thedisk activity light) that these are running. When I used to use Oracle,their equivalent to osql had an option to print a dot (without a carriagereturn) for every "n" statements. This gave a nice "I'm alive" indicator. Ican simulate this by adding a few "print" statements in my sql, but printalways adds a carriage return. Does anyone know a way of doing a print butwithout the addition of a CR (or CR/LF)? So that a second "print" sends itsoutput to the same line as the first?I know this is a nicety and I can live without it, but it would be nice.thanks in advance,Brianwww.cryer.co.uk/brian
View 4 Replies View RelatedI haven't touched MSSS in a looong timeWhen a SELECT returns say 3 rows what does the feedback say ?(eg Oracle/sqlplus says "3 rows selected")What if "no data found", what is the feedback ?thanks
View 4 Replies View RelatedI€™d like everyone€™s help is do some research into an often heard, but rarely explained, complaint related the SQL Express. Your answers will help me plan future versions of SQL Express. Feel free to respond directly in the forum or by sending e-mail to me. (Note: Remove the word online from the e-mail address in my profile or it will bounce.)
The complaint: €œSQL Express is too big.€?
I€™m trying to understand what this really means and what specific technical issues are caused by this €œbigness€?. Here are some questions to help frame your answer.
Size means size
1. Is the size of the SQL Express installer package an issue for you? (SQL Express 32 €“ 52 MB / SQL Express Advanced ~250 MB)
2. Why is this size an issue?
3. Would you be willing to sacrifice functionality to reduce the size of the installer package?
Size means disk space
1. Do the SQL Express binaries take up too much room on the hard drive?
2. Would you be willing to sacrifice functionality to reduce the amount of HD space needed?
3. Do your databases take up too much disk space?
4. Would you be willing to pay money to reduce the size of the database file?
5. How much money?
Size means memory
1. Does SQL Express take up too much memory when it€™s running?
2. What impact does this have on you?
3. SQL Express currently reduces its memory set when it is idle which results in a delay when it becomes active again? Is this a reasonable trade-off to reduce memory usage when you€™re not using the database engine?
4. Do you normally use SQL Express for single user applications (local data store) or for multi-user applications (server data)?
5. If you run SQL Express as a server, do you run it on a dedicated computer or on a computer running other programs as well?
6. What kinds of programs does SQL Express have to share with?
7. Should SQL Express give up memory resources to other programs running on the same machine?
8. Are you willing to accept a reduction in performance in order to have memory resources shared?
Size means something else
1. Is there something I didn€™t cover?
I€™ll be tracking this thread, but will try not to comment to much since this is about your feedback, not my answers.
Mike Wachal
SQL Express Program Management
I wrote a post in the connect forums almost two weeks ago, but haven't got any response yet.
So I'll post here as well, hoping that someone from the SQL team will stumble upon this.
My post is about a possible bug in SQL.
Here's the url:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=342390
Marko
hi all
iwant to make feedback after insert data or delet date Like( the data is sucssfuly way) or(data is delete sucssuse way)
but not alert iwant feedback
thank you
When I call osql -S server -E -h-1 with a -i option, the feedback I get is prefaced with "1> 2> 3>".
The test script is simply:
set nocount on
use master
select name from sysdatabases
How can I suppress the "1> 2> 3>"?
Thanks
[Microsoft follow-up]
I submitted a posting to connect here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287213
Here is what I wrote:
A number of tasks output data to variables or text files. e.g. WMI Data Reader Task, Execute SQL Task, Web Service Task.
Consuming that output in a data-flow isn't particularly easy. You either need a Flat File source adapter (which requires an othrewise superfluous connection manager) or write come code in a script component to parse the recordset. There is simply no easy way to push data from these sources into the dataflow.
Thw built-in mechanism for passing data between different tasks is raw files. Currently they can only be used by the dataflow but I see no reason why they couldn't be used by other tasks as well. It makes complete sense to me for a WMI Datareader Task to push some data into a raw file and then we chew that data up in a dataflow.
The following response came back
Our current architecture actually doesn't have the buffer system as in Data Flow, when you are in the Control Flow. What you are asking would require us to build a similar buffer system in the Control Flow, which is a fundemantal architectural change. We'll not be able to take this, sorry.
I'm afraid I don't understand that response. Obviously I know that buffers are only in the data-flow - but I don't see why that's relevant. Raw files are just files on the file system, same as any other. OK, their format is very proprietary but its you guys that built the format. Essentially all I'm asking you to do is output the data in raw file format as opposed to flat file format. There's no notion of buffers in a raw file because its just a lump of data. Or is there? If not, I'm afraid I don't understand your answer.
Please could you clarify the answer?
-Jamie
Hi,
I would like some critic or feedback on the attach database design.
The one that I am focusing on is : item_id in fr_item table.
I would like to have a central place where from the web it will have id that map to file, posting and map.
I have three tables fr_file, fr_post, fr_geo which map to fr_item.
My question is :
1. Is it good idea to have additional file_id, geo_id, post_id in fr_file, fr_post,fr_geo (as primary key) INSTEAD OF directly put item_id without file_id, geo_id or fr_file.
Thank you in advances
I've been having Spotlight from Quest Software in the back of my mind for quite some time now, and now I just started in a new job where something like this could prove to be really handy. And from the looks of it it seems to be quite impressive but have any of you guys used it? What do you think?
--
Lumbago
"SELECT Rum, Coke, Lime, Ice FROM bar WHERE ClosingTime = 'Late' AND FemaleMaleRatio > 4"
I'm sure just about everyone uses the PRINT command to give feedback as to what their lengthy and involved scripts are doing, as sort of a record.
I cannot figure out how to make the stuff I use in PRINT commands come out in real-time like SELECTs seem to. Does anyone have an answer to this? These are long-running scripts, and I'd rather nip a problem in the bud before the entire script completes if there's a problem I can capture.
___________________________
Geek At Large
Hey folks I realize this forum is more for people who have a basic knowledge of SQL...but if anyone can help I've got a task I'm trying to complete for a friend's webpage. I'm trying to figure out how to build a basic feedback form fer his students to be able to post on his page what they thought about his class and what they would suggest to make his class better...kinda like a guest book in a sense. No need for login information or anything of that nature, just a simple "put your name here, put your feedback here" kinda deal. I greatly appreciate any help anyone can toss my way. Thanks in advance!
-Duff-
Hi!
I have an idea to generate an animation file in SSIS based on the data it imports.
Has anyone done that? Is Flash the best format for this or something else? I have programmed in several languages, and written programs before that wrote other programs, but I have never used any of the animation tools such as Flash. Can anyone point me to some resources I can use to quickly get up to speed on how to write such animations, in whatever tool you'd recommend? The tool needs to be low footprint as the application I have in mind would go on Internet for customers to see.
Any feedback on this idea whatsoever is most welcome indeed.
Brgds
Danny
This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.
There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.
So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.
I don't know how many folks here log into the Microsoft Connect site occasionally to check suggestions and bugs submitted to Microsoft for SQL Server and SSIS (still called DTS on their list). A small pecentage? Almost everyone? (Possibly in this group.) Anyone can vote for feedback they think is important. Theoretically issues with the most votes will get Microsoft's attention first.
Links to a couple new submissions that look interesting:
1. SSMS/QA Style Message Logging for SSIS Execute SQL Tasks
2. ForEach SMO Enumerator Filtering
I vetted these issues in the forum first, so hopefully they're legitimate enough to warrant some useful feedback or even a few high fives!
Hi All,
I have another set of questions for the group to help guide me in planning future versions of SQL Express, this time related to 64-bit support.
Quick History
In SQL Server 2005, SQL Express was only released in the 32-bit architecture. We supported installation to x64 computers using the Windows on Windows implementation that allowed the 32-bit program to install on 64-bit computers. We released two different installer packages, SQLEXPR32.EXE could only be installed on 32-bit platforms, while SQLEXPR.EXE could be installed on both 32-bit and 64-bit platforms, but was the exact same db engine. Package size differences were the result of additional files needed to support WoW installation.
Now to the Point
A number of people have asked that we release a native 64-bit version of SQL Express, which leads me to my questions for this thread:
What advantages will a native 64-bit Express offer you?
Does a 64-bit Express eliminate the need to support 32-bit WoW? (Consider that without WoW, we would not have a single package that could install on both architectures.)
If you could only have either native 64-bit Express or WoW support, which would you choose? (Bonus if you tell me why. )
What user/application scenarios are driving your need for native 64-bit Express?
Once again, I will try to limit my participation in this thread, since it's all about your feedback, but I will be reading it closely. I appreciate the effort you put into answering my questions and want to assure you that your feedback is a significant part of how I plan the future of SQL Express. As always, you can answer directly in this thread or respond to me directly, just remove the word "online" from the e-mail address in my profile.
Let the feedback begin,
Mike Wachal
SQL Express team
Greetings all,
I am looking for feedback on the following situation :) please leave your thoughts.
Little history:
1)We are in the process of migrating some of our applications from SQL 6.5 to SQL 2k.
2)The application was written to access the system tables directly under 6.5 (don't look at me, I know microsofts standing on this, i'm just the dba not the dev)
The situation is this:
The SQL 6.5 to SQL 2k migration is going great, no problems there...
The problem is the change to Unicode for system tables etc. So when they try to run the app it freaks out when it trys to access the data in the system tables getting unicode returned instead of text.
I know Unicode is the underlying arcitecture of SQL 7.0, 2k and so on but does anyone know any possible way or trick to get around this without altering the application code?
As a DBA I have not seen or heard of being able to do anything of the sort but I have to ask to make sure there is nothing I have over looked. This is more a matter of C.Y.A. ;)
Unicode in SQL2K
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_dataencoding.asp
6.5 vs 7.0 and the system table changes
http://www.windowsitpro.com/Article/ArticleID/5658/5658.html?Ad=1
I wonder is there any way I can turn off the "Documentation Feedback" sectionof the Books Online at the bottom of the pages... I'm printing some BooksOnline pages for own reference.Thanks in advance...--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200706/1
View 2 Replies View RelatedThanks for your time: Form collects 30 different categories of data as 1 row into a table. Then this After Insert Trigger inserts unique rows into another table per "if logic statements" for each of the 30 scenarios (see examples below).
How do I BEST apply TryCatch and @@error feedback against the following to minimize risk of lost insert statements, AND LOG their details for follow-up:
tr_A_Form_I_ItemRows
CREATE TRIGGER trg_insertItemRows
ON dbo.A_Form
AFTER INSERT
AS
IF ( @@ROWCOUNT = 0 )
RETURN
SET NOCOUNT ON
-- 1 of 15 Checkbox Driven [Missing WORKSHEET is Valued]:
IF (select distinct i.Fieldname from inserted i) = 1
BEGIN
Insert into... Values(...)
END
-- 1 of 8 Textbox Driven [REPLACE PATIENT NAME is valued]:
IF (select distinct i.C_PtName from inserted i)IS NOT NULL
BEGIN
Insert into... Values(...)
END
I have a package which consists of 3 Execute SQL Tasks -
1) Drop old database & Restore a new one
2) Run DDL
3) Run DML
My task was to put all of these in a transaction. I started using the "TransactionOption" property for this package to be "Required" and each child tasks also were set to the same transaction option property of "Required". However, after running the package, it errored out saying Task 1 cannot be in a transaction (which is logical) so, I removed the transaction required property from the first task and kept it on for the remaining 2 tasks. In addition, I have also set up a failover strategy, where in if these tasks were to fail, the package should restore the previous working copy of the backup. To test this scenario, I deliberately created an error in the DML task, so logically only this task should rollback, instead it rolls back the entire transaction and to my horror the failover step is not executed as well.
Is there something which I am not doing correctly?
How do I go about this?
Thanks,
Deepak.
Hi All,
I have built a package that firstly shrinks a database and then backs it up which is scheduled to run each day, but I would like to add a check (Check Database Integrity) in as the first task and if the Database checks out ok then continue on, if not send an email.
Now, I am unsure if the Check Database Integrity Task in SSIS actually returns the success or failure message back to the package that I am after. The SQL behind the task includes the NO_INFOMSGS option.
Can anyone advise if it is possible to have the status/integrity of the database returned to the SSIS task and to proceed based on the result?
Regards
Good morning, all,
I have an Excel workbook that needs to be imported. It has three
sheets, but it's really the first that is giving me fits. Each of the
three worksheets have header info and instructions on the first 8
rows. Worksheet 1 then has, on row 9, the column names for the group
informtion. Row 10 has the group information. Row 11 has detail
column headers. Row 12 and later have detail information. Worksheets
2 and three do not have detail information, just row 9 with the column
names for the group informtion and Row 10 with group information.
Here is how I am thinking of handling this.
Run a script, outside of SSIS to save each sheet as a CSV file to a
folder. I believe that this must be done because some of the first 8
rows are blank and according to the docs, SSIS cannot have blank rows
in imported Excel sheets.
Loop over the files in the folder.
For each file, exclude the first 8 rows.
if the file name is the first worksheet then
get the next two rows and process group info
get the rest of the worksheet and process detail information
if the file name is not the first worksheet then
get the next two rows and process group info
My questions are: Does this seem feasible? Is there an easier way to
do this? Any hints or tricks that might be helpful? Any pitfalls
that I should watch out for?
Thanks so much for any insights,
Kathryn
I have been investigating the number of connections activeinactive to a certain database server and I have stumbled across an application which seems to not be clearing its database connections.For one instance of a client there was >70 sql connections which eventuated from the closing and reopening one 1 screen in the culprut app. Once the application was closed all of the connections are recycled but its evident that within the application itself it is not correctly reusing already existing open connections.
I have raised a point with the main programmer that we need to investigate more into how the application is managingot managing its ADO .NET connections to SQL.
I am starting with doing some reading here URL... and I was hoping to get some more information about the possible impact of excessive sql connections on the SQL Server itself. Our organization is quite lucky in that our SQl Servers are Overspecced given their workload, bearing that in mind I would like to dig a bit deeper to get some stats if I can to highlight the scope of the issue to the managementprogrammers.Our SQL server peaks at 6500 processes and a good 70% of those are due to this applications mis-management of its sql connections.
My primary data files are ponting to F drive which is 200G and one db has occupied 90% of the space and the applications are not running. What is the best solution
View 1 Replies View RelatedIs there a way to know the tables the mostly requested in a database
How please?
Because we want to copy indexes from DB2 to SQl tables, but since there re so many tables in the DB we want to start with the most requested tables by the applications
Thanks
Hi,when i try to see the properties of database "sales" (sql server express 2005) in Management Studio Express, i get this error message: (besides, when i try to expand the database, i get the error that it's emty)Cannot show requested dialog.ADDITIONAL INFORMATION:Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)------------------------------An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)------------------------------The server principal "Myserveradmin" is not able to access the database "sales" under the current security context. (Microsoft SQL Server, Error: 916)But the webapplication still runs: i can insert records, update, delete ... from asp.net.Any way to recover it?ThanksTartuffe
View 4 Replies View Related