|
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."
|
|
|
|
|
Can someone please tell me the commands for the following:
Deleting a Primary Key
Adding a Primary Key
Turning a Primary Key off and On for Nulls, duplicates, etc.
Thanks,
Michael
|
|
|
|
|
MAW30 wrote: Deleting a Primary Key
Adding a Primary Key
To either delete the field altogether or add a field, look up the ALTER TABLE SQL command.
MAW30 wrote: Turning a Primary Key off and On for Nulls, duplicates, etc.
You can't do this - Primary keys can never be NULL nor does it allow duplicates.
|
|
|
|
|
So.
I'm trying to match multiple codes (in one record) to their descriptions as listed in another table.
So I have...
TABLE 1 with columns: Key, Code1, Code2
TABLE 2 with columns: Code, Description
...which are tables I have no control over.
I want to do an inner join so that my new table will have columns...
Key, Code1, Code1Description, Code2, Code2Description.
Its easy enought to match one
SELECT Table1.*, Table2.description AS Code1Description<br />
FROM Table1 INNER JOIN<br />
Table2 ON Table1.Code1=Table2.Code
How do i expand this to 2 or 3 or 4 different codes???
Please?
|
|
|
|
|
The same way you did it for the first column. ie: adding a second would give:
SELECT t1.*, c1.description AS Code1Description, c2.description AS Code2Description
FROM Table1 t1
INNER JOIN Table2 c1 ON t1.Code1=c1.Code
INNER JOIN Table2 c2 ON t1.Code2=c2.Code With this you should be able to add as many code you would like.
Wout Louwers
|
|
|
|
|
Thank you very much.
Never would have got that on my own.
But on some servers the description table is empty, and i'm not getting any return rows. How can i make it optional, to say
Return row for key='123' and add the descriptions IF they exist in the descriptions table?
|
|
|
|
|
Hi;
my english is bad sorry 
I have a table
review (note, #code_matiere, #num_inscription)
I created a pivot table from it.
and I showed it in a gridview
I add column moyennemodule which is the average of 4 matter and column avggeneral average of moduls.
I mean General sql following:
<code>
SELECT Num_Inscription, [1] AS '1', [2] AS '2', [3] AS '3' , (([ 1 ]*(select COEFF from MATIERE where code_mat=1))+<b>([2]*(select COEFF from MATIERE where code_mat=2)))/ ((select COEFF from MATIERE where code_mat=1)+(select COEFF from MATIERE where code_mat=2))AS moymod1 ,........as moymod2,..............as moymod3,...........asmoymod5</b>
FROM
(SELECT Code_mat, Num_Inscription, Note
FROM Examen ) p
PIVOT
(
SUM(Note)
FOR Code_Mat IN
( [1], [2], [3])
) AS pvt</code>
i have 5 module I must repeat the part in bold and 5 times ,and the overall average which is sum (moymod) / 8 I have to redo the code in bold 8 times so de sql is very long.
Do you have a solution.
thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
