|
Hi
Its simple Use COUNT Function... like as follows
SELECT CAST(ISNULL(COUNT(ProdType),0) AS VARCHAR(10)) + ' '+ ProdType FROM ProdDtls GROUP BY ProdType
Regards,
GVPrabu
|
|
|
|
|
One of my clients has upgraded to 64bit SQL Server 2012. We have moved the test database there and I am having a bizarre issue that's doing my head in...
Even the most simple update query causes an error, which of course, is causing stored procedures to fall over...
update tbl_User
set USR_Email = 'myemail@domain.name.au'
where UserID = 5
causes the following error: "Msg 102, Level 15, State 1, Procedure tbl_Userupdate, Line 53
Incorrect syntax near '@errorNumber'."
The strange part is that the query itself runs fine. Any suggestions? Perhaps a setting that's been missed by the sysadmin who did the installation?
|
|
|
|
|
Alright, so a massive facepalm moment...
Further testing revealed that the problem is with a trigger on the table that was system generated to handle table relationships, and the error raising code in the trigger is being handled differently by SQL 2012 (this code is legacy from many versions of SQL ago)... so if I fix the trigger, I fix the problem (one would assume).
FML.
|
|
|
|
|
I hate f***ing triggers spit for this exact reason, for some completely unknown reason you get an error that just does not make sense and if you are not in the habit of looking under that particular rock you can spend hours chasing down the issue. I allow audit triggers and absolutely none others, the audit triggers are generated with a script that will both remove and create them according to schema table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
These were in the system generated update and insert triggers (handling table relationships and cascades), auto generated by SQL Server about a decade ago when the system diagrams were created... any wonder I didn't think to look there!!
|
|
|
|
|
_Damian S_ wrote: any wonder I didn't think to look there!!
Especially if you are not in the habit of using the bloody things. I wonder how many versions they have upgraded through, I don't recall sql auto generating triggers.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I've two databases consider as OldVersion and NewVersion. What I would like to do is that I need to compare the database for DDL changes (schema changes such as column difference, datatype difference, table difference). I would like to compare and generate a script such a way that it will upgrade the OldVersion database same as NewVersion database.
This is not a straight way to do that. Say for example, if a column is FK, that column could not be altered. This is one constraint. Likewise what are the possible constraints to generate the script? How could I resolve them?
To generate the script for entire database in what order I've to work on. I look for answer such as Drop Index, Drop Constraints, Drop Columns or Alter Column, Alter Index, Add Contraints in particular order.
I know I've some tools in place for that. But I would like to generate it.
The question is lengthier one. Please bear with me and provide your solution.
|
|
|
|
|
We use Red-Gate for this and it uses the dependency relationships to generate the scrip. However on a large change it can screw up and needs to be broken down, I do the tables, then the views and then the procedures.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You mean that you suggesting to use the tool and not to suggest to write script for it?
|
|
|
|
|
Sorry I was not very clear, I use the tool and the generated script. IF it fails (it automatically rolls back the transaction) I then use a subset of the objects, starting with the tables. I repeat this till I get a successful merge. I then save that script and get the next subset, repeating until I get all the changes across to the target database.
At that point I have a set of scripts that can be run sequentially to successfully update the target database. This is a pain but it is better than hand coding which is how I used to do it!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a method that takes a SQL query and a file name, then converts that query into an XML-based Excel file. Works great. The code gets the schema of the query, and uses the column names to generate the column headers in Excel. This, too, works great.
The problem is aliasing the columns into the headers that I want. I am using this notation:
SELECT
"First Name" = view.MeaninglessColumnNameImposedBySystem1,
"Last Name" = view.MeaninglessColumnNameImposedBySystem2,
"Address 1" = view.MeaninglessColumnNameImposedBySystem3,
...
FROM LongViewName AS view
WHERE etc=@etc
Whether I have one column or 50, the query takes several minutes to run; the more data, the longer it takes. If I were to use SELECT *, with no aliasing, I can return the data in a few seconds, regardless of how many rows are returned.
I have also tried view.MeaninglessColumnNameImposedBySystem1 AS [First Name], which does not speed up the results.
Presumably, this is an optimization issue where * lets SQL make assumptions that it cannot when each column is aliased. Any suggestions on what I can do with the query to speed things up?
|
|
|
|
|
Hi,
You can use Column Alias like as follows
SELECT V.MeaninglessColumnNameImposedBySystem1 [First Name],
V.MeaninglessColumnNameImposedBySystem2 [Last Name],
V.MeaninglessColumnNameImposedBySystem3 [Address 1],
...
FROM LongViewName AS V
WHERE etc=@etc
for best practice go for Column names without any space like FirstName, LastName, Address1 ...
Regards
GVPrabu
|
|
|
|
|
If you are returning the data to c# and then converting the datatable to Excel you could always rename the table columns after the retrieval.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am trying to avoid that: the idea is to have one general method that will take any of nine queries to generate different files than to have nine specialist methods that each take only a single query.
|
|
|
|
|
Somewhere you are defining the column names (you want it done on the entry query definition) either in 1 or 9 query constructors, I am suggesting moving the naming to post query execution, sticking that code into 1 method is trivial. Still it is a kludge b/c the aliasing should work 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Like I said, the aliasing is working, but it takes a very long time for the query to return. In one test case, a query with aliased columns took almost 6 minutes to return; the identical query with unaliased columns took less than a second. This kind of time difference is pretty consistent, regardless of how many columns are requested, and scales closely to the number of rows returned (the more rows, the longer the aliased query takes.)
It is very frustrating.
|
|
|
|
|
I traced the problem to a piece of code that uses SqlDataAdapter.Fill to populate a DataSet, then took to the Internet.
It turns out that there is some optimization that SQL does with queries, where it tries to import indexes from underlying tables. This works fine as long as the columns in the view match up with the columns from the tables. When you alias the query's columns, however, things can degenerate into a quagmire of competing indexes; the delay is caused by SQL trying to wait out a resolution. You can get rid of the delay by instructing SQL to abandon arithmetic operations that result in an overflow or underflow. Huh? 
The solution was to prefix the query with SET ARITHABORT ON; which tells SQL to just fly over the swamp rather than try to navigate through it. After the query, tack on ; SET ARITHABORT OFF; to reset the server variable, mainly because there is probably a reason why it set to OFF in the first place.
|
|
|
|
|
Good bit of research, thanks for posting the solution, that with parameter sniffing goes into my odd box of delays to hunt for!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi everybody!
Please can you tell me what is the best tool for creating a database from scratch?
|
|
|
|
|
Member 9611795 wrote: Please can you tell me what is the best tool for creating a database from scratch?
A computer?
If that wasn't the answer you thought you were looking for then you need to understand that you don't choose a database based on tool but rather on the functionality of the database vendor. AFTER you have chosen a database then you look for applications that allow you to manage the database application.
|
|
|
|
|
Pen and paper: figure out what you want the database to do for you and then create a structure to support that. Sketching it out with a pen and paper is the first thing I do (or whiteboard).
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
It's all about learning not working! so I want to know the tool which can help me to insert data and retrieve it and more.
is SQL server the tool that can help me? or ADO.NET?? and what is the difference between them??
|
|
|
|
|
Member 9611795 wrote: I want to know the tool which can help me to insert data and retrieve it and more.
SQL Server is where data is stored, ADO.NET is a set of objects for Creating, Reading, Updating and Deleting data.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Just Like Mark wrote, I start with the whiteboard.
When that gets to small I make the model in Oracle SQL Developer Data Modeler [^], which supports Oracle, SQL Server and DB2 out of the box, and you can make your own design rules as well.
And as an extra plus, it supports source control via Subversion.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
