Using Reporting Service Web service
It is possible to create folder and delete folder. But I couldn't rename the folder using code it tried with setProperties method but name is ready only property. Please help me to resolve the issue.
Thanks in Advance.
Krishna kumar.M
hi all,
Later I found if use the rs.MoveItem we can achive the same
and you will find). It is probably not suppo and you willMy report has a parameter of "End_Date" which is used to create my dataset from a stored procedure. In the dataset, a column called "Begin_Date" is returned with various other information used in the report creation.
Currently, the parameter "End_Date" is used in the page header as part of my report title. My user's whould also like to see the "Begin_Date" value in the page header.
Both End and Begin dates are unique values occuring once per report run. How do I get the "Begin_Date" contained in my dataset as a value to be displayed in the page header title?
many thanks
The current method is to add a hidden text box to the body with that value. Then reference that text box value in the page header.
In future releases we are looking at adding field references to the other page sections.
|||Thank you Brad for the response. Please excuse my lack of understanding. In the Page header, any time I reference a field in the body I get a message basically saying "Fields can not be used in any page headers or footers".
I have tried placing a hidden text box in the body with the value and referencing it in the page header, but with no success. I continue to receive the "Fields can not be used in any page headers or footers" error message.
Have I misunderstood your response? thanks
|||you need to give reference like these for the header textbox
=ReportItems!txtReportHeader.Value
where txtReportHeader is the text box in the body, which contains the value of the dataset field
|||Thanks rakam, it worked like a charm....
I didn't realize you could reference ReportItems from headers and footers. Appreciate the information.
Hi
I need to create a SQL Server job that is dependant on the sucess of another. I'm not able to put both steps in the same job becuase they need to run at different times of the day.
If my first job over runs or hangs for what ever reason I do not want my second job that starts an hour later to start until the first job finishes OR if my backup hangs or over runs I do not want my first job to start.
You can add multiple schedules to a SQL job but I can't see away of assigning individual stepes to the schedules and don't even know if this would do what I need.
How do other people contorl dpendancies between jobs that need to execute at different times of the day and therefore can't exist int he same job.
Thanks for your help
Marcus
In the first job, you could add a second step that upon competition of the first step enables the second job, and then in that second job add a step disables the job upon successful completition.|||Even on that job step you can state to stop on failure, where the next job setps will not be executed.|||With the OP's requirement that the subsequent 'job' or 'steps' run on a different schedule, it seems that 'stop on failure'/'continue on success' would not really be a solution.|||Or else you can program the same using SP_START_JOB for that specified step name, books online is best resource to check for syntax and examples.|||I tried this solution but unfortunately it doesn't work. The master job that issues the start job for ALL the others works fine.
the problem is it starts the step, thus starting the job and immediately goes on to the next one. Basically starting them all at once
Does anyone have any other ideas?
I tried the SSIS task that starts jobs but this has the same behaviour.
I'm really suprised that microsoft hasn't figured out that someome may want to schedule jobs that have dependacies.
cheers
Marcus
Hi
I need to create a SQL Server job that is dependant on the sucess of another. I'm not able to put both steps in the same job becuase they need to run at different times of the day.
If my first job over runs or hangs for what ever reason I do not want my second job that starts an hour later to start until the first job finishes OR if my backup hangs or over runs I do not want my first job to start.
You can add multiple schedules to a SQL job but I can't see away of assigning individual stepes to the schedules and don't even know if this would do what I need.
How do other people contorl dpendancies between jobs that need to execute at different times of the day and therefore can't exist int he same job.
Thanks for your help
Marcus
In the first job, you could add a second step that upon competition of the first step enables the second job, and then in that second job add a step disables the job upon successful completition.|||Even on that job step you can state to stop on failure, where the next job setps will not be executed.|||With the OP's requirement that the subsequent 'job' or 'steps' run on a different schedule, it seems that 'stop on failure'/'continue on success' would not really be a solution.|||Or else you can program the same using SP_START_JOB for that specified step name, books online is best resource to check for syntax and examples.|||I tried this solution but unfortunately it doesn't work. The master job that issues the start job for ALL the others works fine.
the problem is it starts the step, thus starting the job and immediately goes on to the next one. Basically starting them all at once
Does anyone have any other ideas?
I tried the SSIS task that starts jobs but this has the same behaviour.
I'm really suprised that microsoft hasn't figured out that someome may want to schedule jobs that have dependacies.
cheers
Marcus
How can I JOIN the results of two stored procs?I have a two stored procs: sp_Users_GetByID and sp_UserInfo_GetByIDI want to create another stored proc that basically grabs the results from both of these, joins them, and returns that data. I just don't know how...
Has your question been answered here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84858
||| Yes, they answered my question. Thanks!
Hi,
I am currently loading dimensions using a Sql Server Destination and i was wondering if i can create a middle step to insert a certain row so i would identify in my dimension the undefined records... I know i can do it with sql statement but i was wondering if there is a better way.
Best Regards,
Luis Sim?es
Would this not be part of the fact load, you wish to infer the dim member from the fact data when there is not already a matching dim member? You would use either the lookup or join to detect the rows in the fact that do not have a corresponding dim member. Normally I would expect a lookup to be better for the job as there you have many more fact rows than dim rows, so lookups are normally quite effective. The lookup would be used to get the dim key (surrogate key value) and this is then inserted. You could use the error output of the lookup, or even ignore lookup failures and then use a conditional split to get a path with only rows that having missing values. You would then insert the missing Dim members then insert the fact. You will need to ensure only you do only one insert as you could have several facts missing the same dim member in one buffer.
|||I think that was not the desired output...
What i really want to do is to insert just one row with a specific caracteristic and add it to the dimension if it doens't exists already...
That record will be used to identify "undefined" records of dimension X.
Best Regards,
|||You can very easily use a custom script component in "Source" mode to create rows.The rest depends on your architecture, but perhaps you could union your new "unknown" member with the rest of your incoming dimension data. I assume you already have a mechanism to discard/update members that already exist.
Alternatively, you could have a separate Data Flow that tries to load unknowns for all dimensions. Each dimension would have a separate custom source to create the row, a lookup to detect it if already exists, and a destination to insert it if it doesn't.
|||
If you want to do this in the data-flow that populates the dimension table then use a script transformation to create your "Unknown" row and use a UNION transformaiton to put it together with the est of the incoming data. Very simple.
-Jamie
|||
Script and Unions will do the trick, but how often do you populate a Dim table from scratch? Would it not be easier to include the "unknown" record as part of your create table script? You only create a Dim table once, and therefore only need to do this once, so it would make more sense to me just do it manually, and avoid complicating the packge.
It also makes it easier to break that cardinal rule and hard code a surrogate key value. Code a -1 for example as the unknown, and then you can use that as a default value assigned in your pipeline if the regular lookup fails. People may complain about this logic, but it can perform rather well.
--Insert data into existed table
INSERT INTO XLImport
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
Other approaches: http://support.microsoft.com/kb/321686|||Thanks for answer me;
But it return error :
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
i'm trying to connect using sql author.. but it return the same error;
help me, thanks alot|||Open Start->Programs->SQLServer...->Configuration....->Surface Area Configuration, then click by Features link and enable 'Ad Hoc Distributed Queries'|||
Konstantin Kosinsky wrote:
Open Start->Programs->SQLServer...->Configuration....->Surface Area Configuration, then click by Features link and enable 'Ad Hoc Distributed Queries'
Still getting same error after configuring
|||Try this
EXEC sp_configure 'Ad Hoc Distributed Queries', '1';
RECONFIGURE;
|||Hi try this........
Just copy the below stored procedure and run by passing the required parameter.
Stored Procedure:
create procedure sys.sp_addlinkedserver
@.server sysname, -- server name
@.srvproduct nvarchar(128) = NULL, -- product name (dflt to ss)
@.provider nvarchar(128) = NULL, -- oledb provider name
@.datasrc nvarchar(4000) = NULL, -- oledb datasource property
@.location nvarchar(4000) = NULL, -- oledb location property
@.provstr nvarchar(4000) = NULL, -- oledb provider-string property
@.catalog sysname = NULL -- oledb catalog property
as
-- VARIABLES
declare @.retcode int
-- VALIDATE OLEDB PARAMETERS
if @.provider is null
begin
-- NO PROVIDER MEANS CANNOT SPECIFY ANY PROPERTIES!
if @.datasrc is not null or @.location is not null or
@.provstr is not null or @.catalog is not null
begin
raiserror(15426,-1,-1)
return (1)
end
-- THIS MUST BE A WELL-KNOWN "SQL Server" TYPE (DEFAULT IS SS)
if @.srvproduct IS NOT null AND lower(@.srvproduct) <> N'sql server'
begin
raiserror(15427,-1,-1,@.srvproduct)
return (1)
end
-- USE ALL-NULLS FOR SQL-SERVER PROVIDER
select @.srvproduct = NULL
end
else if @.srvproduct in (N'SQL Server') -- WELL-KNOWN PRODUCT
begin
-- ILLEGAL TO SPECIFY PROVIDER/PROPERTIES FOR SQL Server PRODUCT
raiserror(15428,-1,-1,@.srvproduct)
return (1)
end
else if @.srvproduct is null or lower(@.srvproduct) like N'%sql server%'
begin
raiserror(15429,-1,-1,@.srvproduct)
return (1)
end
-- ADD THE LINKED-SERVER
EXEC @.retcode = sys.sp_MSaddserver_internal @.server,
@.srvproduct, @.provider, @.datasrc, @.location, @.provstr, @.catalog,
1, 0, 0 -- @.linkedstyle, @.localentry, @.dup_ok
return @.retcode
-- SUCCESS
return (0) -- sp_addlinkedserver
For Execution:
sys.sp_addlinkedserver 'XLTEST_SP','Excel','Microsoft.Jet.OLEDB.4.0',
'c:\sample.xls','Excel 8.0'
Just leave Location and Catalog
This will create a Linked Server name called XLTEST_SP.
Then do normal procedure!!!!!!!!!!!!!!!!!!!!!!!
--Insert data into existed table
INSERT INTO XLImport
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
Other approaches: http://support.microsoft.com/kb/321686|||Thanks for answer me;
But it return error :
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
i'm trying to connect using sql author.. but it return the same error;
help me, thanks alot|||Open Start->Programs->SQLServer...->Configuration....->Surface Area Configuration, then click by Features link and enable 'Ad Hoc Distributed Queries'|||
Open Start->Programs->SQLServer...->Configuration....->Surface Area Configuration, then click by Features link and enable 'Ad Hoc Distributed Queries'
Still getting same error after configuring
|||Try this
EXEC sp_configure 'Ad Hoc Distributed Queries', '1';
RECONFIGURE;
|||Hi try this........
Just copy the below stored procedure and run by passing the required parameter.
Stored Procedure:
create procedure sys.sp_addlinkedserver
@.server sysname, -- server name
@.srvproduct nvarchar(128) = NULL, -- product name (dflt to ss)
@.provider nvarchar(128) = NULL, -- oledb provider name
@.datasrc nvarchar(4000) = NULL, -- oledb datasource property
@.location nvarchar(4000) = NULL, -- oledb location property
@.provstr nvarchar(4000) = NULL, -- oledb provider-string property
@.catalog sysname = NULL -- oledb catalog property
as
-- VARIABLES
declare @.retcode int
-- VALIDATE OLEDB PARAMETERS
if @.provider is null
begin
-- NO PROVIDER MEANS CANNOT SPECIFY ANY PROPERTIES!
if @.datasrc is not null or @.location is not null or
@.provstr is not null or @.catalog is not null
begin
raiserror(15426,-1,-1)
return (1)
end
-- THIS MUST BE A WELL-KNOWN "SQL Server" TYPE (DEFAULT IS SS)
if @.srvproduct IS NOT null AND lower(@.srvproduct) <> N'sql server'
begin
raiserror(15427,-1,-1,@.srvproduct)
return (1)
end
-- USE ALL-NULLS FOR SQL-SERVER PROVIDER
select @.srvproduct = NULL
end
else if @.srvproduct in (N'SQL Server') -- WELL-KNOWN PRODUCT
begin
-- ILLEGAL TO SPECIFY PROVIDER/PROPERTIES FOR SQL Server PRODUCT
raiserror(15428,-1,-1,@.srvproduct)
return (1)
end
else if @.srvproduct is null or lower(@.srvproduct) like N'%sql server%'
begin
raiserror(15429,-1,-1,@.srvproduct)
return (1)
end
-- ADD THE LINKED-SERVER
EXEC @.retcode = sys.sp_MSaddserver_internal @.server,
@.srvproduct, @.provider, @.datasrc, @.location, @.provstr, @.catalog,
1, 0, 0 -- @.linkedstyle, @.localentry, @.dup_ok
return @.retcode
-- SUCCESS
return (0) -- sp_addlinkedserver
For Execution:
sys.sp_addlinkedserver 'XLTEST_SP','Excel','Microsoft.Jet.OLEDB.4.0',
'c:\sample.xls','Excel 8.0'
Just leave Location and Catalog
This will create a Linked Server name called XLTEST_SP.
Then do normal procedure!!!!!!!!!!!!!!!!!!!!!!!
--Insert data into existed table
INSERT INTO XLImport
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
Other approaches: http://support.microsoft.com/kb/321686|||Thanks for answer me;
But it return error :
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
i'm trying to connect using sql author.. but it return the same error;
help me, thanks alot|||Open Start->Programs->SQLServer...->Configuration....->Surface Area Configuration, then click by Features link and enable 'Ad Hoc Distributed Queries'|||
Open Start->Programs->SQLServer...->Configuration....->Surface Area Configuration, then click by Features link and enable 'Ad Hoc Distributed Queries'
Still getting same error after configuring
|||Try this
EXEC sp_configure 'Ad Hoc Distributed Queries', '1';
RECONFIGURE;
|||Hi try this........
Just copy the below stored procedure and run by passing the required parameter.
Stored Procedure:
create procedure sys.sp_addlinkedserver
@.server sysname, -- server name
@.srvproduct nvarchar(128) = NULL, -- product name (dflt to ss)
@.provider nvarchar(128) = NULL, -- oledb provider name
@.datasrc nvarchar(4000) = NULL, -- oledb datasource property
@.location nvarchar(4000) = NULL, -- oledb location property
@.provstr nvarchar(4000) = NULL, -- oledb provider-string property
@.catalog sysname = NULL -- oledb catalog property
as
-- VARIABLES
declare @.retcode int
-- VALIDATE OLEDB PARAMETERS
if @.provider is null
begin
-- NO PROVIDER MEANS CANNOT SPECIFY ANY PROPERTIES!
if @.datasrc is not null or @.location is not null or
@.provstr is not null or @.catalog is not null
begin
raiserror(15426,-1,-1)
return (1)
end
-- THIS MUST BE A WELL-KNOWN "SQL Server" TYPE (DEFAULT IS SS)
if @.srvproduct IS NOT null AND lower(@.srvproduct) <> N'sql server'
begin
raiserror(15427,-1,-1,@.srvproduct)
return (1)
end
-- USE ALL-NULLS FOR SQL-SERVER PROVIDER
select @.srvproduct = NULL
end
else if @.srvproduct in (N'SQL Server') -- WELL-KNOWN PRODUCT
begin
-- ILLEGAL TO SPECIFY PROVIDER/PROPERTIES FOR SQL Server PRODUCT
raiserror(15428,-1,-1,@.srvproduct)
return (1)
end
else if @.srvproduct is null or lower(@.srvproduct) like N'%sql server%'
begin
raiserror(15429,-1,-1,@.srvproduct)
return (1)
end
-- ADD THE LINKED-SERVER
EXEC @.retcode = sys.sp_MSaddserver_internal @.server,
@.srvproduct, @.provider, @.datasrc, @.location, @.provstr, @.catalog,
1, 0, 0 -- @.linkedstyle, @.localentry, @.dup_ok
return @.retcode
-- SUCCESS
return (0) -- sp_addlinkedserver
For Execution:
sys.sp_addlinkedserver 'XLTEST_SP','Excel','Microsoft.Jet.OLEDB.4.0',
'c:\sample.xls','Excel 8.0'
Just leave Location and Catalog
This will create a Linked Server name called XLTEST_SP.
Then do normal procedure!!!!!!!!!!!!!!!!!!!!!!!
Dear All,
I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.
Regards,
Ricky
You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.
Thanks
Laurentiu
Hi Laurentiu
Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.
Regards,
Ricky
|||Hello,
In order to see who all have access to 'View Any Database' Permission, you can use the following query
SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;
and to DENY access to public you can use the following syntax
DENY VIEW ANY DATABASE TO PUBLIC
The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.
If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.
If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:
select name from sys.databases where has_dbaccess(name) = 1
Thanks
Laurentiu
I have the opposite problem. When using SSMS on the server itself, I cannot see the system databases tree at all. I tet only "Database Snapshots" and my own database.
However, when accessing this server over a VPN connection, I can see and access the system databases.
I am a Sysadmin and would expect to be able to see everything.
And since the server is running Windows Authentication only, I would expect that my credential are the same, no matter where I login from.
My first guess is that I inadvertantly hid the System Database tree in SSMS, but I see no way to do/undo anything remotely like that.
Per your comment, I remain confused as to how to make them visible. Note that the databases are listed in various dialogs where one would normally select a database (Maint Wizard, for example).
"You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.
Thanks
Laurentiu"
Can you see the database entries when you query sys.databases? If the catalogs show you the correct information, but SSMS does not, then the issue should be pursued on the Tools forum.
Thanks
Laurentiu
|||Yes, I can see master, tempdb, model and msdb via "select * from sys.databases".
Will re-post in the Tools forum, thanks.
Dear All,
I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.
Regards,
Ricky
You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.
Thanks
Laurentiu
Hi Laurentiu
Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.
Regards,
Ricky
|||Hello,
In order to see who all have access to 'View Any Database' Permission, you can use the following query
SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;
and to DENY access to public you can use the following syntax
DENY VIEW ANY DATABASE TO PUBLIC
The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.
If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.
If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:
select name from sys.databases where has_dbaccess(name) = 1
Thanks
Laurentiu
Dear All,
I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.
Regards,
Ricky
You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.
Thanks
Laurentiu
Hi Laurentiu
Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.
Regards,
Ricky
|||Hello,
In order to see who all have access to 'View Any Database' Permission, you can use the following query
SELECT l.nameas grantee_name, p.state_desc, p.permission_name
FROMsys.server_permissionsAS p JOINsys.server_principalsAS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name ='VIEW ANY DATABASE';
and to DENY access to public you can use the following syntax
DENY VIEW ANY DATABASE TO PUBLIC
The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.
If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.
If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:
select name from sys.databases where has_dbaccess(name) = 1
Thanks
Laurentiu
I have the opposite problem. When using SSMS on the server itself, I cannot see the system databases tree at all. I tet only "Database Snapshots" and my own database.
However, when accessing this server over a VPN connection, I can see and access the system databases.
I am a Sysadmin and would expect to be able to see everything.
And since the server is running Windows Authentication only, I would expect that my credential are the same, no matter where I login from.
My first guess is that I inadvertantly hid the System Database tree in SSMS, but I see no way to do/undo anything remotely like that.
Per your comment, I remain confused as to how to make them visible. Note that the databases are listed in various dialogs where one would normally select a database (Maint Wizard, for example).
"You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.
Thanks
Laurentiu"
Can you see the database entries when you query sys.databases? If the catalogs show you the correct information, but SSMS does not, then the issue should be pursued on the Tools forum.
Thanks
Laurentiu
|||Yes, I can see master, tempdb, model and msdb via "select * from sys.databases".
Will re-post in the Tools forum, thanks.
How can I get this function be working?
CREATE FUNCTION MyFunc
(
@.MyDate as datetime,
@.MyTableName varchar(50),
)
RETURNS TABLE
AS
RETURN
SELECT * FROM @.MyTableName Where myDate=@.MyDate
? You can't -- passing a table name dynamically would require dynamic SQL, and dynamic SQL is not supported in UDFs... Why do you want to pass a table name dynamically, anyway? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:d565e7f4-4b5a-4e7f-989d-3e6299fbda2c@.discussions.microsoft.com... How can I get this function be working? CREATE FUNCTION MyFunc ( @.MyDate as datetime, @.MyTableName varchar(50), ) RETURNS TABLE AS RETURN SELECT * FROM @.MyTableName Where myDate=@.MyDate|||
Ok. Thanks for the reply.
I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it?
EXECUTE sp_executesql @.SQLSelectString
-- forward the result set in a table so that other procedures can read it.
|||? There are a few methods. I recommend that you read the following article: http://www.sommarskog.se/share_data.html -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:f9f7fbde-732a-43bf-a322-001a6f668f95@.discussions.microsoft.com... Ok. Thanks for the reply. I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it? EXECUTE sp_executesql @.SQLSelectString -- forward the result set in a table so that other procedures can read it.
