Hi.
I need to display a date with his serial week number of the year (from 1-52
weeks).
I wrote a class method that will receive this date and will return the
corresponding week number, and made a dll file out of it.
I added this dll as a reference to my report using Report->Report
Properties->Add Reference.
Now to the tricky part, how do I embed this code in my report?
I found some documented material that I need to uncomment the
<customAssembies> in the rsconfig file. I looked into my config file but no
such <customAssembies> exists.
Does anyone have an example of how to use external dll and to successfully
embed them in the report?
I will highly appreciate any word of wisdom...
Thanks,
GuyGuy,
I didn't figure out your dll question but I believe this will solve
your date problem.
=DatePart(DateInterval.WeekOfYear,Today)
or
=DatePart("ww",Today)
Hope this helps.
Here's a link to the Visual Basic Run-time Library Members
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
Steve|||Steve, thanks a lot.
You have been very helpful.
I also realized how to create GetWeekOfYear( ) in VB.Net and to embed this
function in my report as Code.GetWeekOfYear( ).
I would to rephrase my request for assistance:
1. I created a class in C# and the class method as GetWeekOfYear( ) and
create a dll file of this class.
2. I added this reference to an existing report using the Report->Add
reference command from the Menu toolbar.
3. According to MSDN documentation I need to uncomment the
<CustomAssemblies> in the RSReportingServer.config
However, I didn't find any <CustomAssemblies> in the
RSReportingServer.config, therefore I can't refer to my dll file which
contains the GetWeekOfYear( ) method.
I will appreciate any help in adding and using dll in the Reporting Services
(which is not written in VB.Net which is the natural language in this
environment).
Thanks,
Guy
"sjeffrey@.gmail.com" wrote:
> Guy,
> I didn't figure out your dll question but I believe this will solve
> your date problem.
> =DatePart(DateInterval.WeekOfYear,Today)
> or
> =DatePart("ww",Today)
> Hope this helps.
> Here's a link to the Visual Basic Run-time Library Members
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
> Steve
>|||Why not simply have a reference date table that contains a record for every
date and the week numbers in the record? Then you solve the problem with a
join at the data source. This reference date table has plenty of benefits
as mentioned in other recent posts.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"GuyR" <GuyR@.discussions.microsoft.com> wrote in message
news:4B1CFD27-5D6C-44BA-A07B-D2F186EC4876@.microsoft.com...
> Hi.
> I need to display a date with his serial week number of the year (from
> 1-52
> weeks).
> I wrote a class method that will receive this date and will return the
> corresponding week number, and made a dll file out of it.
> I added this dll as a reference to my report using Report->Report
> Properties->Add Reference.
> Now to the tricky part, how do I embed this code in my report?
> I found some documented material that I need to uncomment the
> <customAssembies> in the rsconfig file. I looked into my config file but
> no
> such <customAssembies> exists.
> Does anyone have an example of how to use external dll and to successfully
> embed them in the report?
> I will highly appreciate any word of wisdom...
> Thanks,
> Guy
>sql
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Thursday, March 29, 2012
Adding a resource (Generic Script) to the SQL Cluster
I would like to add a VB Script to the SQL Cluster so that
in the event of a failover the script will execute on the
new active node. The script goes out and checks for the
existance of some key files once a day.
I am running Windows 2003 SQL 2000 in an Active / Passive
configuration.
I have the VBS running every morning through task Mgr, but
I would like to get it configured to run as a clustered
resource and failover to the active node.
Thanks,
In 2003, there is a "Generic Script" resource type that support VBS and Perl
scripts. Check out the following for more details:
http://msdn.microsoft.com/library/de...ric_script.asp
Regards,
John
"Rodney" <anonymous@.discussions.microsoft.com> wrote in message
news:441301c47333$a3be4aa0$a601280a@.phx.gbl...
> I would like to add a VB Script to the SQL Cluster so that
> in the event of a failover the script will execute on the
> new active node. The script goes out and checks for the
> existance of some key files once a day.
> I am running Windows 2003 SQL 2000 in an Active / Passive
> configuration.
> I have the VBS running every morning through task Mgr, but
> I would like to get it configured to run as a clustered
> resource and failover to the active node.
> Thanks,
in the event of a failover the script will execute on the
new active node. The script goes out and checks for the
existance of some key files once a day.
I am running Windows 2003 SQL 2000 in an Active / Passive
configuration.
I have the VBS running every morning through task Mgr, but
I would like to get it configured to run as a clustered
resource and failover to the active node.
Thanks,
In 2003, there is a "Generic Script" resource type that support VBS and Perl
scripts. Check out the following for more details:
http://msdn.microsoft.com/library/de...ric_script.asp
Regards,
John
"Rodney" <anonymous@.discussions.microsoft.com> wrote in message
news:441301c47333$a3be4aa0$a601280a@.phx.gbl...
> I would like to add a VB Script to the SQL Cluster so that
> in the event of a failover the script will execute on the
> new active node. The script goes out and checks for the
> existance of some key files once a day.
> I am running Windows 2003 SQL 2000 in an Active / Passive
> configuration.
> I have the VBS running every morning through task Mgr, but
> I would like to get it configured to run as a clustered
> resource and failover to the active node.
> Thanks,
Thursday, March 22, 2012
adding a column then using it in a script
Hi,
I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.
LauraReally?
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
INSERT INTO myTable99(Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
SELECT * FROM myTable99
ALTER TABLE myTable99 ADD Col3 int
SELECT * FROM myTable99
UPDATE myTable99 SET Col3 = 1
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO
Why not post your code?|||I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.|||Originally posted by joejcheng
I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.
Did you cut and paste my code?|||I am talking about what Laura might have done. My guess is she cut and paste the script generated by SQL and put in an insert or update statement between BEGIN TRANS and COMMIT.|||Commit! Duh! Sometimes you overlook the easy stuff. Ok Thank you so much.sql
I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.
LauraReally?
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
INSERT INTO myTable99(Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
SELECT * FROM myTable99
ALTER TABLE myTable99 ADD Col3 int
SELECT * FROM myTable99
UPDATE myTable99 SET Col3 = 1
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO
Why not post your code?|||I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.|||Originally posted by joejcheng
I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.
Did you cut and paste my code?|||I am talking about what Laura might have done. My guess is she cut and paste the script generated by SQL and put in an insert or update statement between BEGIN TRANS and COMMIT.|||Commit! Duh! Sometimes you overlook the easy stuff. Ok Thank you so much.sql
Adding a Column in the middle
Can someone give me the syntax for adding a column into
the middle of the table? I know how to do this in E.M,
but I need to script this.Hi,
You cannot add a new column in the middle of the table structure.
The only solution to add the column in the middle:-
1. Down load the data
2. Script the table structure and dependant objects like indexes, Triggers..
3. Recreate the table with new structure (Add the new column in the position
u require)
4. Execute the dependant objects scripts
Note:
If you add the new column using enterprise manager it does the above steps
in back end.
Thanks
Hari
MCDBA
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx
.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.|||Here is how to do it. This is exactly what EM does just to let you know. I
got this via using profiler. Basically I create a table first with three
column, a, b, and c. Then I wanted to add column d prior to c.
CREATE TABLE dbo.Tmp_Table3
(
a char(10) NULL,
b char(10) NULL,
d char(10) NULL,
c char(10) NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.Table3)
EXEC('INSERT INTO dbo.Tmp_Table3 (a, b, c)
SELECT a, b, c FROM dbo.Table3 TABLOCKX')
DROP TABLE dbo.Table3
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=16388
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Table3'',
N''Table3'', ''OBJECT''
', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx
.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.|||Watch what EM does behind the scenes. You can't just add a column to the
middle of the table (and not sure why you care where the column is anyway).
You have to drop the table and re-create it. An alternative is to create a
view with the columns defined in the correct order, but I still don't
understand why column order matters.
http://www.aspfaq.com/
(Reverse address to reply.)
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx
.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
the middle of the table? I know how to do this in E.M,
but I need to script this.Hi,
You cannot add a new column in the middle of the table structure.
The only solution to add the column in the middle:-
1. Down load the data
2. Script the table structure and dependant objects like indexes, Triggers..
3. Recreate the table with new structure (Add the new column in the position
u require)
4. Execute the dependant objects scripts
Note:
If you add the new column using enterprise manager it does the above steps
in back end.
Thanks
Hari
MCDBA
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx
.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.|||Here is how to do it. This is exactly what EM does just to let you know. I
got this via using profiler. Basically I create a table first with three
column, a, b, and c. Then I wanted to add column d prior to c.
CREATE TABLE dbo.Tmp_Table3
(
a char(10) NULL,
b char(10) NULL,
d char(10) NULL,
c char(10) NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.Table3)
EXEC('INSERT INTO dbo.Tmp_Table3 (a, b, c)
SELECT a, b, c FROM dbo.Table3 TABLOCKX')
DROP TABLE dbo.Table3
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=16388
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Table3'',
N''Table3'', ''OBJECT''
', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx
.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.|||Watch what EM does behind the scenes. You can't just add a column to the
middle of the table (and not sure why you care where the column is anyway).
You have to drop the table and re-create it. An alternative is to create a
view with the columns defined in the correct order, but I still don't
understand why column order matters.
http://www.aspfaq.com/
(Reverse address to reply.)
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx
.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
Adding a Column in the middle
Can someone give me the syntax for adding a column into
the middle of the table? I know how to do this in E.M,
but I need to script this.
Hi,
You cannot add a new column in the middle of the table structure.
The only solution to add the column in the middle:-
1. Down load the data
2. Script the table structure and dependant objects like indexes, Triggers..
3. Recreate the table with new structure (Add the new column in the position
u require)
4. Execute the dependant objects scripts
Note:
If you add the new column using enterprise manager it does the above steps
in back end.
Thanks
Hari
MCDBA
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
|||Here is how to do it. This is exactly what EM does just to let you know. I
got this via using profiler. Basically I create a table first with three
column, a, b, and c. Then I wanted to add column d prior to c.
CREATE TABLE dbo.Tmp_Table3
(
a char(10) NULL,
b char(10) NULL,
d char(10) NULL,
c char(10) NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.Table3)
EXEC('INSERT INTO dbo.Tmp_Table3 (a, b, c)
SELECT a, b, c FROM dbo.Table3 TABLOCKX')
DROP TABLE dbo.Table3
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=16388
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Table3'',
N''Table3'', ''OBJECT''
', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
|||Watch what EM does behind the scenes. You can't just add a column to the
middle of the table (and not sure why you care where the column is anyway).
You have to drop the table and re-create it. An alternative is to create a
view with the columns defined in the correct order, but I still don't
understand why column order matters.
http://www.aspfaq.com/
(Reverse address to reply.)
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
the middle of the table? I know how to do this in E.M,
but I need to script this.
Hi,
You cannot add a new column in the middle of the table structure.
The only solution to add the column in the middle:-
1. Down load the data
2. Script the table structure and dependant objects like indexes, Triggers..
3. Recreate the table with new structure (Add the new column in the position
u require)
4. Execute the dependant objects scripts
Note:
If you add the new column using enterprise manager it does the above steps
in back end.
Thanks
Hari
MCDBA
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
|||Here is how to do it. This is exactly what EM does just to let you know. I
got this via using profiler. Basically I create a table first with three
column, a, b, and c. Then I wanted to add column d prior to c.
CREATE TABLE dbo.Tmp_Table3
(
a char(10) NULL,
b char(10) NULL,
d char(10) NULL,
c char(10) NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.Table3)
EXEC('INSERT INTO dbo.Tmp_Table3 (a, b, c)
SELECT a, b, c FROM dbo.Table3 TABLOCKX')
DROP TABLE dbo.Table3
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=16388
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Table3'',
N''Table3'', ''OBJECT''
', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
|||Watch what EM does behind the scenes. You can't just add a column to the
middle of the table (and not sure why you care where the column is anyway).
You have to drop the table and re-create it. An alternative is to create a
view with the columns defined in the correct order, but I still don't
understand why column order matters.
http://www.aspfaq.com/
(Reverse address to reply.)
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
Adding a Column in the middle
Can someone give me the syntax for adding a column into
the middle of the table? I know how to do this in E.M,
but I need to script this.Hi,
You cannot add a new column in the middle of the table structure.
The only solution to add the column in the middle:-
1. Down load the data
2. Script the table structure and dependant objects like indexes, Triggers..
3. Recreate the table with new structure (Add the new column in the position
u require)
4. Execute the dependant objects scripts
Note:
If you add the new column using enterprise manager it does the above steps
in back end.
Thanks
Hari
MCDBA
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.|||Here is how to do it. This is exactly what EM does just to let you know. I
got this via using profiler. Basically I create a table first with three
column, a, b, and c. Then I wanted to add column d prior to c.
CREATE TABLE dbo.Tmp_Table3
(
a char(10) NULL,
b char(10) NULL,
d char(10) NULL,
c char(10) NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.Table3)
EXEC('INSERT INTO dbo.Tmp_Table3 (a, b, c)
SELECT a, b, c FROM dbo.Table3 TABLOCKX')
DROP TABLE dbo.Table3
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=16388
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Table3'',
N''Table3'', ''OBJECT''
', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.|||Watch what EM does behind the scenes. You can't just add a column to the
middle of the table (and not sure why you care where the column is anyway).
You have to drop the table and re-create it. An alternative is to create a
view with the columns defined in the correct order, but I still don't
understand why column order matters.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
the middle of the table? I know how to do this in E.M,
but I need to script this.Hi,
You cannot add a new column in the middle of the table structure.
The only solution to add the column in the middle:-
1. Down load the data
2. Script the table structure and dependant objects like indexes, Triggers..
3. Recreate the table with new structure (Add the new column in the position
u require)
4. Execute the dependant objects scripts
Note:
If you add the new column using enterprise manager it does the above steps
in back end.
Thanks
Hari
MCDBA
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.|||Here is how to do it. This is exactly what EM does just to let you know. I
got this via using profiler. Basically I create a table first with three
column, a, b, and c. Then I wanted to add column d prior to c.
CREATE TABLE dbo.Tmp_Table3
(
a char(10) NULL,
b char(10) NULL,
d char(10) NULL,
c char(10) NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.Table3)
EXEC('INSERT INTO dbo.Tmp_Table3 (a, b, c)
SELECT a, b, c FROM dbo.Table3 TABLOCKX')
DROP TABLE dbo.Table3
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=16388
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Table3'',
N''Table3'', ''OBJECT''
', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.|||Watch what EM does behind the scenes. You can't just add a column to the
middle of the table (and not sure why you care where the column is anyway).
You have to drop the table and re-create it. An alternative is to create a
view with the columns defined in the correct order, but I still don't
understand why column order matters.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
Adding a .sql to MSDE Server
Hi All,
I have a .SQL script file that contains database definitions. How can i run
the .sql script file to add this database to my MSDE SQL server?
Thanks for your help
mustafa
"Mustafa Rabie" <mrabie007@.hotmail.com> wrote in message
news:%23vKwbfJBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a .SQL script file that contains database definitions. How can i
run
> the .sql script file to add this database to my MSDE SQL server?
>
Start a command prompt and use OSQL... enter: OSQL -? to get the available
options.
Steve
|||Hi, this is from "Microsoft SQL Server 2000 Sample Database Scripts"
[http://www.microsoft.com/downloads/d...displaylang=en]
To run the scripts using osql:
1.. Open a Command Prompt window.
2.. From the command prompt, use the cd command to navigate to the folder
containing the SQL Server 2000 Sample Database Scripts:
cd c:\program files\SQL Server 2000 Sample Databases
3.. Run osql specifying either Instpubs.sql or Instnwnd.sql as the input
file. These are some examples:
a.. To install the Northwind database on a default instance on the same
computer:
osql -E -i instnwnd.sql
b.. To install the pubs database on a default instance on a different
computer:
osql -E -S ComputerName -i instpubs.sql
c.. To install the Northwind database on a named instance:
osql -E -S ComputerName/InstanceName -i instnwnd.sql
J.Alberto
[ccs-ve]
Nota: no enviar mensajes a mi direccion e-mail,
solo spam. Avisar para asignarle un filtro y no sea
eliminado.
I have a .SQL script file that contains database definitions. How can i run
the .sql script file to add this database to my MSDE SQL server?
Thanks for your help
mustafa
"Mustafa Rabie" <mrabie007@.hotmail.com> wrote in message
news:%23vKwbfJBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a .SQL script file that contains database definitions. How can i
run
> the .sql script file to add this database to my MSDE SQL server?
>
Start a command prompt and use OSQL... enter: OSQL -? to get the available
options.
Steve
|||Hi, this is from "Microsoft SQL Server 2000 Sample Database Scripts"
[http://www.microsoft.com/downloads/d...displaylang=en]
To run the scripts using osql:
1.. Open a Command Prompt window.
2.. From the command prompt, use the cd command to navigate to the folder
containing the SQL Server 2000 Sample Database Scripts:
cd c:\program files\SQL Server 2000 Sample Databases
3.. Run osql specifying either Instpubs.sql or Instnwnd.sql as the input
file. These are some examples:
a.. To install the Northwind database on a default instance on the same
computer:
osql -E -i instnwnd.sql
b.. To install the pubs database on a default instance on a different
computer:
osql -E -S ComputerName -i instpubs.sql
c.. To install the Northwind database on a named instance:
osql -E -S ComputerName/InstanceName -i instnwnd.sql
J.Alberto
[ccs-ve]
Nota: no enviar mensajes a mi direccion e-mail,
solo spam. Avisar para asignarle un filtro y no sea
eliminado.
Tuesday, March 20, 2012
Add-In Support For Management Studio
Is there add-in support for SQL Server 2005 Management Studio?
I want to build a script management add-in that is callable from the popup menu of the query window. I haven't been able to find anything on building add-ins for Management Studio on MSDN or the web. Did I miss something? Or is SDK/add-in support not available yet for Mangement Studio?
Thanks.
- Grant
Grant,
We do not current support third-party add-ins in Management Studio, though we don't explicitly lock this down. There are a few people out there who have figured out how get their add-in to surface. If you decide to go this route there is no guarantee that we won't break you in a service pack or future release.
Cheers,
Dan
Monday, March 19, 2012
add total row to file via script component
Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like
ABC, 3
DEF, 5
FGH, 6
I have a variable in script component holding the running total I now want to add that to destination as a row e.g.
ABC, 3
DEF, 5
FGH, 6
Total, 14
This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.
Thanks
You'll need to use an asynchronous output to accomplish this. You could add an async output in addition to the synchronous output you get by default, and just write the running total to it.
See this topic in Books Online:
"Creating an Asynchronous Transformation with the Script Component"
|||You could also just use a multicast and an Aggregate transform to accomplish the same thing.|||Great thanks, Asynchronous Transformation workedadd total row to file via script component
Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like
ABC, 3
DEF, 5
FGH, 6
I have a variable in script component holding the running total I now want to add that to destination as a row e.g.
ABC, 3
DEF, 5
FGH, 6
Total, 14
This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.
Thanks
You'll need to use an asynchronous output to accomplish this. You could add an async output in addition to the synchronous output you get by default, and just write the running total to it.
See this topic in Books Online:
"Creating an Asynchronous Transformation with the Script Component"
|||You could also just use a multicast and an Aggregate transform to accomplish the same thing.|||Great thanks, Asynchronous Transformation workedSunday, March 11, 2012
Add table and column descriptions/comments using script
Is it possible to add descriptions/comments to tables and columns using a
system stored procedure?Yes. See sp_addextendedproperty in the Books Online
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?|||Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> > Is it possible to add descriptions/comments to tables and columns using a
> > system stored procedure?
>
>
system stored procedure?Yes. See sp_addextendedproperty in the Books Online
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?|||Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> > Is it possible to add descriptions/comments to tables and columns using a
> > system stored procedure?
>
>
Add table and column descriptions/comments using script
Yes. See sp_addextendedproperty in the Books Online
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
>
>|||Is it possible to add descriptions/comments to tables and columns using a
system stored procedure?|||Yes. See sp_addextendedproperty in the Books Online
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?|||Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
>
>
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
>
>|||Is it possible to add descriptions/comments to tables and columns using a
system stored procedure?|||Yes. See sp_addextendedproperty in the Books Online
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?|||Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
>
>
Add scripttask script programmatically
I'm building packages programmatically. I need to add a ScriptTask to my package and include the script code. The script task itself is easy to add. But I can't figure out how to add the script code to the task.
I found one post in this forum saying the trick is to use the PutSourceCode method of the StriptTaskCodeProvider class, but I can't figure out how to do that.
Can anyone provide a code sample of how this is done?
Thanks.
After looking into this more deeply I found that adding a Script Task is a large job and I opted for a simpler approach to my particular issue. A lot of code needs to be added into the package. You have to create a VSA project, then add the script code it contains. To see an example of what's required, open a .dtsx file containing a Script Task and look for the tag "ScriptProject Name", and examine the ProjectItems it contains.
In case it is of use to anyone in the future, here's what I came up with. This doesn't create both the project and the script code because I didn't go that far, but it shows you the references and objects you'll need to do that.
Add references to Microsoft.SqlServer.ScriptTask and Microsoft.SqlServer.VSAHosting.
Dim exe As Executable = _Package.Executables.Add("STOCK:ScriptTask")
Dim thTask As TaskHost = CType(exe, TaskHost)
thTask.Name = "MyScriptTask"
Dim st As ScriptTask = TryCast(thTask.InnerObject, ScriptTask)
dim Moniker as String = "dts://Scripts/" & st.VsaProjectName & "/" & st.VsaProjectName & ".vsaproj"
st.ReadWriteVariables = "Var1, Var2"
Dim sb As New StringBuilder
sb.AppendLine("'Microsoft SQL Server Integration Services Script Task")
'build your code here
'this inserts the code into the script task
st.CodeProvider.PutSourceCode(Moniker, sb.ToString)
Thursday, March 8, 2012
Add Reference to Excel in Scripting tool in SSIS 2005
How can I add a reference to the Microsoft excel 2003 in the script tool in SSIS 2005? I need to automate Excel for formatting.
Thank you.
Dan
Adding refereneces to external assembleis is documented in Books Online -
Referencing Other Assemblies in Scripting Solutions
http://technet.microsoft.com/en-us/library/ms136007.aspx
For Office applications like Excel that are COM, you will need to download the PIAs if you have not already.You will need to reference the PIA.
Download - http://www.microsoft.com/downloads/details.aspx?FamilyID=3C9A983A-AC14-4125-8BA0-D36D67E0F4AD&displaylang=en
General page of on PIAs if you are not familar already - http://msdn2.microsoft.com/en-us/library/aax7sdch.aspx
Add one column in a table
I got ten database , and want to add one column in serveral tables.
However, I need to add that column in the fifth column .
Any easy script I can use 'thanks a lot"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:elCV8ntPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
NO. There isn't any easy script.
The table has to be dropped and recreated.
WHY to you need to insert the column at a specific position?|||Hi Agnes,
You can't insert column between existing columns. The physical order of
columns in rows in data files however doesn't matter.
The syntax for adding coulmnt to table is described in ALTER TABLE statement
in BOL
here's an example from BOL
ALTER TABLE doc_exf
ADD AddDate smalldatetime NULL
Since you are writing about order of columns I assume that you use * in your
statements. Don't do it. Select fields that you need and in order you need.
HTH
Peter|||That is not necessary because you can rearrange the column when you use the
select syntax.
i.e.
Select col1, col2, col3, col4, col12, col5 .....
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:elCV8ntPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
>|||> However, I need to add that column in the fifth column .
WHY? Are you doing SELECT * in your code? Create a view (and stop using
SELECT * anyway). Are you not bothering to list column names in your insert
statements? Please name the columns.
> Any easy script I can use '
No, T-SQL does not support this. What Enterprise Mangler does is dump the
data into a #temp table, re-create the table, and move the data back. So
not exactly something you'd want to do on a large data set, even if you
could justify it.
A|||Use this procedure :
IF EXISTS(SELECT [NAME] FROM dbo.sysobjects WHERE NAME='SP_ADDCOLUMN')
DROP PROCEDURE SP_ADDCOLUMN
go
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
GO
CREATE PROC sp_addcolumn
@.TableName sysname, --FieldName
@.FieldDefine nvarchar(1000), --COLUMNNAME,DATATYPE,[NULL OR NOT NULL]
'column_b VARCHAR(20) NULL'
@.Position int=-1 --POSITION,For example the value 1,so
need add to the first column,
AS
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@.TableNa
me),'IsUserTable'),0)=0
BEGIN
RAISERROR(N'Invalid "%s"',12,16,@.TableName)
return
END
--Add Field
DECLARE @.s nvarchar(4000)
SET @.s=N'ALTER TABLE '+QUOTENAME(@.TableName)+N' ADD '+@.FieldDefine
BEGIN TRAN
EXEC sp_executesql @.s
IF @.@.ERROR=0
BEGIN
DECLARE @.maxcolid int
SELECT @.Position=ISNULL(@.Position,0),@.maxcolid=
MAX(colid)
FROM syscolumns
WHERE id=OBJECT_ID(@.TableName)
IF @.Position>0 AND @.Position<@.maxcolid
UPDATE syscolumns SET
colid=CASE colid WHEN @.maxcolid THEN @.Position ELSE colid+1 END,
colorder=CASE colid WHEN @.maxcolid THEN @.Position ELSE colid+1 END
WHERE id=OBJECT_ID(@.TableName)
AND colid>=@.Position
COMMIT TRAN
END
ELSE
ROLLBACK TRAN
GO
--Close the System table write ability
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
--
After that run command:
EXECUTE SP_ADDCOLUMN 'B','column_b NVARCHAR(20) NULL',5
Here B is your table name, 5 is your new column position,column_b is your
column name
I have test it, it is ok.
Grant
"Agnes" wrote:
> I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
>
>|||Grant wrote:
> Use this procedure :
[snip]
> --
> After that run command:
> EXECUTE SP_ADDCOLUMN 'B','column_b NVARCHAR(20) NULL',5
> Here B is your table name, 5 is your new column position,column_b is your
> column name
> I have test it, it is ok.
>
DO NOT do it! This does not work for several reasons. In any case,
updating system tables is the surest way to corrupt your data
permanently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
However, I need to add that column in the fifth column .
Any easy script I can use 'thanks a lot"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:elCV8ntPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
NO. There isn't any easy script.
The table has to be dropped and recreated.
WHY to you need to insert the column at a specific position?|||Hi Agnes,
You can't insert column between existing columns. The physical order of
columns in rows in data files however doesn't matter.
The syntax for adding coulmnt to table is described in ALTER TABLE statement
in BOL
here's an example from BOL
ALTER TABLE doc_exf
ADD AddDate smalldatetime NULL
Since you are writing about order of columns I assume that you use * in your
statements. Don't do it. Select fields that you need and in order you need.
HTH
Peter|||That is not necessary because you can rearrange the column when you use the
select syntax.
i.e.
Select col1, col2, col3, col4, col12, col5 .....
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:elCV8ntPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
>|||> However, I need to add that column in the fifth column .
WHY? Are you doing SELECT * in your code? Create a view (and stop using
SELECT * anyway). Are you not bothering to list column names in your insert
statements? Please name the columns.
> Any easy script I can use '
No, T-SQL does not support this. What Enterprise Mangler does is dump the
data into a #temp table, re-create the table, and move the data back. So
not exactly something you'd want to do on a large data set, even if you
could justify it.
A|||Use this procedure :
IF EXISTS(SELECT [NAME] FROM dbo.sysobjects WHERE NAME='SP_ADDCOLUMN')
DROP PROCEDURE SP_ADDCOLUMN
go
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
GO
CREATE PROC sp_addcolumn
@.TableName sysname, --FieldName
@.FieldDefine nvarchar(1000), --COLUMNNAME,DATATYPE,[NULL OR NOT NULL]
'column_b VARCHAR(20) NULL'
@.Position int=-1 --POSITION,For example the value 1,so
need add to the first column,
AS
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@.TableNa
me),'IsUserTable'),0)=0
BEGIN
RAISERROR(N'Invalid "%s"',12,16,@.TableName)
return
END
--Add Field
DECLARE @.s nvarchar(4000)
SET @.s=N'ALTER TABLE '+QUOTENAME(@.TableName)+N' ADD '+@.FieldDefine
BEGIN TRAN
EXEC sp_executesql @.s
IF @.@.ERROR=0
BEGIN
DECLARE @.maxcolid int
SELECT @.Position=ISNULL(@.Position,0),@.maxcolid=
MAX(colid)
FROM syscolumns
WHERE id=OBJECT_ID(@.TableName)
IF @.Position>0 AND @.Position<@.maxcolid
UPDATE syscolumns SET
colid=CASE colid WHEN @.maxcolid THEN @.Position ELSE colid+1 END,
colorder=CASE colid WHEN @.maxcolid THEN @.Position ELSE colid+1 END
WHERE id=OBJECT_ID(@.TableName)
AND colid>=@.Position
COMMIT TRAN
END
ELSE
ROLLBACK TRAN
GO
--Close the System table write ability
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
--
After that run command:
EXECUTE SP_ADDCOLUMN 'B','column_b NVARCHAR(20) NULL',5
Here B is your table name, 5 is your new column position,column_b is your
column name
I have test it, it is ok.
Grant
"Agnes" wrote:
> I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
>
>|||Grant wrote:
> Use this procedure :
[snip]
> --
> After that run command:
> EXECUTE SP_ADDCOLUMN 'B','column_b NVARCHAR(20) NULL',5
> Here B is your table name, 5 is your new column position,column_b is your
> column name
> I have test it, it is ok.
>
DO NOT do it! This does not work for several reasons. In any case,
updating system tables is the surest way to corrupt your data
permanently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Tuesday, March 6, 2012
add new record - weird results
I have written a generic script in asp to add records to a table. The
script works fine with one table but in the other tables it updates
the first record in the table with the values for the new record and
adds a new record with all null values?!? Here is the script:
script works fine with one table but in the other tables it updates
the first record in the table with the values for the new record and
adds a new record with all null values?!? Here is the script:
adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
'INSERT******************************************* ******************
'Open connection to sub-table
if action = "insert" then
cnnFormToDB.Open "SELECT top 1 * FROM " &subtable,
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta",
adOpenKeyset,adLockOptimistic
cnnFormToDB.AddNew
else
cnnFormToDB.Open "SELECT top 1 * FROM " & subtable & " WHERE ID = " &
ID, "DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
End If
if not cnnFormToDB.eof then
cnnFormToDB.MoveFirst
end if
'DELETE******************************************* ********************
if action = "delete" then
cnnFormToDB.Delete
cnnFormToDB.Close
else
'Build 2nd SQL String
For i=0 To Ubound(aFields)
cnnFormToDB(aFields(i)) = aValues(i)
Next
'Insert record into sub-table
cnnFormToDB.Update
The even weirder thing is I know that values in aFields and aValues
are OK because this test script I wrote for one of the tables works
just fine:
adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
cnnFormToDB.Open "SELECT top 1 * FROM FlightsDirect",
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
cnnFormToDB.AddNew
cnnFormToDB("fkCity") = 198
cnnFormToDB("fkDepartureAirport") = 159
cnnFormToDB("ValidFrom") = "17/09/2003"
cnnFormToDB("ValidTo") = "15/10/2003"
cnnFormToDB("fkType") = 1
cnnFormToDB("ReturnFlight") = 1
cnnFormToDB("fkReturnAirport") = 184
cnnFormToDB("Price") = yyyyyy
cnnFormToDB("fkATOL") = 5346
cnnFormToDB.Update
Any suggestions appreciated
Thanks
AlisonButtercup (alison_clark20@.hotmail.com) writes:
> I have written a generic script in asp to add records to a table. The
> script works fine with one table but in the other tables it updates
> the first record in the table with the values for the new record and
> adds a new record with all null values?!? Here is the script:
I cannot really say what is going on. The problem with ADO is that
while it tries to hides to the SQL from you, it does actually makes
you more confused, because you don't know what is going on under the
covers.
You can use the Profiler to see what ADO submits to SQL Server.
However, rather than relying on ADO doing things right by chance, I
would encourage you to use stored procedures instead. Then you don't
use these .AddNew or .Update methods.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Add many users to Reporting Services
I am looking for a script, query or utility that can add many user's at a
time to a role assignemnt in reporting services.
In the gui - I have tried space delimited, comma delimited... and always get
an error.
trying to add
domain\user1 domain\user2...
ThanksThe easiest way to manage this is by using Windows groups.
You can add windows groups to roles in reporting services.
-Sue
On Fri, 25 May 2007 12:35:00 -0700, Brett S.
<BrettS@.discussions.microsoft.com> wrote:
>I am looking for a script, query or utility that can add many user's at a
>time to a role assignemnt in reporting services.
>In the gui - I have tried space delimited, comma delimited... and always ge
t
>an error.
>trying to add
>domain\user1 domain\user2...
>Thanks
time to a role assignemnt in reporting services.
In the gui - I have tried space delimited, comma delimited... and always get
an error.
trying to add
domain\user1 domain\user2...
ThanksThe easiest way to manage this is by using Windows groups.
You can add windows groups to roles in reporting services.
-Sue
On Fri, 25 May 2007 12:35:00 -0700, Brett S.
<BrettS@.discussions.microsoft.com> wrote:
>I am looking for a script, query or utility that can add many user's at a
>time to a role assignemnt in reporting services.
>In the gui - I have tried space delimited, comma delimited... and always ge
t
>an error.
>trying to add
>domain\user1 domain\user2...
>Thanks
Saturday, February 25, 2012
Add Extra Error Columns - From The Component?
I've read a MSDN article on enhancing error outputs with a script component. This solution added the script component astride the error output, and it was able to add a column with the error description text.
I would like to do something a little different. I'd like to add information from my custom component. When the component causes an error, it generally "knows why" - it has available to it the information that made it decide there was an error. I'd like to include that information in error outputs, but I haven't seen a way to do that.
Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.
This might be possible. Try the following (I'm assuming your custom component is synchronous):
1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.
2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).
3. If you got the handle, use it in ProcessInput to set the error description before calling RedirectErrorRow.
|||
Ted Lee - MSFT wrote:
This might be possible. Try the following (I'm assuming your custom component is synchronous):
1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.
That part worked. The extra column was added to the error output. Unfortunately:
2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).
The above caused an error during PreExecute:
The output "TranslateProvidersErrorOutput" (347) is a synchronous output and the buffer type cannot be retrieved for a synchronous output.
The code I tried is:
Code Snippet
public override void ProvideComponentProperties()
{
ComponentMetaData.UsesDispositions = true;
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "TranslateProvidersInput";
input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "TranslateProvidersOutput";
output.SynchronousInputID = input.ID;
output.ExclusionGroup = 1;
// The new code:
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.Name = "TranslateProvidersErrorOutput";
errorOutput.SynchronousInputID = input.ID;
errorOutput.ExclusionGroup = 1;
errorOutput.IsErrorOut = true;
IDTSOutputColumn90 extraColumn = errorOutput.OutputColumnCollection.New();
extraColumn.Name = "ErrorDescription";
extraColumn.SetDataTypeProperties(DataType.DT_WSTR, 255, 0, 0, 0);
_extraColumnLineageID = extraColumn.LineageID;
// The original code:
//AddErrorOutput(
//"TranslateProvidersErrorOutput", input.ID, output.ExclusionGroup);
IDTSRuntimeConnection90 connection =
ComponentMetaData.RuntimeConnectionCollection.New();
connection.Name = "ADONET";
}
public override void PreExecute()
{
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];
_extraColumnIndex =
BufferManager.FindColumnByLineageID(
errorOutput.Buffer, _extraColumnLineageID);
}
The hightlighted code is what failed.
I don't yet understand how buffers work with multiple outputs, so I can't figure out any other way to get the column index. I'm stuck at that point, for now.
If anyone has more ideas, I'll be glad to try them. One workaround I thought of was to add the extra columns to the default output and see if they get replicated to the error output when the default error output is created. This would have the unfortunate side-effect of proliferating error-only columns throughout the data flow, but should work well enough if the extra columns are truly required.
It will be necessary for components in the flow to not add one of these columns if it is already present.
|||John,
I've got this working in my destination component: adding error messages, row #, time, etc.
The difference I think is in this line:
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];
_extraColumnIndex =
BufferManager.FindColumnByLineageID(
errorOutput.Buffer, _extraColumnLineageID);
The equivalent in my code would be:
IDTSInput90 input = ComponentMetaData.InputCollection[0];
_extraColumnIndex =
BufferManager.FindColumnByLineageID(
input.Buffer, _extraColumnLineageID);
It is the input buffer that actually has the slots for the new error columns.
Martin
Add Extra Error Columns - From The Component?
I've read a MSDN article on enhancing error outputs with a script component. This solution added the script component astride the error output, and it was able to add a column with the error description text.
I would like to do something a little different. I'd like to add information from my custom component. When the component causes an error, it generally "knows why" - it has available to it the information that made it decide there was an error. I'd like to include that information in error outputs, but I haven't seen a way to do that.
Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.
This might be possible. Try the following (I'm assuming your custom component is synchronous):
1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.
2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).
3. If you got the handle, use it in ProcessInput to set the error description before calling RedirectErrorRow.
|||
This might be possible. Try the following (I'm assuming your custom component is synchronous):
1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.
That part worked. The extra column was added to the error output. Unfortunately:
2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).
The above caused an error during PreExecute:
The output "TranslateProvidersErrorOutput" (347) is a synchronous output and the buffer type cannot be retrieved for a synchronous output.
The code I tried is:
Code Snippet
public override void ProvideComponentProperties()
{
ComponentMetaData.UsesDispositions = true;
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "TranslateProvidersInput";
input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "TranslateProvidersOutput";
output.SynchronousInputID = input.ID;
output.ExclusionGroup = 1;
// The new code:
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.Name = "TranslateProvidersErrorOutput";
errorOutput.SynchronousInputID = input.ID;
errorOutput.ExclusionGroup = 1;
errorOutput.IsErrorOut = true;
IDTSOutputColumn90 extraColumn = errorOutput.OutputColumnCollection.New();
extraColumn.Name = "ErrorDescription";
extraColumn.SetDataTypeProperties(DataType.DT_WSTR, 255, 0, 0, 0);
_extraColumnLineageID = extraColumn.LineageID;
// The original code:
//AddErrorOutput(
//"TranslateProvidersErrorOutput", input.ID, output.ExclusionGroup);
IDTSRuntimeConnection90 connection =
ComponentMetaData.RuntimeConnectionCollection.New();
connection.Name = "ADONET";
}
public override void PreExecute()
{
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];
_extraColumnIndex =
BufferManager.FindColumnByLineageID(
errorOutput.Buffer, _extraColumnLineageID);
}
The hightlighted code is what failed.
I don't yet understand how buffers work with multiple outputs, so I can't figure out any other way to get the column index. I'm stuck at that point, for now.
If anyone has more ideas, I'll be glad to try them. One workaround I thought of was to add the extra columns to the default output and see if they get replicated to the error output when the default error output is created. This would have the unfortunate side-effect of proliferating error-only columns throughout the data flow, but should work well enough if the extra columns are truly required.
It will be necessary for components in the flow to not add one of these columns if it is already present.
|||John,
I've got this working in my destination component: adding error messages, row #, time, etc.
The difference I think is in this line:
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];
_extraColumnIndex =
BufferManager.FindColumnByLineageID(
errorOutput.Buffer, _extraColumnLineageID);
The equivalent in my code would be:
IDTSInput90 input = ComponentMetaData.InputCollection[0];
_extraColumnIndex =
BufferManager.FindColumnByLineageID(
input.Buffer, _extraColumnLineageID);
It is the input buffer that actually has the slots for the new error columns.
Martin
Thursday, February 16, 2012
Add a user via a script
Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
Woody
Chris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>
|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)[vbcol=seagreen]
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to
>
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
Woody
Chris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>
|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)[vbcol=seagreen]
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to
>
Subscribe to:
Posts (Atom)