Program Flow Question
Aug 27, 2007
I am trying to figure out how to make a report stop at a certain value. For example say I have five categories that I can assign an item. I would like to be able to have the report pull just the first category that applies to the item. For instance I have a bike that falls under categories 1 and 3 and a car the falls under 2, 3, and 5. How can I make it so the report will only show the details that apply to the first category the item falls under. Thanks.
View 2 Replies
ADVERTISEMENT
May 23, 2007
why we use sql exceptions ...
what the program will do if we caught that exception .. i need some suggestions ... i got this exception(String or binary data would be truncated.
The statement has been terminated.).. will it affect the functionality of the program...
hiow can i avoid this exception..
View 1 Replies
View Related
Apr 22, 2004
I'm writing my first serious stored procedure.
Essentially I have an incoming file, each line in the file is a record.
The records share the same initial key fields for the first 10 columns, then the field structure varies depending on a rectype and sequence number.
My initial plan was load the keys into fields, and load the remaining data into a long varchar field.
Then the stored procedure would evaluate the Rectype and Seqno of each record and chop up the Varchar accordingly.
So I set up a cursor to read the temporary table, do a fetch into variables, and go to evaluate the variables.
I want to be able to use a CASE statement to evaluate the fields and then perform various logic, but it's giving me fits because it seems like CASE only really works in Select statements, and won't really allow you to do any sort of GOTO logic.
I chopped the following SQL up and put in a rough cut of what I thought I was doing.
DROP TABLE #FOO
GO
CREATE TABLE #FOO (
[planno] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ssn] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[location] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eligdate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rectype] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[seqno] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[empno] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[payrollcode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companycode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[department] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filler] [char] (34) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[data] [varchar] (366) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
BULK INSERT #FOO FROM '\l32b0021foo.dat'
WITH (FORMATFILE = '\l32b0021foo.fmt')
declare @sessionid varchar(12)
declare @dedcode varchar(5)
declare @eeamt money
declare @eepct decimal
declare @effdt datetime
set @effdt = getdate()
set @sessionid = 'PENDING_BI'
DECLARE Transaction_Cursor CURSOR FOR
SELECT ssn,rectype,seqno,data
from #foo
OPEN Transaction_Cursor
declare @eepssn varchar(9)
declare @rectype varchar(3)
declare @seqno varchar(3)
declare @data varchar(366)
declare @eeccoid varchar(6)
declare @eeceeid varchar(12)
declare @eecempno varchar(9)
declare @companycode varchar(5)
declare @type varchar(20)
FETCH NEXT FROM Transaction_Cursor into @eepssn,@rectype,@seqno,@data
WHILE @@FETCH_STATUS = 0
BEGIN
select @companycode = cmpcompanycode,
@eeccoid = eeccoid,
@eeceeid = eeceeid,
@eecempno = eecempno
from company,empcomp
where eeceeid = (select eepeeid from emppers where eepssn = @eepssn )
and eecemplstatus = 'A'
and cmpcoid = eeccoid
CASE @RECTYPE+@SEQNO
when '001001' then goto parse_pretax
when '002001' then goto parse_LOAN
else select @RECTYPE+@SEQNO+' not recognized!'
end
process_it:
insert into foo2 (empno,companycode,amt,pct)
values (@eecempno,@companycode,@eeamt,@eepct)
FETCH NEXT FROM Transaction_Cursor into @eepssn,@rectype,@seqno,@data
END
CLOSE Transaction_Cursor
DEALLOCATE Transaction_Cursor
goto bypass
parse_pretax:
let @eepct = substring(@data,1,5)
goto process_it
parse_loan:
let @eeamt = substring(@data,27,11)
goto process_it
bypass:
I could sketch it out a little bit better in Northwind or Pubs, but I think I just need a smack upside the head and a little edification.
View 1 Replies
View Related
May 21, 2007
Can I return a value in a variable from a SSIS program back to C# after the SSIS program is run from C#?
View 1 Replies
View Related
Feb 14, 2006
Hi, All,
I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source.
I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source,
The SQL Looks like this:
select * from ccst_acctsys_account
where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >?
THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter.
Thanks in Advance
View 2 Replies
View Related
Mar 9, 2007
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.
I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.
Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?
Also, how would I update the original source table where source.id = objRects.id?
Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.
Thanks,
Steve
View 17 Replies
View Related
Jan 17, 2008
Dear All!
My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database.
Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any
Thanks
View 3 Replies
View Related
May 17, 2007
Hi everyone,
Primary platform is 64 bit cluster.
How to move information allocated in SSIS variables from Data Flow to Control Flow layers??
We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again????
Thanks in advance and regards,
View 4 Replies
View Related
Jan 12, 2006
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task.
Is there a way to do that using an SQL statement as the source of the value in a data flow?
OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
View 2 Replies
View Related
Jul 22, 2007
Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go:
A small control flow, with large data flow tasks
A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated.
Thanks,
Ricardo
View 7 Replies
View Related
Aug 29, 2007
Hello,
Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?
Thanks,
Yoann
View 15 Replies
View Related
Aug 29, 2001
i have a program which should give the out like this
XAPIALTMKT20010829105525.TXT
but its giving the output = 'X'
which is just the first initial...how
can i get this...
if i don't write the @filename=....
only then its giving the output..but i want to store it in the
varable..how can i do that
================================
declare
@v datetime ,
@filename varchar
select @v=getdate()
select @filename='XAPIALTMKT'
+CONVERT(varchar, @v, 112)
+SUBSTRING(CONVERT(varchar, @v, 108), 1, 2)
+SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)
+SUBSTRING(CONVERT(varchar, @v, 108), 7, 2)
+'.TXT'
print @filename
================================================
thanks,
Harish
View 2 Replies
View Related
Jul 23, 2005
It is very interesting to me that most people in this group can't do simpleSQL. Are you viewing MySQL as a simple file system?As long as you do that,you willnot understand SQL in any dialect. So from now on:When you have a question about SQL. post the table structures. Uh, "WithCreate".Post some sample data in the form of INSERTS.Regards,Rich--The journey is the reward.
View 2 Replies
View Related
Dec 28, 2007
Hi,
I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog:
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM.
However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots)
http://i248.photobucket.com/albums/gg168/boston_sql92/1.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/2.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/3.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/4.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/5.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/6.jpg
The same package works fine against a similar test table with 150k rows.
http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg
The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table.
Any hints,advice would be appreciated.
View 18 Replies
View Related
May 22, 2006
Hello, i installed :
SQL Server 2005 Express Edition with Advanced Services
Install Microsoft SQL Server 2005 Express Edition with Advanced Services
but I do not see any program in my program menu to open it. Is this a stand alone program I can open? Thanks
View 2 Replies
View Related
Oct 10, 2007
Hi All,
I am trying to send an email from a cmd prompt from SQL Server 2005 server using mailsend program. I am getting the message "Could not send mail". I am really stumped, don't know how to fix it. Any suggesstions?
Thanks.
View 3 Replies
View Related
Oct 9, 2007
Hi All,
I have installed SQL Server 2005 and created a job. I made the job failed to test the mailsend program. The notification step that uses the mailsend succeeded but I did not get an email. I am using mailsend program on SQL Server 2000 and it works fine. I am really stomped and don't know what to do to fix it?
Thanks.
View 9 Replies
View Related
May 14, 2008
I've have following table,
tblHIT - this table will reveive record every minutes
Date | Time | Main_ID | Hit
------------------------------------
3/1/2006 | 100 | 200 | 8700
...
...
...
4/14/2008 | 100 | 200 | 4500
4/14/2008 | 100 | 201 | 8700
4/14/2008 | 200 | 200 | 3500
4/14/2008 | 300 | 201 | 7700
...
...
I've also 1 job in SQL Server Agent will execute STORED PROCEDURE and will filter 7 days record before from current date and insert it into tblHIT_7days. tblHIT_7days shown as follow
tblHIT_7days
Date | Time | Main_ID | Hit
------------------------------------
3/7/2008 | 100 | 200 | 8700
...
...
...
4/13/2008 | 100 | 200 | 4500
4/13/2008 | 100 | 201 | 8700
4/13/2008 | 200 | 200 | 3500
4/13/2008 | 300 | 201 | 7700
I've plan to create TRIGGER. This TRIGGER will insert the appropriate record from tblHIT into tblHIT_7days and also delete a appropriate record in tblHIT_7days. This INSERT and DELETE transaction depend on current date.
Is it possible, TRIGGER can do this?
View 6 Replies
View Related
Oct 12, 2007
Hi,
In SQL Server is it possible to have a trigger that will throw an event which could be caught by C# program running in the back ground?
Is this even possible? If it is, have u any links to help/examples?
Regards!
View 4 Replies
View Related
Oct 26, 2006
I am about to build a program that will require the use of DB. In the pass I used Access as my back end, but this time I would like to use Sql Server. I just have a few questions before I begin.
1. After deploying and installing my program, do I need to have sql server installed on users system or can the DB file exist on its own and just use ADO.NET to access the file? (As I done in the pass to access my Access mdb file)
2. If I can deploy a sql file -- can it be done with Sql Server Express?
3. If it can't be done, what db program should I use?
View 3 Replies
View Related
Apr 15, 2008
On some occasions when trying to send data back to the server, the program will lock up for up to 30 minutes. The code below is the rda data transfer process and the function IsConnected() determines whether there is a connection.
If Not MainFrm.IsConnected Then
rda.ConnectionManager = True
End If
If MainFrm.IsConnected Then
Dim sCon As String = "Provider=SQLOLEDB;Data Source=175.21.21.121;Initial
Catalog=OurDb;User ID=UserId;Password=pwd"
rda.InternetUrl = "http://Ourwebsite.co.uk/tfr/sqlcesa30.dll"
rda.LocalConnectionString = "Data Source=Program FilesSysdata.sdf"
For i As Integer = 0 To RecCount - 1
Try
rda.SubmitSql(UpdateRec(i, 0), sCon)
Catch Ex As SqlCeException
ErrTxt = Ex.ToString
End Try
Next
Public Shared Function IsConnected() As Boolean
Dim request As HttpWebRequest
Dim response As HttpWebResponse
Try
request = CType(WebRequest.Create("http://www.intel.com/"), HttpWebRequest)
response = CType(request.GetResponse(), HttpWebResponse)
If response.StatusCode = HttpStatusCode.OK Then
response.Close()
Return True
Else
response.Close()
Return False
End If
Catch Ex As WebException
Return False
End Try
End Function
There are rda properties which determine a timeout. How can I use these to stop my program from locking up if the signal is weak?
View 1 Replies
View Related
Jan 4, 2007
Hi,
please have look into the code and let me know the solution plz.
string ProID;
try
{
using(SqlConnection conn=new SqlConnection(source))
{
conn.Open();
DataSet ds=new DataSet();
DDLProject.Items.Clear();
SqlCommand cmd=new SqlCommand("SP_ProjectSelect",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("Name",SqlDbType.NChar,30,"@Name");
cmd.UpdatedRowSource=UpdateRowSource.None;
cmd.Parameters["Name"].Value=DDLProductLine.SelectedItem;
SqlDataReader dr=cmd.ExecuteReader(); ------------->>>> Here iam getting Following Error.
while(dr.Read())
{ Response.Write(dr["ID"].ToString());
}
}
}
catch(System.Exception ex)
{
Response.Write(ex);
}
Error:
System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at MIS.UI.ResourceList.DDLProductLine_SelectedIndexChanged(Object sender, EventArgs e) in c:inetpubwwwrootdotnet pgmsmisui
esourcelist.aspx.cs:line 121
Any suggestion plzz,where went wrong.
Thanks in advance
Regards
Mahesh Reddy
View 2 Replies
View Related
May 15, 2007
I have sql server 2005 (full version) installed on my local machine, while the databases (on the server) I want to program against are in sql 2000. Could I program against sql 2000 databases using sql 2005? If yes, what do I need to know before I start doing that? Any suggestions are welcome.
View 1 Replies
View Related
May 29, 2008
I have written this Program For Updating the profile Information .At the Page_Load Event I read the profile information from the database and write it in the respective Textboxes .At the Button_Click event I have written a program to Update the data from the respective Textboxes.The program executes but doesnot updates .public partial class CreateProfile : System.Web.UI.Page{ SqlConnection con; SqlCommand com; string un = HttpContext.Current.User.Identity.Name; protected void Page_Load(object sender, EventArgs e) { con = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Profiledata.mdf;Integrated Security=True;User Instance=True"); com = new SqlCommand("select * from profiletable where username='" + un + "' ", con); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(com); SqlCommandBuilder cb = new SqlCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(ds); dt = ds.Tables[0]; Textname.Text = dt.Rows[0][1].ToString(); com.Cancel(); } protected void Button2_Click(object sender, EventArgs e) { con = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Profiledata.mdf;Integrated Security=True;User Instance=True"); con.Open(); com = new SqlCommand("update profiletable set name='" + Textname.Text + "' where username='" + un + "' ", con); com.ExecuteNonQuery(); com.Cancel(); con.Close(); }} Plz help me with this program........................
View 4 Replies
View Related
Apr 6, 2001
I need to create a program that will run Client Access to download data from the AS400 to a flat file, then run SQL DTS to import the data into a table for use by another software package.
I've created a bat file that does that using the CA RTOPCB command and the SQL DTSRUN utility. Problem is that it appears that I need to first check to see if there is a table in SQL and if there is to delete it. Otherwise, rather than overlay the existing data in the table it adds to it.
Is there a way to issue a SQL drop table in DOS? Or am I missing something that could be done in SQL?
The DTSRUN is using a local package and CA is using a transaction request.
View 1 Replies
View Related
Mar 8, 2001
does anyone know of tools that allow free format screen design, easily programmable data validation and prgramming for use with SQL. I am used to using 4GL languages and want to avoid low level programming. For example to fifind a record and display the details i am used to a line of code that would allow the data field to do the find on to be entered and the next line of code would say find fieldname using index name and the results displayed
please say yes !!!
View 2 Replies
View Related
Apr 3, 2003
I have this Visual Basic program that if you are already logged into the application, the program get the username and password automatically to run the program. If the user runs the program not logged into the application, the program prompts username and password.
I need to know the T-SQL statement that will get the username and password when the user is already logged into the application. Basically I have the username part but the password keeps coming back as NULL and will not allow program to run.
Please help.
Sharon
View 4 Replies
View Related
Sep 11, 2003
Hi,
I am in the middle of developing procedure for keyword search for our website.
Input parameter is a string consisting keywords in comma delimited format.
This is the example of data I get from business group which I use to populate my key_search table.
product_id keyword
1 Microsoft, training
1CA, DBA
1CA, developer
1CA, network admin
1AZ, DBA
1AK, developer
1MN, DBA
1MN, developer
1OH, developer
2Microsoft, training
2AZ, DBA
2AZ, developer
2IL, developer
2MN, DBA
2CA, developer
2NY, business analyst
2NY, DBA
2NY, developer
2NY, programmer
3Oracle, training
3finance
4Oracle demo
4logistic
4Oracle Financials
4Financial Analyzer
They have provided search string examples like 'Microsoft, DBA, CA'
or 'CA'
or 'Microsoft, developer' or 'training'
I have script ready to remove comma from the string and store words from the input string in a temporary table.
But this is the easiest part.
The Confusing part now for me is to write the optimal code for retrieving the exact match from the key_search table as there is no limit on how many words can be in the string, it can be 1, 2, 3 or more.
Any suggestions on how should I handle this search?
View 6 Replies
View Related
Jun 27, 2001
Hi,
My company has recently migrated our Informix database to SQL server 2K. The data is successfully migrated and the client are able to connect to the database. However, when trying to run a VB application to access the data, it keeps on prompting for SQL server login whenever an sql statement is executed. What could be the problem? We are using SQL server 2k on Windows 2000 and on the client Windows 95. Thanks!
View 2 Replies
View Related
Jun 11, 2004
Hi Techies,
I have a bcp which generates .txt file perfectly. I just wanted to know how can i generate a text file in distributed environment.
Assuming that my Sql Server is running in machine A. I wanted the bcp to generate in Machine B. What are the permission's i should give in order to generate it in Machine B.
Regards
--Tanveer
View 3 Replies
View Related
May 19, 2004
Hi,
Is there a stored proc or a function I can call from Query Analyzer to execute another .EXE and return when it's finished?
I'd like something like
SET @RET = sp_CallEXE('c:mypathmyprogram.exe myparameters')
(Except for the name of the function, there should be something that does this right? I mean, DTS does it already so it must be referring to a function or a SP.)
I want an answer to so that I can validate that everything went well (hence the @RET variable)
Thanks,
Skip.
View 1 Replies
View Related
Nov 24, 2005
Does anyone know where to purchase a good (or great) SQL hour scheduler program, and pulls up information from a SQL server and puts this information into a schedule (hourly schedule). I'm looking for something that will show the schedule that people work, the information off the SQL server needed is the "agent" name. If anyone could help me, that would be greatly appreciated.
Thanks.
View 1 Replies
View Related
Jun 2, 2007
my database is 4.1. When I attempt to install i get:
'/usr/local/mysql-5.0/data/mysql.sock'
To me this indicates 5.0
I'm stumped!!!!
All help is appreciated.
Thanks
View 1 Replies
View Related
