|
set rowcount 5
select xyz
from table
order by ....
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I am using sql bulk copy to insert into table from txt file. The following code is is being used for row terminator but it does not work. it works with '\n'. But it should be work with Unix system as well.
@BQUERY AS nVARCHAR(250)
SET @BQUERY = 'BULK INSERT tblCustomer
FROM ''' + @FILEPATH + '''
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\x0A''
)'
EXECUTE SP_EXECUTESQL @BQUERY
Shahzad Aslam
Software Engineer
Email: shehzadaslam@hotmail.com
|
|
|
|
|
Unix row terminators are not the same as windows. Windows uses \r\n, unix only uses \r
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have tested this too, still not working.
Shahzad Aslam
Software Engineer
Email: shehzadaslam@hotmail.com
|
|
|
|
|
Try doing a hex dump of your data and see exactly what character(s) delimit the lines. You can use multiple characters in your format file as record delimiters, so you should be OK.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have read the file using StreamReader it has given some info about file. File is encoded with UTF-8 with code page 65001. It shows \n as line terminator which is not work with SQL bulk Insert.
Shahzad Aslam
Software Engineer
Email: shehzadaslam@hotmail.com
|
|
|
|
|
I guess you tried
ROWTERMINATOR = ''\x0D''
(hex for \n)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Friends,
I am updating and inserting excel datas from sql stored procedure
alter PROCEDURE SP_ExcelMan @File_Name as varchar(50) = ''
AS
BEGIN
SET NOCOUNT ON
DECLARE @Cmd varchar(1000)
DECLARE @fn varchar(500)
DECLARE @provider varchar(100)
DECLARE @ExcelString varchar(100)
-- New File Name to be created
IF @File_Name = ''
Select @fn = 'D:\Test.xls'
ELSE
Select @fn = 'D:\' + @File_Name + '.xls'
-- FileCopy command string formation
SELECT @Cmd = 'Copy D:\Template.xls ' + @fn
-- FielCopy command execution through Shell Command
EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT
-- Mentioning the OLEDB Rpovider and excel destination filename
set @provider = 'Provider=Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;HDR=Yes;Database=' + @fn
-- Executing the OPENROWSET Command for copying the select contents to Excel sheet.
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'')
select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity FROM [Sheet2$]'')
select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as OrderDate,qty as Quantity from sales')
SET NOCOUNT OFF
END
But i am getting the error
Could not locate registry entry for OLE DB provider 'Provider=Microsoft.Jet.OLEDB.4.0'.
OLE DB error trace [Non-interface error: Provider not registered.].
Please help me to clear the error
Thanks in Advance
|
|
|
|
|
in one of my sp that returns refference cursors with some data this sp i want to call in another sp through execute immediate then how can i get the value or out put of the sp in this queery...how to assing the variable in the execute immediate uery
|
|
|
|
|
hi,
i m using this store procedure in my project
ALTER procedure getkeyname(@city sysname,@key varchar(50))
as
exec
('select distinct a.keywordname,a.keywordid from keyword As a INNER
JOIN ' + @city+ ' As b on a.keywordid=b.keywordid and
a.keywordname like ' + @key)
but i got an error like..
invalid column name that i entered in @key parameter
i want to pass my table name dynamically and parameter name but it accept only @city as a table name parameter and it treat @key as Colunm name like @city but i want to pass @key as parameter.
please help me
no knowledge in .net
|
|
|
|
|
Dyamic SQL - Something you have to be very careful in constructing because it is so easy to make a mistake and punch a big massive security hole in your database.
The way you have constructed the SQL make SQL Server interpret @key as a column name. You have to pass @key as a parameter. Actually, you should look up sp_executesql as it provides more protection against attack.
Now, as a further defence against attack you must check that @city relates to a real table first. You can do this by checking against INFORMATION_SCHEMA.TABLES like this:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @city)
BEGIN
-- The table exists, we can run the dynamic SQL
END
ELSE
BEGIN
-- The table does not exist. Perform error processing.
END
|
|
|
|
|
@key must be in quotes.
ALTER procedure getkeyname(@city sysname,@key varchar(50))
as
exec
('select distinct a.keywordname,a.keywordid from keyword As a INNER
JOIN ' + @city + ' As b on a.keywordid=b.keywordid and
a.keywordname like ''' + @key + '''')
|
|
|
|
|
i have the following code:
SqlCommand insertcommand = new SqlCommand();
insertcommand.Connection = con; //connection to SQLserver2000
insertcommand.CommandType = CommandType.StoredProcedure;
insertcommand.CommandText = "InsertQueryoperation";
SqlParameter AwardNum = new SqlParameter("@Award_num",SqlDbType.Int);
//@Award_num SQL Variable on InsertQueryoperation procedure
AwardNum.Direction = ParameterDirection.Output;
// parameter should sent the data to DataSource
txtAward.Text=AwardNum.Value.ToString();
insertcommand.Parameters.Add(AwardNum);
insertcommand.Connection.Open();
insertcommand.ExecuteNonQuery(); //NullReferenceException Accoured here
insertcommand.Connection.Close();
what is the problem with that code?
regards
|
|
|
|
|
r u define @Award_num int output in ur store procedure
if no then write it
no knowledge in .net
|
|
|
|
|
yes i did, here is the stored procedure:
i create it by command builder
CREATE PROCEDURE dbo.InsertQueryoperation
(
@Award_num int,
@patient_name varchar(40),
@Operation_date smalldatetime,
@Duration smalldatetime,
@Surgean_name varchar(40),
@Type_of_operation varchar(20),
@Anaesthiologist_name varchar(40),
@Type_of_anaesthesia varchar(20),
@ID_num int
)
AS
SET NOCOUNT OFF
INSERT INTO Operation_details
(Award_num, patient_name, Operation_date, Duration, Surgean_name, Type_of_operation, Anaesthiologist_name, Type_of_anaesthesia, ID_num)
VALUES (@Award_num,@patient_name,@Operation_date,@Duration,@Surgean_name,@Type_of_operation,@Anaesthiologist_name,@Type_of_anaesthesia,@ID_num);
SELECT Operation_ID, Award_num, patient_name, Operation_date, Duration, Surgean_name, Type_of_operation, Anaesthiologist_name, Type_of_anaesthesia, surgean_ID, Anaesthiologist_ID, ID_num FROM Operation_details WHERE (Operation_ID = SCOPE_IDENTITY())
|
|
|
|
|
CREATE PROCEDURE dbo.InsertQueryoperation
(
@Award_num int output,
@patient_name varchar(40),
@Operation_date smalldatetime,
@Duration smalldatetime,
@Surgean_name varchar(40),
@Type_of_operation varchar(20),
@Anaesthiologist_name varchar(40),
@Type_of_anaesthesia varchar(20),
@ID_num int
)
AS
SET NOCOUNT OFF
INSERT INTO Operation_details
(Award_num, patient_name, Operation_date, Duration, Surgean_name, Type_of_operation, Anaesthiologist_name, Type_of_anaesthesia, ID_num)
VALUES (@Award_num,@patient_name,@Operation_date,@Duration,@Surgean_name,@Type_of_operation,@Anaesthiologist_name,@Type_of_anaesthesia,@ID_num);
SELECT Operation_ID, Award_num, patient_name, Operation_date, Duration, Surgean_name, Type_of_operation, Anaesthiologist_name, Type_of_anaesthesia, surgean_ID, Anaesthiologist_ID, ID_num FROM Operation_details WHERE (Operation_ID = SCOPE_IDENTITY())
i hv change ur parameter @award_num int output
pest this code to ur store procedure.
try this
no knowledge in .net
|
|
|
|
|
|
Hey! Im an sql beginner and i have got a small problem with a query:
select avg(dbo.GetProcessingTime(InterchangeId)) from Messages
i want to call the function only once per InterchangeId
like
select distinct InterchangeId from Messages -- Run the avg(Function(...)) on this result only
Any suggestions?
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
select distinct avg(dbo.GetProcessingTime(InterchangeId)) from Messages
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hm yeah but that selects the avg(.. only once?
and avg(distinct functioncall(... gets the result of the functioncall distinct(what if it is the same for two different Ids?)
if I understand it correctly 
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
Can you post here data how are stored on table, and write result which you want to get.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I also have a function to get the processing time for an interchange, it looks like this:
ALTER function [dbo].[GetProcessingTime] ( @interchangeId uniqueidentifier )
returns int
AS
BEGIN
DECLARE @recDate DateTime
DECLARE @sendDate DateTime
DECLARE @return int
-- Get receivetime
select top 1 @recDate = mf.[Event/Timestamp] from dbo.Ports p INNER JOIN
BizTalkDTADb.dbo.dtav_MessageFacts mf
INNER JOIN
dbo.Messages m ON mf.[MessageInstance/InstanceID] = m.MessageId ON
p.Name = mf.[Event/Port] INNER JOIN
dbo.PortTypes pt ON p.FK_PortType = pt.Id
WHERE pt.Description='ReceivePort' and m.[InterchangeId] = @interchangeId
order by mf.[Event/Timestamp] asc --first
select top 1 @sendDate = mf.[Event/Timestamp] from dbo.Ports p INNER JOIN
BizTalkDTADb.dbo.dtav_MessageFacts mf
INNER JOIN
dbo.Messages m ON mf.[MessageInstance/InstanceID] = m.MessageId ON
p.Name = mf.[Event/Port] INNER JOIN
dbo.PortTypes pt ON p.FK_PortType = pt.Id
WHERE pt.Description='SendPort' and m.[InterchangeId] = @interchangeId
order by mf.[Event/Timestamp] desc -- latest
set @return = DATEDIFF(millisecond ,@recDate,@sendDate)
return @return
END
It gets the time difference between the first and the last message in the interchange in milliseconds.
Now what i want to do is:
get the average processing time for each unique interchange
hence:
ALTER proc [dbo].[GetAverageProcessingTime](@startDate datetime, @endDate datetime)
as
select avg(distinct dbo.GetProcessingTime(InterchangeId)) from Messages where WrittenDate between @startDate and @endDate am confused about the distinct in the last procedure, it feels.. wrong 
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
try to group by InterchangeId like:
select distinct avg(dbo.GetProcessingTime(InterchangeId)) from Messages<br />
Group By InterchangeId <br />
Having WrittenDate <br />
between @startDate and @endDate
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I think i solved it! 
ALTER proc [dbo].[GetAverageProcessingTime](@startDate datetime, @endDate datetime)
as
begin
DECLARE @InterchangeIds TABLE
(
InterchangeId uniqueIdentifier
)
insert into @InterchangeIds (InterchangeId)
select distinct m.InterchangeId from Messages m where WrittenDate between @startDate and @endDate
select avg(dbo.GetProcessingTime(InterchangeId)) from @InterchangeIds
end
thanks
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
Cool,sorry for my unusefull answers 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
