|
As Bob said - data types, probably a blank cell that is treated as "".
I always import into a staging table of varchars and use a stored proc to do the transforms AFTER loading the data. Dates will also screw you with weird formats.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have tried to import into a staging table of varchar and use a query to do the transforms AFTER loading the data, but it failed.
Please tell me the stored procedure to do the transforms AFTER loading the data.
|
|
|
|
|
a_b111 wrote: Please tell me the stored procedure to do the transforms AFTER loading the data.
You have to write it, in your OP you said you could get it to work using string - varchar so that part should work.
Now you have the data in front of you and you know where it has to go - so write the procedure to do the work. This removes the LOAD from the potential problems you can have, now all you need to do is transform the data into your tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
a_b111 wrote: Kindly tell if I am missing something.
Yes. 
|
|
|
|
|
hi frnds,
I need to transfer data from a single source column to multiple destination columns.
Sample data:
SOURCE TABLE:
F1 F2 F3 <----header
A 234 D11 D12 D13
B 235 D21 D22 D23
DESTINATION TABLE:
col1 col2 col3 col4
A 234 F1 D11
A 234 F2 D12
A 234 F3 D13
B 235 F1 D21
b 235 F2 D22
B 235 F3 D23
Is it possible through activex scripts?? If Yes, how??
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
Hi,
for each tuple in source table you have to create 3 instances in destination table,
you can do this via stored procedure, by help of various methods of choice.
take temp table with columns [SrNo numeric identity(1,1)] and four column of source table.
transfer source table into temp table
take max SrNo in a variable say count
for each row of temp table from 0 to count
BEGIN
insert destination table([source col1],[source col2],F1,[source col3]
insert destination table([source col1],[source col2],F2,[source col4]
insert destination table([source col1],[source col2],F3,[source col5]
END
I think this way will transfer all your record from source table to destination table in desired manner.
Bi
|
|
|
|
|
Hello,
I installed sql server 2005 and i have sql configuration manager.so please tell me how to access it to create database and tables ,so that i can work on .net
Thanks
|
|
|
|
|
|
I have sql server configuration manager.when i click on that
sql server 2005 services
sql server 2005 network configuration
sql native client configuration
I don't have management studio.
I tried to load again it's giving warning:
The current system does not meet the recommended hardware requirements for this SQL Server release. For detailed hardware and software requirements, see the readme file or SQL Server Books Online.
I saw it requires 512MB of RAM and i have 256MB
Thanks
|
|
|
|
|
mkalantri wrote: I saw it requires 512MB of RAM and i have 256MB
There's your problem. If you have so little RAM, you're not really going to be able to accomplish much with a database. You really need to beef up your system first.
|
|
|
|
|
mkalantri wrote: I saw it requires 512MB of RAM and i have 256MB
Well, there you go. 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Based upon your question it is appearent you have a ways to go before you need to concern yourself with .Net and the CLR.
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
Over the last several days you have asked question after question that if you just did a little homework you could get the answers, such as "why am I getting error ______?" We are not here to hold your hand the entire way. Buy a book, do some research, then come back with some code.
Blog link to be reinstated at a later date.
|
|
|
|
|
Says it all - you obviously do not have the ability to achieve even the basics as a developer. If you are just learning you should either do a course or get a book. If this is your job then quit.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello friends,
i have a requirement in which i have to convert mm/dd/year that is 06/13/2008 to 13th june, 2008
i have to do this in t-sql can anyone help me out
cheers
chandu
|
|
|
|
|
declare @DateValue as varchar(20)<br />
set @DateValue='10/01/2008'<br />
<br />
select case <br />
when substring(@DateValue,4,2) = 1 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),2,1)+'st'<br />
when substring(@DateValue,4,2) = 2 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),2,1)+'nd'<br />
when substring(@DateValue,4,2) = 3 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),2,1)+'rd' <br />
when substring(@DateValue,4,2) <= 9 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),2,1)+'th'<br />
when substring(@DateValue,4,2) > 9 then<br />
<br />
<br />
case <br />
when substring(@DateValue,5,1) = 1 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'st'<br />
when substring(@DateValue,5,1) = 2 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'nd'<br />
when substring(@DateValue,5,1) = 3 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'rd' <br />
when substring(@DateValue,5,1) <= 9 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'th' <br />
when substring(@DateValue,5,1) > 9 then <br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'th'<br />
end<br />
<br />
end<br />
<br />
+ substring (convert(varchar ,cast(@DateValue as datetime),106),3,4)+','+<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),7,5)<br />
as DateTimeValue
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
modified on Thursday, September 11, 2008 3:23 PM
|
|
|
|
|
You almost got it read BOl on datetime type and convert method. Blues reply actually gives you the answer but read BOL anyway.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use the DATENAME function, eg.
SELECT DATENAME(mm, GETDATE())
selects the name of the month in the culture of the server instance.
|
|
|
|
|
We have a number of stored procedures which process hundreds, sometimes thousands of records at a crunch. When these are called from our front-end data management app, we want to prevent the user from doing anything but wait; we also want to provide feedback so the user knows things are being done.
In our earlier front-end, written in VB6, this was done by calling RdoQueryObj.Execute rdAsynchEnable and polling RdoQueryObj.StillExecuting. Every one hundred records processed, the stored procedure would update an entry in a table, InterfaceCounter. In each iteration of the RdoQueryObj.StillExecuting loop, the front-end reads this entry and updates a display showing the user how many records have been processed. This worked great.
We are updating this VB6 app to VB.Net, using Visual Studio 2008. This process has been translated to get an IAsyncResult object from SqlCommand.BeginExecuteNonQuery and polling IAsyncResultObj.IsComplete. Inside the loop, the process remains essentially the same.
The problem is that the first time the stored procedure makes an entry into InterfaceCounter, IAsyncResultObj.IsComplete gets set to True. This passes control to the line SqlCommandObj.EndExecuteNonQuery(IAsyncResultObj) like it should. But since the stored procedure is not really finished, the application hangs and the user is left wondering what happened.
This is an example of one of the stored procedures. As you can see, it occurs as part of a cursor.
OPEN CURS1
FETCH CURS1 INTO @KEE
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE PROCESS @KEE
SET @COUNTER = @COUNTER + 1
IF @COUNTER % 100 = 0
BEGIN
UPDATE INTERFACECOUNTER
SET COUNTER = @COUNTER
WHERE JOBNAME = @JOBNAME
AND USERNAME = @USERNAME
END
FETCH CURS1 INTO @KEE
END I have verified that the UPDATE is, indeed, where IsComplete is being set.
This is the code I'm using in the application, trimmed down to the minimum needed to replicate the problem.
Dim Conn As SqlConnection = Nothing
Dim Cmd As SqlCommand = Nothing
Dim Result As IAsyncResult = Nothing
Dim Dream As New TimeSpan(0, 0, 1)
Conn = New SqlConnection(Db.ConnectionString)
Cmd = New SqlCommand
Cmd.Connection = Conn
Conn.Open()
Cmd.CommandText = CmdText
Cmd.CommandType = CommandType.StoredProcedure
Cmd.CommandTimeout = 1800
If SPParamList IsNot Nothing Then Cmd.Parameters.AddRange(SPParamList.ToArray)
Result = Cmd.BeginExecuteNonQuery()
Do While Not Result.IsCompleted
System.Threading.Thread.Sleep(Dream)
Debug.Write(".")
Loop
Cmd.EndExecuteNonQuery(Result)Additional information, in case anyone wants to know:
App: VB.Net 3.5
DB: MSSQL Server 2003
The connection string includes the flag Asynchronous Processing=true.
Again, the VB6 version is running just fine, same as it has for years. The problem exists only for the VB.Net version running on the same database.
Any suggestions?
Added: I also wanted to add that the line EXECUTE PROCESS @KEE in the SQL code above does a number of updates itself and executes a number of calls to other stored procedures which also do updates. Each time PROCESS is executed, at least a dozen updates get done. The problem occurs only in the update within the main cursor loop, after doing 100 records.
modified on Thursday, September 11, 2008 1:48 PM
|
|
|
|
|
I'll state upfront that I don't understand why it behaves as you describe and thus cannot really offer anything towards fixing the bug, if there is one. But I do think you can work around the problem by using multiple threads instead of asynchronous processes. By creating a worker thread (using the BackgroundWorker is an easy way to accomplish this - it uses an event-driven model with a DoWork event, allowing you to simply write the handler and it will then run on the background thread, and will notify you when the thread stops running) your application can avoid freezing and report progress as the procedure executes.
|
|
|
|
|
By the way, multiple threads are of course used with your current technique, and perhaps you do not need any async flag in your connection string - could be worth trying at least.
We make fairly heavy use of background threads to execute long-running queries in our product, though we create the threads explicitly and have implemented a polling system that makes it easy to run any code in the background and poll a business object for information. We have to use a polling model because it's a web application, ie. a client-server solution, and with this in place the reusable bit takes care of thread management (including queueing), generating polls from the client and performing a callback to a client-side function so we can update the user interface on the fly. We've had lots of bugs of course but they've always been logical and due to errors we made in our code, not the result of any quirks or bugs elsewhere.
|
|
|
|
|
As I said above, the call to EXECUTE PROCESS executes a number of other stored procedures. All of them, like the root procedure, have SET NOCOUNT ON as the first line of code and SET NOCOUNT OFF as the last line of code. Removing these two lines from the sub procedures fixed the problem.
As best we can figure out, the scope of the NOCOUNT flag is global to the original procedure and not to the current procedure, like you would think. The net result was that when the UPDATE in the original procedure was run, the NOCOUNT flag was off and the server obligingly provided feedback that one row had been updated. That was enough for the IAsynchResult object to consider the process done. 
So now, everything works just the way we want it to work.
|
|
|
|
|
I'm trying to count data in a table. I was able to do it, however, when a data exists more than once, it shows it again and counts it again
for example:
column_name Count
string 2
another string 1
same string 2
This is my query:
SELECT table_1,
(SELECT COUNT(column_1) FROM Records as t1 WHERE t1.column_1 = table_1.column_1) AS Count
FROM table_1
Can anyone suggest an approach that would solve such redundancy? Thanks!
|
|
|
|
|
try this
select distinct c1,count(c1) from table1 group by c1
Bob
Ashfield Consultants Ltd
|
|
|
|
|
oh shucks! stupid me! thanks! 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
