|
You are thinking that SQL is a proceedural language. It is a set based language. It identifies that the set operation failed and the reason why. What seems easy on the surface may be extremely difficult to implement when the queries get highly convoluted. Example: Which row should the error system identify when the source set is composed of a 5 way join and 3 correlated subqueries?
On your side of the fence: I would like SQL to identify the column name that rejected the input data because it is to long.
|
|
|
|
|
I want to be able to click a button that changes all the datasources from selecting from one table to another. eg. If I click a button labeled "January", I want all datasources on my page to select all the same data, but from the January table. And if I click Feb, to select all the same data from the Feb table. All data is within the same database.
Is there a query that can do this or any piece of code that can change them all to select out of the table I need?
Thanks 
|
|
|
|
|
Is there a reason why your data is in different tables per month? Would it make more sense to have a month id or some such on just one table as a foreign key?
|
|
|
|
|
ye it does make more sense to have it in one table... Figured its more efficient too... So I added it all into one table. should work out just as fine.
Thanks
|
|
|
|
|
Daniel_Logan wrote: ye it does make more sense to have it in one table... Figured its more efficient too... So I added it all into one table. should work out just as fine.
Did you normalise your database first and then perform denormalisation after carefully evaluating the impact?
|
|
|
|
|
I didn't normalize as I didn't expect it to be more than the one table and there are no primary ID fields... It was pretty much a dump of all our phone calls. So didn't expect it to be complex data. As it is, I was trying to make it more complicated than it was by putting things into separate tables. But I can do it all from one table which makes it complex at other points but simpler and better normally
Thanks 
|
|
|
|
|
Hi,
I am trying to write a stored procedure that takes a comma separated letter. I have a split function that returns the splitted letters. I have select some values from the tables in the database where the title starts with each splitted letter. I thought I should use cursor that contains each letter and in the while loop i put my select statement with the conditions.
Is this the correct way. Or are there any ideas for this?
thanks,
Regards,
shakthi
|
|
|
|
|
SELECT *
FROM Books B
WHERE SUBSTRING(B.Title, 1, 1) IN (
SELECT Letter FROM dbo.SplitFunction(@commaList)
)
IMHO, cursors are evil. They are sometimes necessary, like when you have to call a procedure for each row in a query or some other operation which can't be expressed in a subquery or join. But overall you should unless this is your situation you should look for an alternative. And this situation is required less now with the advent of the CROSS APPLY clause. SQL is best when it is batch processing. Anytime you are doing something in a loop you are asking for performance problems somewhere.
|
|
|
|
|
Very nice solution Mark.
I have one similar question: Do you have an idea how to handle such scenarios in which the comma separated list contains words of different lengths instead of letters.
e.g. Taking this book's problem as an example: Lets say we want something like:
Select * from Books where Books.Title.StartsWith( <br />
Select StartingString from dbo.SplitFunction( 'Microsoft Press', 'Aspress', 'Oreally', 'Sams' )
|
|
|
|
|
|
Thanks for the reply Mark, Let me go through your article and write you back.
|
|
|
|
|
Hi Mark,
It seems you didnt get my point completely. Your article converts a comma separated list into a table but my problem was how to match starting string from that table. Let us look at my original question again:
Select * from Books where Books.Title.StartsWith(
Select StartingString from dbo.SplitFunction( 'Microsoft Press,Aspress,Oreally,Sams' )
Note that I am not using an IN clause. I want some StartsWith function that T-sql doesnt have.
Also, note that the comma separated list does not contain equal-length strings so we cant do something like
WHERE SUBSTRING(Books.Title, 1, N) in (select .. from SplitFunction)
since N is not constant here.
Hope you get my point.
Thanks,
Mehroz
|
|
|
|
|
Hi All,
Could you please let me know how to set up a CSV file transfet from an FTP site to a Table in a SQL Database table ?
Do i have to use Ftp Bulk Insert Task? How do set up?
Please advice.
Thank you
|
|
|
|
|
The short answer to your question is, yes you will need the Ftp task. Use Ftp as your source and then you will need to choose the correct destination task (for example Sql Server destination). Most likely you will probably need to do some sort of data conversion as well. There are transformation tasks for this as well.
If you need anything more specific than that you will need to ask a question specific to a problem to are experiencing and describe why it isn't working.
I also suggest you get a book, or use Google to look for tutorials and HowTo topics on using SSIS. A forum is not the place for you to have someone do your work for you.
|
|
|
|
|
Thank you very much for your response.Its much appreciated.To be very specific i have used the Ftp Task to download the file from the FTP server to a local Pc where the Sql Database runs.The next step is to Upload the file on to the Sql table i have used Bulkinsert Task.I have manhed to upload a flat file (*.txt) file.However when i Use a Csv File excatly in the same format to the flat file It doesnot copy the data on to my table.
I hope its clear now.
Thank you
|
|
|
|
|
Are you getting any error messages? If so what do they say? Simply saying it doesn't work isn't enough information to go on.
|
|
|
|
|
Thank you Once again.On the Bulk insert progress it says : Bulk insert Task is Initiated ,and Bulk insert task is completed.However the actual data is not copied on to the table.Hope this will help.
Thank you.
|
|
|
|
|
View is a virtual table the database does not store view data ........
I got this line from a site.
I just want to know that does the database not store view data
but i created a view on a table and i opened that view and also it's showing data in the view.....
Plz explain
|
|
|
|
|
Hi,
Please know that a view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.
Once you have defined a view, you can reference it like any other table in a database.
A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
For more details please visit the following link and go through the article.
http://www.sql-server-performance.com/articles/dev/views_in_sql_server_p1.aspx[^]
Hope this helps .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
I have to add a not null column in a table.
I am using
IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE column_name = 'xyz'
AND table_name = 'abc')
BEGIN
ALTER TABLE abc WITH NOCHECK ADD xyz MONEY NOT NULL
END
It gives me error
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'xyz' cannot be added to non-empty table 'abc' because it does not satisfy these conditions.
can anybody have any idea, what's wrong with my SP?
|
|
|
|
|
Nothing is wrong.
salon wrote: ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'xyz' cannot be added to non-empty table 'abc' because it does not satisfy these conditions.
It says it all. You cannot add NOT NULL columns to an existing table unless you specify a default as, obviously, the new column will contain NULL.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If you want to add a NOT NULL column you will need to create a new table, for example if your table's name is "abc" then create a table with the new schema (i.e. the new column defined as NOT NULL) and name that table "abc_new". Then copy the data from "abc" to "abc_new". You will need to either provide a default value thru a constraint or your copy proceedure. After you have completed the copy, drop "abc" and then rename "abc_new" to "abc" with the system stored procedure 'sp_rename'.
|
|
|
|
|
hi
i have created adialog based application.but i just found out recently that it is hard to link a database to adialog("such as displaying results in an edit box ")
Is it really that difficult??
and can some one tell me how to do it???
please i really need it ("its for my project and i will reach my time limit soon  ")
please >>>any help will be appreciated.
thank u in advance
Lahom
|
|
|
|
|
where add related tables automatically on diagram sql server 2005?
|
|
|
|
|
I guess you have to start from here [^]
I Love SQL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
