|
InStr(AUD_COMMENTS,tTempEx1,1,1)-(InStr(AUD_COMMENTS,tTempEx +,1,1)+2)
|
|
|
|
|
You're looking for the parts about CharIndex and SubString, and the documentation is here[^] 
I are troll
|
|
|
|
|
Hi All,
There is a table with 200,000 rows and 7-8 columns with registeration number as a unique key.
Now i have xls sheet where i have got 16,000 registeration numbers. I need to crossmatch these numbers with the one in sql 2008 database to see how many matches with the database ?
Any help would be greatly appreciated.
Thanks
|
|
|
|
|
You need to import the Excel spreadsheet into a new table in the database and then join the existing table onto the new table using the registration number as the join condition.
It definitely isn't definatley
|
|
|
|
|
cheers. I will try to do that now.
|
|
|
|
|
I have created new table with registeration number id and now trying to import the xls sheet into that table but data type is not matching. In xls the data type is 'double' and in sql there is no option to select data as double. Tried bigint etc but no success.
The below is the source information, So how to match it with new table ?
[Source Information]
Source Location : C:\abc.xls
Source Provider : Microsoft.ACE.OLEDB.12.0
Table: `'Users'`
Column: regxyz
Column Type: Double
SSIS Type: double-precision float [DT_R8]
Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\JetToSSIS.xml
Any idea ??
Thanks
|
|
|
|
|
I'm confused - are you saying that the data type of the registration number in the Excel spreadsheet is a double? Or are we talking about another column in the spreadsheet? What is the data type of the registration number in your database table?
It definitely isn't definatley
|
|
|
|
|
Basically the excel spreadsheet contains list of only one column which is registeration number - 5 to 8 digits numbers and while importing it through sql import wizard, it says source is double type and does not match with the column i created in new table in sql.
The reg number in the old databasse table is nvarchar and the new one i created just now is bigint but i will now try it with nvarchar ?
So once i get import the reg no to the sql table then i want to match that with the existing reg no in database to see how many reg no already exist.
|
|
|
|
|
The SQL import wizard usually looks at the first 10 or 20 rows of data to figure out the type - I'd be surprised if it was incorrectly recognising an integer as a double.
In any case, the columns you're comparing should ideally have the same data type (probably int / big int).
If you're not even sure how to write the query, you'll probably want to invest in a book or two or do some research online (i.e. http://www.w3schools.com/sql/default.asp[^]). Joining two tables together is one of the most fundamental database tasks you can do.
To get you started, something like:
<br />
select<br />
t1.RegNum<br />
from<br />
DBTable t1<br />
inner join ExcelTable t2<br />
on t1.RegNum = t2.RegNum<br />
will work. If you can't get the two columns to be the same database, something like this might work better (but I can't guarantee the results)
<br />
select<br />
t1.RegNum<br />
from<br />
DBTable t1<br />
inner join ExcelTable t2<br />
on cast(t1.RegNum as varchar(max)) = cast(t2.RegNum as varchar(max))<br />
It definitely isn't definatley
|
|
|
|
|
yeh i know how to join the tables.
Thanksa l lot for your help mate.Was just confused as its been a while working on sql.
|
|
|
|
|
yeh now i was able to import it. Now i just have to compare the two tables
|
|
|
|
|
Hi,
kindly let me know the following query command into MS-Access.
SELECT A.*, B.MNAME
FROM DETAIL A, MASTER B
WHERE A.MCODE=B.MCODE(+)
Thank you in Advance
(Riaz)
|
|
|
|
|
Well, if you know Oracle you should know that the (+) indicates an OUTER JOIN. Does that give you a clue?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I have a table containing 1 crore tuples(rows).
At the creation time , i have applied indexes on it.
Should i defrag it peiodically ??
Only the Select Operation is performed on it..
Kindly suggest...
|
|
|
|
|
Aman786Singh wrote: Only the Select Operation is performed on it
It won't get fragmented indeed, since you
- only read from it
- are using a database, not a file-system
Hope this helps 
I are troll
|
|
|
|
|
Eddy Vluggen wrote: are using a database, not a file-system
you expect a read-only file-system to get fragmented over time?
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
|
WHEN I RAN INDEXDEFFRAG..
THEN WHAT THIS RESULT MEANS???
PAGE SCANNED PAGE MOVED
245 0
|
|
|
|
|
You were right
"DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance."
I are troll
|
|
|
|
|
i have a database with a large log file database and i don't know how to do to make it smaller,,,
the file have a large area space what can i do???
|
|
|
|
|
You are not telling us what type of DB you are using. Beware of erasing log files or minimizing log levels since mistakes do happen and when they do, these files are life savers especially in production.
Natza Mitzi
|
|
|
|
|
thanks...
sorry,,, SQL SERVER database
would u help me ???
modified on Tuesday, March 17, 2009 7:06 PM
|
|
|
|
|
Do you know what recovery model you are using (simple vs full)? Are you backing up the database? What version of SQL Server is it?
Scott
|
|
|
|
|
run this if you are working in sql 2005 server.
but for this u need to make your database offline first and then run this code.
--INF: How to Shrink the SQL Server 7.0 Transaction Log
-- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- SQL7 http://www.support.microsoft.com/kb/256650
-- SQL2000 http://support.microsoft.com/kb/272318/en-us
-- SQL2005 http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sysfiles
-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
SELECT @LogicalFileName = 'Test_Log', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 305 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
|
|
|
|
|
thanks for all,,,
i'am sorry the sql server recovery model is full and its version is sql server 2000
i have to reduce the size of this log file
when i deleted it the database don't work, it is suspect and didn't work
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
