I have to download a file from a ftp site using either SQL/T-Sql or ActiveX script. I'm using SQL Server 2000. Please help
Thanks in advancehttp://www.nigelrivett.net/s_ftp_PutFile.html
thanks to nigelrivett|||I wanted to say that :).
Showing posts with label t-sql. Show all posts
Showing posts with label t-sql. Show all posts
Tuesday, March 27, 2012
Monday, March 26, 2012
generation of sql for an alter column etc
Hi.
I have a database I need to supply something (I'm assuming a t-sql script..
maybe something else is better) to update customer tables with.
The operations include mostly changing varchar lengths, though a couple of
columns were renamed.
I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
to generate the scripts.
I can't just send alter table scripts because I'm involving all sorts of
constraints that have to be disabled/or dropped, the alter made, then have
them enabled/ or re-created.
Basically I'm hoping to get the tools to do the rather large amount of work
for me. I'm targetting sql server 2000.
Can someone make a knowledgeable suggestion?
Regards
Jeff KishJeff Kish wrote:
> Hi.
> I have a database I need to supply something (I'm assuming a t-sql script..
> maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple of
> columns were renamed.
Not a good idea IMHO although you can use sp_rename.
> I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
> to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.
Then generate the SQL for the target state and insert drops yourself.
> Basically I'm hoping to get the tools to do the rather large amount of work
> for me. I'm targetting sql server 2000.
> Can someone make a knowledgeable suggestion?
I don't think you will be able to get this out of EM - at least not
directly. It would basically mean to trace your operations and generate
SQL from that. I don't think EM will do that for such a complex set of
operations. You'll have to do some manual work.
Kind regards
robert|||Jeff Kish (jeff.kish@.mro.com) writes:
> I have a database I need to supply something (I'm assuming a t-sql
> script.. maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple
> of columns were renamed.
> I'd like to maybe figure out how to get Enterprise Manager or Query
> Analyzer to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.
> Basically I'm hoping to get the tools to do the rather large amount of
> work for me. I'm targetting sql server 2000.
Composing change scripts for production environments is not a task to
take lightly. Particularly not if you have to apply them while the system
is operating. (If the system closes for business, you may be able to repair
a disaster by restorin a backup.)
It requires good understanding of what can go wrong, and how to prevent
that. For instance, if you need to drop constraints to alter a column,
you should probably wrap that in a transaction, so you don't end up with
losing the constraint.
At the same time, ALTER operations that require changes to the physical
data pages, can take a huge toll on the transaction log, causing it to
grow rapidly. (Changing varchar lengths should be metadata so that should
be safe.)
You can use Enterprise Manager to have it to generate change scripts.
However, there are several flaw in those scripts, and you need to review
them carefully, and also make several changes to them. For instance, the
transaction scope in those scripts are wacko.
What may prove to be a show-stopper is that EM works with SQL 6.5 as its
target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
update as create new table and copy data over. Which sometimes is the right
thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
There are other tools on the market. Red Gate's SQL Compare get a lot
of positive acclaim, but I have not used it myself.
One potential problem is that you don't know the name of the constraints,
because they were added without a name, so all there is a system-generated
name. In this case, you need to retrieve the name, and then run DROP
CONSTRAINT dynamically. I would suggest that you restore the constraints
with a given name.
Speaking of constraints, make sure that you re-add them WITH CHECK. The
script from EM uses WITH NOCHEK, which means that they are not checked. This
is a lot faster, but it also means that the optimizer will neglect them,
which can have expensive consequences.
Finally, before you run in production, test on a copy of production!
--
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|||On Fri, 24 Mar 2006 13:45:19 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Jeff Kish (jeff.kish@.mro.com) writes:
>> I have a database I need to supply something (I'm assuming a t-sql
<snip>
>Composing change scripts for production environments is not a task to
>take lightly. Particularly not if you have to apply them while the system
>is operating. (If the system closes for business, you may be able to repair
>a disaster by restorin a backup.)
>It requires good understanding of what can go wrong, and how to prevent
>that. For instance, if you need to drop constraints to alter a column,
>you should probably wrap that in a transaction, so you don't end up with
>losing the constraint.
ahh.. I had no idea that transactions could wrap/rollback ddl. I don't think
that is the case in Oracle.
>At the same time, ALTER operations that require changes to the physical
>data pages, can take a huge toll on the transaction log, causing it to
>grow rapidly. (Changing varchar lengths should be metadata so that should
>be safe.)
>You can use Enterprise Manager to have it to generate change scripts.
>However, there are several flaw in those scripts, and you need to review
>them carefully, and also make several changes to them. For instance, the
>transaction scope in those scripts are wacko.
Can you tell me how? I'm having some problem seeing how to get the equivalent
alter table etc scripts out of EM. I looked, honest. I'll even look some more.
>What may prove to be a show-stopper is that EM works with SQL 6.5 as its
>target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
>have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
>update as create new table and copy data over. Which sometimes is the right
>thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
target is only sql server 2000 right now.
>There are other tools on the market. Red Gate's SQL Compare get a lot
>of positive acclaim, but I have not used it myself.
>One potential problem is that you don't know the name of the constraints,
>because they were added without a name, so all there is a system-generated
>name. In this case, you need to retrieve the name, and then run DROP
>CONSTRAINT dynamically. I would suggest that you restore the constraints
>with a given name.
mmm not sure I understand.. they are originally added specifically. can I just
disable them or do I need to drop them?
>Speaking of constraints, make sure that you re-add them WITH CHECK. The
>script from EM uses WITH NOCHEK, which means that they are not checked. This
>is a lot faster, but it also means that the optimizer will neglect them,
>which can have expensive consequences.
>Finally, before you run in production, test on a copy of production!
of course! the scars I have should remind me of that. :> )
thanks so much.
Jeff Kish|||Jeff Kish (jeff.kish@.mro.com) writes:
>>You can use Enterprise Manager to have it to generate change scripts.
>>However, there are several flaw in those scripts, and you need to review
>>them carefully, and also make several changes to them. For instance, the
>>transaction scope in those scripts are wacko.
>>
> Can you tell me how? I'm having some problem seeing how to get the
> equivalent alter table etc scripts out of EM. I looked, honest. I'll
> even look some more.
Right-click table and select Modify Table. As I said, it is not likely
it will generate ALTER TABLE commands, those you will have to write
yourself. But at least you will get some code to recreate constraints.
Just be sure to change WITH NOCHECK to WITH CHECK.
>>One potential problem is that you don't know the name of the constraints,
>>because they were added without a name, so all there is a system-generated
>>name. In this case, you need to retrieve the name, and then run DROP
>>CONSTRAINT dynamically. I would suggest that you restore the constraints
>>with a given name.
>>
> mmm not sure I understand.. they are originally added specifically. can
> I just disable them or do I need to drop them?
The error message from ALTER TABLE makes it clear that you have to
drop the constraint. Keep in mind that a FK column must match the
PK column it refers to, so if you change a PK/FK pair, you need to
drop constraints for both columns before changing.
Yes, it follows that from what I say that you should mainly generate
the script manually. This may seem like a crazy idea, but since it's
so important that you understand what the script does, it can be
dangerous to leave that in the hands of a tool.
--
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
Friday, March 23, 2012
Generating unique nvarchar unique key
Hi,
Would like to know whether it is possible to generate a unique key for a
nvarchar column. This key will need to generate by a T-SQL Stored Proc, the
format as follow:
XX-ZZZZ
XX is month, where I think it should be able to generate using DATEPART(mm,
GETDATE())
- is fixed delimiter
ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
So, everytime we run the Stored Proc to generate this key, the statement
will need to break the ZZZZ and increase by one in order to generate the new
id (concatenate with XX-).
But we run into duplication key issue when this Stored Proc was called by
several concurrent clients (like every few thousand records, duplication of
keys will occur once, but occurance totally random).
Would like to know whether there are any better ways to ensure the key
generated (with above format) will always be unique?
ThanksIf you plan to use this as a primary key, it's a very bad idea. The best
primary key is an auto-incremented integer. MSSQL will manage everything for
you with an identity field. If you absolutely need that field you're talking
about (XX-ZZZZ), then create a second field and populate it with a process
that runs every hour or every few minutes. And most importantly, don't make
it part of the primary key.
What you are suggesting is an invitation for deadlocks, and a whole bunch of
other problems. For instance, are you 150% sure that you won't have more
than 10 000 records per month?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Thalia Mei" <thaliamei@.gmail.com> wrote in message
news:F165CCBF-5AC9-4BAF-B48F-477728F8A399@.microsoft.com...
> Hi,
> Would like to know whether it is possible to generate a unique key for a
> nvarchar column. This key will need to generate by a T-SQL Stored Proc,
> the format as follow:
> XX-ZZZZ
> XX is month, where I think it should be able to generate using
> DATEPART(mm, GETDATE())
> - is fixed delimiter
> ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
> So, everytime we run the Stored Proc to generate this key, the statement
> will need to break the ZZZZ and increase by one in order to generate the
> new id (concatenate with XX-).
> But we run into duplication key issue when this Stored Proc was called by
> several concurrent clients (like every few thousand records, duplication
> of keys will occur once, but occurance totally random).
> Would like to know whether there are any better ways to ensure the key
> generated (with above format) will always be unique?
> Thanks
Would like to know whether it is possible to generate a unique key for a
nvarchar column. This key will need to generate by a T-SQL Stored Proc, the
format as follow:
XX-ZZZZ
XX is month, where I think it should be able to generate using DATEPART(mm,
GETDATE())
- is fixed delimiter
ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
So, everytime we run the Stored Proc to generate this key, the statement
will need to break the ZZZZ and increase by one in order to generate the new
id (concatenate with XX-).
But we run into duplication key issue when this Stored Proc was called by
several concurrent clients (like every few thousand records, duplication of
keys will occur once, but occurance totally random).
Would like to know whether there are any better ways to ensure the key
generated (with above format) will always be unique?
ThanksIf you plan to use this as a primary key, it's a very bad idea. The best
primary key is an auto-incremented integer. MSSQL will manage everything for
you with an identity field. If you absolutely need that field you're talking
about (XX-ZZZZ), then create a second field and populate it with a process
that runs every hour or every few minutes. And most importantly, don't make
it part of the primary key.
What you are suggesting is an invitation for deadlocks, and a whole bunch of
other problems. For instance, are you 150% sure that you won't have more
than 10 000 records per month?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Thalia Mei" <thaliamei@.gmail.com> wrote in message
news:F165CCBF-5AC9-4BAF-B48F-477728F8A399@.microsoft.com...
> Hi,
> Would like to know whether it is possible to generate a unique key for a
> nvarchar column. This key will need to generate by a T-SQL Stored Proc,
> the format as follow:
> XX-ZZZZ
> XX is month, where I think it should be able to generate using
> DATEPART(mm, GETDATE())
> - is fixed delimiter
> ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
> So, everytime we run the Stored Proc to generate this key, the statement
> will need to break the ZZZZ and increase by one in order to generate the
> new id (concatenate with XX-).
> But we run into duplication key issue when this Stored Proc was called by
> several concurrent clients (like every few thousand records, duplication
> of keys will occur once, but occurance totally random).
> Would like to know whether there are any better ways to ensure the key
> generated (with above format) will always be unique?
> Thanks
Monday, March 19, 2012
generating database script
Is it possible to generate the t-sql script for a database schema and the
data from .Net(C#) at all?
Cheers
Ollie Richesfound this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
>|||The only accessible code I know of for this is in DMO, which is a COM based
API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/in...rate_script.asp
With 2005, we can use SMO for this, which is managed code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> the
>|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
programmatic[vbcol=seagreen]
the[vbcol=seagreen]
>|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
data from .Net(C#) at all?
Cheers
Ollie Richesfound this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
>|||The only accessible code I know of for this is in DMO, which is a COM based
API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/in...rate_script.asp
With 2005, we can use SMO for this, which is managed code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> the
>|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
programmatic[vbcol=seagreen]
the[vbcol=seagreen]
>|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
generating database script
Is it possible to generate the t-sql script for a database schema and the
data from .Net(C#) at all?
Cheers
Ollie Richesfound this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to generate the t-sql script for a database schema and
the
> > data from .Net(C#) at all?
> >
> > Cheers
> >
> > Ollie Riches
> >
> >
>|||The only accessible code I know of for this is in DMO, which is a COM based API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/info_generate_script.asp
With 2005, we can use SMO for this, which is managed code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
>> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
>> --
>> Miha Markic [MVP C#] - RightHand .NET consulting & development
>> www.rthand.com
>> SLODUG - Slovene Developer Users Group www.codezone-si.info
>> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
>> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
>> > Is it possible to generate the t-sql script for a database schema and
> the
>> > data from .Net(C#) at all?
>> >
>> > Cheers
>> >
>> > Ollie Riches
>> >
>> >
>>
>|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> > thanks but I don't want a code generator, I am looking for a
programmatic
> > approach to scripting out the database schema and the dat contained in
the
> > database, just like you can through sql server 2000 enterprise manager
> >
> > Cheers
> >
> > Ollie Riches
> >
> > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> > news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> >> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> >>
> >> --
> >> Miha Markic [MVP C#] - RightHand .NET consulting & development
> >> www.rthand.com
> >> SLODUG - Slovene Developer Users Group www.codezone-si.info
> >>
> >> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> >> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> >> > Is it possible to generate the t-sql script for a database schema and
> > the
> >> > data from .Net(C#) at all?
> >> >
> >> > Cheers
> >> >
> >> > Ollie Riches
> >> >
> >> >
> >>
> >>
> >
> >
>|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
data from .Net(C#) at all?
Cheers
Ollie Richesfound this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to generate the t-sql script for a database schema and
the
> > data from .Net(C#) at all?
> >
> > Cheers
> >
> > Ollie Riches
> >
> >
>|||The only accessible code I know of for this is in DMO, which is a COM based API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/info_generate_script.asp
With 2005, we can use SMO for this, which is managed code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
>> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
>> --
>> Miha Markic [MVP C#] - RightHand .NET consulting & development
>> www.rthand.com
>> SLODUG - Slovene Developer Users Group www.codezone-si.info
>> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
>> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
>> > Is it possible to generate the t-sql script for a database schema and
> the
>> > data from .Net(C#) at all?
>> >
>> > Cheers
>> >
>> > Ollie Riches
>> >
>> >
>>
>|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> > thanks but I don't want a code generator, I am looking for a
programmatic
> > approach to scripting out the database schema and the dat contained in
the
> > database, just like you can through sql server 2000 enterprise manager
> >
> > Cheers
> >
> > Ollie Riches
> >
> > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> > news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> >> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> >>
> >> --
> >> Miha Markic [MVP C#] - RightHand .NET consulting & development
> >> www.rthand.com
> >> SLODUG - Slovene Developer Users Group www.codezone-si.info
> >>
> >> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> >> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> >> > Is it possible to generate the t-sql script for a database schema and
> > the
> >> > data from .Net(C#) at all?
> >> >
> >> > Cheers
> >> >
> >> > Ollie Riches
> >> >
> >> >
> >>
> >>
> >
> >
>|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
generating database script
Is it possible to generate the t-sql script for a database schema and the
data from .Net(C#) at all?
Cheers
Ollie Riches
found this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
the
>
|||The only accessible code I know of for this is in DMO, which is a COM based API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
With 2005, we can use SMO for this, which is managed code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> the
>
|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny[vbcol=seagreen]
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/inf...ate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
programmatic[vbcol=seagreen]
the
>
|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
data from .Net(C#) at all?
Cheers
Ollie Riches
found this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
the
>
|||The only accessible code I know of for this is in DMO, which is a COM based API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
With 2005, we can use SMO for this, which is managed code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> the
>
|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny[vbcol=seagreen]
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/inf...ate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
programmatic[vbcol=seagreen]
the
>
|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
Generating Create Alter Drop Statements
Im using SQL Server 2005 Standard and am trying to get T-SQL to do something that can be done easily through the UI, Genernate SQL Scripts for Create Alter Drop for objects. In the SQL manager its as easy a right clicking a DB Object and going to tasks.
Im wondering if there is a way of doing that through T-SQL.
Thanks in advance!
There is no easy way to do the same from TSQL. Best is to use SMO or SSIS to script out the objects. You could use the built-in metadata function OBJECT_DEFINITION or sp_helptext system stored procedure or query system catalog views. But you will have to code lot of the logic to determine dependencies, generate drop statements or ALTER TABLE etc. So it is best to use one of the methods above.Wednesday, March 7, 2012
Generate Sequential Numbers - T-SQL
Hi
i have a table with a field called response id. i would like to update this
field with sequential numbers starting from 1000.
can someone please advise me how i go about this.
any help would be appreciated.
thanks heaps in advance
Kind Regards
RidhimaTested on NORTHWIND:
alter table ORDERS
add RESPONSEID int
GO
update ORDERS
set RESPONSEID = RowNumber + 999
from ORDERS inner join
(select ROW_NUMBER() OVER (order by OrderID) as RowNumber, ORDERID from
ORDERS) as TMP on
TMP.ORDERID = ORDERS.ORDERID
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Ridhima" <Ridhima@.discussions.microsoft.com> wrote in message
news:65055160-3C5A-4278-B453-C4DA6344731D@.microsoft.com...
> Hi
> i have a table with a field called response id. i would like to update
> this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima|||On Nov 15, 9:14 pm, Ridhima <Ridh...@.discussions.microsoft.com> wrote:
> Hi
> i have a table with a field called response id. i would like to update this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima
Another way to control this moving forward is to select Design for the
table in SSMS and select the ResponseID field. In the Column
Properties section, select Identity Specification >> (Is Identity) and
set it to Yes. Set Identity Increment to 1 and then set Identity Seed
to 1000 (which is the starting integer value of the ResponseID field).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That was my first thought too. But when I tested it, it created "random"
values in the ResponseID field.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:29bb8aeb-5839-42b5-8e27-57978af3f160@.v4g2000hsf.googlegroups.com...
> On Nov 15, 9:14 pm, Ridhima <Ridh...@.discussions.microsoft.com> wrote:
>> Hi
>> i have a table with a field called response id. i would like to update
>> this
>> field with sequential numbers starting from 1000.
>> can someone please advise me how i go about this.
>> any help would be appreciated.
>> thanks heaps in advance
>> Kind Regards
>> Ridhima
>
> Another way to control this moving forward is to select Design for the
> table in SSMS and select the ResponseID field. In the Column
> Properties section, select Identity Specification >> (Is Identity) and
> set it to Yes. Set Identity Increment to 1 and then set Identity Seed
> to 1000 (which is the starting integer value of the ResponseID field).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||declare @.secuence int
set @.secuence = 999
update table
set @.secuence = response_id = @.secuence + 1
PS.
It does work ! Have faith !
"Ridhima" <Ridhima@.discussions.microsoft.com> escribió en el mensaje
news:65055160-3C5A-4278-B453-C4DA6344731D@.microsoft.com...
> Hi
> i have a table with a field called response id. i would like to update
> this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima
i have a table with a field called response id. i would like to update this
field with sequential numbers starting from 1000.
can someone please advise me how i go about this.
any help would be appreciated.
thanks heaps in advance
Kind Regards
RidhimaTested on NORTHWIND:
alter table ORDERS
add RESPONSEID int
GO
update ORDERS
set RESPONSEID = RowNumber + 999
from ORDERS inner join
(select ROW_NUMBER() OVER (order by OrderID) as RowNumber, ORDERID from
ORDERS) as TMP on
TMP.ORDERID = ORDERS.ORDERID
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Ridhima" <Ridhima@.discussions.microsoft.com> wrote in message
news:65055160-3C5A-4278-B453-C4DA6344731D@.microsoft.com...
> Hi
> i have a table with a field called response id. i would like to update
> this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima|||On Nov 15, 9:14 pm, Ridhima <Ridh...@.discussions.microsoft.com> wrote:
> Hi
> i have a table with a field called response id. i would like to update this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima
Another way to control this moving forward is to select Design for the
table in SSMS and select the ResponseID field. In the Column
Properties section, select Identity Specification >> (Is Identity) and
set it to Yes. Set Identity Increment to 1 and then set Identity Seed
to 1000 (which is the starting integer value of the ResponseID field).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That was my first thought too. But when I tested it, it created "random"
values in the ResponseID field.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:29bb8aeb-5839-42b5-8e27-57978af3f160@.v4g2000hsf.googlegroups.com...
> On Nov 15, 9:14 pm, Ridhima <Ridh...@.discussions.microsoft.com> wrote:
>> Hi
>> i have a table with a field called response id. i would like to update
>> this
>> field with sequential numbers starting from 1000.
>> can someone please advise me how i go about this.
>> any help would be appreciated.
>> thanks heaps in advance
>> Kind Regards
>> Ridhima
>
> Another way to control this moving forward is to select Design for the
> table in SSMS and select the ResponseID field. In the Column
> Properties section, select Identity Specification >> (Is Identity) and
> set it to Yes. Set Identity Increment to 1 and then set Identity Seed
> to 1000 (which is the starting integer value of the ResponseID field).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||declare @.secuence int
set @.secuence = 999
update table
set @.secuence = response_id = @.secuence + 1
PS.
It does work ! Have faith !
"Ridhima" <Ridhima@.discussions.microsoft.com> escribió en el mensaje
news:65055160-3C5A-4278-B453-C4DA6344731D@.microsoft.com...
> Hi
> i have a table with a field called response id. i would like to update
> this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima
Friday, February 24, 2012
Generate Exchange Task from T-SQL
Our organization would like to add tasks to users' Exchange accounts from our SQL Server using a USP. Basically, we are looking for the same functionality as the xp_sendmail syntax provides, but instead of sending an email to a user, we would like to create a task in the user's Exchange Tasks folder based on the information passed from our database via the USP.
Here is an example:
A client must receive paperwork every 6 months based on a date stored in our SQL database.
2 weeks prior to the date the paperwork is due, a USP would detect that John Doe has upcoming paperwork needed.
Bob is John Doe's sales rep. The USP would create a new task in Bob's Exchange Tasks folder indicating that John Doe's paperwork is due on such-and-such a date, setting reminders, etc.
We are currently running SQL 2000 and Exchange 2003 in an Active Directory environment. Any help or pointers would be greatly appreciated!!
Thank you - Jeremy
Best is to use CDO (Colloborative Data Objects) or Outlook Object Data Model outside of the database. You could write code using sp_OA* SPs but it is not going to be a robust implementation. You can use a SQLAgent job with ActiveXScript task to do the task creation. See below links for more details on how to use CDO and Outlook object model.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/exchanchor/htms/msexchsvr_cdo_top.asp
http://msdn2.microsoft.com/en-us/library/ms268893.aspx
There are lots of KB articles that contains code for using CDO / Outlook Object Model to create messages, appointments, items, tasks etc.
Sunday, February 19, 2012
generate all possible subsets from master set?
Hi.
I have a master set of values, lets say
(1,2,3)
for example. I want to use T-sql to generate all possible subsets of this master set. Order of values is unimportant, what I want is unique sets, i.e.
(1)
(2)
(3)
(1,2)
(1,3)
(2,3)
(1,2,3)
thx.Can I ask why?
In a table or a string?
I'm thinking CROSS JOIN...|||Back up a sec', manster.
Do 1, 2, and 3 represent columns? In that case, you would write a cross-join query as suggested by Brett.
But if 1, 2, 3, ... to N represent values in a single column, and you want all the permutations, you will need to write a stored proc that loops through the dataset N times.
blindman|||thanks, all.
Cross join, most likely. I'm working this up from scratch to complete a current project. The higher-ups are handing me a list of fields from our DB, fifteen max, and then asking for all possible subsets of these fifteen, after which we test the subsets to see which offer the most "value."
if someone can offer a cross join example using numbers 1,2,3 as above, that would be great. I'll just key the number back to the actual field names.
thx.|||That sounds bizzare!
Can you post the DDL of the table?
And some sample data?
And are you're higher ups high?|||yes, I know it sounds bizarre, but you'd have to see the data in the table to understand why they're asking for this, and I can't show it.
bottom line is this: I have a list of 15 fields in a single table and I need to generate all possible subsets of these 15 fields.
table1
F1 F2 F3 etc.
output is all possible subsets of these fields:
F1
F2
F3
F1 F2
F2 F3
F1 F3
F1 F2 F3
thx!|||Have a look at Arnolds Reply...you'd need to marry rows to numbers somehow
You'll also need
CREATE TABLE Numbers(n int)
GO
DECLARE @.x int
SET NOCOUNT ON
SELECT @.x = 1
WHILE @.x < 101 BEGIN
INSERT INTO Numbers (n) SELECT @.x
SELECT @.x = @.x + 1
END
SET NOCOUNT OFF
To play with it...vary cool though...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30752|||What kind of processing power are your higher-ups prepared to pay for?
...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.
blindman|||I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.
Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.
thx|||Originally posted by blindman
What kind of processing power are your higher-ups prepared to pay for?
...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.
blindman
LOL...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30646
Some Calculations here...
Say we have a table of 10,000 measly rows...
You want to join col1 to each of the other 14 columns to represent permutations
OK Col1 + Col2 = 100,000
So i't like Col1 , Row 1 + Col2, Every row
Do that 14 more times for all the other columns...1.4 million rows
And we haven't yet begun! That's still just 2 dimensions of the data
Now lets see, Three dimensions
I guess that would be Col1, Row 1, Col2, Row 1, Col3, All the Rows
Then Col1, Row1, Col2, Row2, Col3 All the rows of data
So what that would be 10,000 * 10,000 * 10,000
Blindman help me out here, sound right?
The for all other cols * 14, or 14 million?
4 Dimensions...
140,000,000? Just a guess...
That about right?|||here's a link to a web page that does what I'm trying to do, but this routine truncates at 500 entries returned...
mine is n=15, k=15
and I'm looking for lex order, list of elements.
http://www.theory.cs.uvic.ca/~cos/gen/comb.html|||Originally posted by manster
I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.
Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.
thx
Was busy doing calcs when you posted...
Are you trying to build a "pick list" of what fields you want to select for an end user..
Even still that's a lot of combinations..its 15 factorial..more actually the way you want it..
DECLARE @.x BIGINT
SELECT @.x = 1*2*3*4*5*6*7*8*9*10*11*12--*13*14*15
SELECT @.x
I get an arithmetic overflow at 13...|||I guess you could call it a pick list as long as all subsets are represented.
I'm looking back at the original field list and see that the main required fields number about 10, so 15 was an over-estimation on my part.
thx.|||But that's only bit data...is that what you're looking for?|||yes, just the subsets taken from the "master" list of fields. I'm only interested in the fieldname subsets, not the records in the db at this point.|||You are still talking about over 4 million permutations.
blindman
I have a master set of values, lets say
(1,2,3)
for example. I want to use T-sql to generate all possible subsets of this master set. Order of values is unimportant, what I want is unique sets, i.e.
(1)
(2)
(3)
(1,2)
(1,3)
(2,3)
(1,2,3)
thx.Can I ask why?
In a table or a string?
I'm thinking CROSS JOIN...|||Back up a sec', manster.
Do 1, 2, and 3 represent columns? In that case, you would write a cross-join query as suggested by Brett.
But if 1, 2, 3, ... to N represent values in a single column, and you want all the permutations, you will need to write a stored proc that loops through the dataset N times.
blindman|||thanks, all.
Cross join, most likely. I'm working this up from scratch to complete a current project. The higher-ups are handing me a list of fields from our DB, fifteen max, and then asking for all possible subsets of these fifteen, after which we test the subsets to see which offer the most "value."
if someone can offer a cross join example using numbers 1,2,3 as above, that would be great. I'll just key the number back to the actual field names.
thx.|||That sounds bizzare!
Can you post the DDL of the table?
And some sample data?
And are you're higher ups high?|||yes, I know it sounds bizarre, but you'd have to see the data in the table to understand why they're asking for this, and I can't show it.
bottom line is this: I have a list of 15 fields in a single table and I need to generate all possible subsets of these 15 fields.
table1
F1 F2 F3 etc.
output is all possible subsets of these fields:
F1
F2
F3
F1 F2
F2 F3
F1 F3
F1 F2 F3
thx!|||Have a look at Arnolds Reply...you'd need to marry rows to numbers somehow
You'll also need
CREATE TABLE Numbers(n int)
GO
DECLARE @.x int
SET NOCOUNT ON
SELECT @.x = 1
WHILE @.x < 101 BEGIN
INSERT INTO Numbers (n) SELECT @.x
SELECT @.x = @.x + 1
END
SET NOCOUNT OFF
To play with it...vary cool though...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30752|||What kind of processing power are your higher-ups prepared to pay for?
...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.
blindman|||I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.
Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.
thx|||Originally posted by blindman
What kind of processing power are your higher-ups prepared to pay for?
...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.
blindman
LOL...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30646
Some Calculations here...
Say we have a table of 10,000 measly rows...
You want to join col1 to each of the other 14 columns to represent permutations
OK Col1 + Col2 = 100,000
So i't like Col1 , Row 1 + Col2, Every row
Do that 14 more times for all the other columns...1.4 million rows
And we haven't yet begun! That's still just 2 dimensions of the data
Now lets see, Three dimensions
I guess that would be Col1, Row 1, Col2, Row 1, Col3, All the Rows
Then Col1, Row1, Col2, Row2, Col3 All the rows of data
So what that would be 10,000 * 10,000 * 10,000
Blindman help me out here, sound right?
The for all other cols * 14, or 14 million?
4 Dimensions...
140,000,000? Just a guess...
That about right?|||here's a link to a web page that does what I'm trying to do, but this routine truncates at 500 entries returned...
mine is n=15, k=15
and I'm looking for lex order, list of elements.
http://www.theory.cs.uvic.ca/~cos/gen/comb.html|||Originally posted by manster
I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.
Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.
thx
Was busy doing calcs when you posted...
Are you trying to build a "pick list" of what fields you want to select for an end user..
Even still that's a lot of combinations..its 15 factorial..more actually the way you want it..
DECLARE @.x BIGINT
SELECT @.x = 1*2*3*4*5*6*7*8*9*10*11*12--*13*14*15
SELECT @.x
I get an arithmetic overflow at 13...|||I guess you could call it a pick list as long as all subsets are represented.
I'm looking back at the original field list and see that the main required fields number about 10, so 15 was an over-estimation on my part.
thx.|||But that's only bit data...is that what you're looking for?|||yes, just the subsets taken from the "master" list of fields. I'm only interested in the fieldname subsets, not the records in the db at this point.|||You are still talking about over 4 million permutations.
blindman
Generate a SQL Script to create Indexes
I would like to put together a T-SQL script using Table A that has all of
the table names.
The T-SQL will read the table names and generate the sql statements to
indexes for these tables.
Table A will be in the same database as the tables that would like to create
the indexes. I know you can do this with SQL Server Manager from All Task-
>
Generate SQL Script, but I would like to automate this task.
Please help me with this task.
Thank You,You can find the names of all the tables and in your database in the
information_schema.tables view, and all the columns in the
information_schema.columns view.
Jacco Schalkwijk
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:5DD35EDB-0634-4A66-B4EE-04FDBD467BB4@.microsoft.com...
> I would like to put together a T-SQL script using Table A that has all of
> the table names.
> The T-SQL will read the table names and generate the sql statements to
> indexes for these tables.
> Table A will be in the same database as the tables that would like to
> create
> the indexes. I know you can do this with SQL Server Manager from All
> Task->
> Generate SQL Script, but I would like to automate this task.
>
> Please help me with this task.
> Thank You,
>|||Joe
You create an index on the column not on the Table. You have to know all
columns that you want to create an index. It is really important because if
you create redundant index or useless index it may lead to performance hit
of the query.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:5DD35EDB-0634-4A66-B4EE-04FDBD467BB4@.microsoft.com...
> I would like to put together a T-SQL script using Table A that has all of
> the table names.
> The T-SQL will read the table names and generate the sql statements to
> indexes for these tables.
> Table A will be in the same database as the tables that would like to
create
> the indexes. I know you can do this with SQL Server Manager from All
Task->
> Generate SQL Script, but I would like to automate this task.
>
> Please help me with this task.
> Thank You,
>
the table names.
The T-SQL will read the table names and generate the sql statements to
indexes for these tables.
Table A will be in the same database as the tables that would like to create
the indexes. I know you can do this with SQL Server Manager from All Task-
>
Generate SQL Script, but I would like to automate this task.
Please help me with this task.
Thank You,You can find the names of all the tables and in your database in the
information_schema.tables view, and all the columns in the
information_schema.columns view.
Jacco Schalkwijk
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:5DD35EDB-0634-4A66-B4EE-04FDBD467BB4@.microsoft.com...
> I would like to put together a T-SQL script using Table A that has all of
> the table names.
> The T-SQL will read the table names and generate the sql statements to
> indexes for these tables.
> Table A will be in the same database as the tables that would like to
> create
> the indexes. I know you can do this with SQL Server Manager from All
> Task->
> Generate SQL Script, but I would like to automate this task.
>
> Please help me with this task.
> Thank You,
>|||Joe
You create an index on the column not on the Table. You have to know all
columns that you want to create an index. It is really important because if
you create redundant index or useless index it may lead to performance hit
of the query.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:5DD35EDB-0634-4A66-B4EE-04FDBD467BB4@.microsoft.com...
> I would like to put together a T-SQL script using Table A that has all of
> the table names.
> The T-SQL will read the table names and generate the sql statements to
> indexes for these tables.
> Table A will be in the same database as the tables that would like to
create
> the indexes. I know you can do this with SQL Server Manager from All
Task->
> Generate SQL Script, but I would like to automate this task.
>
> Please help me with this task.
> Thank You,
>
General t-sql help.
The following is my code. What I am trying to do is find all the students a teacher as assessed during a give time. Then find out which assessment was done the most recently. After that I will then be aggregating those results. I have never written any pl/sql or T-SQL... heck I don't even know what to call it!
The first sql command is doing what I want it to. I can only assume the cursor is working correctly.
Any help would be greatly appreciated.
Bryan
ALTER procedure Domain
@.UserID numeric,
@.StartDate datetime,
@.EndDate datetime
AS
-- Variable Decleration
DECLARE @.SessionID varchar(1000)
DECLARE @.EachSessionID numeric
--Cursor to find all Children that the teacher has assessed for the given time
DECLARE ChildID_cursor CURSOR
FOR
SELECT DISTINCT childID
FROM capsession
WHERE userid = @.UserID
AND sessiondate BETWEEN @.StartDate AND @.EndDate
-- looping through all Children to find there most recent assessment.
OPEN ChildID_cursor
DECLARE @.ChildID numeric
FETCH NEXT FROM ChildID_cursor INTO @.ChildID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
DECLARE SessionID_cursor CURSOR
FOR
SELECT TOP 1 CAPSessionID
FROM CapSession
WHERE (ChildID = @.ChildID) AND (SessionDate BETWEEN @.StartDate AND @.EndDate)
ORDER BY SessionDate DESC
END
FETCH NEXT FROM ChildID_cursor INTO @.ChildID
END
CLOSE ChildID_cursor
OPEN SessionID_cursor
FETCH NEXT FROM SessionID_cursor into @.EachSessionID
SET @.SessionID = ''
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.SessionID = @.SessionID + @.EachSessionID+ ','
END
FETCH NEXT FROM ChildID_cursor INTO @.ChildID
END
CLOSE SessionID_cursor
RETURN @.EachSessionID
You can do what you need with a single SELECT statement. I believe this will cover it:
SELECT childID, MAX(SessionID) AS SessionID
FROM capsession
WHERE userid = @.UserID
AND sessiondate BETWEEN @.StartDate AND @.EndDate
GROUP BY childID
|||First of all that SQL worked perfectly Thanks! You really knocked that one out of the park. I have a couple questions for you. My understanding of T-SQL is that it should be used for procedural work. I mean if all I am going to do is return a simple result set why use it? (Just trying to understand the theory so I can be a better programmer) Second is there a good book you can recommend so I can learn the basics.
Thanks again for your rapid and expert advice.
Bryan|||I'm glad that helped you!
I had a typo in my post -- I meant "andnot RESORT to cursors. " By procedural code, I mean code thatdoes processing in a line-by-line fashion, typically using loops toiterate through a set of data. With T-SQL, you can perform tasksin one fell swoop, which is called a set-based approach - this wouldinclude, among others, SELECT, UPDATE, and INSERT commands.
Boy, I've learned most of what I know just by participating onlistservs and forums and migrating from Visual FoxPro to Access to SQLServer.. SQL Server 2000 Books Onlineis Microsoft's documentation for SQL Server and is an awesome freeresource -- I refer to it continually. Anyone doing SQL workshould have it. Several people I know and respect haverecommended this book:Rob Veiera's Professional SQL Server 2000 Programming. Another good resource is Ken Henderson'sThe Guru's Guide to Transact-SQL, although I don't think of this as a beginner's book.
Subscribe to:
Posts (Atom)