.NET, Java and other high level database API's in various language often provide techniques known as prepared statements and parameter binding as opposed to sending plain text commands to the Database server. What I would like to know is what happens when you execute a statement like this: SqlCommand cmd = new SqlCommand("GetMemberByID"...
What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET Forum). At the moment, most of the database access is done by building the SQL inline in C# and calling to the SQL Server DB. So I'm trying to ...
My firm have a talented and smart operations staff who are working very hard. I'd like to give them a SQL-execution tool that helps them avoid common, easily-detected SQL mistakes that are easy to make when they are in a hurry. Can anyone suggest such a tool? Details follow. Part of the operations team remit is writing very complex ad-h...
I am trying to generate a report by querying 2 databases (Sybase) in classic ASP. I have created 2 connection strings: connA for databaseA connB for databaseB Both databases are present on the same server (don't know if this matters) Queries: q1 = SELECT column1 INTO #temp FROM databaseA..table1 WHERE xyz="A" q2 = SELECT ...
I'm using ColdFusion to return a result set from a SQL database and turn it into a list. I need some way to generate an Alphabetical Navigation bar for that list. I have ColdFusion and the jQuery library available. I'm looking to generate something like this: A | B | C | ... A A B B B C D Where clicking on one of the letters ...
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server MySQL PostgreSQL SQLite Oracle I am currently doing something like the following in SQL Se...
Every now and then in a high volume .NET application, you might see this exception when you try to execute a query: System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. According to my research, this is something that "just happens" and not much can be done to prevent it. It...
Hello everyone, What options are there in the industry for enterprise reporting? I'm currently using SSRS 2005, and know that there is another version coming out with the new release of MSSQL. But, it seems like it might also be a good time to investigate the market to see what else is out there. What have you encountered? Do you li...
I have a Monthly Status database view I need to build a report based on. The data in the view looks something like this: Category | Revenue | Yearh | Month Bikes 10 000 2008 1 Bikes 12 000 2008 2 Bikes 12 000 2008 3 Bikes 15 000 2008 1 Bikes 11 000 2007 ...
I have a table with a 'filename' column. I recently performed an insert into this column but in my haste forgot to append the file extension to all the filenames entered. Fortunately they are all '.jpg' images. How can I easily update the 'filename' column of these inserted fields (assuming I can select the recent rows based on known i...
I was looking for a good free debugger for STP (Stored Procedures) as I need to make changes to a few of them as a part of my recent project. The stored procedures are stored in independent '.sql' files and not in the code. A debugger would speed things up quite a it in my case. Could anyone recommend any tools that can be used for this...
So I'm getting a new job working with databases (Microsoft SQL Server to be precise). I know nothing about SQL much less SQL Server. They said they'd train me, but I want to take some initiative to learn about it on my own to be ahead. Where's the best place to start (tutorials, books, etc)? I want to learn more about the SQL languag...
I'm not quite sure if this is possible, or falls into the category of Pivot tables, but I figured I'd go to the pros to see. I have three basic tables: Card, Property, and CardProperty. Since cards do not have the same properties, and often multiple values for the same property, I decided to use the union table approach to store data in...
I've found myself increasingly unsatisfied with the DataSet/DataTable/DataRow paradigm in .Net, mostly because it's often a couple of steps more complicated than what I really want to do. In cases where I'm binding to controls, DataSets are fine. But in other cases, there seems to be a fair amount of mental overhead. I've played a bit...
Ok, I realize I might be downvoted into oblivion for this question, especially given my stance on the matter, but I really need to see some honest, thoughtful debate on the merits of the currently accepted enterprise application design paradigm. I am not convinced that entity objects should exist. By entity objects I mean the typical t...
Since both a Table Scan and a Clustered Index Scan essentially scan all records in the table, why is a Clustered Index Scan supposedly better? As an example - what's the performance difference between the following when there are many records?: declare @temp table( SomeColumn varchar(50) ) insert into @temp select 'SomeVal' selec...
When you have a query or stored procedure that needs performance tuning, what are some of the first things you try? ...
What is the best way to remove duplicate rows from a fairly large table (i.e. 300,000+ rows)? The rows of course will not be perfect duplicates because of the existence of the RowID identity field. MyTable ----------- RowID int not null identity(1,1) primary key, Col1 varchar(20) not null, Col2 varchar(2048) not null, Col3 tinyint not ...
Every project invariably needs some type of reporting functionality. From a foreach loop in your language of choice to a full blow BI platform. To get the job done what tools, widgets, platforms has the group used with success, frustration and failure? ...
Example: select ename from emp where hiredate = todate('01/05/81','dd/mm/yy') and select ename from emp where hiredate = todate('01/05/81','dd/mm/rr') return different results ...