Transact SQL :: Back Fill Blanks With Data In The Same Row
May 8, 2015
I have a small question here is below how my data look
K_ID ColA ColB
ColC ColD
1 X12134 ABCD
' ' 1/1/2015
2 X12134 ABCD
' ' 2/1/2015
3 X12134 ABCD
DE02635 3/1/2015
I want the output like this
K_ID ColA ColB
ColC ColD
1 X12134 ABCD
DE02635 1/1/2015
2 X12134 ABCD
DE02635 2/1/2015
3 X12134 ABCD
DE02635 3/1/2015
I tried using Over by, Lag functions.. But some where i am missing the logic in achieving the Output...
View 28 Replies
ADVERTISEMENT
Jul 17, 2015
Table 1:
------- ----- ---- ----
Name Add No RowID
------- ----- ---- -------
aa #a-1,India 10
bb #a-1,India 11
aa #a-1,India 12
----------------------------------------------------
table 1 inserting to Table 2 (Using 1st Data flow)
Table 2:
------- ----- ----
Name Add ID(Note:Here Identity1,1)
------- ----- ----
aa #a-1,India 1
bb #a-1,India 2
aa #a-1,India 3
----------------------------------------------------
My Requirement is Update Table 1 set Column::No=Table 2.ID
based on Exact Match of
Table1.Name=Table2.Name and
Table1.Add=Table2.Add
It means Get back the Id for Source Table 1
2nd Data flow
Source(Table1:Name, Add,No)
|
--LOOKUP(Table2:Name, Add::Matched Look Columns Name, Add and
Tick Mark on ID)
|(Match)
-->OLEDB Command: update Table1 set N0=? where RowID=?(Here Param_0= NO ,Param_1=RowID)
Here My Issue is if Table 1 had Duplicates(same Name, Add, but Row Id is different it is Updating Same ID for Table 1.No It means Get Back ID correctly not updating Result::
Table 1:
------- ----- ---- ----
Name Add No RowID
------- ----- ---- -------
aa #a-1,India 1 10
bb #a-1,India 2 11
aa #a-1,India 1 12
----------------------------------------------------
My correct Output is 3 instead of Result:Table1 1.NO 1 where RowID =12
It caused by LOOKUP , It picking Top1 ID while Matching Look on fields.
How Should I update the (Identity) Get Back Table 2.ID to Source Table1. NO in Above logic in SSIS?
View 11 Replies
View Related
May 1, 2014
I am try to see if there are any blanks in a node of a table that has xml data in one of the columns. The query I use is returning zero results.
Select COUNT(*)from ENTITY
Where CONVERT(XML, Ent_root_xml, 0 ).value('(//UD_PQ_FLAG/node())[1]', 'VARCHAR(50)')= ''
View 1 Replies
View Related
Apr 3, 2008
I have a comma delimited data file TestData.txt of the form:
123,asss,aweqrrr ,ssdsff , ,2wwwrwrr
434,sff,dgfdgd ,sffsfsfete ,sd ,dff
I want to load this data into SQL Server tables so that the data will be imported without the trailing spaces(blanks) after each data element .
Thus I will like to trim the data of the extra spaces (blanks) before import.
Desired data to be loaded :
123,asss,aweqrrr,ssdsff,,2wwwrwrr
434,sff,dgfdgd,sffsfsfete,sd,dff
Any help will be most welcomed
View 4 Replies
View Related
Oct 19, 2007
Hi,
With DTS, the default behavior is to import blanks ("") in a data file as nulls in the table.
However, in SSIS, the default behavior is to import blanks as blanks.
I need to ensure that the packages I'm migrating import the data in the exact same way. I am wondering what is the easiest way to convert blanks to nulls?
Right now I'm using a script to convert the blanks to nulls before they are written to the table, but just wondering if there's an easier way.
Thanks
View 30 Replies
View Related
Apr 2, 2008
I hope someone has seen this one...I've got a very simple web method that returns a DataTable from my database. It worked fine until I changed this line of code:
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? ORDER BY Timestamp desc";
To this line of code:
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";
Here is the complete webmethod:
[WebMethod]
public DataTable GetConfession (int ConfessionID, int CategoryID) {
DataTable dt = new DataTable();
dt.TableName = "XMLConfession";
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";
using (OleDbConnection cn = new OleDbConnection (ConnectionString)) {
using (OleDbCommand cmd = new OleDbCommand (SqlString, cn)) {
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue ("@ConfessionID", ConfessionID);
cmd.Parameters.AddWithValue ("@CategoryID", CategoryID);
cn.Open();
OleDbDataAdapter da = new OleDbDataAdapter (cmd);
da.Fill (dt);
}
}
return dt;
}
To cause the error, all I did was add the AND statement into the query. Now I get this .NET error message every time I try to run this program:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at System.Data.Common.UnsafeNativeMethods.ICommandWithParameters.SetParameterInfo(IntPtr cParams, IntPtr[] rgParamOrdinals, tagDBPARAMBINDINFO[] rgParamBindInfo) at System.Data.OleDb.OleDbCommand.ApplyParameterBindings(ICommandWithParameters commandWithParameters, tagDBPARAMBINDINFO[] bindInfo) at System.Data.OleDb.OleDbCommand.CreateAccessor() at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior behavior, Boolean throwifnotsupported) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
I am using SQL Server Compact Edition 3.5 and C# .NET 2.0. It's worth noting that the web service does this outside of the program as well, so I don't think the problem is in the code that calls it. Also worth noting is that the query itself works fine when I substitute values in and run it in .NET Server Explorer.
Thanks in advance for any assistance.
View 5 Replies
View Related
May 2, 2007
Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!
View 2 Replies
View Related
Jul 8, 2013
I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
So I have 2 tables for products in the system
products
+-----------+------------+
|productid |productname |
|Int |varchar(50) |
+-----------+------------+
productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int |Int |Text |Money |Int |
| |FK_From_ | | | |
| |productid_| | | |
| |products | | | |
+---------+----------+------------+------+------+
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock]
AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock
FROM tab_productdetails INNER JOIN
tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
View 1 Replies
View Related
Oct 5, 2015
how can I dynamically set up the period between now and one year before in a query like this:
where cast(LEFT(period_start,8)as DATE)>='2014-10-01'
and cast(LEFT(period_end,8)as DATE)<='2015-09-30' and
Practically in the second or third of every month I need to run the period late of one year.
View 6 Replies
View Related
Jun 5, 2015
I have 3 requests to do and all 3 are below in SQL , i am planning to keep them as job and run on regular intervals as per request ..
1.Poll the contract approval table for no prepared new contracts. Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE ContractPrePared>DATEADD(MINUTE, -30, GETDATE()))
BEGIN
SEND DBMAIL ---i have this part working already
END
2.Poll the contract approval table for no new signed contracts. Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE DateSigned>DATEADD(MINUTE, -30, GETDATE())
BEGIN
SEND DBMAIL ---i have this part working already
END
3.Poll the lead table for no new leads. Check every 5 minutes. 24/7.
IF NOT EXISTS (SELECT * FROM Lead WITH ( NOLOCK ) WHERE DateCreated > DATEADD(MINUTE, -5, GETDATE()))
BEGIN
SEND DBMAIL ---i have this part working already
END
I am planning to keep 1,2,3, in seperate sp's and run them as JOBS....how can i achieve below a,b ?
a.The procedures should accept a parameter that indicates the interval, in minutes, that they should look back
b.Each step in 3 jobs should invoke the procedures, specifying the interval with which the job runs at
View 7 Replies
View Related
Oct 28, 2006
Can anyone explaing briefly, why would this error appears???
Thank you
View 6 Replies
View Related
Jun 19, 2015
I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to send
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.
View 5 Replies
View Related
Oct 28, 2006
Hello guys! I am relavtively new to ASP.NET programming ang was just starting out on my first project. I am using ASP.NET2.0 technology by using Visual Web Developer 2005 Express Edition and of course with SQL 2005 Express Edition. I would like to develop a database for our IP addresses, so one field of my table in a SQL data is the field for IP addresses.I would like to write a program wherein after clicking the button, that field will be automatically filled up with IP addresses (e.g, from 192.168.0.0 to 192.168.0.255).How do I accomplish this kind of dynamic filling up of fields? Thanks a lot!
View 1 Replies
View Related
Jul 14, 2014
I am working with some data that is not so clean. I have some rows that are Very close to being a duplicate row. I need to take all the rows with the same SKU number and fill in the missing data for the Price, Cost or the SalePrice, what ever may be missing. The data is set like @Diamonds MissingData and I need the row to look like @DiamondsWithData table. I can remove the duplicate values once I get the missing data filled in.
DECLARE @DiamondsMissingData TABLE
(
SKU varchar(20)
,VendorNumber varchar(20)
,VendorSKU varchar(20)
,GradingReportType varchar(20)
,GradingReportNumber bigint
[code]....
View 3 Replies
View Related
Jan 30, 2008
I want to fill text box using sqldatasource ...
how can i do that ??
thanx in advace ..
View 10 Replies
View Related
Aug 7, 2015
I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table
if the update is successful or not. What i'm looking for is:
1) Is there a way to identify what records were updated.
2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.
I'm on sql server 2008.
View 34 Replies
View Related
Jun 11, 2007
Hi,
I was wondering if one could manage the color of the data series in line and bar charts much like what you could do in Excel?
Even changing the default order of the colors would be nice, just so different charts actually look...well, different.
Thanks!
View 2 Replies
View Related
Feb 19, 2008
Ok, so I can connect to the database without any errors, however im not sure about the syntax for filling a table. Heres what i have so far in the pageload. Like i said this all works with out any errors. Thanks in advance for the help.testDS = New DataSet()
testDataTable = New DataTable("Tbl")testDataTable.Columns.Add("username")
testDataTable.Columns.Add("datecompleted")testDataTable.Columns.Add("lastfive")
testDS.Tables.Add(testDataTable)Me.dgrdSearch0.DataSource = testDS.Tables("Tbl")
Dim Conn As Object = Server.CreateObject("ADODB.Connection")
Dim strConn = "DRIVER={SQL Server};SERVER=serverName;UID=userID;PWD=password;DATABASE=net"Dim DSNtest As String = strConn
Dim sql As String = "SELECT * FROM Tbl"
Conn.open(DSNtest)
Conn.close()
View 1 Replies
View Related
Apr 9, 2008
HI
I need help
how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist
my controls are as follows
<asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>"
SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee] where CurrentEmployee_YN=1 order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%;
border-collapse: collapse; height: 32px; visibility: hidden;" id="table0">
<tr>
<td style="width: 159px; visibility: hidden;">
</td>
<td style="width: 170px">
</td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Order No：</strong></td>
<td bgcolor="#eeeddb" style="width: 26%; height: 25px">
<asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Account No：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo"
Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Today's Date:</strong></td>
<td>
<asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Travel Consultant：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant"
Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td>
</tr>
</table>
<center>
</center>
<center>
</center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%;
border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2">
<tr>
<td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px">
<font color="#000000" size="5">Enter Passenger(s) Details</font></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Surname：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 20px">
<strong>
Name：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 20px">
<asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name"
Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Initials：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox>
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Title：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="156px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Prof"></asp:ListItem>
<asp:ListItem Value="Min"></asp:ListItem>
<asp:ListItem Value="Other"></asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1"
Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Department</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Department" runat="server"></asp:TextBox></td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Cost Centre</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Tel：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Fax：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
</table>
cost centre and department are from cost table
and the rest are from employee table
View 10 Replies
View Related
Mar 7, 2007
I thought I'd post this quick problem and answer, as I couldn't find the answer when searching for it.
I tried to call a stored procedure on SQL Server 2000 using the System.Data.SqlClient objects, and was not expecting any unusual issues. However when I came to call the Fill method I received the error "Arithmetic overflow error converting expression to data type int."
My first checks were the obvious ones of verifying that I'd provided all the correct datatypes and had no unexpected null values, but I found nothing out of order. The problem turns out to be a difference on the maximum values for integers between C# and SQL Server 2000. Previously having hit issues with SQL Server integers requiring Long Integer types in VB6, I was aware that these are 32-bit integers, so I was passing in Int32 variables. The problem was that Int32.MaxValue is not a valid integer for SQL Server. Seeing as I was providing an abitrary upper value for records-per-page (to fetch all in one page), I was simply able to change this to Int16.MaxValue and will hit no further problems as this is also well beyond any expected range for this parameter.
If anyone can name off the top of their heads what value should be provided as a maximum integer for SQL Server 2000, this might be a useful addition, but otherwise I hope this spares others some hunting if they also experience this problem.
James Burton
View 1 Replies
View Related
Jan 19, 2007
It's well known issue, that one can't use any dataset fields in a
report header/footer directly. One of the approach is to create
query-based parameter that basically equals
=First(Fields!@FieldName@.Value, "@DataSetName@") and use that
parameter value instead. But it doesn't work in my case!
My report displays some entity description and is parametrized with
EntityID param. Its header contains entity name that, according to the
approach, is queried from the data source through the EntityName
report parameter. There's important issue: the report is displayed in
ReportViewer control, that is embedded into my application and entity
ID parameter isn't ser by user in ReportViewer parameters area. Its
default value is changed by the application with SetReportParameters()
web method every time a user wants to view the report according to the
entity the user is exploring in the application. But after the report
has been rendered, its header always contains not actual (outdated)
entity name. Nevertheless, the report body contains actual data
(including entity name). If I alter entity ID parameter in ReportViewer
or in web-based Report Manager and refresh report, header displays
correct entity name.
What's wrong in the workflow described?
View 3 Replies
View Related
Apr 24, 2007
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies
View Related
May 14, 2015
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
View 2 Replies
View Related
Jun 9, 2015
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies
View Related
Aug 31, 2005
Nevil Mascarenhas writes "Hi SQL team,
I am just a beginer in SQL
Here is sample output of a SQL query
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD
5453 QWE 22-08-05 01:00
In this above example, I would like to replace blank fields in Startdate and Starttime coloumn with XXX
Output required is
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD XXX XXX
5453 QWE 22-08-05 01:00
Can you please guide me."
View 1 Replies
View Related
Jul 26, 2006
I wrote a simple data flow with an OLE DB source and destination. I do a direct mapping of the columns ( colA - > colA) with no transformations needed. I found that colA on the destinatin does not allow NULLS (required by the program that accesses that database) while colA on the source supports and has NULLs, Is there any accomodaiton for handling NULLS (like mapping them to blanks) in the direct copy approach or do I need to read each row and test colA_ISNull?
TIA,
barkingdog
View 3 Replies
View Related
Jan 18, 2006
Quick Question I hope.
Does having a lot of blanks in a column cause errors ?
In 2 or 3 packages where I get 95% of the data in the Error Colomn I can find nothing wrong with the data at all ? it all either seems perfect or there is no data in the column in question usually I would have maybe 800 rows where data would have been inserted but in the other 40, 000 rows the column is blank
Using the "Retain null values from the source as null values in the destination" doesnt seem to make a differance.
This is the error description
The data value cannot be converted for reasons other than sign mismatch or data overflow.
Anyone know of a solution / reason why this keeps happening
Thanks
View 12 Replies
View Related
Aug 16, 2013
Here after the Union, the col. WWDTA should have data but its' coming back blanks, but all cols are aligned. why is this?
SELECT
ALL T01.OHORD#, T01.OHSLR#,T01.OHORDT, T01.OHORDD,
T01.OHTTN$, '' as WWDTA
FROM ASTDTA.OEORHDOH T01,
ASTDTA.OETRANOT T02
WHERE T01.OHORD# = T02.OTORD#
[Code] ....
View 2 Replies
View Related
Mar 5, 2004
I posted this scenario earlier and ndinakar said my code worked on (I guess) his own machine but has not worked on mine! Does that not sound funny? I need to ge through with this simple but frustrating stuff in time. I've implemented more complex snippets before this but I just can't figure the problem out.
Can anyone out there tell me what I could be doing wrongly? I wrote a simple stored procedure called 'proc_insert_webuser' in a database that has been moved to a remote server called LAGOS-NTS3. I executed it from SQL Query Analyser and it worked. When, however, I executed the same stored procedure from ASP.Net it gives an impression that a new record has been inserted and even displays the newly generated ID column to me. On checking the base table I found only blank columns with only the ID column having the generated IDs!
I'm perplexed. I checked the code and can't possibly spot the error. Can anyone help. Found below are snippets used.
CREATE PROCEDURE proc_insert_webuser
(
@applicantidnumeric(18) output,
@firstnamevarchar(18),
@midname varchar(18),
@lastname varchar(50),
@secretcodevarchar(18),
@my_errnumeric(18) output
)
AS
IF (SELECT COUNT(ApplicantID) FROM tbl_webuser WHERE Firstname = @firstname AND Midname = @midname AND Lastname = @lastname) = 0
BEGIN
begin transaction
INSERT INTO tbl_webuser (Firstname, Midname, Lastname, SecretCode)
VALUES (@Firstname, @Midname, @Lastname, @SecretCode)
commit transaction
SET @applicantid = @@IDENTITY
END
ELSE
BEGIN
RAISERROR('Error! Execution aborted. A matching user profile exist.', 16, 1)
SELECT @my_err = @@ERROR
END
RETURN
Then the ASP.Net stuff:
Sub PostData()
'connection string production
Dim cstring As String = "User ID = sa; Password = ; database = E-Recruitment; Server = LAGOS-NTS3; Connect Timeout = 60"
'connection instantiation
Dim cnx As SqlConnection = New SqlConnection(cstring)
Try
'open connection
cnx.Open()
'instantiates and execute a command object
Dim cmd As SqlCommand = New SqlCommand
With cmd
.Connection = cnx
.CommandText = "proc_insert_webuser"
.CommandType = CommandType.StoredProcedure
Dim param1 As New SqlClient.SqlParameter("@applicantid", 0)
param1.DbType = DbType.Single
param1.Direction = ParameterDirection.Output
.Parameters.Add(param1)
Dim param2 As New SqlClient.SqlParameter("@firstname", txtFirstNM.Text)
param2.DbType = DbType.String
param2.Direction = ParameterDirection.Input
.Parameters.Add(param2)
Dim param3 As New SqlClient.SqlParameter("@midname", txtMidNM.Text)
param3.DbType = DbType.String
param3.Direction = ParameterDirection.Input
.Parameters.Add(param3)
Dim param4 As New SqlClient.SqlParameter("@lastname", txtLastNM.Text)
param4.DbType = DbType.String
param4.Direction = ParameterDirection.Input
.Parameters.Add(param4)
Dim param5 As New SqlClient.SqlParameter("@secretcode", txtPWD.Text)
param5.DbType = DbType.String
param5.Direction = ParameterDirection.Input
.Parameters.Add(param5)
Dim param6 As New SqlClient.SqlParameter("@my_err", 0)
param6.DbType = DbType.Single
param6.Direction = ParameterDirection.Output
.Parameters.Add(param6)
.ExecuteNonQuery()
'pick the auto-number
Dim strAppID = .Parameters("@applicantid").Value
'notifies the user of record success
lblMessage.Text = "Profile created. Your ApplicantID is : " & strAppID & " Kindly take note of your UserID. You'll need it for future logins."
End With
Catch ex As Exception
'notifies the user of any error(s)
lblMessage.Text = Err.Description & " originating from " & Err.Source
Finally
cnx.Close()
End Try
End Sub
==================
I'm calling this procedure from the click event of a command button. What could be happening to the code? I have done similar things and got positive results! Any hint from anyone out there?
View 6 Replies
View Related
Mar 10, 2000
A table was created in version 6.5 that has 2 blank spaces and a slash '/' in it's name (i.e. Item Type w/Groups). This was done in error and now the table cannot be dropped or renamed and dropped.
Does anyone know how I can delete this table? Please let me know.
View 1 Replies
View Related
Dec 6, 2004
Hi,
I'm trying to create a view from a table in which I want to concatenate to columns to a new column. The concatenation part works fine when data "supports" calculations, i.e. no nulls. The data however is "infected" in two ways I have Null-values and I have blanks alongside with some real information. I concatenate via a CASE statement but I can't figure out how to take the 3 situations into account (nulls, blanks and data) and create one meaningfull column in my view.
The result should be
column1 + "-" if column2 is null or blank
column1 + "-" + column2 if column2 has meaningfull data
Both the involved columns are text (varchar).
If anyone has been in this situation before could you please provide som code that handles the situation.
Regards
Kim Hansen
View 2 Replies
View Related
Mar 11, 2008
Hello,
In my .NET.ASP application I generate a random five digit password, like so:
string password = Membership.GeneratePassword(5, 1);
The result could be a password like "0l$IE" and I the save this password to an SQL server. The problem is however that with the generated password five blanks are also added. Instead of "0l$IE" I get "0l$IE ". I hope you understand what I mean? Is there an easy way to correct this?
I appreciate any help!
View 6 Replies
View Related
Feb 27, 2008
I have an Excel spreadsheet with a field that may contain blanks (empty). When I filter on that field to show only the blanks, I get 4000 records returned. But if I import the spreadsheet into SQL Server, the test in there for blanks ('') returns nothing. However, if I test for Nulls in that field, I get 5000 records returned. I don't know which one (Excel or SQL Server) is giving me the right answer. Can anyone help me understand what's going on?
Thanks!
View 1 Replies
View Related
