|
IMO this would be a bad idea. This would place your transaction (even if you're not doing an update) in a distributed context and distributed transactions are really expensive. I don't know if Sybase supports the same syntax, but SQL Server will allow you to insert the results of a stored procedure directly into a table like this:
<br />
INSERT INTO myTable(col1, col2, col3)<br />
EXEC myProc @param1, @param2<br />
This works if the table definition exactly matches the output of the stored proc. To accomplish this you could define a temp table, store the results of the stored proc in the temp table and then select the column you want from the temp table. Like this:
<br />
CREATE TABLE #myTable(col1, col2, col3)<br />
<br />
INSERT INTO myTable(col1, col2, col3)<br />
EXEC myProc @param1, @param2<br />
<br />
SELECT col1 FROM #myTable<br />
<br />
DROP TABLE #myTable<br />
You'll have to look up the syntax, as I don't know if Sybase supports it, but it would be vastly better than running a remote query.
|
|
|
|
|
Thanks but the select column there in your script... It's already done within stored proc but I'm calling the stored proc from an Excel addin developed by my company - wouldn't be able to consume the select.
Mark J. Miller wrote:
CREATE TABLE #myTable(col1, col2, col3)
INSERT INTO myTable(col1, col2, col3)
EXEC myProc @param1, @param2
SELECT col1 FROM #myTable
DROP TABLE #myTable
I also tried selecting @@identity after executing the stored proc, no luck.
Thanks
Norman
devy
|
|
|
|
|
We have set up replication between a SQL 2005 server and a number of laptops with SQL 2K5 Express installed.
On the laptops we use Windows Synchronization Manager to manage the synchronization.
Everything works fine. The only remaining problem is that we are not able to save the login data (username/password) for Subscriber, Publisher and so on. Because of the way our customer has set up their main SQL server we need to use SQL authentication (instead of the preferred Windows authentication).
In the current situation the user has to re-enter usernames and password each time.
Is there a solution for this?
Kind regards,
Brian Wakhutu
Nairobi Kenya
|
|
|
|
|
I'm in SQL 2000 running a query.
I am inserting a lot of record from 1 table to another. A record on the input table doesn't match the destination table format.
I'm getting the following.
Server: Msg 8152, Level 16, State 9, Line 106
String or binary data would be truncated.
The statement has been terminated.
How do I get Query analyzer to tell me which record from the source table is in causing my problem.
Should I Try...Catch? Not sure this works in SQL2000.
Any help welcome.
|
|
|
|
|
check fields which have datatype varchar and set length to 255 or size which you need.
I Love SQL
|
|
|
|
|
Hi thanks,
there are many fields VARCHAR and INT datetime and such, It may not be the Varchar that's the problem. I really need some SQL to allow me to trap the record in error and amend the record, not the table.
H.
|
|
|
|
|
I know of no error trapping (although there might be some low level undocumented stuff) for identifying the row within the set that caused the error. It might not even be very helpful if it is the first of 1 million errors within the set anyway.
Visually compare the sizes of the all varchar (and binary, if any) columns between the table and your input query. At least one of the columns of the input query has a column size that can be larger than is accepted by the table it is being input into.
You can then run a check on the query to find which rows are offending.
<code>SELECT
PrimaryKey,
FROM
WhatEverSource
WHERE
LEN(VarCharColumn) > 25</code>
You can also force the truncation during input which would eliminate the error but, lose some data.
|
|
|
|
|
That's great mike.....
I hate SQL for this reason, it's like something from the 1970's (**this will get them SQL fans going**  )
Why can it not say 'I cannot insert...' and then show me the row !! How simple would that be ??
I do some checking, thanks again...
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
