Showing posts with label working. Show all posts
Showing posts with label working. Show all posts

Thursday, March 29, 2012

Adding a step to CreateUserWizard

Hey all,

I'm working in Visual Web Developer 2005 Express. I'm a total newb so please bare with me. I'm trying to add a step to my CreateUserWizard that I put in using the automatic site administration utility. I've added a wizard step in between the "Sign up for your new account" step and the "complete" step. The purpose of the new step is to collect additional information about the user. I've added a FormView control and used an SQLDataSource to link it to a new table in the database established by the administration utility, and set up the templates to recieve the data.


The one kink in the process seems to be associating the UserID created in step one with the additional information submitted in step 2. I made a UserID column, of data type uniqueidetifier in my new table and set up a foreign key relationship with the UserID columns in the aspnet_Membership table and the aspnet_Users table. When I run it as is I get the following error after I try to insert the data entered in the second step (additional information) into my new database table:

Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query

I guess my question is:How do I take the UserID generated in step 1, and pass it into the new table and into the new row being created in step 2? If possible, I would like to do it without any code (haha probably not going to happen, but worth a shot) but any assistance anyone could offer would be greatly appreciated. Thanks everyone.

Matt Downey

You can grab the userID like this

Membership.GetUser(CreateUserWizard1.UserName).ProviderUserKey

where createuserwizard1 is the ID of the 1st step of your wizard.

Tuesday, March 20, 2012

Added SP2 now have linked server error

I added SP2 to my 2005 server and now I can't connect to a previously working
linked Oracle server. I get this:
TITLE: Microsoft SQL Server Management Studio
--
"The test connection to the linked server failed."
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "ISRSREPLICATEDLINK".
OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
message "Error while trying to retrieve text for error ORA-01019
". (Microsoft SQL Server, Error: 7303)
What could have changed? Testing the connection via the ODBC works but
inside of SQL MAnagement Studio it fails.Also somewhere in troubleshooting this I saw an error about unable to
allocate user side memory.
"TLuebke" wrote:
> I added SP2 to my 2005 server and now I can't connect to a previously working
> linked Oracle server. I get this:
> TITLE: Microsoft SQL Server Management Studio
> --
> "The test connection to the linked server failed."
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "ISRSREPLICATEDLINK".
> OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
> message "Error while trying to retrieve text for error ORA-01019
> ". (Microsoft SQL Server, Error: 7303)
> What could have changed? Testing the connection via the ODBC works but
> inside of SQL MAnagement Studio it fails.
>
>|||TLuebke,
Have you tried reinstalling your Oracle provider? I did some research, but
no clear indication of what your problem is. One interesting thing is that
http://technet.microsoft.com/en-us/library/bb332055.aspx suggests using the
Oracle provider for OLE DB (OraOLEDB.Oracle.1) rather than MSDAORA, in case
that is useful to you.
RLF
"TLuebke" <TLuebke@.discussions.microsoft.com> wrote in message
news:57778530-E08D-46F2-ADD9-0D7D31236C74@.microsoft.com...
> Also somewhere in troubleshooting this I saw an error about unable to
> allocate user side memory.
> "TLuebke" wrote:
>> I added SP2 to my 2005 server and now I can't connect to a previously
>> working
>> linked Oracle server. I get this:
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> "The test connection to the linked server failed."
>> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
>> linked server "ISRSREPLICATEDLINK".
>> OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
>> message "Error while trying to retrieve text for error ORA-01019
>> ". (Microsoft SQL Server, Error: 7303)
>> What could have changed? Testing the connection via the ODBC works but
>> inside of SQL MAnagement Studio it fails.
>>
>>

Added a column to my SQL database and now text field will not write to it. Why

I have a web form that is an interface for a database. The code was working fine until a field needed to be added. So I added the new field, updated the data adapter and data set, and when i enter the data into the field, all of the old fields are getting updated, but the new ones aren't. I am at wits end as to why. Can someone please help!!!

I am using VS Studio 2000 ASP.NET and C#. The code is below. The bold items are the new fields.

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace TrafficDept

{

///<summary>

/// Summary description for AddOwner.

///</summary>

publicclass AddOwner : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Button Button1;

protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator3;

protected System.Data.SqlClient.SqlConnection sqlConnection1;

protected System.Web.UI.WebControls.TextBox TextBox1;

protected System.Web.UI.WebControls.TextBox TextBox2;

protected System.Data.SqlClient.SqlDataAdapter daAddOwner;

protected System.Web.UI.WebControls.TextBox tbOtherOwnerLName;

protected System.Web.UI.WebControls.Panel namePanel;

protected System.Web.UI.WebControls.TextBox tbOtherOwnerMI;

protected System.Web.UI.WebControls.TextBox tbOtherOwnerFName;

protected System.Web.UI.WebControls.TextBox tbSecondOwnerLName;

protected System.Web.UI.WebControls.TextBox tbSecondOwnerMI;

protected System.Web.UI.WebControls.TextBox tbSecondOwnerFName;

protected System.Web.UI.WebControls.Button Button4;

protected SiteCubed.EditWorksProfessional tbNote;

protected System.Web.UI.WebControls.Panel notePanel;

protected System.Web.UI.WebControls.DropDownList ddlStatus;

protected PeterBlum.PetersDatePackage.DateTextBox tbRegDate;

protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2;

protected System.Web.UI.WebControls.TextBox tbOwner;

protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;

protected System.Web.UI.WebControls.TextBox tbOwnerMI;

protected System.Web.UI.WebControls.TextBox tbOwnerLName;

protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator4;

protected System.Web.UI.WebControls.TextBox tbBusinessName;

protected System.Web.UI.WebControls.TextBox tbAddress;

protected System.Web.UI.WebControls.TextBox tbRegNo;

protected System.Web.UI.WebControls.TextBox tbPreviousOwner;

protected System.Web.UI.WebControls.TextBox tbPOwnerAddress;

protected System.Web.UI.WebControls.TextBox tbPORegNo;

protected PeterBlum.PetersDatePackage.DateTextBox tbTransferDate;

protected System.Web.UI.WebControls.DropDownList ddlMeans;

protected System.Web.UI.WebControls.Button Button3;

protected System.Web.UI.WebControls.Button Button2;

protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;

protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;

protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;

protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;

protected TrafficDept.dsAddOwner dsAddOwner1;

privatevoid Page_Load(object sender, System.EventArgs e)

{

// Put user code to initialize the page here

}

#region Web Form Designer generated code

privatevoid Button2_Click(object sender, System.EventArgs e)

{

Response.Redirect("AddOthers.aspx");

}

privatevoid Button1_Click(object sender, System.EventArgs e)

{

daAddOwner.Fill(dsAddOwner1);

dsAddOwner.OwnershipRow drOwner = dsAddOwner1.Ownership.NewOwnershipRow();

drOwner.Plates_ID =int.Parse(Session["PlatesID"].ToString());

drOwner.Status = ddlStatus.SelectedItem.Text;

drOwner.Current_Legal_Owner = tbOwner.Text;

drOwner.Owner_CurrentLegalOwnerMI = tbOwnerMI.Text.ToString();

drOwner.Owner_CurrentLegalOwnerLName = tbOwnerLName.Text;

drOwner.Owner_CurrentBusinessName = tbBusinessName.Text;

if (!tbRegDate.xIsEmpty)

drOwner.Date_of_Registration = DateTime.Parse(tbRegDate.Text.ToString());

drOwner.Address_of_Current_Owner = tbAddress.Text;

drOwner.Reg_No_for_Current_Owner = tbRegNo.Text;

drOwner.Previous_Owner = tbPreviousOwner.Text;

drOwner.Previous_Owner_Address = tbPOwnerAddress.Text;

drOwner.Reg_No_for_Previous_Owner = tbPORegNo.Text;

if (!tbTransferDate.xIsEmpty)

drOwner.Date_of_Transfer = DateTime.Parse(tbTransferDate.Text.ToString());

drOwner.By_Means_of = ddlMeans.SelectedItem.Text;

drOwner.Owner_SecondaryLegalOwnerFName = tbSecondOwnerFName.Text;

drOwner.Owner_SecondaryLegalOwnerMI = tbSecondOwnerMI.Text;

drOwner.Owner_SecondaryLegalOwnerLName = tbSecondOwnerLName.Text;

drOwner.Owner_OtherLegalOwnerFName = tbOtherOwnerFName.Text;

drOwner.Owner_OtherLegalOwnerMI = tbOtherOwnerMI.Text;

drOwner.Owner_OtherLegalOwnerLName = tbOtherOwnerLName.Text;

drOwner.Owner_Notes = tbNote.Text;

dsAddOwner1.Ownership.Rows.Add(drOwner);

daAddOwner.Update(dsAddOwner1);

Response.Redirect("AddOthersTP.aspx");

}

privatevoid Button3_Click(object sender, System.EventArgs e)

{

//daAddOwner.Fill(dsAddOwner1);

//dsAddOwner.OwnershipRow drAddOwner = dsAddOwner1.Ownership.NewOwnershipRow();

namePanel.Visible =true;

}

privatevoid Button4_Click(object sender, System.EventArgs e)

{

notePanel.Visible =true;

}

}

}

I found my problem. The .dll was never being rebuilt.

add_months function

Hi - I am running an SQL on my oracle database.

I have used the add_months function in my select statement and that is all working fine.

I now want a parameter to show me only rows where the result of the add_months function is 1st July.

I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

SQL is as follows

select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
from accounts
where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
;

Any advise would be appreciated.
Regards,
BethOriginally posted by elisabeth
Hi - I am running an SQL on my oracle database.

I have used the add_months function in my select statement and that is all working fine.

I now want a parameter to show me only rows where the result of the add_months function is 1st July.

I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

SQL is as follows

select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
from accounts
where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
;

Any advise would be appreciated.
Regards,
Beth
Never use a character string literal like '01-JUL-2003' where a DATE is required. Instead, use TO_DATE with an explicit format mask to properly convert it to a date:

TO_DATE('01-JUL-2003','DD-MON-YYYY')

Add/Remove Column/Row Groups dynamically in Reporting Services

Hi
Presently I'm working with OLAP and Reporting Services. In which users
are allowed to drag and drop the dimensions and the measures from the
list available for the cube , On droping of the dimension/measure on
Row/Column. I need to generate the matrix. Is it possible in RS, If it
menas, give me idea on this else notes for how to proceed with this.
Regards
Santhimurthy DessingouIf you're using SQL Server 2005, you could probably do this with the new
Report Builder tool, you just need to create a Report Model with all the
dimensions and measures you need.
Alternatively, try searching for a thread called "RS/MDX - Column Parameter
/ Field name in dataset problem"
Or if you use Google groups, you find it at
http://groups.google.no/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/642b5c0d703335b6/568564e89b6bc038?lnk=st&q=Re%3A+RS%2FMDX+-+Column+Parameter+%2F+Field+name+in+dataset+problem&rnum=1&hl=no#568564e89b6bc038
It's a suggestion on how to create a dynamic report. It's limited by a given
number of columns, but it might be possible to elaborate on the ideas to get
what you want.
Kaisa M. Lindahl Lervik
<santhimurthyd@.gmail.com> wrote in message
news:1140600388.351554.135920@.g44g2000cwa.googlegroups.com...
> Hi
> Presently I'm working with OLAP and Reporting Services. In which users
> are allowed to drag and drop the dimensions and the measures from the
> list available for the cube , On droping of the dimension/measure on
> Row/Column. I need to generate the matrix. Is it possible in RS, If it
> menas, give me idea on this else notes for how to proceed with this.
> Regards
> Santhimurthy Dessingou
>

add working days to a date

I want to be able to pass a date to a function and add 4 days to it. The
quirk is that it needs to be 4 working days ie. Mon - Fri excl holidays.
so
I have a table of Holidays with 2 records in 2/1/2006 (2nd Jan) and 3/1/2005
(3rd Jan)
The 31/12/05 and 1/1/06 are a Sat and Sun
I have a date 30/12/2005 to which I want to add 4 working days (this to be
variable) therefore the date returned would be 9/1/06 (9th Jan)
In vb I made use of the Wday function and DLookup
How would I do the same thing in SQL
Thanks
CREATE TABLE [dbo].[tblHols] (
[Holiday] [datetime] NOT NULL
) ON [PRIMARY]
GO>I want to be able to pass a date to a function and add 4 days to it. The
>quirk is that it needs to be 4 working days ie. Mon - Fri excl holidays.
Your best bet is a calendar table. Please see http://www.aspfaq.com/2519
for a thorough treatment (I think one of the examples even shows you how to
estimate delivery date, which sounds pretty much like what you're asking
for).|||I have created the calendar table as suggested but the select query takes 43
secs to run. How could I speed this up? A similar function in VB takes far
less time.
Here are the scripts
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
isWday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20060101'
WHILE @.dt < '20300101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
UPDATE dbo.Calendar SET
isWday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 1
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 2
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 3
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 4
Declare @.dte datetime
SET @.dte = '20060101'
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nltssPAGHA.2984@.TK2MSFTNGP09.phx.gbl...
> Your best bet is a calendar table. Please see http://www.aspfaq.com/2519
> for a thorough treatment (I think one of the examples even shows you how
> to estimate delivery date, which sounds pretty much like what you're
> asking for).
>|||On Thu, 15 Dec 2005 09:52:05 -0000, Newbie wrote:

>I have created the calendar table as suggested but the select query takes 4
3
>secs to run. How could I speed this up? A similar function in VB takes fa
r
>less time.
Hi Newbie,
Just to clarify: you ARE aware that the Calendar table should be a
permanent one, aren't you? Create and populate it once, then just use
it. Don't drop after use and re-create before the next use.

>Declare @.dte datetime
>SET @.dte = '20060101'
>SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
To speed this up, try this modification:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
For a real speed gain, find a reasonable ratio for non-business days vs
total days and round up to be on the safe side. To be on the safe side,
I'll use a ratio of 1 to 2:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 9 * 2, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Having just posted a different project regarding dates, I thought I would gi
ve this a shoot. It turned into more work then expected, but should be nice
for a toolbox. This solution appears to run in the millisecond range and I
could probably get it faster. Currently, for holidays, I am calculating th
em each call. I could cache using a hashtable or something, but the speed s
till seems to be ok in any event. Attached is a class that contains many bus
iness date functions such as:
public static DateTime AddQuarters(DateTime date, int quarters);
public static DateTime AddWorkDays(DateTime startDate, int workDays, Holiday
[] holidays);
public static DateTime AddWorkDays(DateTime startDate, int workDays, DateTim
e[] holidays);
public static DateTime AddWorkDays(DateTime startDate, int workDays, bool ho
lidaysAreWorkDays);
public static string DaysHoursMinutesSecondsMilliseconds(Date
Time start, Dat
eTime end);
public static DateTime[] GetDayOfWInMonth(DateTime date, DayOfW[] days
OfTheW);
public static DateTime[] GetDayOfWInMonth(DateTime date, DayOfW dayOfW
);
public static DateTime[] GetDayOfWInRange(DateTime startDate, DateTime en
dDate, DayOfW dayOfW);
public static DateTime[] GetDayOfWInRange(DateTime startDate, DateTime en
dDate, DayOfW[] daysOfW);
public static DateTime GetEndOf(PeriodType periodType, DateTime date);
public static DateTime GetEndOfDay(DateTime date);
public static DateTime GetEndOfMinute(DateTime date);
public static DateTime GetEndOfMonth(DateTime date);
public static DateTime GetEndOfQuarter(DateTime date);
public static DateTime GetEndOfQuarter(int year, int quarter);
public static DateTime GetEndOfW(DateTime date);
public static DateTime GetEndOfYear(DateTime date);
public static DateTime GetEndOfYear(int Year);
public static Holiday[] GetHolidays(int year);
public static Holiday[] GetHolidays(DateTime startDate, DateTime endDate);
private static DateTime GetNextQuarter(DateTime date);
private static DateTime GetPriorQuarter(DateTime date);
public static int GetQuarter(DateTime date);
public static DateTime GetStartOf(PeriodType periodType, DateTime date);
public static DateTime GetStartOfDay(DateTime date);
public static DateTime GetStartOfMinute(DateTime date);
public static DateTime GetStartOfMonth(DateTime date);
public static DateTime GetStartOfQuarter(DateTime date);
public static DateTime GetStartOfQuarter(int year, int quarter);
public static DateTime GetStartOfW(DateTime date);
public static DateTime GetStartOfYear(DateTime date);
public static DateTime GetStartOfYear(int Year);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, Holiday[] holidays);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, DateTime[] holidays);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, bool holidaysAreWorkDays);
public static string HoursAndMinutes(DateTime start, DateTime end);
public static bool IsBetweenDay(DateTime date, DateTime day);
public static bool IsBetweenDays(DateTime date, DateTime startDate, DateTime
endDate);
public static bool IsBetweenMonth(DateTime date, DateTime month);
public static bool IsBetweenQuarter(DateTime date, int quarter);
public static bool IsBetweenQuarter(DateTime date, int year, int quarter);
public static bool IsHoliday(DateTime date);
public static bool IsHoliday(DateTime date, Holiday[] holidays);
public static bool IsHoliday(DateTime date, DateTime[] holidays);
public static bool IsWorkDay(DateTime date);
// The following is a CLR UDF that calls this class for AddWorkDays. This e
xample udf calls DateFunctions.AddWorkDays() and skips any of the 10 Federal
holidays. You could also add your own holidays (i.e. non-work days) add ca
ll a different overload. Other UDFs that call the different static methods
on the DateFunctions class could be written as well.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlUtils;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static DateTime AddWorkDays(DateTime date, int workDays)
{
DateTime start = DateTime.Now;
DateTime day = DateFunctions.AddWorkDays(date, workDays, false);
DateTime end = DateTime.Now;
TimeSpan ts = end - start;
Console.WriteLine(ts.ToString());
return day;
}
};
Please let me know if you have any questions. I still need more testing on
everything, but the AddWorkDays seems to be working pretty well. Let me kno
w if you questions. Cheers!
--
William Stacey [MVP]|||Brilliant! It now takes less than 1 second when using the second mod
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:o0v3q1t8ckqoo7em98ull7k3s0vnrglksg@.
4ax.com...
> On Thu, 15 Dec 2005 09:52:05 -0000, Newbie wrote:
>
> Hi Newbie,
> Just to clarify: you ARE aware that the Calendar table should be a
> permanent one, aren't you? Create and populate it once, then just use
> it. Don't drop after use and re-create before the next use.
>
> To speed this up, try this modification:
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> For a real speed gain, find a reasonable ratio for non-business days vs
> total days and round up to be on the safe side. To be on the safe side,
> I'll use a ratio of 1 to 2:
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 9 * 2, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is another fun little thing you can do with the lib. Print a calendar o
f any month(s). This basically works by enumerating the Calendar ws for a
ny month using GetMonthCalendarWs as shown in code below:
Output
---
January 2006
SU MO TU WE TH FR SA
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
February 2006
SU MO TU WE TH FR SA
01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28
March 2006
SU MO TU WE TH FR SA
01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
...
// Put in windows or console app. Reference the sqlutils.dll and run.
private void button2_Click(object sender, EventArgs e)
{
Console.WriteLine();
for (int i = 1; i <= 12; i++)
{
PrintMonthCalendar(new DateTime(2006, i, 1));
Console.WriteLine();
}
}
private void PrintMonthCalendar(DateTime date)
{
Console.WriteLine(date.ToString("MMMM yyyy"));
Console.WriteLine("SU MO TU WE TH FR SA");
DateRange[] cal = DateRange.GetMonthCalendarWs(date, false);
for (int i = 0; i < cal.Length; i++)
{
DateRange day = cal[i];
if (day == null)
Console.Write(" " + " ");
else
Console.Write(day.StartDate.Day.ToString().PadLeft(2, '0') + " ");
if ((i != 0) && ((i + 1) % 7 == 0))
Console.WriteLine();
}
}
--
William Stacey [MVP]

Tuesday, March 6, 2012

Add new article to transactional replication

This one is driving me crazy...
We are using Transactional Replication with the subscribers initialized from
a backup (no snapshot). It's working great, but now we need to add several
new articles to the publication and have them picked up in the subscribers.
The only way I can get this to work is to drop the subscriber, backup the
published database, restore the previously made backup over the destination
databases and then re-create the subscriptions. There has to be a better
way?
I've tried using the sp_reinitializesubscription, but that doesn't work
since we aren't using a snapshot. I've tried skipping the restore, and just
creating the tables in both the source and destination databases. Doing
this, the subscription is re-created sucessfully, but data in the new
articles is not actually being replicated.
Replication isn't my forte, and the documentation on initializing with
backups is pretty sparse, so any advice is appreciated.
Use sp_addarticle followed by sp_addsubscription with @.sync_type =
automatic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Saturday, February 25, 2012

Add into 2 tables (VS 2005)

Hi, not sure if I've posted at the right area but here goes:

I'm working on a project using Visual Studio 2005 and SQL express server for the database.

Since I'm using VS 05, I use datasources to select, insert, delete, update from my db.

Question is, how do i insert, delete, update from 2 tables using datasource? is it even allowed?

If it's not allowed, are there any solutions other than using the codebehind?

Please help. Thank you.

hi galovesongs,

you can you any of these two solutions

1. on the vs.net

Making use of system.transaction

you can put two of your delete, insert, update inside a transaction

placing two statement in a transaction context

is the best way to go.

pls find a four page article on this link

http://www.devx.com/vb/Door/10608

2. on the database

you can make use of the trigger

i moved the thread here. for better opinions

Monday, February 13, 2012

Add a clustered index in a published table

Hi,
I am using SQL 2K standard and have created a transactional replication
between two sql servers using push subscritpion. It is working fine. Now I
want to add a clustered index in one of the published tables and I get an
error message saying that "Cannot alter the table because it is being
published for replication". Based on this message, I assume I will have to
disable all publishing and distribution, add the clustered index and then
redo the publishing and subscriptions for all the tables from scratch again.
Am I right? Is there another way to do this?
Any help on this will be very much appreciated.
Wingman
Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
are you doing anything else in the same batch eg changing the PK?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick response.
Here are the steps I did.
1. Open the existing publishing database in the SQL enterprise manager.
2. Select 'Design' of the table which is a published table in the
publication.
3. Select the primiary key
4. Check the box of the clustered index.
5. Close the design window and click 'save' to save the changes.
6. Then the error occurs.
There are no other batches involved and I was just doing the above. I hope
this clarifies any questions for you.
Wing
"Paul Ibison" wrote:

> Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
> are you doing anything else in the same batch eg changing the PK?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I can't test this right now, but I'd try to run this command:
CREATE CLUSTERED INDEX index_name ON table ( column_name )
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yep, I did and it ran succussfully without any problem. The clustered index
is created. Thanks for this suggestion.
Can you explain why I can't do this in EM? Is it just the limitation of
using this type of interface?
Also, do I need to do the 'create clustered index...' in the subscription
server or the push replication will take care of it? In the future, if I
need to add non-clustered index, will this be the way to do it?
Wingman
"Paul Ibison" wrote:

> I can't test this right now, but I'd try to run this command:
> CREATE CLUSTERED INDEX index_name ON table ( column_name )
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you run profiler when applying changes to a table using the Enterprise
Manager interface you can usually determine the cause of tis type of problem.
In this case the table was probably being dropped in the background. The same
issue applies in SQL Server 2005 where if you select to replicate DDL
changes, the Management Studio will fail whereas ALTER TABLE will succeed.
If you want this applied to the subscribers you have to apply initiate this
yourself. You could use sp_addscriptexec to use the replication setup or just
apply it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry, don't mean to keep the discussion going that long but I have a few
follow-up questions regarding sp_addscriptexec.
1. Are there any benefits and differences using sp_addscriptexec compared
to manually executing the script in the subscriber computer?
2. If I use the sp_addscriptexec command to move the scritpt to the
subscriber(s), when or under what situation will the script be excecuted and
how often?
3. If I decide that I no longer need those scripts after using
sp_addscriptexec, how do I remove it from the subscriber computer?
Wingman
"Paul Ibison" wrote:

> If you run profiler when applying changes to a table using the Enterprise
> Manager interface you can usually determine the cause of tis type of problem.
> In this case the table was probably being dropped in the background. The same
> issue applies in SQL Server 2005 where if you select to replicate DDL
> changes, the Management Studio will fail whereas ALTER TABLE will succeed.
> If you want this applied to the subscribers you have to apply initiate this
> yourself. You could use sp_addscriptexec to use the replication setup or just
> apply it manually.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||There are no extra benefits of sp_addscriptexec in terms of running the
script. However, it may be the case that you have dozens of subscribers, or
perhaps some are offline. In this was it gives you convenience and assures
you of getting the script to the subscribers. They'll receive the script when
they synchronize, and the script won't be run more than once and won't be
retained on the subscriber's computer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, thanks.
When I go to the subscription computer and check all replicated tables, I
notice that none of the primary key is defined but the index key for the
primary key is there. Why is it?
Also, when I checked the snapshot properties of the pubished article
defaults in EM, the non-clustered index check box is grayed out. Is there a
way I can activate it so I can uncheck it because I don't want the
non-clustered index to be replicated?
Wingman
"Paul Ibison" wrote:

> There are no extra benefits of sp_addscriptexec in terms of running the
> script. However, it may be the case that you have dozens of subscribers, or
> perhaps some are offline. In this was it gives you convenience and assures
> you of getting the script to the subscribers. They'll receive the script when
> they synchronize, and the script won't be run more than once and won't be
> retained on the subscriber's computer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||PKs are replicated as unique indexes by default in transactional replication.
If you enable the DRI option checkbox on the snapshot tab of the article
properties, the PK will be recreated on teh subscriber. For the chackboxes on
this tab, some are related to others, so try checking/unchecking others to
see if you can enable the nonclustered option. However not all combinations
are supported using this method and in some cases you have to resort to
scripting the replication instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Sunday, February 12, 2012

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Thursday, February 9, 2012

Actually, its a T-SQL question

I am trying to update a project using plaintext sql commands to stored procedures. I am currently working on

orderby = request.querystring("orderby")
if orderby = "" then orderby = "name"
sql="select * from products order by " & orderby"
rs.open sql,conn,3,3

(yes, that's classic ASP but its the t-sql that's a problem, not the aspWink [;)])
orderby can be "name","code","qty","price"
The stored procedure I have is

CREATE PROCEDURE DBO.GetSortedProducts_sp
@.sort int
AS</P><P> SELECT
*
FROM
PRODUCTS
ORDER BY
case
when @.sort=1 then name --varchar(100)
when @.sort=2 then code --varchar(20)
when @.sort=3 then qty --int
when @.sort=4 then price --decimal 9(19,4)
end
GO

Only @.sort=3 and @.sort=4 work. For @.sort=1 and @.sort=2, I get the following error:
Server: Msg 8114, Level 16, State 5, Procedure GetSortedProducts_sp, Line 5
Error converting data type varchar to numeric.
I have included the column types in the stored procedure above for reference.
If I write out the stored procedure in Query Analyzer I get the following error:
Syntax error converting the varchar value 'Prod-1' to a column of data type int.
Note that if I use "select * from products order by code" then it works fine. Any help please?

Confused [8-)]|||My guess is when you are using CASE the columns should all be of same datatype.|||As Dinakar said, with the CASE construct you've used all options must result in the same datatype.
Try this approach instead:
SELECT
*
FROM
PRODUCTS
ORDER BY
case when @.sort=1 then name end,
case when @.sort=2 then code end,
case when @.sort=3 then qty end,
case when @.sort=4 then price end

|||interesting...thx

ActiveX&VB.net versus transformation tasks

Hello all -

Working on DTS packages we used to have two different ways of exporting data to spreadsheet... We could do it by a single transformation task or either writing an ActiveX script, after running a procedure.

I am able to cover the first way already, while dealing with SSIS... But, I wonder if it is worth to research on how I would write a vb.net code to load data into a spreadsheet (considering we are advised to try vb.net instead of ActiveX while working on these new packages).

It is always a huge discussion to know what is the best way of doing something so my point here is to hear some of you and decide if I just keep exporting data by doing transformation tasks or if I will ever go trying to deploy a vb.net code to do that... What is the best on performance, etc...?

Thanks in advance for all your help as always.

SSIS contains an Excel Destination that you can use.|||

Hi Phil.

Thanks for your reply. I know about the excel destination which is very much the same of doing transformation tasks in our old dts packages.

My question was... If I generate the spreadsheet through a vb.net code like we used to do with ActiveX scripts for dts packages, will I win anything on performance or something else? Example...

Working on dts packages we would always choose to generate a spreadsheet through procedures combined with ActiveX scripts because the procedure would have a query plan and would have less overhead, comparing with data transformation tasks. I don't know about SSIS because I am new here Smile If you can add any insight to this I really appreciate that.

Thanks again.

|||I think you'll see better performance from the built-in Excel Destination. Script components are generally slower than pre-built components.