|
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
|
|
|
|
|
Hai,
Y'day i attend a written test for job.i got this question.
what is the ending statement for sql query(choose three)
1)alter,2)rollback 3)create 4)insert 5)delete.
|
|
|
|
|
I don't understand the question. What is meant by "ending statement"?
|
|
|
|
|
Don't take the job - the question does not make sense as stated.
However
Alter and Create modify the the database the others modify the the data is the closest you will get.
|
|
|
|
|
Hi Guys,
Havent been working much on triggers and well have to get one to work now, so need some help to get the holy grail.
I have a table called username and when ever a record have been changed I need to update the last update time in column lastvisittime. The problem is that with this trigger below it updates every record in the table and not just the single one that is being updated.
CREATE TRIGGER [TimeUpdate] ON [dbo].[username] <br />
FOR UPDATE<br />
AS<br />
UPDATE username set lastvisittime = dbo.TINT(getdate())
Generally I need to know how do I find out what row was currently supposed to be updated? Isteadof updating the whole table as it does now.
Have been trying good old uncle google but didnt find any clue to it, so hope the experts here can help 
N.b. dbo.TINT(getdate()) is just a function that changes the datetime to an int value.
Cheers,
Ronni
modified on Saturday, April 5, 2008 6:57 AM
|
|
|
|
|
Okay found out myself.
CREATE TRIGGER [TimeUpdate] ON [dbo].[username]
FOR UPDATE
AS
DECLARE @ID int
Select @ID = id from inserted
UPDATE username set lastvisittime = dbo.TINT(getdate()) where id = @ID
|
|
|
|
|
I design Data base in access 2007 and move it to Sqlexpress 2005 Database.
I can see the tables and all in sqlserver management studio.
But when I connect to the sqlexpress from Vs 2005 I do not see any stuff from access.
I am not sure what is going on here.
Both sqlexpress and Vs are on the same machine.
Cheers.
Tapas Shome
System Software Engineer
Keen Computer Solutions
1408 Erin Street
Winnipeg, Manitoba
Canada R3E 2S8
http://www.keencomputer.com
|
|
|
|
|
how you connect on vs.net?
|
|
|
|
|
Hi all, I'm having a problem with updating a row in the table when the value was originally NULL. Using c# and Visual Studio 2005 by the way.
Through my front end I can modify existing values and commit those changes to the database just fine, but the problem arises when I try and change a field that was NULL to a non-NULL value.
(Oh, I can also change a field that has a value to NULL just fine)
When I get to my DbAdapter.Update(DATASET HERE); line I catch an exception with the following text:
"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
And the thing is, when I comment out (of the update stored procedure in SQL) the part of the WHERE clause that states ((@IsNull_FIELDNAME = 1 AND [FIELDNAME] IS NULL) OR ([FIELDNAME] = @Original_FIELDNAME))where FIELDNAME is the one field that I'm trying to update, it works fine.
I can't figure out why that WHERE clause is failing. I'm not updating the database anywhere else in the code other than the one spot that is failing out when I try and fill a previously NULL field. I've used VS2005's auto-generating wizard to create my stored procedures and command generators so there isn't any disagreement between the table/SP/c# that I can find.
When I breakpoint at the DbAdapter.Update(DATASET HERE); line my dataset has the right values in it.
Does anyone have any ideas? I'm pretty stuck here.
Thanks
|
|
|
|
|
you can using isnull fungtion to check on null
isnull([FIELDNAME], @Original_FIELDNAME,FIELDNAME)
|
|
|
|
|
Hello!
I will like to know following in context of SQL Server 2005:
1. Is 1 SP executed as 1 transaction?
2. In case of BEGIN TRAN/TRY/CATCH blocks, what happens if the following scenario occurs:
create proc...
declare x....
BEGIN TRAN
BEGIN TRY
....
COMMIT TRAN
END TRY
BEGIN CATCH
....
ROLLBACK TRAN
END CATCH
....
<some exception="" occurs="" here="">
....
end
will the SP be rolled back till start? or just the part after END CATCH? or just the statement causing exception?
Tried playing with SP, but seriously confused myself!!!
If anyone could provide useful link related to the problem, it will be great! Tried googling but could'nt find anything useful. 
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
It depends. If your procedure is called from w/in an existing transaction then commit/rollback will affect both the inner and outer transactions.
You could use @@TRANCOUNT to determine wither or not you need to rollback or commit.
<br />
BEGIN TRY<br />
<br />
IF @@TRANCOUNT = 1<br />
COMMIT TRANSACTION<br />
END TRY<br />
BEGIN CATCH<br />
IF @@TRANCOUNT > 0<br />
ROLLBACK TRANSACTION<br />
<br />
END CATCH<br />
<br />
This way if your stored proc is the outer transaction it will commit on no errors. Then if there is an error in a nested transaction that was rolled back you won't get an error trying to rollback.
Search google for: sql server nested transactions
You should get plenty of results explaining this topic.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
