MS SQL Server :: SPLIT A String Inside SSIS/T-SQL Code?

How To Split A Delimeted String...
hi,
i have a simple question, i have a sql server table named "Restaurant" and a field "Cuisines",
the values in the field Cuisines contains delimeted string. (e.g. "C1,C2,C3...C10" ,maximum to 10 items or less)
now, i want to split the values from Cuisines ("C1,C2,C3...C10") and passed all of them to a SP parameter.
e.g
@item0 nvarchar = "C1",@item1 nvarchar = "C2",@item2 nvarchar = "C3",@item3 nvarchar = "C4",@item4 nvarchar = "C5",@item5 nvarchar = "C6",@item6 nvarchar = "C7",@item7 nvarchar = "C8",@item8 nvarchar = "C9",@item9 nvarchar = "C10"
if the field "Cuisines" contains less than 10 items, the value of the remaining parameters is null.
any response will be appreciated. thanks.

View Replies !

SQL String Split With | Pipe
I've got a dilemma here. I'm currently reading records into EasyListBox (ELB) dropdownlist. The criteria in the dropdownlist is based on the select from another ELB dropdownlist. The problem is that the data contains rows with pipes that serve as separters. For example, one of the options that comes back now might be 123 | 456 | 789. I need to be able to make each of them its own row in the dropdownlist. Can anyone advice? Most records don't have any pipes and come throught fine, but some dont.
My code below:
Dim sAT As String = Replace(elbAttribute.SelectedValue, "'", "''")
Dim adapter As New SQLDataAdapter("Select Feature_Type As Attribute_Name, FEATURE_TEXT_VALUE as Attribute_Value, (select distinct FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID from FILTER_PROFILES, FILTER_FAMILY_FEATURE_EXCLUDED, FAMILY_ALLOWED_FEATURE_TYPES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FILTER_PROFILE_ID And FAMILY_ALLOWED_FEATURE_TYPES.FAMILY_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FAMILY_ID And FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID = SHARED_FEATURE_TYPES.FEATURE_TYPE_ID And (SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "')) As ExclusionFlag From SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES, PRODUCT_FEATURE_VALUES Where PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = SHARED_FEATURE_VALUES.Feature_Value_ID And SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "' UNION Select distinct Column_Name As Attribute_Name, SMALL_TEXT_VALUE As Attribute_Value, (select FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID from FILTER_PROFILES, FILTER_ATTRIBUTES_EXCLUDED where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_ATTRIBUTES_EXCLUDED.FILTER_PROFILE_ID And FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID) As ExclusionFlag From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES, PRODUCTS Where PRODUCTS.PRODUCT_ID = EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID And not SMALL_TEXT_VALUE is null And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID > 1499 And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID And Column_Name = '" & sAT & "'", myConnection) Dim dt As New DataTable() adapter.Fill(dt) elbValue.DataSource = dt elbValue.DataBind()

View Replies !

Sql Charindex Split String
HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!

View Replies !

Split The String Into Columns

I have a table called products with the values like

ProductId ProductName
10            A
20           D,E,F,G
30           B,C
40           H,I,J

I need to display each productid's with

ProductId ProductName
10           A

20           D
20           E
20           F
20           G
30           B
30          C
40          H
40          I
40          J

I will be appreciated if you can send me the code.

Thanks,
Mears

View Replies !

Split String Using Delimiter
Hi,

I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2'

I need to split the above string and get each of those values... ' / ' delimiter

Can some one please help on this.

Thanks

View Replies !

How To Split A Comma In A String
I have a string

@string = 'abc,def,ghi'

Now I have to display

abc
def
ghi

I have to separate a comma in that
Is there an function like split in sql

plz help me
Thanks in Advance

Suresh Kumar

View Replies !

Split Command In String
hi all, i am still a newbie in sql

i got a problem with my query

for example:
-------------------------------
DECLARE @LongString VARCHAR(10)
@LongString = "Hello-Word"
------------------------
i want to change the LongString value by removing the "-" however i i need to identify if the @longString got "-" and delete it if it found how do i do that in sql

Thanks a lot guys

arifliminto86

View Replies !

How To Pull Data In The Inline Code Block Inside A Template?
The code block is embedded into a grid view item template. <ItemTemplate> <% if ((bool)Eval("isNew")) { %> New Post <% } %></ItemTemplate> but as per rule Eval and Bind can be only use bind the data. How would i pull the information from sqldatasource in the template like this?

View Replies !

Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function

View Replies !

How Do I Split And Insert A Comma Delimited String To A Table?
I am passing in a string of dates, delimited by a comma.

So

01/01/04, 02/01/04, 03/01/04

etc

I would like to enter each of these values into a table via an INSERT stored procedure.

Does anyone have any code for this?

View Replies !

Uniqueidentifier Param Inside A SQL String
I have a uniqueidentifier probleme when i want to execute my sql string. The "PortalID" is my uniqueidentifier passed on my StoredProc parameter. But i cant make it work. It said that i cant add a nvarchar with a uniqueidentifier.!!
what is the best way to do it!! i try to convert(nvarchar(40), @PortalID) but its not workingdeclare @sql nvarchar(4000)
set @sql = 'select
t.[PortalID],
t.[City],
t.[Name],
t.[Code],
ts.*
from [Hockey_Team_Statistics] ts inner join [Hockey_Teams] t on (t.[TeamID] = ts.[TeamID])
where ts.[SeasonYear] = '+CONVERT(nvarchar(10),@SeasonYear)+' and ts.[LeagueMode] = '+CONVERT(nvarchar(1),@LeagueMode)
+'t.[PortalID] = '+@PortalID+'

order by '+ @SortExpression +''

exec sp_executesql @sql

View Replies !

Making Managed Code Calls Inside SQL Server In Context Of The Client
Dear all,

I am very new to the subject of writing CLR code inside SQL Server, so I apologise if my questions seem naive.

I have a requirement to populate an asp.net 2.0 GridView control with data columns, some of which are directly from a SQL Server 2005 database, but some of which are calculated by calling CLR methods passing the values from the database columns to those methods.

However, the methods I need to call only make sense in the process context of the client web site which is calling the stored procedure which I want to write to return the data columns.

In effect, I want to be able to make a remote procedure call from within the SQL Server CLR code to the methods available in the client process.

Is this possible? If so, could someone please refer me to an example of how to do it.

If it can be done, it opens up lots of very cool possibilities!

Thanks.

View Replies !

Help! Trying To Find Records Inside Text String
Hi, got a problem and can't figure this one out.

basically i've got a field containing a value '(14)(12)(33)(22)' and i want to compare it to a table containing those values in separate cells...

record 1 : (01)
record 2 : (02)
etc...

and i want to compare this one field to those records to see whether that text string contains anything the table contains.

i've tried ContainsTable & IN but still can't figure this out.

any help greatly appreciated
:o

View Replies !

Formula For Replacing ENTER Key Inside A String
Hi guys,
I have a large text that can have some ENTER character inside of it. The problem is that the text must be inserted into a txt file in a single row. So what I need is to find those ENTER character and replace them with a space so as to have the whole string in one row.
How can I build that formula in derivide column?
Thanks for your help

View Replies !

How To Count # Of Occurrences Of A Character Inside A String?
I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:

NNNNNRJKSURNNNEJNNNN

Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?

View Replies !

Find The Occurrence Of A Particular Character Inside A String
Hi all,

Am searching for a built-in function that retrives me the occurrence (counts) of a particular character inside a string.

For e.g. In the String 'SqlServer'. I want to find the no of the character 'e', which shud retrive the value as 2.

Can this be acheived through any function.

Cheers !
ash

View Replies !

How To Write Condition In The Conditional Split Control In SSIS
HI i need to write the Condition for Insert and Update Reccord depending upon the Prod_ID. How to write the Follwing condtion in the Condition Split? pls Anyone give me the Solution?

" if Prod_ID Exist then UPDATE Records

if Prod_ID Not EXIST then INSERT Records "

how to write the above conditon in the Condional Split?

Thanks & Regards,

Jeyakumar.M

chennai

View Replies !

SSIS - Conditional Split, Date Compare Problem!! Please Help
Right the answer is probably simple but the Internet and books and everything has been no joy to me whatsoever.

I want to split my data stream based on the date. So I want to use a conditional split object to do this.

I entered the following as my case date_created > (DT_DBTIMESTAMP)"01/10/2000"

When I move off the line it stays black so appears to be okay, yet when I run my package it says it is not a boolean result and fails. Can anyone please tell me what I am doing wrong.

Also I cannot filter in the source call due to the sheer amount of work being done on the data before the split.

Thanks in advance for any help

View Replies !

Cursor, Conditional Split Task, Nested Joins In SSIS

Hello

Can anybody help me out in
1) implementing cursors in SSIS. I want to process each row at a time from a dataset. I was trying to use Foreachloop container but in vain. Can you please answer in detail.

my few other questions are:
1) Can i do nested inner join in SSIS. If yes, how? ( I have three table i need to join Tab1 to table 2 and get join the table 3 to get the respective data)
2) I have a resultsets. I want to split the data according to data in a col.
Say for instance:
Col1 Col2
A        1
A         2
B        3
C        4
C         5
i want to split the data according A, B and C . i.e., if Col1= A then do this, if Col1= B then do this..etc. How can i do this using conditional split task in SSIS

View Replies !

Critical Issue With ADO .Net Inside A SSIS Packages
Hi everyone,

I've got a serious problem with SSIS packages. We've built an application -done with Framework 2.0- which throw SSIS packages on demand -according a criteria on NTFS-
Everything's fine but those packages which own ADO .Net connections. When our application meet some of them appears in our log:

I attached you the snippet of code:

pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "ninot", EventsSSIS)
Till here, fine no throws any event.

sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)
'here throws OnError event when meet the task issued

If sResultDts = DTSExecResult.Success Then
...

No se pudo adquirir la conexiÃ³n "SRVDESASQL2005.msdb.usrSSIS". Puede ser que la conexiÃ³n no estÃ© configurada correctamente o que no tenga los permisos adecuados en esta conexiÃ³n.

Tranlate into english, more or less:

It doesn't acquire the connection "<server.database.user>." It could be that the connection is not fine customized or maybe permissions are not suitable for this connection.

At the same time we've verified that all data sources are available. In a fact that same packages from the server is launched without incidences (manually)

Please let me know any comment or further information or any link related with this problem. With OLE Db connections we haven't problems at all.

Thanks in advance,

View Replies !

Update SSIS Variables Inside DTS Task ?
Hi all,

I use a "Exec DTS 2000" task.

I have a variable user::MYVAR in my SSIS package, wich value is "PARENT".

I pass this variable as an outer variable to my dts 2000 package, wich receive it correctly, and then update it to "UPDATED".

After this task finishes, when I come back to SSIS, MYVAR is still at "PARENT".

Is it possible to see the update from the parent package ?

Thanks

View Replies !

Strip DTD Out; Help With Some XSLT Inside Of SSIS XML Task
I'm reading over this thread:   http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884062&SiteID=1

and I'm kinda lost as to what to do to strip out the dtd from an XML file I am downloading.   I do NOT know XSLT and for that reason, I can't follow his logic.

My SSIS package downloads my XML file just fine, now I need to do a strip of the DTD line in my XML Task.

The person who provided the solution in the above post said to do this...

Code Snippet
Operation Type: XSLT
Source Type: Variable
Source: Variable's name containing the xml text
Save Operation Result: True
DestinationType: Variable
OverwriteDestination: True
Destination: Variable's name which is to contain the original xml minus the DTD.
SecondOperandType: Variable

That stuff I understood. I'll replace variables with my files because they are stored that way, but from what I can tell, that's not my problem.

The stuff he says below this comment is going over my head like a ton of bricks. I can't figure out how to do it.

This is the kind of line of my XML that I want to strip out.

https://www.myaddy.com/pbdr.dtd"[]>

and then he said this...

Code Snippet
Since XSL doesn't know about DTDs, telling it to copy everything strips out DTDs.   Then use the Variable specified in the Xml task's SecondOperand as the Source data for the xml source.

Code Snippet

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<xsl:copy-of select="." />

</< FONT>xsl:template>

</< FONT>xsl:stylesheet>

A note on how to paste a multi-line xml document into a Integration Services String variable:

Integration Services String variables textboxes are not multi-line, in the Windows sense of a line (CR+LF),

So, in order to paste multi-line text (which xml docs almost always are), save a temporary copy with a unix line ending.

That is, create an xml file in visual studio, and paste your sample original xml in there. Go to File/Advanced Save options, and save the xml with the the settings of Encoding: Unicode (utf-8 without signature) - CodePage 65001, and most importantly, set the Line endings dropdown to "Unix (LF)".

After selecting "OK", copy and paste the text from Visual Studio's xml file editor into the IS variable, and you'll note all the xml data appears.

So can anyone walk me through a dummies version of what he is suggesting to do?

Thanks,
Keith

View Replies !

Source Code For The Standard SSIS Components That Come With SSIS?
Hi,

I'm finding that the standard components often just don't quite meet my
needs, but would only need some fairly minor changes to save me and my team a lot of
work (and produce more elegant solutions). So I was just wondering whether the source code was available for the standard components that come with SSIS, or if there is anyway to extend their functionality?
Or do you just have to start form scratch?

Thanks,
Lawrie

View Replies !

Performance Comparision - Stored Procedures VS SQL (Inside SSIS)
I am working on a technical design of data integration ETL package which will be moving data from SQL Server Source to DB2 destination. I currently have two options, when moving data to DB2(IBM AS400). I can call a AS400 Stored Procedure, and pass in the data to the stored procedure, and perform the insert processing within the AS400 environment or I could do inserts from SSIS in a DFT and write individually to AS400 tables. My question is from a performance and good practice perspective, which method should I move forward with. I need a possible list of pros- and cons when using AS400 Sproc vs using SQL within SSIS? I would really appreciate response from individuals who have done something similar in the past. Thanks a lot and I am really looking forward to responses.

View Replies !

How To Execute SSIS Package Inside Of Visual Studio?
How to execute SSIS package inside of Visual Studio?

I have opened the file, but do not see any way to execute it. Please advise. Thanks

View Replies !

How To Capture Validation Errors Inside SSIS Package
Hi

I have created a package which executes every 10 mins. Last week end for maintenance purpose, I shutdown my database. Now as an initial execution process, my package does the default validation steps on which the database connection validation step fails. As this is the default functionality of SSIS I am not able to capture this error. Is there anyway to capture this error inside SSIS Package?

Thanks in advance.

Gnan

View Replies !

Serious Script Component Bug - Clears Out All Code Inside Component

No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.

I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.

And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.

I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.

I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.

I can reproduce this on 2 different computers.

Anyone experience this problem ? Any idea how to stop it ? Or debug it ?

Here is a slimmed down code sample of what causes the error :

Public Class ScriptMain
Public Sub Main()
        Try
            Dim xmlDoc As New XmlDocument
            xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
            MsgBox("xmlLoaded")  --this doesn't display once the package starts "acting up"
        Catch ex As Exception
            MsgBox(ex.Message)
            UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
        End Try
        Dts.TaskResult = Dts.Results.Success
    End Sub
    Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
        Try
            Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
            Dim rd As StreamReader = New StreamReader(fileName)
            Dim xml As String = rd.ReadToEnd()
            Xml = Xml.Replace(invalidChar, String.Empty)
            xml = xml.Replace("", String.Empty)
            xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
            xml = xml.Replace("]]>]]>", "]]>")
            MsgBox("replaced")
            rd.Close()
            Dim wr As StreamWriter = New StreamWriter(fileName)
            wr.Write(xml)
            wr.Close()
            Dim xdoc As XmlDocument = New XmlDocument()
            xdoc.Load(fileName)
        Catch ex As Exception
            UpdateXML(fileName, ex.Message)
        End Try
    End Sub
End Class

View Replies !

SSIS - How To Set ServerName And DatabaseName At Run Time Inside FOREACH LOOP?
I am new to SSIS world, so my question is very basic.

Setup:

In a company I work for we have 12 SQL servers each running between 1 and 3 databases with anywhere between 10 to 20 tables. I need to query some of these tables and merge results to the destination database.

The list of all these tables is stored in the separate table <SOURCES> of the following format [ServerName,DatabaseName,TableName]. Tables of my interest have identical structure (same columns) accross servers and databases.

Question:

How can I loop over servers and databases specified in <SOURCES> to run otherwise identical query against these tables?

I can easily retrieve [ServerName,DatabaseName,TableName] from <SOURCES> as string variables using FOREACH loop. The problem is now - how do I use string variables to set up Server, Database and Table name at run-time?

Thank you

View Replies !

Connection String Designer Code
I currently have the following code in my designer file
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="select Site_name, system_id, ASP_Archive, sitetimes,HPOV_ROC, UPPER(CircuitType) as CircuitType, QwestCircuit_ID, SiteConfig,Site_Nat, PVC_VCI from tblASPCustomerWan order by Asp_Archive asc"
UpdateCommand="UPDATE tblASPCustomerWan SET [Site_name] = @Site_name, [system_id] = @system_id, [ASP_Archive] = @ASP_Archive, [sitetimes] = @sitetimes, [HPOV_ROC] = @HPOV_ROC, [CircuitType] = @CircuitType, [QwestCircuit_ID] = @QwestCircuit_ID, [SiteConfig]= @SiteConfig, [Site_Nat]=@Site_Nat,[PVC_VCI]=@PVC_VCI"
ConnectionString="server=localhost;Trusted_Connection=yes;uid=portal_user;pwd=Cr@zyP@55w0rd;database=CusPortal_Staging" />
I would like the change the connection so it takes it value from System.Configuration.ConfigurationManager.AppSettings("appStrConnection"), how can i do that??

View Replies !

MSSQL - Can Evaluate String As Code?
Hi,
I just want to know if it is possible to evaluate a string stored in variable (e.g. @cString = 'SELECT * FROM my_table') as a code (execute it).
Thanks.

View Replies !

How To Copy Connection String Code

hi,

i was able to succesfully configure my remote server over the internet and tried using express edition to connect to my remote server and everything when successful.

is there a way to copy that connection string used to connect to my remote server so as i wanted to create a project in vb using that connection string..

where can i find the connection string code used for my connection remotely ..

i am using express to connect to my sql server

thanks

View Replies !

Convert String From Textbox To Int - SQL Code
How do I convert for example, a value comming from C# app "TextboxGBDataDeleted" to int column GBdatadeleted? I have a RangeValidator on this textbox to accept only numbers. I don't want to write C# code. I woul prefer to do this is SQL. Thank you.

My SQL update statement:

INSERT INTO KPITbl
(Lead, WRM, PTDB, PAR, PM, RequestingLOB, LOB, StartLocation, FinishLocation, Description, ProjectType, ServerName, ServerType, DCOorSTANDALONE, Responsible,
Status, RAG, StartDates, EndDates, TreeOrDomainImpacted, NumOfSites, NumOfUsers, GBdatamoved, GBdatadeleted, NumOfSrvrsAdded,
NumOfSrvrsDecommed, NumOfAppsDeployed, EUTEngineeringConsult, Comments, TimeSpend, Complexity, ECM, LastUpdated)
VALUES (@Lead,@WRM,@PTDB,@PAR,@PM,@RequestingLOB,@LOB,@StartLocation,@FinishLocation,@Description,@ProjectType,@ServerName,@ServerType,@DCOorSTANDALONE,@Responsible,@Status,@RAG,@StartDates,@EndDates,@TreeOrDomainImpacted,@NumOfSites,@NumOfUsers,@GBdatamoved,@GBdatadeleted,@NumOfSrvrsAdded,@NumOfSrvrsDecommed,@NumOfAppsDeployed,@EUTEngineeringConsult,@Comments,@TimeSpend,@Complexity,@ECM,(getdate() ) )

~~~~~~~~~~~~~~~~~~~~~~MY TABLE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE [dbo].[KPITbl](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[Lead] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WRM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PTDB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAR] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequestingLOB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOB] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinishLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Responsible] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RAG] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDates] [smalldatetime] NULL,
[EndDates] [smalldatetime] NULL,
[TreeorDomainImpacted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSites] [int] NULL,
[NumOfUsers] [int] NULL,
[GBdatamoved] [int] NULL,
[GBdatadeleted] [int] NULL,
[NumOfSrvrsAdded] [int] NULL,
[NumOfSrvrsDecommed] [int] NULL,
[NumOfAppsDeployed] [int] NULL,
[EUTEngineeringConsult] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeSpend] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Complexity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdated] [smalldatetime] NULL,
[DCOorSTANDALONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ECM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusCompletedDate] [smalldatetime] NULL,
[StatusCancelledDate] [smalldatetime] NULL,
CONSTRAINT [PK_Sheet1a] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

View Replies !

Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.

In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.

Linda

Here is the old VBScript Code:

Public Sub Main()

Option Explicit

Function Main()

Dim MovementDataDir

Dim MovementArchiveDataDir

Dim MovementDataFile

Dim MovementArchiveDataFile

Dim FileNameRoot

Dim FileNameExtension, DecimalLocation

Dim CurMonth, CurDay

Dim FileApplicationDate

Dim fso ' File System Object

Dim folder

Dim FileCollection

Dim MovementFile

'======================================================================

'Create text strings of today's date to be appended to the archived file.

FileApplicationDate = Now

CurMonth = Month(FileApplicationDate)

CurDay = Day(FileApplicationDate)

If Len(CurMonth) = 1 Then

CurMonth = "0" & CurMonth

End If

If Len(CurDay) = 1 Then

CurDay = "0" & CurDay

End If

FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate)

'=====================================================================

' Set the movement data directory from the global variable.

MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value

MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value

fso = CreateObject("Scripting.FileSystemObject")

folder = fso.GetFolder(MovementDataDir)

FileCollection = folder.Files

' Loop through all files in the data directory.

For Each MovementFile In FileCollection

' Get the full path name of the current data file.

MovementDataFile = MovementDataDir & "" & MovementFile.Name

' Get the full path name of the archive data file.

MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name

DecimalLocation = InStr(1, MovementArchiveDataFile, ".")

FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1)

FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1)

MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension

If (fso.FileExists(MovementDataFile)) Then

fso.CopyFile(MovementDataFile, MovementArchiveDataFile)

' If the archive file was coppied, then delete the old copy.

If (fso.FileExists(MovementArchiveDataFile)) Then

fso.DeleteFile(MovementDataFile)

End If

End If

Next

fso = Nothing

folder = Nothing

FileCollection = Nothing

Main = DTSTaskExecResult_Success

End Function

View Replies !

Problem In C# Code Behind And With SQL String Date And The Between Statement
HiI have a problem to search between two dates, if I use Convert(varchar(10),ContactCreateDate,105) in a single textbow and for a single search date it is okay, but when I use the Convert(varchar(10),ContactCreateDate,105) against two textboxes it did't function.Can anybody help me ?MY codeif (TBContactSearchDateStart.Text.Length > 0 && TBContactSearchDateEnd.Text.Length > 0)
{
strWhere += " AND Convert(varchar(10),ContactCreateDate,105) BETWEEN '%" + TBContactSearchDateStart.Text + "%' AND '%" + TBContactSearchDateEnd.Text + "%'";

View Replies !

Referring To Sql Connection String In Web.config From Code Behind
Every time I move my project from my computer to the testing server I have to change the connection string references in the aspx side and in my code behind.
For the code behind I declared the SqlConnection string at the top of the code-behind page for both connection strings and comment out the one not in use. Obviously I then comment out the string not in use in the WebConfig as well.
Being superlatively lazy I always look for the easiest and quickest way to do most anything - connection strings included. Having to comment out strings twice brought rise to the question of whether I can refer to the connection string in the web.config file from the code-behind page. I'm sure it can be done, and I did a good amount of hunting around, but I couldn't find any examples of code that would do that.
Currently, as I said above, I have two connection strings declared at the top of my code-behind. Here's an example of one:Private sqlConn As New SqlConnection("Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD")
Then, I just use sqlConn as usual in my binding without having to "dim" it in every sub:sdaPersonnel = New SqlDataAdapter(strSqlPersonnel, sqlConn)
Then there's the SqlConnections set up by the wizard on the aspx side:<asp:SqlDataSource ID="sqlDataSourcePayrollCompany" Runat="Server" ConnectionString="<%$ ConnectionStrings:DATABASECONNECTIONSTRING %>" ...>
And for the connection in the web.config:<add name="DATABASECONNECTIONSTRING" connectionString="Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD" providerName="System.Data.SqlClient" />
So, what would be the code in the code-behind page to refer to the connection string in the web.config file?
Thanks!

View Replies !

Connection String And Permission Errors In The CLR Code
I have written a CLR function in C# to access a sql server different from the current server where I have deplyed the assembly.

DataSet ds = new DataSet();

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.CommandType = CommandType.StoredProcedure;

string connString = "Data Source=SQLSERVER;Initial Catalog=DATABASE;User ID=userid;Password=password;";

SqlConnection sqlConn = new SqlConnection(connString);

sqlCmd.Connection = sqlConn;

sqlConn.Open();

try

{

SqlDataAdapter sda = new SqlDataAdapter();

sqlCmd.CommandText = "StoredProcedureName";

sqlCmd.Parameters.Add("@Param1", SqlDbType.VarChar);

sqlCmd.Parameters["@Param1"].Value = sParam1;

sda.SelectCommand = sqlCmd;

sda.Fill(ds);

}

finally

{

sqlConn.Close();

}

return ds;

I was getting the following error

"Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."

so I tried doing the following

SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);

pSql.Assert();

and then I get this error

"Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."

Any idea how to remedy this ?

Thanks in advance
- Kan

View Replies !

Code For Connection String Using Sql Server 2000 As Backend With Asp.net 1.1 Along With Vb.net
Hi all,
Please help me for the code of connectivity
it was working fine,but,when i hav written code for validation for id n password,its showing error as
Violation of PRIMARY KEY constraint 'PK_Login'. Cannot insert duplicate key in object 'login_detail'. The statement has been terminated.
Here is my code given below.
Please help me
Imports System
Imports System.IO
Imports System.Xml
Imports System.Text
Imports System.Security.Cryptography
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Data
Imports System.Data.SqlClientPublic Class WebForm1
Inherits System.Web.UI.Page#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End SubProtected WithEvents txtUser As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPwd As System.Web.UI.WebControls.TextBoxProtected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents lblError As System.Web.UI.WebControls.Label
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End RegionPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim objConn As SqlClient.SqlConnection
Dim ds As New DataSetDim m_strConnection As String = "server=172.16.152.17;Database=Trial;UID=abhi_Asset4;PWD=L!nux001;Connect Timeout=100"objConn = New SqlClient.SqlConnection
objConn.ConnectionString = m_strConnection
objConn.Open()
Dim strSQL As String
strSQL = "insert into login_detail(UserID,Password) values('" + txtUser.Text + "','" + txtPwd.Text + "')"Dim objCommand As SqlClient.SqlCommandobjCommand = New SqlClient.SqlCommand(strSQL, objConn)
objCommand.CommandText = strSQL
objCommand.ExecuteNonQuery()
objConn.Close()

End SubPrivate Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
If (txtUser.Text.Trim() = "") Then
lblError.Text = "UserID should not be blank"
ElseIf (Convert.ToInt32(txtUser.Text.Length) < 5) Then
lblError.Text = "UserID length must be more than 5"
ElseIf (Convert.ToInt32(txtUser.Text.Length) > 20) Then
lblError.Text = "UserID length must not be greater than 20"
ElseIf (txtPwd.Text.Trim() = "") Then
lblError.Text = "Password should not be blank"
ElseIf (Convert.ToInt32(txtPwd.Text.Length) < 5) Then
lblError.Text = "Password length must be more than 5"
ElseIf (Convert.ToInt32(txtPwd.Text.Length) > 20) Then
lblError.Text = "Password length must not be greater than 20"
Else
Server.Transfer("Success.aspx")
End IfEnd Sub
End Class

View Replies !

Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.

View Replies !

'Too Many Characters' Error In Sql String - Working In SQL Management Studio But Not In Code Behind???
Hello i constructed the following query in SQL management studio and it runs fine, exactly how i want it to. However when i run it in my code behind i get the error: 'Too many characters in character literal'
I desperatly need to use this query exactly how it displays in the mangement tool - why wont it work the same in my code behind?
(I am using c# in my code behind and binding the results of the query to a gridview)
Regards
"SELECT DISTINCT (dbo.AuthorList(publicationID))+ ';' + ISNULL(' Editor: '+Publication.editor, '')+ '' + ISNULL(' "'+Publication.title+'"', '')+ '' + ISNULL(' Book: "'+Publication.booktitle+'"', '')+ '' + ISNULL(' '+Publication.type, '')+ '' + ISNULL(' '+Publication.journal, '')+ '' + ISNULL(' Series: '+Publication.series+'.', '')+ '' + ISNULL(' '+Publication.school, '')+ '' + ISNULL(' '+Publication.institution, '')+ '' + ISNULL(' '+Publication.organisation, '')+ '' + ISNULL(' '+Publication.month, '')+ '' + ISNULL(' ('+convert(nvarchar, Publication.year)+')', '')+ '' + ISNULL(' '+Publication.publisher, '')+ '' + ISNULL(' '+Publication.address+'.', '')+ '' + ISNULL(' '+Publication.howpublished, '')+ '' + ISNULL(' Ed: '+convert(nvarchar, Publication.edition)+'.', '')+ '' + ISNULL(' '+Publication.note, '')+ '' + ISNULL(' Vol:'+Publication.volume+'.', '')+ '' + ISNULL(' No:'+Publication.number+'.', '')+ '' + ISNULL(' Pages:'+Publication.pages+'.', '')+ '' + ISNULL(' Chapter:'+Publication.chapter+'.', '') AS SingleColumn, Publication.publicationID FROM Publication;"

View Replies !

How To Convert A Returning String From Custom Code To A Function In Expression?
I have a custom code function that return string like:

"SUM(Fields(Parameters!dept.Value + ""_1HeadCount"").Value) +

SUM(Fields(Parameters!dept.Value + ""_2HeadCount"").Value) +

SUM(Fields(Parameters!dept.Value + ""_3HeadCount"").Value) "

How do I convert this string to a function inside the expression (in this case "SUM", "Fields", "Parameters!", etc.)?

The expression of my TextBox show the string as a string instead of converting them to proper function and display the corrent value.

The reason I need to build the string is because the Fields number is dynamic and I need a loop to increase the number in the field name.

Any suggest is highly appreciated!

Thanks,

Tabbey

View Replies !

Custom Code: Enterprise Library And Connection String In The Web Config
Hello,

My report uses a dll in order to calculate a field and has been set up correctly to access it. The method in the dll connects to a database using the Microsoft Enterprise Library. I have placed all of the enterprise library dlls in to the Private Assemblies of Visual Studio but the report has the following error:

The requested database ABC is not defined in configuration

The reason for this error is becuase the connection string is held in the project's web config. The reports are in the same solution but are within their own report project.

What do I need to do to set up the report to access this connection string?

Many thanks.

View Replies !

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error Has Occurred. Error Code: 0x8000FFFF.
Hi All,

Recently in an SSIS package I am getting the following error for a particular Data flow task.

Error: 2008-01-25 12:01:48.58

Code: 0xC0202009

Source: Import Datasynapse Data User Events Source [3017]

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

End Error

Error: 2008-01-25 12:01:48.73

Code: 0xC004701A

Source: Import Datasynapse Data DTS.Pipeline

Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.

End Error

Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?

Since this is very urgent, immediate response would be very much appreciated.

Thanks & Regards,
Prakash Srinivasan

View Replies !

EXEC Inside CASE Inside SELECT
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.

@val1 and @val2 are passed in

CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)

INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2

SELECT COUNT(*) FROM #TEMP WHERE mybool = 1

If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.

Any suggestions?

View Replies !