Hello,I have two tables, Articles and Comments, with the following columns:Articles - [ArticleId] (PK), [ArticleTitle], [ArticleText], [ArticlePubDate]Comments - [CommentId] (PK), [ArticleId] (FK), [CommentTitle], [CommentText]I need to display on a web page the articles published during the last week and their comments.What I need is:1. Get Articles (DONE) 2. Get Comments for each ArticleThe solution I see are:1. Create a Stored procedure that somehow outputs 2 tables: Articles and Comments associated with those articles2. Create 2 Stored procedures: The first one outputs the articles. The second output all comments given an article ID In this case, while the data is being displayed on the page it will load the comments for each article. The problem is that I will have many round trips to the server.I know how to use (2) but this would give me many round trips to the database.Could someone help me out with this?Thanks,Miguel
I have a SQL script file which creates a table and and inserts all data required by the client app into the table.
The table also holds images in a varbinary field. How do I insert an image held on the file system (e.g. "C:ImagesMyImage.gif") into a varbinary field using a script that is run using sqlcmd on the command line?
The MSDN Library topic Using the sqlcmd utility(SQL Server Express) states "To access the sqlcmd utility, click Start, click Run, and type sqlcmd.exe."
A command prompt window opens with "SQLCMD" in the title bar. I cannot enter anything in the command prompt window and the window remains open for only about five or six seconds.
The version I have installed is MS SQL Server 2005 Express with SP2. The cumulative updates package 3 does not list this problem, so I did not install it.
I am not sure if this has been asked before but I couldn't find any thread talking about this.
Let's say we have a parameter in the .sql input file called @Start_Date, how can we pass the value of a particular date, for example, "02-28-2007" to @Start_Date via SQLCMD? is it possible?
I'm trying to skip the need to write a simple windows application...if things can be achieved via dos command line, that will keep everything simple!
I have installed SQL Express 2005 and I want to create a new DB using sqlcmd.
I am running a command like: sqlcmd -i file.ini (I am logged like Administrator of PC)
the content of the file.ini is:
-- Get the SQL Server data path DECLARE @data_path nvarchar(256);
SET @data_path = 'c:Program FilesMyTestdatabase'
-- execute the CREATE DATABASE statement EXECUTE ('CREATE DATABASE DataCollection ON ( NAME = DC_dat, FILENAME = '''+ @data_path + 'DC.mdf'', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = DC_log, FILENAME = '''+ @data_path + 'DClog.ldf'', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )' ) ; GO
I obtain this error:
Msg 1802, Level 16, State 4, Server TESTQ5SQLEXPRESS, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Msg 5123, Level 16, State 1, Server TESTQ5SQLEXPRESS, Line 1 CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'c:Program FilesMyTestdatabaseDC.mdf'.
If I change the security of directory database (I add "modify" permission to Users windows group) the error disappear.
I am logged like Administrator... why is it necessary to change directory permission?
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
Hey everyone, I have been stuck on this for a few hours now and I think I am missing something simple. I have 2 tables. A Groups table and a GroupMembership table. Groups Table GroupID (PK) GroupName GroupDescription CreatedBy GroupMembership Table GMembershipID (PK) GroupID (FK) UserID(FK) I want to pull all the GroupNames, GroupDescriptions, and CreatedBy for all the groups the user is NOT in. Is this really simple and I am just missing it after hours of frustration? Or did I just set the database up badly? Thanks, Chris
I'm learning ASP.net, transitioning from ColdFusion. So far so go... but there is 1 thing that I can't seem to find a simple answer to. How can I do a simple output of a query resultset to the screen or to a label? In CF it would be simple like this: <cfquery name="getSomeData" datasource="somedatasource">SELECT somestuffFROM sometableWHERE something = something</cfquery> <cfoutput>#getsomedata.somestuff#</cfoutput> FYI: using the queryname.column returns only the first row in the CF query result I have seen examples with: label.text = somevarname which doesn't work for the query... but what about the query result? thanks
Is it possible to use stored procedure with output parameter and retrieves the values of that output parameter, in an OLE DB Command within a Data Flow?
What I wanted to do is to get the newly created identity of a row so that I can insert it to the main data set in data flow. I'm not even sure if there is even a much better design to achieve this. I've rummaged the internet but everything I got were all about Execute SQL Task.
Hi all, I am trying to do a very basic ALTER Command and am trying to change its DEFAULT value. Code below is what I currently have:
Code Snippet
ALTER TABLE Table_1
ALTER COLUMN TEST VARCHAR(1000) NULL DEFAULT 2
Thanks, Onam.
*UPDATE* I found this code but are there alternative methods? Additionally, if I was to update its DEFAULT value again how would I go about doing that? Do I first have to remove the CONSTRAINT and then run the command?
Code Snippet
ALTER TABLE Table_1 ADD CONSTRAINT DF_Albumns_Comment DEFAULT 2 FOR TEST
how to get the quantity from output of sql query command for example, I could get the container name by below command
select container_name from sysibmadm.snapcontainer get the container number by select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART
now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.
I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,
Select SerialNumber From SerialNumbers Where OrderID = @OrderID
If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.
I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.
However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.
I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).
I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped. Any light shed would be greatly appreciated. Many thanks in advance, Tamim.
Table A on Server 1 (3 ROWS) ID Name Address ID1 A B ID2 X Y ID3 M N
There is another table on a different server which looks like
Table B on Server 2 PKColumn ID Details 1 ID1 Desc1 2 ID1 Desc2 3 ID1 Desc 3 4 ID2 Desc 5 ID2 Description
As you can see the ID is the common column for these two tables, I want to get the Query the above 2 tables and the output should be dumped into a new table on Server2.
I am using the following SSIS Package
OledbDataSource-------> OledbCommand(Select * from TableB where ID =?)
From here, how can insert the rows returned from the oledb command into another table. Since, for each row of TableA it will return some output rows...How can I insert all these into the New Table.
Please help on configuring the output of the oledb command.
I have installed VS 2008 Beta 2 which includes SQL CE 3.5, (I have VS 2005 installed too).
I can not get any INSERT command to work!
First i have tried the VS wizard for a new connection and a dataset. but after inserting a row into one of the dataset tables and then updating it to SQL CE with tha data adapter, nothing happens! NO ERROR and NO inserted row in the database file! so the identity of the inserted row in dataset table doesnt get updated.
Then i tried a simple code without any dataset in a clean solution:
Code Snippet
Dim con As New SqlCeConnection("Data Source=|DataDirectory|MyDatabase#1.sdf") Dim cmd As New SqlCeCommand("INSERT INTO [Table1](Name) VALUES('TestValue')", con) Try con.Open() cmd.ExecuteNonQuery() Catch ex As SqlCeException MessageBox.Show(ex.Message) Finally con.Close() End Try NO ERROR! NO INSERTED ROW! Nothing happen!
We use a number of similar databases and frequently create a newdatabase using a backup restore of another similar database. We try tokeep changes between databases in _Additional tables - like AccountAdditional, Sale_Additional so most tables stay the same. The latestrestored database (I'll call it DBaseA) is behaving differently in VB6code and I need help trying to make it work.I have been using use an ADODB.Command to execute a stored procedureusing adCmdStoredProc and Parameters.Refresh to get an output parametervalue which is an integer. Here is a sample of the VB:Public Function UnlockAccount(Account_ID As String) As BooleanDim LCmd As ADODB.CommandOn Error GoTo ERROR_UnlockAccountUnlockAccount = TrueIf Account_ID <> "" ThenIf DBConnect() ThenSet LCmd = New ADODB.CommandLCmd.ActiveConnection = CN(0)LCmd.CommandTimeout = 0LCmd.CommandType = adCmdStoredProcLCmd.CommandText = "Unlock_Account"LCmd.Parameters.RefreshLCmd.Parameters("@Account_ID").VALUE = Account_IDLCmd.Execute , , adExecuteNoRecordsIf LCmd.Parameters("@Status") <> 0 ThenUnlockAccount = FalseEnd IfElsemsgbox "UnlockAccount: DBConnect Failed", "UnlockAccount"End IfEnd IfExit FunctionERROR_UnlockAccount:UnlockAccount = Falsemsgbox "UnlockAccount: App Error: " & Err & " " & _Err.Description, "UnlockAccount"DBConnect TrueExit FunctionResume'for debuggingEnd Functionand this is one of the sps that fails - can't be more simple!Create PROCEDURE Unlock_Account( @Account_ID UNIQUEIDENTIFIER,@Status INTEGER =null OUTPUT) AS/* Strip functionality */SET @Status = 0goThis code is still working in at least 4 other databases. It fails indatabase DBaseA with the error:Invalid character for cast conversionWe are talking about the exact same table definition and the exact samestored procedure just in different databases.I fumbled around on the Internet and found a mention in a PowerBuilderweb site of additional parameters in the connect string which seems tofix the problem in SOME of the stored procs - but not all.The added parameters are:DelimitIdentifier='No';MsgTerse='Yes';CallEscape='No';FormatArgsAsExp='N'They are not documented in MS but are in Sybase?? No idea why, but someof the stored proc functions work when I add this to the connect string.The complete connect string is:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBaseA;Data Source=PHASE2-S500,1433;Network Library=DBMSSOCN;DelimitIdentifier='No';MsgTerse='Yes';CallEscape='No';FormatArgsAsExp='N'The databases are on different servers - but they are running the sameversion of SQL2000, the same version of Windows. I see no differencesin options set in the different servers or between databases that workand this one. And dbcc checkdb runs clean.The compiled code that fails, fails across the board - not just on my(developer) PC. The same source code works on the same tables in otherdatabases, including the one we copied....The final kicker - if I build a SQL statement string and get therecordset instead, it works like a charm. But I have to fix about 20 VBfunctions and check that I do have a recordset and SET NOCOUNT ON in all20 sps.I feel this is either something so obvious I will kick myself or soserious Microsoft will be digging into it.Any ideas anyone??Sandie*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I want to make a very simple package: Export all rows in a table to a flat file. This package I can create pretty much by only using the wizards. Now to my problems:
H is a header post, in this case with date and time following. D is a details post, that is all the rows that was exported. E is and end post, containing only the number of rows in the file, including H and E posts.
2) I need to set the file name dynamically, preferably using date and time to name the file.
I´ve done this very same thing in T-SQL, like so:
Code Snippet USE AVK GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO SELECT * FROM tempProducts GO CREATE VIEW EXPORT_ORDERS AS SELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line UNION ALL SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5), Volume), '') AS Data_Line FROM dbo.tempProducts UNION ALL SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line FROM dbo.tempProducts AS tempProducts_1 GO IF @@ROWCOUNT > 0 BEGIN BEGIN TRANSACTION SELECT * FROM tempProducts DECLARE @date char(8) DECLARE @time char(8) DECLARE @sql VARCHAR(150) SELECT @date = CONVERT(char(8), getdate(),112) SELECT @time = CONVERT(char(8), getdate(),108) SELECT @time = REPLACE(@time,':','')
DECLARE @dt char(14) SELECT @dt = @date + '_' + @time SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla' EXEC master..xp_cmdshell @sql
--WAITFOR DELAY '0:00:10'; DELETE FROM tempProducts
COMMIT TRANSACTION END DROP VIEW EXPORT_ORDERS GO
But I´m sure it can be done in SSIS aswell, giving me some nice options for i.e. error handling aswell. Pointers please
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON
GO -- ============================================= ALTER PROCEDURE [dbo].[spPK] -- Add the parameters for the stored procedure here @varNSC varchar(4), @varNC varchar(2), @varIIN varchar(7), @varIMCDMC varchar(8), @varOut as int output AS Declare @varPK int set @varPK = 0 BEGIN
--This checks Method 1 --NSC = @varNSC --NC = @varNC --IIN = @varIIN begin if exists (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varPK = (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varOut = @varPK if @varPK <> 0 Return end
[There are some more methods here]
Return
END
How do I get at the output value?
I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.
I have a data flow and inside the data flow , i have a ole db source and ole db command task to execute an insert transaction ( SP).. i would like to save the error output if the insert didn;t happend into a error log table.. but when I darg an error output line ( red) to another ole db command to insert an error log , i can only see two columns( error code and error column) are available in OLEDB command advance editor related to errors.. this doesn;t tell you much information about the error.how can i grap the error reson(desc) as the error output and store into a erro log table? so that i can see what the problem is?
I've built a simple custom data flow transformation component following the Hands On Lab (http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en) and the Books Online (ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9/html/adc70cc5-f79c-4bb6-8387-f0f2cdfaad11.htm and ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9/html/b694d21f-9919-402d-9192-666c6449b0b7.htm).
All it is supposed to do is create an output column and set its value to the result of calling a web service method (the transformation is synchronous). Everything seems fine, but when I run the data flow task that contains it, it doesn't generate any output. The Visual Studio debugger displays it as yellow, with 1,385 rows going into it, but the data viewer attached to its output is empty. The output metadata looks just like I expect: all of my input columns plus the new column, correctly typed. No validation or run-time warnings or errors are reported.
I'll include the entire C# file below, which only overrrides the ProvideComponentProperties, Validate, PreExecute, ProcessInput, and PostExecute methods of the parent PipelineComponent class.
Since this is effectively a specialization of the DerivedColumn transformation, could I inherit from the class that implements the DC component instead of PipelineComponent? How do I even find out what that class is?
Thanks! Here's the code: using System; // using System.Collections.Generic; // using System.Text;
using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace CustomComponents { [DtsPipelineComponent(DisplayName = "GID", ComponentType = ComponentType.Transform)] public class GidComponent : PipelineComponent { /// /// Column indexes for faster processing. /// private int[] inputColumnBufferIndex; private int outputColumnBufferIndex;
/// /// The GID web service. /// private GID.WS_PDF.PDFProcessService gidService = null;
/// /// Called to initialize/reset the component. /// public override void ProvideComponentProperties() { base.ProvideComponentProperties(); // Remove any existing metadata: base.RemoveAllInputsOutputsAndCustomProperties(); // Create the input and the output: IDTSInput90 input = this.ComponentMetaData.InputCollection.New(); input.Name = "Input"; IDTSOutput90 output = this.ComponentMetaData.OutputCollection.New(); output.Name = "Output"; // The output is synchronous with the input: output.SynchronousInputID = input.ID; // Create the GID output column (16-character Unicode string): IDTSOutputColumn90 outputColumn = output.OutputColumnCollection.New(); outputColumn.Name = "GID"; outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 16, 0, 0, 0); }
/// /// Only 1 input and 1 output with 1 column is supported. /// /// public override DTSValidationStatus Validate() { bool cancel = false; DTSValidationStatus status = base.Validate(); if (status == DTSValidationStatus.VS_ISVALID) { // The input and output are created above and should be exactly as specified // (unless someone manually edited the persisted XML): if (ComponentMetaData.InputCollection.Count != 1) { this.ComponentMetaData.FireError(0, ComponentMetaData.Name, "Invalid metadata: component accepts 1 Input.", string.Empty, 0, out cancel); status = DTSValidationStatus.VS_ISCORRUPT; } else if (ComponentMetaData.OutputCollection.Count != 1) { this.ComponentMetaData.FireError(0, ComponentMetaData.Name, "Invalid metadata: component provides 1 Output.", string.Empty, 0, out cancel); status = DTSValidationStatus.VS_ISCORRUPT; } else if (ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count != 1) { this.ComponentMetaData.FireError(0, ComponentMetaData.Name, "Invalid metadata: component Output must be 1 column.", string.Empty, 0, out cancel); status = DTSValidationStatus.VS_ISCORRUPT; } // And the output column should be a Unicode string: else if ((ComponentMetaData.OutputCollection[0].OutputColumnCollection[0].DataType != DataType.DT_WSTR) || (ComponentMetaData.OutputCollection[0].OutputColumnCollection[0].Length != 16)) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "Invalid metadata: component Output column data type must be (DT_WSTR, 16).", string.Empty, 0, out cancel); status = DTSValidationStatus.VS_ISBROKEN; } } return status; }
/// /// Called before executing, to cache the buffer column indexes. /// public override void PreExecute() { base.PreExecute(); // Get the index of each input column in the buffer: IDTSInput90 input = ComponentMetaData.InputCollection[0]; inputColumnBufferIndex = new int[input.InputColumnCollection.Count]; for (int col = 0; col < input.InputColumnCollection.Count; col++) { inputColumnBufferIndex[col] = BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[col].LineageID); } // Get the index of the output column in the buffer: IDTSOutput90 output = ComponentMetaData.OutputCollection[0]; outputColumnBufferIndex = BufferManager.FindColumnByLineageID(input.Buffer, output.OutputColumnCollection[0].LineageID); // Get the GID web service: gidService = new GID.WS_PDF.PDFProcessService(); }
/// /// Called to process the buffer: /// Get a new GID and save it in the output column. /// /// /// public override void ProcessInput(int inputID, PipelineBuffer buffer) { if (! buffer.EndOfRowset) { try { while (buffer.NextRow()) { // Set the output column value to a new GID: buffer.SetString(outputColumnBufferIndex, gidService.getGID()); } } catch (System.Exception ex) { bool cancel = false; ComponentMetaData.FireError(0, ComponentMetaData.Name, ex.Message, string.Empty, 0, out cancel); throw new Exception("Could not process input buffer."); } } }
/// /// Called after executing, to clean up. /// public override void PostExecute() { base.PostExecute(); // Resign from the GID service: gidService = null; } } }
hello it seems lack of c# skills is getting the better of me here...iv been trying to get this to work for hours now without success. I have just started working in c# so i am a beginner :) all i want to do it query the database, check to see if a title exists, if yes then populate textbox saying 'title in use' but if the title doesnt exists then continue executing rest of code to add the title. You'll notice iv tried to use string variables and sqlDataAdapter but i cant get these to work either...please help so far i have this that keeps giving me the error that my button click is undefined: protected void bookButton_Click(object sender, EventArgs e){ //string title;try{SqlConnection conn1 = new SqlConnection(); conn1.ConnectionString = "Data Source=Gemma-PC\SQLEXPRESS;" +"Initial Catalog=SoSym;" + "Integrated Security=SSPI;";SqlCommand findTitle = new SqlCommand("SELECT title FROM Publication WHERE title='" + titleTextBox.Text + "';", conn1);
conn1.Open(); findTitle.ExecuteNonQuery(); /* DataSet myDataSetTitle = new DataSet("PublicationTitle"); myDataSetTitle.Clear(); myDataAdapterTitle.Fill(myDataSetTitle);//myDataSet contains results from above SELECT statement title = myDataSetTitle.Tables[0].Rows[0]["title"].ToString();*/ }catch(Exception ex) {TitleInvalidMessage.Text = "Title Not Accepted: This title is already in use by another publication" +ex.Message; } //title does not exist in table so continue and add to database //rest of code here
I created a SSIS package that has a flat file manager whose connection string is a package variable, the reason is that there is a foreachfile container loop to loop thru CSV format files in a directory and load them to a sql table.
The package execution is working in the designer studio as well as in the management studio-- a copy was saved to a sql 2005 server where a sql job is deployed to run the package. However when I ran the job, it claims successful but doesn€™t do anything in reality. If I checked the box failing the package upon validation warming, the job will fail with the error: the command line parameters are invalid. The command line looks like below:
One thing that I think maybe wrong is on the data source tab of the job step GUI, the flat file manager€™s connection string is blank, compared to other connection managers having related values.
I am trying to select a particular record from a table and for some reason it only returns 1 record when I know there are at least 6. I've tried Rtrim and left to make sure that I'm getting the exact string. The "MyID" field is varchar(32) and the "ExpireDate field is datetime and I have tried the below. Any ideas what could be wrong?
Select DISTINCT K.MyID, KP.[EXPIREDATE] --, K.ACTIVATIONKEY From [KEY] K INNER JOIN KEY_PRODUCT KP ON K.MyKey = KP.MyKey Where K.MyID = '013BEB73C2CF11D39F3600105A05264C'
I have a simple select statement that I am having a complex with. I am a C# developer trying to expand and gain a little more knowledge of SQL. I want to send a string to a SQLCommand catagory = 'news,alerts,events' and return a result set if it matches any term.
This is for a blog I am creating for a church and the catagory is like the tags you normally see. I could use something like:
SELECT GUID,CATAGORY,DESCRIPTION,TITLE,ETC FROM RSS_FEEDS WHERE CATAGORY LIKE 'news,alerts,events'
I can seperate the keywords with any character if needed.
WHERE (ITEM_CATEGORY LIKE '%news,events%')
This only returns ones that have all like what is there not the ones having news only
Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks
Hi I Have the following table SequenceNumber___TypeID8_________________IMG7_________________IMG6_________________IMG5_________________IMG4_________________IMG3_________________IMG2_________________FLP2_________________IMG I want to pull the data out in the following format, SequenceNumber___TypeID8_________________IMG2_________________FLP This basically shows the highest SequenceNumber of each TypeID, I've tried many different SQL queries but I can't seem to get it! Any ideas?
