November 24, 2014 at 4:26 am
Bonjour je suis débutante en SQL SERVER MANAGEMENT STUDIO 2008.
dans mon projet je voudrais afficher les résultas de la procédure "DetailRaport" dans un tableau excel,
je me suis servit des explication et des codes trouvées sur internet pour écrire ce code:
USE [POINTAGE]
GO
CREATE ASSEMBLY ExportToExcel
FROM 'D:\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\CLR\ExcelExport.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
CREATE PROCEDURE [dbo].[SP_CLR_Export_Excel]
-- Add the parameters for the stored procedure here
@proc [nvarchar](100),
@path [nvarchar](200),
@filename [nvarchar](100),
@params xml,
AS EXTERNAL NAME SP_CLR_ExportToExcel.StoredProcedure.ExportToExcel
GO
DECLARE @path varchar(4000),
@file varchar(4000),
@params XML
SET @path = 'C:\XLS\'
SET @file = 'MonfichierExcel'
-- les Paramètres de la procédure appelées
SET @params = <params> <param name="@pUserID" value="null ">
<param name="@pDepartmentID" value="null ">
<param name="@pStartDate" value="null ">
<param name="@pEndDate" value="null "> </params>
EXECUTE SP_CLR_Export_Excel 'dbo.DetailRaport', @path, @file, @params;
GO
Mon problème c'est que j'ai des erreurs aux lignes :
-CREATE PROCEDURE [dbo].[SP_CLR_Export_Excel] (syntaxe non valide: "CREATE PROCEDURE" doit être la seul
instruction du lot
-<params>...</params>: <params> (syntaxe incorrect) et </params> nom de colonne non valide
- EXECUTE SP_CLR_Export_Excel: impossible de trouver la procédure stokées SP_CLR_Export_Excel.
s'il vous plaît quequ'un peut-il m'aider à corriger ce code, je ne vois pas trop comment corriger ces erreurs.
Mes remerciements d'avance.
November 24, 2014 at 7:30 am
anglais, svp
Gerald Britton, Pluralsight courses
November 24, 2014 at 7:37 am
I can't speak French but I can tell you where the problems are in your code. First is the procedure declaration. You were missing parenthesis around your parmeters. Second issue is the code after your procedure. You were missing the single quotes around your xml.
Here are the two pieces with valid syntax.
CREATE PROCEDURE [dbo].[SP_CLR_Export_Excel]
(
-- Add the parameters for the stored procedure here
@proc [nvarchar](100),
@path [nvarchar](200),
@filename [nvarchar](100),
@params xml
)AS EXTERNAL NAME SP_CLR_ExportToExcel.StoredProcedure.ExportToExcel
GO
DECLARE @path varchar(4000),
@file varchar(4000),
@params XML
SET @path = 'C:\XLS\'
SET @file = 'MonfichierExcel'
-- les Paramètres de la procédure appelées
SET @params = '<params> <param name="@pUserID" value="null ">
<param name="@pDepartmentID" value="null ">
<param name="@pStartDate" value="null ">
<param name="@pEndDate" value="null "> </params>'
EXECUTE SP_CLR_Export_Excel 'dbo.DetailRaport', @path, @file, @params;
GO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 24, 2014 at 7:48 am
anglais, svp
Very roughly, it's:
---
Hi, I'm a beginner with SQL 2008. In my project/work I would like to export the output of the 'DetailRaport' procedure into an Excel table. I used tutorials and snippets of code found online to write this code:
[...]
Could somebody please help me correct this code; I can't see how to correct these errors. Thanks in advance.
---
I think Sean's got it covered though :-D.
November 24, 2014 at 8:02 am
Beatrix Kiddo (11/24/2014)
anglais, svp
Very roughly, it's:
---
Hi, I'm a beginner with SQL 2008. In my project/work I would like to export the output of the 'DetailRaport' procedure into an Excel table. I used tutorials and snippets of code found online to write this code:
[...]
Could somebody please help me correct this code; I can't see how to correct these errors. Thanks in advance.
---
I think Sean's got it covered though :-D.
I can't even pretend to decipher French without an interpreter but...
syntaxe non valide: "CREATE PROCEDURE"
made it pretty obvious even to me what the question was.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 24, 2014 at 9:39 am
The error she's describing is the one stating that CREATE PROCEDURE needs to be the first in the batch.
WantedButter -
Votre error consiste de n'avoir pas separe the CREATE PROCEDURE du code avant.
Sean a corrige des erreurs de syntaxe a l'interieure to CREATE RPOCEDURE, mais il y a une autre correction ici:
USE [POINTAGE]
GO
CREATE ASSEMBLY ExportToExcel
FROM 'D:\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\CLR\ExcelExport.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO --<-- ajouter le separateur GO
CREATE PROCEDURE [dbo].[SP_CLR_Export_Excel]
Avec le separateur avant et apres, la requete pour creeer la procedure est toute seul et sera la "premiere"/seule instruction.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply