People tend to live up to what's expected of them and they tend to do better
when treated as if they are capable of success.These are the lessons of The Pygmalion Effect.
Pygmalion first appeared in Greek mythology as a king of
Cyprus who carved and then fell in love with a statue of a woman, which
Aphrodite brought to life as Galatea.
Much later, George Barnard Shaw wrote a play, entitiled Pygmalion, about Henry Higgins (the gentleman) and Lisa Doolittle (the cockney flower girl whom Henry turns bets he can turn into a lady).
-- in the end,
i will be able to query a file straight from sql server:
-- ** select * from
txt_delimited...book1#csv ** if the
file name is book1.csv i saved a text delimited file in a folder txt_del on my
desktop. you can drag and drop any file into that particular folder and do a
quick-query.
-- note: if you
super-tweaked your database collation, your collation must be case insensitive!
declare
-------------------------------------------------------------------
@nameoflinkedfiletype sysname,
@describemyfile nvarchar(256),
@provider nvarchar(256),
@myfilefolder nvarchar(4000),
@extrafileinfo nvarchar(4000),
@sqlserverloginame sysname
-------------------------------------------------------------------------------------
set @nameoflinkedfiletype = 'txt_del'
set @describemyfile = 'text'
set @provider = 'microsoft.jet.oledb.4.0'
set @myfilefolder = 'c:\users\[username]\desktop\txt_del'
set @extrafileinfo = 'text;hdr=yes;fmt=delimited'
set @sqlserverloginame = 'machine name\login'
exec
master.dbo.sp_addlinkedserver
@server = @nameoflinkedfiletype,
@srvproduct = @describemyfile,
@provider = @provider,
@datasrc = @myfilefolder,
@provstr = @extrafileinfo
-- add a linked server login.... etc..
exec
master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = @nameoflinkedfiletype,
@locallogin = @sqlserverloginame,
@useself = 'true'
-- then from
now on, you can simply type a select on whatever file you have on that folder!!!
likek ..
SELECT verse
INTO poetry
FROM txt_del...Book1#csv AS Book1#csv_1
SELECT OBJECT_NAME(sys.sysindexes.id) AS [object name], INDEX_COL(OBJECT_NAME(sys.sysindexes.id), sys.sysindexes.indid, 1) AS [column]
FROM sys.sysindexes
INNER JOIN
sys.sysobjects
ON sys.sysobjects.id = sys.sysindexes.id
WHERE (sys.sysobjects.xtype
IN ('u', 'v')) AND (sys.sysindexes.status
= 0) AND (INDEX_COL(OBJECT_NAME(sys.sysindexes.id), sys.sysindexes.indid, 1) IS NOT NULL)
ORDER BY [object name]
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE with
override
GO
IF OBJECT_ID('#MyTable') IS NOT NULL
BEGIN
DROP TABLE
#MyTable
END
SELECT *
INTO #MyTable
FROM
OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0'
,'Data
Source="C:\Event_Leads\G2WAttendee_02125312";Extended
Properties="text;HDR=No;FMT=Delimited";'
)...[G2WAttendee#csv]
Select *
from #MyTable
DROP TABLE #MyTable
EXEC sp_configure 'Ad Hoc Distributed
Queries', 0
GO
RECONFIGURE with override
GO
EXEC sp_configure 'show
advanced options', 0
GO
RECONFIGURE
GO
01/15/2008 for January 15, 2008.
SELECT , CONVERT(VARCHAR(12), ExpDate, 1) AS Expires
-- create a #session table so it will be cleaned up when
you close out of the window
INTO [#Oneyear]
FROM Mytbl
END TRY
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception.
--note: * I have heard ASP.NET will give the Raiserror message...
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END
There are a few reasons why you would get a NULL return string error.
Other interesting things
Look up schema.ini formatting. You can define what each column format is, according to Jet standards...
