Create SCHEMA - Basic Question - 2005

I am trying to execute the following T-Sql snippet and it gives an
error:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]
Error = Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'SCHEMA'.
I can't for the life of me work out what's worng. Can someone point me
in the right direction please? Thanks.'create schema' must the be the first line in a batch. here is the
workaround.
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
Exec('CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]')
-oj
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1150474587.056097.296010@.c74g2000cwc.googlegroups.com...
>
> I am trying to execute the following T-Sql snippet and it gives an
> error:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
> CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]
> Error = Msg 156, Level 15, State 1, Line 26
> Incorrect syntax near the keyword 'SCHEMA'.
> I can't for the life of me work out what's worng. Can someone point me
> in the right direction please? Thanks.
>|||> 'create schema' must the be the first line in a batch. here is the
> workaround.
Wouldn't it be nice if the error message were similar to the one you get
when you try CREATE PROCEDURE in the middle of a batch? e.g. why isn't this
error returned instead of incorrect syntax:
Msg 111, Level 15, State 1, Line 2
'CREATE/ALTER SCHEMA' must be the first statement in a query batch.
A|||yeah...you know how to send a bug/wish report, right. ;-)
-oj
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23V9N94WkGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Wouldn't it be nice if the error message were similar to the one you get
> when you try CREATE PROCEDURE in the middle of a batch? e.g. why isn't
> this error returned instead of incorrect syntax:
> Msg 111, Level 15, State 1, Line 2
> 'CREATE/ALTER SCHEMA' must be the first statement in a query batch.
>
> A
>sql

Create new sql server login

Hi,
Is there any way to execute sp_addlogin stored procedure to
create a new sql server login from a login account who has minimum
privileges on database. (He is not a member of either symin or security
admin).
My problem is I need to create new sql server login from code
from my application. At runtime the DBA may not willing to give a login with
symin for my application use. So I am looking for an alternative to
execute sp_addlogin procedure with minimum privileged account.
Please help me.
VenkatWhat about use a role for from code?
"venkat" wrote:

> Hi,
>
> Is there any way to execute sp_addlogin stored procedure to
> create a new sql server login from a login account who has minimum
> privileges on database. (He is not a member of either symin or security
> admin).
>
> My problem is I need to create new sql server login from code
> from my application. At runtime the DBA may not willing to give a login wi
th
> symin for my application use. So I am looking for an alternative to
> execute sp_addlogin procedure with minimum privileged account.
>
> Please help me.
>
> --
> Venkat
>
>|||Actually my application is a multi user application. So if a new user
account need to be created then the corresponding database access also be
created. So in this case the logged in user (Administrator defined in
application) has permission to create new user. So my point is if we want to
create a new sql server login the logged in user (in this case
administrator) must be member of symin and securityadmin. But at the
deployment side we are facing problem as there are no permissions on db.
(Means not a member of symin). So I am looking for an alternative to come
out of this problem by using any stored procedures which can be run by a
normal user.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:744BCE5E-5968-4FC6-9BE8-5710F70C50FE@.microsoft.com...
> What about use a role for from code?
> "venkat" wrote:
>|||In SQL Server 2005, you have additional options except symin or securitya
dmin. Are you on 2005?
If no, you need to be either symin or securityadmin. Only way around that
would be to create your
own version of the sp_addlogin stored procedure (see the source code for tha
t procedure and you will
see that it checks against securityadmin).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"venkat" <tammana@.inooga.com> wrote in message news:e6$%23j05DGHA.2036@.TK2MSFTNGP14.phx.gbl
..
> Actually my application is a multi user application. So if a new user acco
unt need to be created
> then the corresponding database access also be created. So in this case th
e logged in user
> (Administrator defined in application) has permission to create new user.
So my point is if we
> want to create a new sql server login the logged in user (in this case adm
inistrator) must be
> member of symin and securityadmin. But at the deployment side we are fa
cing problem as there
> are no permissions on db. (Means not a member of symin). So I am lookin
g for an alternative to
> come out of this problem by using any stored procedures which can be run b
y a normal user.
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:744BCE5E-5968-4FC6-9BE8-5710F70C50FE@.microsoft.com...
>|||venkat (tammana@.inooga.com) writes:
> Is there any way to execute sp_addlogin stored procedure to
> create a new sql server login from a login account who has minimum
> privileges on database. (He is not a member of either symin or security
> admin).
>
> My problem is I need to create new sql server login from code
> from my application. At runtime the DBA may not willing to give a login
> with symin for my application use. So I am looking for an alternative
> to execute sp_addlogin procedure with minimum privileged account.
Assuming that you are on SQL 2000, the one way I can think of is
application role. (SQL 20005 has more options). You would in that
case only use the application role for this particular task, and this
application role must exist in master. I have not tested that this
actually works, though.
I will have to say that I am not sure that this sounds like a very good
idea. Anyone who adds logins, should probably have explicit access to
do so, as this is a quite serious thing to do. This is paricularly
important if the application is to run a server together with many
other applications. I don't think a DBA would accept an application
whihc permitted users to create logins on his server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Create HTTP request in Stored Procedure

Hi,

I want to know if it is possible to create and execute a http request in SQL Server. I want to run the request in the database as alot of the data in the url would be retrieved from the database.

I want to create a stored procedure that runs the request. The stored procedure must be called from a trigger on a table.

The reason I want to go this way is because it would be alot faster that to get the data from the database and then execute the http request in the application. Another reason that I want it done on the database side is because the internet connection of my client is not as fast as the one I am using for development so I don't want the pages to load to many times and get the data between the server and the client computer through as fast as possible.

Any ideas on how I would do this?

Without a more detailed understanding of your particular problem domain, it's difficult to be certain of this but in general I'd recommend against this approach. It seems like a poor use of resources that could be utilized by the core database services. For example, if the triggers are synchronous operations, I would imagine that they would hold a row lock for the duration of the HTTP request...and given that network requests are usually high-latency operations, that wouldn't be a good thing in a multi-user environment.

If the web data that is related to the row data is unchanging or slowly changing, you could consider using a service to collect this data and cache copies of it in the database (a benefit of which is that you would have a DB only query from your client application). If the web data changes frequently/continuously, expecting your DB client to handle this amalgamation seems appropriate. That client code could exist in the actual end-user client application or, if you're comfortable with multi-tier application programming, behind a web service that aggregates all of this information for the end-user client application (NOTE: your SQL Server can be exposed as a web service, which could help consolidate the design of the overall system).

You may also want to consider looking at SQL Server Integration Services...this technology is meant to aggregate data from heterogeneous sources as you've described.

As to writing a stored proc that makes the web request, I don't see anything in the TSQL reference that stands out as a likely candidate, but this meets my expectations for the reasons I mention above. If you were to write an extended stored procedure (see http://msdn2.microsoft.com/en-us/library/ms164716.aspx for more information), you could do whatever you wanted. You could also potentially use CLR Integration (additional information is provided via a link at the previous URL). Note that in both cases, you'd likely have to manage issues around configuration and delegation.

|||

Thank you!

I think I'll go the web service route on this one then.

The data is slow changing. I want to do the following:

I'm writing a system that manages rugby referees. A member would be appointed to official a game as either the referee or a touch judge and then there would be performance reveiwers appointed on some of the game. The web request functionality come in when the member would be notified of his appoint on a specific game and would also be notified if there are any changes on that specific game he appointed on - this would then happen for all the game he/she is appointed on.

You've mentioned the of a web service...from what I understand from my problem and what you have said, a web service would be a good solution? From what I understand what would happen with the web service is that I would monitor the specific table and then act as soon as there are changes to the table?

I am not at all familiar with SQL Server Integration Services or CLR Integration so I wouldn't actually know which would be the better more effective solution. If you need me to give more detail on what should happen I can do so, but I need the best and most effective solution. I have more than enough resource about SQL Server so if I don't know how to do it I would be able to find out, but I couldn't find anything on HTTP requests in SQL so I wanted to know if it was possible seeing I couldn't get any information.

|||What does the web request do? It seems like the intent of the system is to send a notification when some property of the match changes (e.g. start time changes from 4pm to 5pm)...I can envision a web front end for referees, touch judges, and performance reviewers to enter information (e.g. an email address to which schedule updates should be sent), but I'm not yet clear as to why this data would reside outside the database.|||

It should send out an sms to the referee who's match it is so that he/she can be notified of the change.

Email would also be used. The email will be used when a member is appointed, then the details will be emailed to the individual.

|||

Aaaah! I get it now...I was thinking the HTTP request would be pulling information into the database, not pushing data out via an HTTP POST or something similar.

Since many carriers allow for email to be routed to their customers phones via SMS, can you have a general email delivery of information? As an example, Cingular allows you to send an SMS to someone from your email account by sending the message to <10-digit phone number>@.cingularme.com. This would allow you to use sp_send_dbmail to send all of your outbound info...you could then choose different reports/queries to generate different notification types.

If that doesn't work for you, your next best bet is to write either an extended stored procedure or a managed stored procedure.

|||

Ok help me here.

I will be hosting remotely on a webserver over which I do not have control. The service provider won't allow me to send email throuh the server and from what I can gather I do not have access to SQl server integration services on that server.

With regards to the Extended Stored Procedures I understand this from it - help me if I'm wrong. I have to compile a dll which would need to be executed by a stored procedure. The dll would need to be on the same server as the database? When the stored procedure is then executed, it will pass the results to the dll and methods in the dll will then be executed?

So if I understand correctly it will work as follow in my sittuation...

A referee is appointed on a specific game or details on a game he is appointed on changes. When this happens I call a stored procedure which in turn call a dll that I have written which sends the notification to the referee.

You said something about a webservice as well. How would I be going about to send the data to the webservice to perfrom the functions a I require?

I have gone through a couple of stuff of which this one is best from what I have seen. I am still to figure out precily how I would impliment that to solve my problem but I will get there. In the meantime some extra help will do!!

Thanks

|||

That gives me a much better idea of the limitations you're faced with...

Your understanding of the extended stored procedure is essentially correct (NOTE: you could also potentially use a managed stored procedure -- which is the CLR integration you've linked above -- that used web request functionality from the .Net Framework...not sure if the security policy within SQL Server would support that, however). When a table update happened, a trigger would call the extended stored procedure.

Your options, as you've discovered, are somewhat limited because you don't control the server setup. Perhaps it would be drastically easier to implement a polling solution...as a simplistic example, you could implement a service/app that ran on your machine and periodically (every hour?) queried the database to determine if there were updated records for which updates needed to be sent.

Create HTTP request in Stored Procedure

Hi,

I want to know if it is possible to create and execute a http request in SQL Server. I want to run the request in the database as alot of the data in the url would be retrieved from the database.

I want to create a stored procedure that runs the request. The stored procedure must be called from a trigger on a table.

The reason I want to go this way is because it would be alot faster that to get the data from the database and then execute the http request in the application. Another reason that I want it done on the database side is because the internet connection of my client is not as fast as the one I am using for development so I don't want the pages to load to many times and get the data between the server and the client computer through as fast as possible.

Any ideas on how I would do this?

Without a more detailed understanding of your particular problem domain, it's difficult to be certain of this but in general I'd recommend against this approach. It seems like a poor use of resources that could be utilized by the core database services. For example, if the triggers are synchronous operations, I would imagine that they would hold a row lock for the duration of the HTTP request...and given that network requests are usually high-latency operations, that wouldn't be a good thing in a multi-user environment.

If the web data that is related to the row data is unchanging or slowly changing, you could consider using a service to collect this data and cache copies of it in the database (a benefit of which is that you would have a DB only query from your client application). If the web data changes frequently/continuously, expecting your DB client to handle this amalgamation seems appropriate. That client code could exist in the actual end-user client application or, if you're comfortable with multi-tier application programming, behind a web service that aggregates all of this information for the end-user client application (NOTE: your SQL Server can be exposed as a web service, which could help consolidate the design of the overall system).

You may also want to consider looking at SQL Server Integration Services...this technology is meant to aggregate data from heterogeneous sources as you've described.

As to writing a stored proc that makes the web request, I don't see anything in the TSQL reference that stands out as a likely candidate, but this meets my expectations for the reasons I mention above. If you were to write an extended stored procedure (see http://msdn2.microsoft.com/en-us/library/ms164716.aspx for more information), you could do whatever you wanted. You could also potentially use CLR Integration (additional information is provided via a link at the previous URL). Note that in both cases, you'd likely have to manage issues around configuration and delegation.

|||

Thank you!

I think I'll go the web service route on this one then.

The data is slow changing. I want to do the following:

I'm writing a system that manages rugby referees. A member would be appointed to official a game as either the referee or a touch judge and then there would be performance reveiwers appointed on some of the game. The web request functionality come in when the member would be notified of his appoint on a specific game and would also be notified if there are any changes on that specific game he appointed on - this would then happen for all the game he/she is appointed on.

You've mentioned the of a web service...from what I understand from my problem and what you have said, a web service would be a good solution? From what I understand what would happen with the web service is that I would monitor the specific table and then act as soon as there are changes to the table?

I am not at all familiar with SQL Server Integration Services or CLR Integration so I wouldn't actually know which would be the better more effective solution. If you need me to give more detail on what should happen I can do so, but I need the best and most effective solution. I have more than enough resource about SQL Server so if I don't know how to do it I would be able to find out, but I couldn't find anything on HTTP requests in SQL so I wanted to know if it was possible seeing I couldn't get any information.

|||What does the web request do? It seems like the intent of the system is to send a notification when some property of the match changes (e.g. start time changes from 4pm to 5pm)...I can envision a web front end for referees, touch judges, and performance reviewers to enter information (e.g. an email address to which schedule updates should be sent), but I'm not yet clear as to why this data would reside outside the database.|||

It should send out an sms to the referee who's match it is so that he/she can be notified of the change.

Email would also be used. The email will be used when a member is appointed, then the details will be emailed to the individual.

|||

Aaaah! I get it now...I was thinking the HTTP request would be pulling information into the database, not pushing data out via an HTTP POST or something similar.

Since many carriers allow for email to be routed to their customers phones via SMS, can you have a general email delivery of information? As an example, Cingular allows you to send an SMS to someone from your email account by sending the message to <10-digit phone number>@.cingularme.com. This would allow you to use sp_send_dbmail to send all of your outbound info...you could then choose different reports/queries to generate different notification types.

If that doesn't work for you, your next best bet is to write either an extended stored procedure or a managed stored procedure.

|||

Ok help me here.

I will be hosting remotely on a webserver over which I do not have control. The service provider won't allow me to send email throuh the server and from what I can gather I do not have access to SQl server integration services on that server.

With regards to the Extended Stored Procedures I understand this from it - help me if I'm wrong. I have to compile a dll which would need to be executed by a stored procedure. The dll would need to be on the same server as the database? When the stored procedure is then executed, it will pass the results to the dll and methods in the dll will then be executed?

So if I understand correctly it will work as follow in my sittuation...

A referee is appointed on a specific game or details on a game he is appointed on changes. When this happens I call a stored procedure which in turn call a dll that I have written which sends the notification to the referee.

You said something about a webservice as well. How would I be going about to send the data to the webservice to perfrom the functions a I require?

I have gone through a couple of stuff of which this one is best from what I have seen. I am still to figure out precily how I would impliment that to solve my problem but I will get there. In the meantime some extra help will do!!

Thanks

|||

That gives me a much better idea of the limitations you're faced with...

Your understanding of the extended stored procedure is essentially correct (NOTE: you could also potentially use a managed stored procedure -- which is the CLR integration you've linked above -- that used web request functionality from the .Net Framework...not sure if the security policy within SQL Server would support that, however). When a table update happened, a trigger would call the extended stored procedure.

Your options, as you've discovered, are somewhat limited because you don't control the server setup. Perhaps it would be drastically easier to implement a polling solution...as a simplistic example, you could implement a service/app that ran on your machine and periodically (every hour?) queried the database to determine if there were updated records for which updates needed to be sent.

Create HTTP request in Stored Procedure

Hi,

I want to know if it is possible to create and execute a http request in SQL Server. I want to run the request in the database as alot of the data in the url would be retrieved from the database.

I want to create a stored procedure that runs the request. The stored procedure must be called from a trigger on a table.

The reason I want to go this way is because it would be alot faster that to get the data from the database and then execute the http request in the application. Another reason that I want it done on the database side is because the internet connection of my client is not as fast as the one I am using for development so I don't want the pages to load to many times and get the data between the server and the client computer through as fast as possible.

Any ideas on how I would do this?

Without a more detailed understanding of your particular problem domain, it's difficult to be certain of this but in general I'd recommend against this approach. It seems like a poor use of resources that could be utilized by the core database services. For example, if the triggers are synchronous operations, I would imagine that they would hold a row lock for the duration of the HTTP request...and given that network requests are usually high-latency operations, that wouldn't be a good thing in a multi-user environment.

If the web data that is related to the row data is unchanging or slowly changing, you could consider using a service to collect this data and cache copies of it in the database (a benefit of which is that you would have a DB only query from your client application). If the web data changes frequently/continuously, expecting your DB client to handle this amalgamation seems appropriate. That client code could exist in the actual end-user client application or, if you're comfortable with multi-tier application programming, behind a web service that aggregates all of this information for the end-user client application (NOTE: your SQL Server can be exposed as a web service, which could help consolidate the design of the overall system).

You may also want to consider looking at SQL Server Integration Services...this technology is meant to aggregate data from heterogeneous sources as you've described.

As to writing a stored proc that makes the web request, I don't see anything in the TSQL reference that stands out as a likely candidate, but this meets my expectations for the reasons I mention above. If you were to write an extended stored procedure (see http://msdn2.microsoft.com/en-us/library/ms164716.aspx for more information), you could do whatever you wanted. You could also potentially use CLR Integration (additional information is provided via a link at the previous URL). Note that in both cases, you'd likely have to manage issues around configuration and delegation.

|||

Thank you!

I think I'll go the web service route on this one then.

The data is slow changing. I want to do the following:

I'm writing a system that manages rugby referees. A member would be appointed to official a game as either the referee or a touch judge and then there would be performance reveiwers appointed on some of the game. The web request functionality come in when the member would be notified of his appoint on a specific game and would also be notified if there are any changes on that specific game he appointed on - this would then happen for all the game he/she is appointed on.

You've mentioned the of a web service...from what I understand from my problem and what you have said, a web service would be a good solution? From what I understand what would happen with the web service is that I would monitor the specific table and then act as soon as there are changes to the table?

I am not at all familiar with SQL Server Integration Services or CLR Integration so I wouldn't actually know which would be the better more effective solution. If you need me to give more detail on what should happen I can do so, but I need the best and most effective solution. I have more than enough resource about SQL Server so if I don't know how to do it I would be able to find out, but I couldn't find anything on HTTP requests in SQL so I wanted to know if it was possible seeing I couldn't get any information.

|||What does the web request do? It seems like the intent of the system is to send a notification when some property of the match changes (e.g. start time changes from 4pm to 5pm)...I can envision a web front end for referees, touch judges, and performance reviewers to enter information (e.g. an email address to which schedule updates should be sent), but I'm not yet clear as to why this data would reside outside the database.|||

It should send out an sms to the referee who's match it is so that he/she can be notified of the change.

Email would also be used. The email will be used when a member is appointed, then the details will be emailed to the individual.

|||

Aaaah! I get it now...I was thinking the HTTP request would be pulling information into the database, not pushing data out via an HTTP POST or something similar.

Since many carriers allow for email to be routed to their customers phones via SMS, can you have a general email delivery of information? As an example, Cingular allows you to send an SMS to someone from your email account by sending the message to <10-digit phone number>@.cingularme.com. This would allow you to use sp_send_dbmail to send all of your outbound info...you could then choose different reports/queries to generate different notification types.

If that doesn't work for you, your next best bet is to write either an extended stored procedure or a managed stored procedure.

|||

Ok help me here.

I will be hosting remotely on a webserver over which I do not have control. The service provider won't allow me to send email throuh the server and from what I can gather I do not have access to SQl server integration services on that server.

With regards to the Extended Stored Procedures I understand this from it - help me if I'm wrong. I have to compile a dll which would need to be executed by a stored procedure. The dll would need to be on the same server as the database? When the stored procedure is then executed, it will pass the results to the dll and methods in the dll will then be executed?

So if I understand correctly it will work as follow in my sittuation...

A referee is appointed on a specific game or details on a game he is appointed on changes. When this happens I call a stored procedure which in turn call a dll that I have written which sends the notification to the referee.

You said something about a webservice as well. How would I be going about to send the data to the webservice to perfrom the functions a I require?

I have gone through a couple of stuff of which this one is best from what I have seen. I am still to figure out precily how I would impliment that to solve my problem but I will get there. In the meantime some extra help will do!!

Thanks

|||

That gives me a much better idea of the limitations you're faced with...

Your understanding of the extended stored procedure is essentially correct (NOTE: you could also potentially use a managed stored procedure -- which is the CLR integration you've linked above -- that used web request functionality from the .Net Framework...not sure if the security policy within SQL Server would support that, however). When a table update happened, a trigger would call the extended stored procedure.

Your options, as you've discovered, are somewhat limited because you don't control the server setup. Perhaps it would be drastically easier to implement a polling solution...as a simplistic example, you could implement a service/app that ran on your machine and periodically (every hour?) queried the database to determine if there were updated records for which updates needed to be sent.

Create File Option In File Connection Manager

May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.

1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task

2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)

3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.

IMHO, these features would be very important for developer productivity.

Marco Russo
http://sqljunkies.com/weblog/sqlbi

Hi Marco,

All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.

For #2, Copy/Paste might be a short term solution.

regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.

(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)
|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.

Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!

-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?

regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.

Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.

Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.

Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:

- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.

|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)

|||

Ok here's my wish list,

1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)

2. Read only access to the whole package from componentmetadata, not just that related to the component.

3. Parallel For each loops. Performance.

4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop

5. Debug support for script component (not just the task)

6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.

7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.

8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance

9. IIS Log file connection both source and destination would be good. But would settle for source.

10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.

11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers

That'll do for now.

|||Thanks Simon. Excellent input. Thanks!
Anyone else?
K|||

I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.

1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.

2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .

3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.

These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.

What everyone else think about these features.

Thanks
Sutha

|||I've already fed alot of stuff back to Kirk offline but just for the edification of everyone else, here are some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx

-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!

It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.

-Jamie

Create File Option In File Connection Manager

May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.

1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task

2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)

3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.

IMHO, these features would be very important for developer productivity.

Marco Russo
http://sqljunkies.com/weblog/sqlbi

Hi Marco,

All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.

For #2, Copy/Paste might be a short term solution.

regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.

(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.

Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!

-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?

regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.

Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.

Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.

Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:

- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)
|||

Ok here's my wish list,

1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)

2. Read only access to the whole package from componentmetadata, not just that related to the component.

3. Parallel For each loops. Performance.

4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop

5. Debug support for script component (not just the task)

6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.

7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.

8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance

9. IIS Log file connection both source and destination would be good. But would settle for source.

10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.

11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers

That'll do for now.

|||Thanks Simon. Excellent input. Thanks!
Anyone else?
K|||

I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.

1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.

2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .

3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.

These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.

What everyone else think about these features.

Thanks
Sutha

|||I've already fed alot of stuff back to Kirk offline but just for the edification of everyone else, here are some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx

-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!

It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.

-Jamie

Create database fails

HI All,
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MS SQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
Suchi
Are these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:

> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MS SQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>
|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:

Create database fails

HI All,
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
SuchiAre these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:
> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:
> > HI All,
> >
> > Environment:
> >
> > Windows2003,
> >
> > SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> >
> > When I execute below simple create database command without specifying the
> > file name, i got below error. If i explicitly specify the file ( just mdf
> > file), the database is getting created.
> >
> >
> > CREATE DATABASE TESTDB
> >
> > Error:
> >
> > Server: Msg 5105, Level 16, State 2, Line 1
> > Device activation error. The physical file name '\TestDB.mdf' may be
> > incorrect.
> > Server: Msg 1802, Level 16, State 1, Line 1
> > CREATE DATABASE failed. Some file names listed could not be created. Check
> > previous errors.
> >
> >
> > The
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> > and
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> > are having same value ( E:\data )......
> >
> > Can any one tell me how to resolve this?
> >
> > Thanks,
> > Suchi
> >

Create CSV from SQL query

Hello,
I would like to be able to create a csvfile when I execute a query in
SQL. Is there an SQL command or procedure that allows for this?
If a query is executed, how could I dump the results into a CSV file (or
any other format - but preferibly csv). I would rather not used
externalized tools (ie write my own echo or cscript, or DLL to write to a
file within a SQL function or procedure).
Thank you
James.James,
You can do it easily in a DTS package. Just create a
source server and an output text file(you can set the
delimter in the file properties. Create a transform data
task and place your query in the task. Then run the DTS
package to output the results to the file.
I hope that helps.
Derek
>--Original Message--
>Hello,
> I would like to be able to create a csvfile when I
execute a query in
>SQL. Is there an SQL command or procedure that allows
for this?
> If a query is executed, how could I dump the results
into a CSV file (or
>any other format - but preferibly csv). I would rather
not used
>externalized tools (ie write my own echo or cscript, or
DLL to write to a
>file within a SQL function or procedure).
> Thank you
> James.
>
>.
>