creating xml and passing it on.

I'm rather new to xml in sql server 2000.
So i searched around for a while, but i can't quite understand how should i
make a stored procedure, that will create xml from a select clause and then
pass it as an input parameter to another stored procedure?
Basically i can't figure out how can i use "for xml" other than stored
procedure resultset. Or it is not possible otherwise?
Thanks in advance,
Martin.XML capabilities in SQL 2000 is limited compared to what is offered in SQL
2005. If you are starting out, spend some time at www.sqlxml.org and you'll
find most answers to your questions there.
Anith

creating new tables vs passing parameters

Hi,
I am using an Access ADP front end with a SQL Server backend.
I have a report which is based on a stored procedure with multiple parameters.
I have a Search form with multiple drop downs and the parameters are passed to the Sp from this form.

I had many problems passing the parameters to the Report since some of them may not be supplied and a default of % should be used.

I finally decided to creat a new table using the SP and insert the data to that table.

My question is if I create a temp table named "A" each time the report is ran, and say two users run the report at the same time, what will happen?

Is there any way to creat a temp table that SQL Server would take care of its name?

ThanksThere is no problem if you create a temp table during the execution of a SP. SQLServer will take care of you temp table and identify it after the connection user, so that even if two different users executes the same store proc the temp tables that are created are different. A temporary table is scoped to the execution of the batch (here the store proc), that means the table will be removed after the store proc ends.

But I still don't understant why you are not satisfied with store procs and which is the big problem in passing parameters to one. what the benefit will be if you create a temp table?

ionut calin|||The problem is that I want the SP to be the recordsource for a report.
See, I have a search form with the below criteria:

Caller
Property Manager
Date
BuildingID
Comm. Status
Ops Mgr

Then, I have a stored procedure based on 3 joins. The user should be able to check the Parameters he wants to search for and the SP will return the results. For example if I want all the records for Date='2/2/2002' and BuildingID='55555', the SP will return all the recods for these values and % (any thing) for other parameters.

I am using an access front end and I use VB to pass the paramertes to the SP. Its fine so far. But when I set the recordsource of the report to a sql string executing the SP with the parameters the report gets the first parameter and not the second or third or... one.
I don't get why?
Thats why I decided to set the recordsource of the report to a table.
You can see the VB code and the SP below:

Stored Procedure:---------------
---------------
CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@.RCaller varchar(50) = '%',
@.RPropMgr varchar(50) = '%',
@.RDate varchar(50) = '%',
@.RComStat varchar(50) = '%',
@.ROpsMgr varchar(50) = '%',
@.RBID varchar(50) = '%')

AS SELECT
dbo.tblComIssue.ComID,
dbo.tblProperty.AVPID, d
bo.tblProperty.OpsMgrID,
dbo.tblProperty.ProjectMgrID,
dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle,
dbo.tblProperty.Division,
dbo.tblComIssue.ComDate,
dbo.tblComIssue.ComType,
dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
dbo.tblComResponse.ResID,
dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr,
dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON
dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID

WHERE (
dbo.tblComIssue.ContactID LIKE @.RCaller OR
dbo.tblComIssue.ContactID IS NULL) AND

(dbo.tblProperty.ContactID LIKE @.RPropMgr OR
dbo.tblProperty.ContactID IS NULL) AND

(dbo.tblComIssue.IssueClosed LIKE @.RComStat OR
dbo.tblComIssue.IssueClosed IS NULL) AND

(dbo.tblProperty.OpsMgrID LIKE @.ROpsMgr OR
dbo.tblProperty.OpsMgrID IS NULL) AND

(dbo.tblProperty.BuildingID LIKE @.RBID OR
dbo.tblProperty.BuildingID IS NULL) AND

(CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE CONVERT(Varchar(10), @.RDate, 101) OR
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) IS NULL)

----------------------

VB Code:------------------
'I have check boxes for each parameter. If the check box is check and the users wants to serach by that check box I will add that parameter to the string:

Dim strSQL As String
Dim Param

Param = ""

If Me!ChCaller = True Then
Param = Param & "@.RCaller=" & "'" & Me!RCaller & "'"
End If

If Me!ChBID = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RBID=" & "'" & Me!RBID & "'"
End If

If Me!ChPropMgr = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RPropMgr=" & Me!RPropMgr
End If

If Me!ChDate = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RDate=" & "'" & Me!RDate & "'"
End If

If Me!ChComStat = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RComStat=" & "'" & Me!RComStat "'"&
End If

If Me!ChOpsMgr = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.ROpsMgr=" & "'" & Me!ROpsMgr & "'"
End If

strSQL = "execute SP_Report_ComIssue_Custom_Param " & Param

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewDesign
Reports("Rpt_SP_Rport_ComIsssue_AdvSearch").RecordSource = strSQL

DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview|||Sorry for the delay, but not my fault. I've wrote a replay for you post it but it didn't apear (I don't know why).

First of all, passing parameters to stored procs:

-I would try to set null as the default value for params (i've had problem passing null value through an ODBC connection (here you have an OLEDB one, and it just might work, but why take chances)

So store proc would look like this:

CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@.RCaller varchar(50) = NULL,
@.RPropMgr varchar(50) = NULL,
@.RComStat varchar(50) = NULL,
@.ROpsMgr varchar(50) = NULL,
@.RBID varchar(50) = NULL)

AS SELECT

--@.RDate varchar(50) = '%', I can't see the point of this parm

dbo.tblComIssue.ComID,
dbo.tblProperty.AVPID, d
bo.tblProperty.OpsMgrID,
dbo.tblProperty.ProjectMgrID,
dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle,
dbo.tblProperty.Division,
dbo.tblComIssue.ComDate,
dbo.tblComIssue.ComType,
dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
dbo.tblComResponse.ResID,
dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr,
dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON
dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID

WHERE (
dbo.tblComIssue.ContactID IS NULL or @.RCaller is null or dbo.tblComIssue.ContactID LIKE '%'+@.RCaller
) AND

(
dbo.tblProperty.ContactID IS NULL or @.RPropMgr is null or dbo.tblProperty.ContactID LIKE '%'+@.RPropMgr
) AND

(
dbo.tblComIssue.IssueClosed IS NULL or @.RComStat is null or
dbo.tblComIssue.IssueClosed LIKE '%'+@.RComStat
) AND

(
dbo.tblProperty.OpsMgrID IS NULL or @.ROpsMgr is null or
dbo.tblProperty.OpsMgrID LIKE '%'+@.ROpsMgr
) AND

(
dbo.tblProperty.BuildingID IS NULL or @.RBID is null or
dbo.tblProperty.BuildingID LIKE '%'+@.RBID OR
) --AND

--this make no sense at all. What's the point of next possible condition
--"03/12/2003" like "%01/12/2003". You can not use like to compare a
--date field with what another date, or another date part. No sense at all
--(at least not for me) so I've remove it
--(CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE
--CONVERT(Varchar(10), @.RDate, 101) OR
--CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) IS NULL)

VB:

In VB when you want to call a sub, a functin or a method and you don't want to give explicit values for the parameters that have a default one, you simply "step over" the parameter, but still put commas, like:

DOCmd.SomeMethod 1,,3,4 (here param 2 takes whatever its default value is)

In SQLServer store proc you can't just simply ignore a param. If you want a parameter to take its default value you must write DEFAULT instead of explicit value.

exec SomeStoreProc 1,DEFAULT,3,4 (You don't have to specify param names like @.PARAM1=1, @.PARAM2=default and so on...)

So here is your VB code:

Dim strSQL As String
Dim Param

Param = ""

If Me!ChCaller = True Then
Param = "'" & Me!RCaller & "' , "
else
Param="DEFAULT , "
End If

If Me!ChBID = True Then
Param = Param & "'" & Me!RBID & "' , "
else
Param=Param & "DEFAULT , "
End If

If Me!ChPropMgr = True Then
Param = Param & "'" & Me!RPropMgr & "' , "
else
Param=Param & "DEFAULT , "
End If

'I removed the follwoing lines (for the known reason)
'If Me!ChDate = True Then
'If Not Param = "" Then
'Param = Param & ","
'End If
'Param = Param & "@.RDate=" & "'" & Me!RDate & "'"
'End If

If Me!ChComStat = True Then
Param = Param & "'" & Me!RComStat "' , "
else
Param=Param & "DEFAULT , "
End If

If Me!ChOpsMgr = True Then
Param = Param & "'" & Me!ROpsMgr & "'"
else
Param=Param & "DEFAULT"
End If

strSQL = "execute SP_Report_ComIssue_Custom_Param " & Param

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewDesign
Reports("Rpt_SP_Rport_ComIsssue_AdvSearch").RecordSource = strSQL

DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview

I am not too familiar with .adp type of project, and I'm not sure if this can really work:

RecordSource = "execute SP_Report_ComIssue_Custom_Param " & Param

but if you said that it works its ok. The main problem was the way that you passed the parameters.

Another thing. If you use Access XP .adp's then your store procedures are seen by Access as querys, so you can set that query as record source for report in design view, and not need to change afterwards (only need to set parameters value for querys -> it's very simple using ADODB and ADOX objects hierachy-> see Catalog object in ADO help)

In Access 2000, .adp's works with SQLServer7 and sees store proces as store procs. I don't think that you can set the record source of a report to a store proc (only to a table or a query, maybe that was thw reason they change the way that store procs aree seen in Access 2002)

Anyway record source property of Access report sucks, you can not for instance set the recor source to a ADO recordset (or to a DAO recordset for that matter). I think this is relly stupid, because prevent programmers from benefit the real power of SQLServer store procs, and I mean it store procs are the best (and now user defined functions too).

Anyway, good luck!

ionut calin

Creating new table from passed variabe in MSSQL Stored procedure

Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...

CREATE PROCEDURE dbo.CreateTable
@.TableName NVARCHAR(50)
AS
BEGIN

create table @.TableName ([id] int identity(1,1),[diller] int)

end
GO

THIS CANT BE TRUE !!!!!!!!You need to do the following to get it working

CREATE PROCEDURE dbo.CreateTable
@.TableName NVARCHAR(50)
AS

BEGIN
Declare @.SQLStatement nvarchar(4000)

SET @.SQLStatement = 'create table ' + @.TableName + '([id] int identity(1,1),[diller] int)'

Exec (@.SQLStatement )

end
GO

That should do it.

Let me know if you have any questions.|||If it aint to much i would very much like to check for the tables existens before i create it... so basicly i would need to check if the variable exists as a table before i insert it something like

if not exists(Select * From @.TableName)
begin

Stuff...

End

And by the way thk you for the quick reply...|||You can have it as
if not exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = @.TableName)
begin

Stuff...

End

Recently, I wrote article on how to check the objects in database. You can check it at www.aspalliance.com/349 to understand the information_schema views.

Let me know if the above does not work for you.

AP|||Thk you very much been pulling my hair over this... and good article very helpfull... i just startet programming in SQL well my vision is to create websites structured from SQL so that changing stuff around the page is going to be an ease for the user..

thx again...

Jakob

Creating GUI with SSIS or Passing parameter(s) in SSIS

Hello All!

I have two questions to ask in this one thread. I would appreciate any feedback.

1. Is it possible to create GUI from SSIS using macro so that it can display forms or dialogs? If so how can I create a form that can be used to pass the parameters for the execution of the SSIS?

2. Is it possible to pass parameter(s) to SSIS? If yes, how can we do it...Please provide me with any example.

I wait to hear from you!

Thanks,
Niben

1. Yes, you can use the Script task to show forms and gather user input. But you shouldn't. See #2 for information on the better approach

2. You can set values in an SSIS package from an external source in several ways. Configurations are an option that allow you to store values in an XML file or database table. These values are read in at run-time. These are used primarily for things like connection strings, and other relatively static information that may change once in a while, or when you move from test to production.

You can also set the value of a variable or property of the package when you execute it by using the /SET option of DTEXEC. This method is preferred for values that change each time the package is executed. In the case that you need user input for your package, I would suggest gathering it up front by creating a GUI in your choice of languages, then pass the values collected to the SSIS package using the /SET command.