|
Yes
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Well the question does not make sense:
ID infers a unique identifier
Count(*) requires a group by
So your question is can I group by a unique identifier. Try rephrasing the question (I think you just got it wrong) and give us some more detail.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why do you use Count(*), it is better to use Count(ID)
ID infers a unique identifier in that table
CREATE PROCEDURE titles_count @@TITLE varchar(40) = '%', @@count int OUTPUT
AS
SELECT @@count = count(title_id)
FROM titles
WHERE title LIKE @@TITLE
GO
Venky
|
|
|
|
|
Why is it better to use count(field) instead of count(*), I have never heard that there is a difference in cost
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to import data to Microsoft Sql Server 2005 from .csv file
In .csv file I am having a column which has numeric data . During the import process I am receiving data conversion error.
In case I used this column as string then it works, but I want column as numeric.
Kindly tell if I am missing something.
Thanks in Advanced.
|
|
|
|
|
a_b111 wrote: which has numeric data . During the import process I am receiving data conversion error
Either you have a non-numeric value, or your numeric data type does not match that of your file. Check all your values against your data types.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
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
|
|
|
|
|
I suggest you to make a function with code down below.
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
|
|
|
|
|