Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Programatically Creating Dataflow Tasks Require Assembly Reference


Hi,

I am creating dataflow tasj using following

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

These refer to Microsoft.SqlServer.DTSPipelineWrap.dll and Microsoft.SQLServer.DTSRuntimeWrap.dll. While these assemblies were already there in my dev machine I don't find these files in production enviornment for SQL server 2005. I am refering these assemblies from following path in my local machine : C:Program FilesMicrosoft SQL Server90SDKAssemblies.

How to install these assemblies in prod env, offcource one option is to copy it and then put it in GAC thru script . Why does not it gets installed while installation of SQL server 2005. Are these assemly dependent on SP1 ?

Thanks

Mohit


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Programatically Created DataFLow Task Fails
Hi,

I havea package in which i have programatically created dataflow task. It used to work fine but now it fails with series of errors out of which one is below

Error 30002: Type 'MainPipe' is not defined. Line 63 Columns 33-40 Line Text: Dim DataFlowTask As MainPipe = CType(DataFlowTaskHost.InnerObject, MainPipe)

It seems all the function and classes referd from following dlls is not working:
Microsoft.SqlServer.DTSRuntimeWrap.dll
Microsoft.SqlServer.DTSPipelineWrap.dll


I say so because i get the following error also:

Error 30652: Reference required to assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' containing the type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90'. Add one to your project. Line 86 Columns 33-64 Line Text: DtsConvert.ToConnectionManager90(ChildPackage.Connections(""Source"")) These dlls are in GAC and in C:Program FilesMicrosoft SQL Server90SDKAssemblies with same version but still it gives above error.

Thanks
Mohit

View Replies !   View Related
Creating A System.Management Assembly In Order For My Own Assembly To Work?
Hi

I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.

I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.

in another separatenamespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.

namespace inlineCLRsql{


public static class test{


public static void PrintMessage(){



inLineLib.Queue q = new inLineLib.Queue();

int i = q.queueId ;

Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());



}

}

}

to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.

so what I did is (and this is where I need to know if I just ruined sql server or not):

1- ALTER DATABASEmyDB SET TRUSTWORTHY ON;.

2- CREATE ASSEMBLY SystemManagement

FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Management.dll'

WITH PERMISSION_SET = UNSAFE

3- CREATE ASSEMBLY inLineLibMaster

FROM 'D:inLineServerinLineLibinDebuginLineLib.dll'

WITH PERMISSION_SET = unsafe

4- and finally

CREATE ASSEMBLY inLineLib

FROM 'D:inLineServerCLRSQLinlineCLRsqlinDebuginlineCLRsql.dll'

WITH PERMISSION_SET = SAFE



Everything works after those steps (which took some trial and error). I can create a sproc like:

CREATE PROC sp_test AS

EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage

and it returns the Queue ID

Is there anything unadvisable about the steps above?



Thanks for your help



M





View Replies !   View Related
BIG SSIS PACKAGE (200 DATAFLOW TASKS)
Thanks in advance in reading this thread.



I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).

The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.

When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...

I have attached the an lousing .jpeg.

It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.


Thanks in Advance.

View Replies !   View Related
Can We Format The Data In Dataflow Tasks?


hi all;
i have a doubt;
can we modify the data / format the data in a dataflow task?
Requirement

1. Excel file source
2. Convert data "Dec-07"(Mon-YY format)into "Dec-2007"(Mon-YYYY)
3. OLEDB Destination

how to do the second step?

View Replies !   View Related
Changeing Dataflow Tasks Propeties By Coding
Hi,
Is it possible to change the Dataflow tasks properties with VB coding? I've managed to change the properties of the control Flow tasks with VB code! as I wan to create a generic dataflow that will change everytime I run it.
Cheers

View Replies !   View Related
SSIS PACKAGE + 200 DataFlow Tasks && T-SQL Validations
Thanks in advance in reading this thread.



I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).

The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.

When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...

I have attached the an lousing .jpeg.

It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE)that are dependent of some tables, start working before the SSIS package Ends.


Thanks in Advance.

View Replies !   View Related
Setup 5 Dataflow Tasks In Series Or Parallel?
I've create a package that currently uses 5 DataFlow tasks connected in series to get data from 5 different files and place that information into 5 different temp tables. Each Dataflow task contains only a OLE Source, a row countanda OLE destination. My question is - Is it normal practise to keep each of these separate, or should I put them all into a single DataFlow? The package should only continue if all five dataflow task complete successfully.

View Replies !   View Related
Special Error Handle In A Dataflow Transformation Tasks
Hello,


How would you do a log in a massive rows loading, I'm having problems because every row error(because ofcasting, format, lookup) ina transformation task is redirected to a text file as a log, this is ok whenonly exist one error by row, but in the case when I have two errors in the same row detected by diferents transformation tasks only the first one is reported to the text file, I have to wait to the second information load, after I correct the first error, to find the second one, I need to validate as many errors exists by row in the same load...

which component or which strategy can I use in a SSIS Packge to achieve this?

thanks

View Replies !   View Related
SSIS - Need To Reference .NET Assembly Without GAC
I have a .NET assembly that I need to reference and use within my SSIS script task.
The only way I can acheive this is to strongly name my assembly and put it in the GAC.
This is not possible as my assembly is already in production and uses other 3rd party assemblies that would also need to be registered in the GAC.
As a workaround, I have created a .Net console application that references my assembly, that call from a SSIS Process Task.
Does anyone know of another way I could use my .NET assembly within my SSIS package?
Any help appreciated.
 Regards,
Paul.

View Replies !   View Related
Assembly Reference Issue
I have an external reference to an assembly that uses the following external references of its own:

System
System.Data
System.Xml

In BIDS everything works without any issues so I decided to deploy the package to my database server. In doing so, I copied the DLL reference for my external assembly to obth the GAC and the v2.0.50727 folder and I even went as far as to copy the assembly folder into the Program Files folder.

Despite these efforts, I keep obtaining the following error:

The script threw an exception: Could not load file or Assembly 'MyComponent, Version-1.0.0.0,Culture=neutral,PublicKeyToken=f5bb784d481b2932' or one of its dependencies. The system cannot find the file specified.

I checked the version of my DLL in all 3 places and both the version and the token are the same.

I checked the properties within the script task and it has the exact same path to the v2.0.50727 folder (which shouldn't matter given the GAC...).

Does anybody has any ideas why this exception is occurring?

Thank you for your help!

View Replies !   View Related
How To Reference Dataflow Elements In A SQL Statement Embedded In A Lookup Transformation?


Hi guys,

I need to use a SQL statement to lookup a value from a SQL server database table that relates to a column in my dataflow.

Imagine a SQL database table called 'cars' with values of

Year | Description
2005 Ferrari 355
2005 Ferrari 355 Spider
2006 Ferrari 355 F1

In my data flow I have Year and Model eg.

Year | Model
2005 355
2006 355


In my SQL statement I want to select from the 'cars' table where the years match exactly but the 'description' is like the 'Model'. eg. %355%

So essentially, howdo I construct the 'like' clause in the select statement to reference the'Model' column please?

thanks for your help,

Chris


View Replies !   View Related
Reference Report Items Within A Custom Assembly?
How do I reference report items (such as textboxes, datasets, tables, etc...) from within a custom assembly?

Thanks!

View Replies !   View Related
CREATE ASSEMBLY ERROR -- An Implicit Reference Of Some Kind ???
When I run this ...

CREATE ASSEMBLY asmRANDRTCalendaringGateway FROM 'E:sqlRANDRTCalendaringGateway.dll' WITH PERMISSION_SET = UNSAFE

I get this error ...

Msg 10301, Level 16, State 1, Line 1
Assembly 'RANDRTCalendaringGateway' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

Even though I do not explicity refer to system.web in the DLL's code ...

using Microsoft.Win32;
using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using log4net;
using ADODB;
using MAPI;
using CDO;
using System.Runtime.InteropServices;

Is the problem just staring me in the face ?

cheers

View Replies !   View Related
Error Creating Mdf - Connection To SQL Server Files (*.mdf) Require SQL Express 2005 To....
Connection to SQL server files (*.mdf) require SQL express 2005 to function properly.  please verify the installation of the component or download from http://go.microsoft.com/fwlink/?linkid=49251
 
I AM GOING TO RIP MY HAIR OUT WITH THIS PROBLEM.  I have reinstalled both sql server express 2005 and VWD about 5 times with the same problem.  please, please, please someone throw me a bone here  and help me resolve this problem. 
When I create a new EMPTY website and I rightclick on my website in the solution explorer and choose add item, I chooe SQL Database, I give it a name 'database.mdf' and click add.  I get the following message:
you are attempting to add a database to an asp.net application.  for a database to be gfenerally consumable in your site, it should be placed inside the 'app_data' folder.  would you like to place the database inside the 'app_data' folder?  I click YES (I know this message is normal)
then I get the following message:
Connection to SQL server files (*.mdf) require SQL express 2005 to function properly.  please verify the installation of the component or download from http://go.microsoft.com/fwlink/?linkid=49251
I can add anything else but this damn mdf file. 
thanks for all your help in advance.
 
 

View Replies !   View Related
Null Reference Error When Using A Custom Assembly That Uses An App.config File
I have a custom assembly that uses a config file to obtain database connection settings. This dll is then used by my sql report. I have added refrerenced to all the required dll's in my report and added the required code groups to all the necessary files. I have also placed my app.config file in C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServer and C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies
However when I actually run the report in the designer (vs2005) I am receiving Null reference exceptions when my code tries to read my config file because it is reading the machine.config file and not my app.config file. If I hard code my connection string everything works fine.

Can anyone tell me why the machine.config file would be referenced instead of my app.config file and how I can stop it from doing this?

View Replies !   View Related
Creating A Table Programatically!
Hi SQ Gurus,
Please help me with this issue that I am facing. I want to create a table programatically but want to pass its name as a variable. I would like to do something as follows:
CREATE PROCEDURE BS_Create
(@Name nvarchar(25))
AS
CREATE TABLE [dbo].[@Name](
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Name] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The table name is passed in as a parameter. No matter what I pass as a parameter, the table name is '@Name', which is not what I want. Instead, if the variable contains the value 'Product', I would like to see a table with the name 'Product' to be created.

How can I solve this problem.

Any help is appreciated!
Krish Chandra

View Replies !   View Related
CS0246: The Type Or Namespace Name 'SqlConnection' Could Not Be Found (are You Missing A Using Directive Or An Assembly Reference?)
Hello,I just lost all my data doing a system restore and now trying to rebuild my web project.However, Am getting the error below, trying to use a class to run my Stored procedure.What is wrong herethanksEhiCompiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:Line 22: //SqlConnection con = new SqlConnection("cellulant_ConnectionString");
Line 23: //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cellulant_ConnectionString"].ToString());
Line 24: SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"].ToString());
Line 25:
Line 26: Source File: c:inetpubwwwrootcellulant1App_Codesignup_data-entry.cs    Line: 24   using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;


public class signup_data_entry
{
public signup_data_entry(DateTime dob1)
{

//SqlConnection con = new SqlConnection("cellulant_ConnectionString");
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cellulant_ConnectionString"].ToString());
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"].ToString());


SqlCommand command = new SqlCommand("Cellulant_Users_registration", con);
command.CommandType = CommandType.StoredProcedure;

con.Open(); 

View Replies !   View Related
Creating An MS SQL Data Table Programatically
I use Visual Studio and VB.

If I want to create an SQL data table on the server, I go to Server
Explorer and make a small table with the corerct columns. Then I add
rows programatically as needed from the Application I'm working with.

It would suit me, for a current job, to be able to create the table itself programatically.

I don't know how to do this. Can it be done ? If so, could someone give
me a starter, please ? Four cols with a key in the first.

David Morley

View Replies !   View Related
Creating Calculated Members Programatically
Hi

I've read the MSDN documentation about creating calculated members and states that we can only create session scope or query scope members.

I need to create a static calculated member, one that stays there until it's deletion... That is possibel using Analysis Services, but what about with MDX or DSO?

Thanks in advance

View Replies !   View Related
Problem In Creating A Database Programatically....
I am crateing the database on the fly using db directory in VC++. I am able to create database. I am also able to create login. These can be easily done by logging as master using the username and password sa and "" resp. No

View Replies !   View Related
Problem In Creating A Database Programatically....
I am crateing the database on the fly using db directory in VC++. I am able to create database. I am also able to create login. These can be easily done by logging as master using the username and password sa and "" resp. Now I want to add a user to the database that i created. but it is being added to the master database. I know that I have to change my login from sa ,"" to the username and password that i gave during login. but i dont know how to change the login. Please help. If you have any code for that then that will be of great help.
Hoping to get a positive response as soon as possible..
Thanx
Nitin Gulati

View Replies !   View Related
Creating Script Task Programatically.
Hi All,
I am trying to create Script task programmatically. i am struck in one place where we will add the script code. I have given my code.
My Code:

TaskHost taskHost = package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") as TaskHost;

taskHost.Name = "Create SO";

taskHost.Properties["ReadWriteVariables"].SetValue(taskHost, "User:alesId");

Equvivalent XML code generated by the above code.



Code Block
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">
<DTS:Property DTS:Name="ExecutionLocation">0</< FONT>DTS:Property>

<DTS:Property DTS:Name="ObjectName">Create SO</< FONT>DTS:Property>
<DTS:Property DTS:Name="DTSID">{4498C2BE-58B6-433C-8F3E-0268BD267BAE}</< FONT>DTS:Property>
<DTS:Property DTS:Name="Description"></< FONT>DTS:Property>
<DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</< FONT>DTS:Property>
<DTS:Property DTS:Name="DisableEventHandlers">0</< FONT>DTS:Property>


<DTS:ObjectData>
<ScriptProject Name="ScriptTask_681442c33ab344b7bedb4492b01245a8" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="True" ReadOnlyVariables="" ReadWriteVariables="User::SalesId"/>
</< STRONG>DTS:ObjectData>



Generated XML when done thru SSIS Editor. i have given only the DTSObject..thats where i am struck.



Code Block
<DTS:ObjectData>
<ScriptProject Name="ScriptTask_d17b2b95a3984e37a536f2a6e017e2c9" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="True" ReadOnlyVariables="" ReadWriteVariables="User::SalesId">
<ProjectItem Name="dts://Scripts/ScriptTask_d17b2b95a3984e37a536f2a6e017e2c9/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math





In the DTSObject, i dont know how to add details thru C# code. Please help me.
Thanks.

View Replies !   View Related
Creating Model Based On UDM Programatically
Hi all,

I konw of the following way to create a Model based on UDM (OLAP CUBE):

Select the shared data source in report manager

Click on Generate model button

Enter a Model Name, Description and Location Click Ok


Is there a way to do this using code (C#, VB)?



Thanks,

LIran

View Replies !   View Related
Changing Field Property To Not Require From Require?
Hi everyone,

I have a table which have several fields. I have a field that is made required by mistake. I would like to change this field to not require. Can someone help with the syntax?

Any help is greatly appreciate.

View Replies !   View Related
SQL Sever 2005 - Creating Alias Names Programatically
Is there a way we can programatically create a SQL server Alias name (SQL 2005 - SQL Native client configuration) using either T-SQL, C# etc. - Manmeet

View Replies !   View Related
Reference To Preceeding Component From Custom Dataflow Transformation Component
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.

I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.

Does anyone have any suggestions?

TIA . . . Ed

View Replies !   View Related
Error While Creating Assembly
I am trying to create an assembly on a sql server 2005 machine but it gives me following error:


Msg 33009, Level 16, State 2, Line 2
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION statement.



I tried using the alter authorization statement to change the owner.
It did not work.

I am able to create same assembly on another test database but can not create it on this database.

Is this because of orphan logins?

Thanks for the help.

Harshal.

View Replies !   View Related
Error While Creating Assembly
I am trying to create an assembly on a sql server 2005 machine but it gives me following error:


Msg 33009, Level 16, State 2, Line 2
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION statement.



I tried using the alter authorization statement to change the owner.
It did not work.

The same assembly is created on another test database but can not create it on this database.

Is this because of orphan logins?

Thanks for the help.

Harshal.

View Replies !   View Related
Error Creating Assembly


I encountered something strange, deploying the following assemblies


CREATE ASSEMBLY [Sentry]

AUTHORIZATION [dbo]

FROM 'c:clrSentry.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS;

GO


CREATE ASSEMBLY [Sentry.XmlSerializers]

AUTHORIZATION [dbo]

FROM 'C:clrSentry.XmlSerializers.dll'

WITH PERMISSION_SET = SAFE;

GO



Sentry.XmlSerializers errored out with:
Msg 6218, Level 16, State 2, Line 2
CREATE ASSEMBLY for assembly 'Sentry.XmlSerializers' failed because assembly 'Sentry.XmlSerializers' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializerContract::CanSerialize][mdToken=0x600006e][offset 0x00000001][token 0x01000019]System.TypeLoadException: Could not load type 'Sentry.SentryDataService.SentryDataService' from assembly 'Sentry, Version=1.0.2764.18017, Culture=neutral, PublicKeyToken=null'. Type load failed.


The interesting thing is that when I deploy this locally on my machine, there are no problems whatsoever. Using sp_configure the servers appear to be set up the same way. The database also has trustworthy set on for both.

Anything, I could be missing? Thanks


View Replies !   View Related
Problem Creating Assembly Using Other Assemblies
Hi,I have developed a Stored procedure to output text to a text file, after doing a lot of reading here.
I want to be able to output data to a Postgres database, to be used for web mapping. I have referenced an Assembly Npgsql.dll so I can send updates to the postgres database when my ms sql database is updated. However, after building my class library, when I try to CREATE ASSEMBLY in sql server 2005 express, it sends out a message:
Msg 10301, Level 16, State 1, Line 1
Assembly 'ClassLibrary1' references assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(error not found)). Please load the referenced assembly into the current database and retry your request.
The error seems to be in the Npgsql.dll assembly, can anybody please shed some light on this.
I tried to load the system.drawing assembly, and can do so in Unrestricted mode. It then spits out the same problem for System.Windows.Forms.dll.
I am still very new to all of this so any help would be appreciated.
Cheers,
Jatz91.

View Replies !   View Related
Problem Creating A Function From An Assembly
I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas??????????

Thanks

Marek Kluczynski

View Replies !   View Related
Creating A UDT Type With Create Assembly
I created a UDT Type (using MS Server Express Edition  and Visual Basic 2005 express) but I get some errors:





Msg 6556, Level 16, State 1, Line 6

CREATE TYPE failed because it could not find type 'UDTProj' in assembly 'UDTProj'.

Msg 6597, Level 16, State 1, Line 6

CREATE TYPE failed.



That is my code:



Public Structure UDT_interval

Private adresse As String

Private beginn As Date

Private ende As Date



End Structure



************in Transact-SQL

//register the UDTProj assembly

USE TestData;

CREATE ASSEMBLY UDTProj

FROM 'C:MS Visual StudioMyOutputinReleaseUDTProj.dll '

WITH PERMISSION_SET = SAFE;



// create the UDTProj type

CREATE TYPE dbo.UDTProj

EXTERNAL NAME UDTProj.[UDTProj];



or



CREATE TYPE dbo.UDT_interval

EXTERNAL NAME UDTProj.[UDT_interval];



>Msg 6556, Level 16, State 1, Line 6

CREATE TYPE failed because it could not find type 'UDT_interval' in assembly 'UDTProj'.

Msg 6597, Level 16, State 1, Line 6

CREATE TYPE failed.



can anyone help me?

Regards.

D.M

View Replies !   View Related
Dealing With .dll Files After Creating Assembly
Hi
I want to use CLR for developing database object such as stored procedures.
I have read the "Getting Started with CLR Integration" from MSDN help and successfully create my first procedure.
I create an assembly in SQLServer2005 with this code:

CREATE ASSEMBLY helloworld from 'c:helloworld.dll' WITH PERMISSION_SET = SAFE

My questions are :
How should I deal with helloworld.dll after creating Assembly?
Can I delete this file?
What should I do for uploading my application on the webserver?
Should I upload any .dll file?


View Replies !   View Related
Creating SQL2000 Scheduled Tasks
Hi Guys

I googled this an I got wonderful resul... no, no I didnt.
Could someone please provide a link or a step by step guide on how to schedule the running of SP's that will then send the results to an email address.

Thanks! This will be VERY useful!

~Coin

View Replies !   View Related
2005: Creating Aggregate Function From .NET Assembly
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/

View Replies !   View Related
Creating A Custom Assembly - Can't Find Directory For DLL
Hello,

The main problem I am currentlyfacing is that I do not have the following directory to place the DLL file:C:Program FilesMicrosoft SQL Server80ToolsReport Designer. It is the "Report Designer" part that is missing. I tried adding it, but that didn't work.

Any ideas why that is?

Thank you.

-Gumbatman

View Replies !   View Related
Creating A Parameter File For The Data Flow Tasks ??? VerY Urgent


Hi,

I need to parameterize some values in the data flow so that i canchnage the values directly in parameter file and re run the data flow for new value in the passed in the parameter. This can be easy for other who do not know about the flow of data flow task as to where to change the variable/parameter.

How can this be accomplished. I want the data flow task to refer to this file before it starts executing and pick the appropriate value from the file.

Or is their any better way to accompalish what i want to do here in SSIS.???

tHNAKS FOR UR HELP FOLKS !!!



View Replies !   View Related
Severe Error Occurring When Creating Assembly With External Access Permission
I had created a CLR function in my db and was able to execute it successfully a couple of months ago. But when I tried to execute it today it was throwing errors saying there was something wrong with the permissions on the assembly. So I decided to drop everything and recreate it except I can not longer create the assembly with EXTERNAL ACCESS permissions. Whenever I try to create the assembly I get the followng error:

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

I also tried to create the assembly with Unsafe permissions and got the same error. Does anyone know why this error would be occurring now? I tried creating the same assembly on a different SQL 2005 server and it creates successfully and can be executed successfully. Any help would be greatly appreciated!!



Thanks!

GN

View Replies !   View Related
ALTER ASSEMBLY Error Msg 6509 An Error Occurred While Gathering Metadata From Assembly ‘&&<Assembly Name&&>’ With HRESULT 0x1.
I work with February CTP of SqlServer 2008.
I have an Assembly with several UDTs inside. Version of assembly is 1.0.*
I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version).
Then I try to update assembly in SqlServer. I use
ALTER ASSEMBLY <name>
FROM <path>
WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA
statement for this. Statement runs with error:
Msg 6509An error occurred while gathering metadata from assembly <Assembly name> with HRESULT 0x1.
I found the list of condition for ALTER ASSEMBLY in MSDN:
ALTER ASSEMBLY statement cannot be used to change the following:
The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly.
The signatures of methods in the assembly that are called from other assemblies.
The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly.
The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly.
The FillRow method name attribute for CLR table-valued functions.
The Accumulate and Terminate method signature for user-defined aggregates.
System assemblies.
Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead.
Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes:
Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed.
Adding new public methods.
Modifying private methods in any way.

But I havent done any changes in source code, so new version of assembly satisfies all this conditions.
What could be the reason for such behavior?
P.S. Ive got the same error, if I add or change any method in assembly before rebuilding.



View Replies !   View Related
Execute A Query Inside Dataflow And Use The Fields Returned To Continue Dataflow... How?
Dear Friends,

I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...

I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(

How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?

Thanks!!

View Replies !   View Related
Msg 6573 Method, Property Or Field In Assembly Is Not Static. VB.Net Assembly In SQL Server Problem


I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:


CREATE FUNCTION dbo.Temperature(@FluidName char, @InpCode Char, @Units Char, @Prop1 varchar, @Prop2 varChar)

RETURNS VarChar

AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature


Error returned:


Msg 6573, Level 16, State 1, Procedure Temperature, Line 21

Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.



Here is the code (part of it)in the VB class:

Header:


Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

Imports System.Runtime.InteropServices

Imports System.Security

Imports System.Security.Permissions





Namespace FluidProperties



'Option Strict Off

'Option Explicit On

Public Partial Class Fluids

Function:


Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object

Call CalcProp(FluidName, InpCode, Units, Prop1, Prop2)

Temperature = ConvertUnits("-T", Units, T, 0)

End Function


If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.

I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?

Thanks

Buck

View Replies !   View Related
Error Registering Assembly Using CREATE ASSEMBLY
We have written a test CRL stored procedure to test replacing one of our complex stored procedures but cant get it deployed to our SQL server that hosts a mirrored configuration of our production database (very locked down). It works fine on our development instances (not very locked down). It only references the default assemblies that were added when we created the project. All it does is use Context Connection=true to get data, loops though some records and returns the data using SQLContext. CLR is enabled on SQL server, the assembly is strongly signed, and we tried deploy using the binary string with the SAFE setting.


CREATE ASSEMBLY for assembly 'SQLCLRTest2' failed because assembly 'SQLCLRTest2' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : SQLCLRTest2.StoredProcedures::GetLift][mdToken=0x600001e] Type load failed.
[token 0x02000008] Type load failed.


View Replies !   View Related
&&"Failed To Load Expression Host Assembly. Details: StrongName Cannot Have An Empty String For The Assembly Name. &&"
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website.  I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report.  The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process.  Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."

 

First of all, I could not figure out where and how to do this.  Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report:

 

"Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name."

Please help.

View Replies !   View Related
Error: CREATE ASSEMBLY For Assembly
I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard.
I import System.IO? and have therefore set the permission levels to EXTERNAL_ACCESS.

I am receiving the same error message that many folks have received.

CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.
The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.

My CLR access is on?

I have tried

1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [BuiltinAdministrators].
2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to My Windows Authentication ID?.
3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON
4) In Visual Studio .NET 2005 Set the permission levels to external
5) Tried BuiltinAdministrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.

I can compile BUT NOT DEPLOY

Any help would be greatly appreciated.
Regards Steve

View Replies !   View Related
CREATE ASSEMBLY Using Assembly Binary?!?!
I was trying to understand how VS.NET2005 was deploying .NET CLR assemblies to SQL2005 so I ran a trace and found some interesting results.

VS.NET creates some SQL that looks pretty interesting:

CREATE ASSEMBLY [AssemblyNameHere]
FROM 0x4D5A90000300000004000000FFFF000......<continue binary data>
WITH PERMISSION_SET = EXTERNAL_ACCESS

Boy howdy!

I have tried to reproduce this and create my own deployment application but I cant figure out how they create this binary stream. The info in BOL is not much help and I have not found any samples anywhere on how to create this stream in c#.

Anyone out there been able to get this to work?

-Ben

View Replies !   View Related
Help (require Code)
right im programming a database program and i need some code. (the database is in sql/msde)
This is what i am attempting to do:
I have a page with textboxes on it with buttons at the bottom
i need to program the database with the add function

this is the code that i have already
Imports system.data.sqlclient

'You need two objects

Dim conn as new sqlconnection
dim comm as new sqlcommand

conn.connectionstring = "Your UDL String here"
comm.text = "insert into books (isbn, title, author, publisher, description, price,)
'books is the name of the databse
values ( '1234' , 'Title', 'Author', 'Publisher' )
conn.open
comm.connection = conn
comm.executenonquery
comm.close
conn.close

now i think i need to insert this at the top of the page but im not 100% sure (btw i have the udl string)

so what do i need to add to this code to make the page submit data that has been enterd into textboxes into a database table(sql/msde) on the click of the button?

thanks for any help provided

View Replies !   View Related
URGENT REQUIRE HELP
Hi

1) I am facing the problem of database suspect how do I solve the problem.

2) When I tried to cofigure my SQL mail I am not getting the option or Icon of SQL MAIL in the enterprise manager. I think it probably got deleted , how do I retrieve it .


Thanks for the help in advance

Arya Sharma

View Replies !   View Related
PLEASE HELP Aspnet_regsql.exe - What Does It Require To Run Stand-alone?
Does anyone know what aspnet_regsql.exe requires to run stand-alone? I have to apply the .NET 2.0 Management/Role API to a sql2000 database on a machine that does not have the .net 2.0 framework installed.

View Replies !   View Related
Require Solution For This SQL Problem
Hi,I have two tables TableA and TableB. I have a stored procedure whichruns every morning and based on some logic dumps rows from TableA toTableB. In Table B there are two additional colums ID and RunID. ID isa normal sequence applied for all rows. But the RunId should beconstant for a run of stored proc.So for e.g. say structure of Table A and Table BTable A Table Bcol1 ID RunID col1Now when I run stored proc I want rows copied as belowTableA TableBcol1 ID RunID col1row1 1 1 row1row2 2 1 row2The next day when stored prc runs I want data asTableA TableBcol1 ID RunID col1row1 1 1 row1row2 2 1 row2row1 3 2 row1row2 4 2 row2So for every run of stored proc each day I want the Run ID incrementedonly by one and ID is normal sequence which increments for allrowsinserted.Please help.Nick

View Replies !   View Related
Basic OLAP Require !!
Hi Champs!!

I am totally new in OLAP and want know more prior to start....pls tell me know the difference between these..

Regular/Virtual/Linked cubes..
Star schema/ snow flake/ parent-child/ virtual dimension/ mining model

Thanks in advance
-Deepak Kumar.

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved