Showing posts with label working. Show all posts

Thursday, March 29, 2012

Delete data from GridView and ObjectDataSource

The function that is supposed to delete a row, is not working. The function is called, and the windows is refreshed, but the row is not deleted.
Can anyone see anything wrong with this code:

public static void DeleteBlog(int original_BlogID)
{
string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID";
SqlConnection myConnection = new SqlConnection(Blog.ConnectionString);
SqlCommand command = new SqlCommand(insertCommand, myConnection);

command.Parameters.Add(new SqlParameter("@.BlogID", original_BlogID));

myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
}You must be using ASP.NET 1.1. The Parameters.Add contructor has 2 overloads with 2 parameters:

Add(parameterName, object)
Add(parameterName, SqlDbType)

Since your original_BlogID is numeric, ADO.NET is assuming you are using the 2nd overload (parameterName, SqlDbType), so your value is not assigned to the parameter. Try it this way instead:

command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_BlogID;

Or, as a shortcut:

command.Parameters.Add("@.BlogID", SqlDbType.Int).Value = original_BlogID;

The overload with the object parameter has been deprecated in ADO.NET 2.0 because of this issue, and has been replaced with the method AddWithValue.|||

Still not working, and I cannot see what's wrong either :(
The page is refreshed, but no rows are deleted .

public static void DeleteBlogg(int original_blogID) { //System.Diagnostics.Debug.WriteLine("original_blogID og BlogID" + original_blogID + BlogID); string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID"; SqlConnection myConnection = new SqlConnection(Blog.ConnectionString); SqlCommand command = new SqlCommand(insertCommand, myConnection); command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_blogID; myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); }|||and this is the aspx page:
Still it is not working, the page is refreshed, but no rows are deleted

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteBlogg"
SelectMethod="ListMyBlogs" TypeName="Blog">
<DeleteParameters>
<asp:Parameter Name="original_blogID" Type="Int32" />
</DeleteParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="Message" HeaderText="Message" SortExpression="Message" />
<asp:BoundField DataField="MessageCreated" HeaderText="MessageCreated" ReadOnly="True"
SortExpression="MessageCreated" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="BlogID" HeaderText="BlogID" SortExpression="BlogID" />
<asp:BoundField DataField="MessageUpdated" HeaderText="MessageUpdated" ReadOnly="True"
SortExpression="MessageUpdated" />
</Columns>
</asp:GridView>

Tuesday, March 27, 2012

Delete connection file

I've inherited a procedure that performs a few transforms and writes to flat files in a working directory using a connection in connection manager.

After the manipulation, the system cleans up by deleting the working files. Works fine in development, but from the command line gives:

The process cannot access the file <filename> because it is being used by another process.".

I suspect this is because connection manager still has the file open.

Any ideas?

Thanks

Guy

Yes, you are right.try closing the connection.or try a switch from command line to do it.|||Any ideas on how to close a connection? As far as I can see a connection is opened by reference to it, but I can't see any way to close a connection.|||

//release here

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData();

managedOleInstance.ReleaseConnections();

|||

So this would be in a script componenent after the last valid use of the connection and before the deletion I take it?

I would also probably need to force RetainSameConnection to use only one connection the whole way through.

|||

Can u show the code you have written?

Then i can favor you better.

Also, can u solve my problem?

I have a flat file with name "Employee.txt " (Full url: C:\Employee.txt ) .The File content is like this

Anil,Engineering,1997
Sunil,Sales,1981
Kumar,Inventory,1991
Rajesh,Engineering,1992

(Note: Items are Comma Seperated)

Now, i have a SQL Server database called "EmployeeDB" which has 2 tables "TblEmp1", "TblEmp2".
The Table is like this.

TblEmp1 : Columns
EmpName EmpDept EmpjoinDate

TblEmp2 : Columns
EName EDate Edept

using integration services (SSIS) i need code to Create a dtsx package so that i can push the flat file content to these 2 tables.
And the condition is :

After Executing the package Data loaded in TblEmp1 should be like this

EmpName EmpDept EmpjoinDate
Anil Engineering 1997
Sunil Sales 1981
Kumar Inventory 1991
Rajesh Engineering 1992

(No change in order compared to source)
And Data loaded inTBLEmp2 should be like this

EName EDate Edept
Anil 1997 Engineering
Sunil 1981 Sales
Kumar 1991 Inventory
Rajesh 1992 Engineering

Now, i know that we need to do like this in wizard
1) Create a flat file source component.
2) Create flat file connection and set the properties of flat file (delimeters and other things)
3) Create a Multicast Component.
4) Create a Path between Flat file source and Multicast.
5) Create 2 destination component(each for a table).
6) Create path from multicast to 2 destination components
7) Create a OledbConnection and set table names for 2 destination components..
7) Now,i have to do mapping for destination1.
8) Now, i have to do mapping for destination2( this mapping will be different from mapping done for destination1 because iam not inserting the data in the same order in which iam doing for TBLEmp1.

I have done it in wizard.I need to do it through code and i know that its not complicated.The main problem is Mapping differently for 2 destinations from source.for 1st one we can have a forloop for mapping.but for 2nd one iam confused!!

Please Get back ASAP today.
Thanks in Advance,
Anil Kumar MS

|||

Sorry mate, our days in Sydney obviously end earlier than wherever you are...

Firstly, I don't have code, just components. I've already outlined the relevant bits of what happened.

Secondly, with respect of your problem. Is there a constraint linking the two tables? If not, what you're doing looks just dandy. If there is, you may need to change your order or temporalily relax the constraint.

If its just a question of mapping why not just use the mapping tab on the destination?

Regards

Thursday, March 22, 2012

Delete & create a partition

Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
--
Arnold
"Dan Guzman" wrote:
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> > Hello All,
> >
> > I am working on a hugeee table partionned in 20.
> >
> > Working on one partition at one time, I need to drop and re-create a
> > partition before inserting treated data.
> >
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> >
> > Thanks !!
> > Arnold.
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> To effectively truncate a partition, SWITCH the desired partition into a
>> staging table. The staging table needs to be on the same filegroup(s)
>> with
>> like schema and indexes. You can then drop or truncate the staging table
>> to
>> permanently remove the data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "r.no" <rno@.discussions.microsoft.com> wrote in message
>> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>> > Hello All,
>> >
>> > I am working on a hugeee table partionned in 20.
>> >
>> > Working on one partition at one time, I need to drop and re-create a
>> > partition before inserting treated data.
>> >
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> >
>> > Thanks !!
>> > Arnold.

Delete & create a partition

Delet function not working on Gridview

I 'm having trouble with the delete function on Gridview - the update works great but I keep getting the error below when I try to delete

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.

I using the products table in Northwind to learn this stuff.

The code that I used is below:

@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Gigs.aspx.cs"Inherits="Gigs" %>

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

htmlxmlns="http://www.w3.org/1999/xhtml">

headrunat="server"><title>Untitled Page</title>

head>

body><formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @.ProductID"InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@.ProductName, @.SupplierID, @.CategoryID, @.QuantityPerUnit, @.UnitPrice, @.UnitsInStock, @.UnitsOnOrder, @.ReorderLevel, @.Discontinued)"SelectCommand="SELECT * FROM [Products]"UpdateCommand="UPDATE [Products] SET [ProductName] = @.ProductName, [SupplierID] = @.SupplierID, [CategoryID] = @.CategoryID, [QuantityPerUnit] = @.QuantityPerUnit, [UnitPrice] = @.UnitPrice, [UnitsInStock] = @.UnitsInStock, [UnitsOnOrder] = @.UnitsOnOrder, [ReorderLevel] = @.ReorderLevel, [Discontinued] = @.Discontinued WHERE [ProductID] = @.ProductID"><DeleteParameters><asp:ParameterName="ProductID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/><asp:ParameterName="ProductID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/></InsertParameters></asp:SqlDataSource>

</div><asp:GridViewID="GridView1"runat="server"AllowPaging="True"AutoGenerateColumns="False"CellPadding="4"DataKeyNames="ProductID"DataSourceID="SqlDataSource1"ForeColor="#333333"GridLines="None"><FooterStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><Columns><asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="ProductName"HeaderText="ProductName"SortExpression="ProductName"/><asp:BoundFieldDataField="SupplierID"HeaderText="SupplierID"SortExpression="SupplierID"/><asp:BoundFieldDataField="CategoryID"HeaderText="CategoryID"SortExpression="CategoryID"/><asp:BoundFieldDataField="QuantityPerUnit"HeaderText="QuantityPerUnit"SortExpression="QuantityPerUnit"/><asp:BoundFieldDataField="UnitPrice"HeaderText="UnitPrice"SortExpression="UnitPrice"/><asp:BoundFieldDataField="UnitsInStock"HeaderText="UnitsInStock"SortExpression="UnitsInStock"/><asp:BoundFieldDataField="UnitsOnOrder"HeaderText="UnitsOnOrder"SortExpression="UnitsOnOrder"/><asp:BoundFieldDataField="ReorderLevel"HeaderText="ReorderLevel"SortExpression="ReorderLevel"/><asp:CheckBoxFieldDataField="Discontinued"HeaderText="Discontinued"SortExpression="Discontinued"/></Columns><RowStyleBackColor="#FFFBD6"ForeColor="#333333"/><SelectedRowStyleBackColor="#FFCC66"Font-Bold="True"ForeColor="Navy"/><PagerStyleBackColor="#FFCC66"ForeColor="#333333"HorizontalAlign="Center"/><HeaderStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"/></asp:GridView></form>

body>

html>

Any help would be appreciated - thanx

The table has a foreign key constraint.

If a product is part of an order, you may not delete the product.

Otherwise, when someone tried to look at the order, they would get an error about a missing product.

|||

Thanx for your help - The reason I'm using the Products table in Northwind is, "I tried building a table based on my clients original data (his music shows itinerary) but it would not allow me to click the Checkbox in the Wizard - "Generate additional INSERT, UPDATE, and DELETE statements" when configuring the db

Can you tell me how I can remove the foreign key constraints (or) how can I duplicate the table, rename it and then remove all foreign key constraints

thanx

|||

Open up the Keys folder under the table.

The foreign keys have a prefix FK_. Right click one and select Modify.

When you built your original table, did you give it a primary key? If a table does not have a primary key, you will not be able to generate Insert, Delete and Update statements.,

|||

thank you so much - The primary key issue was the problem and now I have had no problems adding, deleting and modyfing the table. I am having a problem with the formatting of a datetime field. It keeps returning the date as well as the time (12:00 AM). Could you point me in the right direction to take care of this. Do I need to change the field or can I deal with it by formatting the column in the GridView?

thanx for all of your help

|||

In the GridView date column set the DataFormatString to {0:d} or {0:D} or {0:yyyy-MM-dd} etc.

And set the column's HtmlEncode property to false.

|||

Thanx Steve - Everything seems to be working fine now. Except,...

When I try to put this little app on my server (www.discountasp.net) - I get errors

Server Error in '/dave' Application.

Configuration Error

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:Unrecognized attribute 'xmlns'.

Source Error:

Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>Line 12: <connectionStrings/>

Source File:E:\web\nyguitarsne\htdocs\dave\web.config Line:10

I must be missing something crucial about deployment?

|||

PS - the site is sitting here:

www.caterdata.com/dave

|||

It's hard to say without seeing the whole file but it looks like you have a closing connectionStrings tag without an opening tag. The fix below may help (or you could delete both tags)

Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>

new: <connectionStrings>Line 12: <connectionStrings/>

|||

The reason I'm confused is that it is working fine on localhost.

Do I need to call the site Administrator and set something up that I'm not aware of?

|||

It looks like the web.config file on you local machine and the server are different.

Sometimes they should be. For instance, the datasource on a local machine may not exist on the server.

|||

Try making sure the server is setup top use ASP.NET 2.0 and not ASP.NET 1.0

I have seen this cause weird problems as you are having.

David

|||

Try making sure the server is setup to use ASP.NET 2.0 and not ASP.NET 1.0

I have seen this cause weird problems as you are having.

David

sql

Delegation through Linked Server Stops working

I have a Linked Server from SQL 2005 to a SQL 2000 server. I have it
configured to use delegation. This will work fine for a while and then
suddenly stop working. Sometimes it works for an hour, sometimes for a day.
I have to restart the SQL 2005 server and it will begin to work again. The
error is:
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Any ideas?A few others have reported similar issues - with no
solutions. I worked at a place where we had delegation
sporadically failing and then working after reboots. A
ticket was opened with Microsoft but the issue was never
resolved. I would guess it's a Kerberos issue, not a SQL
issue. Make sure AD is clean and you don't have duplicate or
bad SPNs for all machines involved. Make sure all machines
involved have times sync working correctly, using the same
time server.
I'd suggest getting the Kerberos Delegation troubleshooting
doc available at:
http://www.microsoft.com/downloads/...&DisplayLang=en
We also installed a tool that would do verbose logging for
Kerberos errors - I just looked and couldn't find the tool.
Maybe if someone else knows they will jump in and provide a
link for that tool.
It can be a difficult issue to troubleshoot and you may want
to consider opening up a support ticket with Microsoft
Product Support.
-Sue
On Wed, 16 Aug 2006 12:43:01 -0700, Sheriff
<Sheriff@.discussions.microsoft.com> wrote:

>I have a Linked Server from SQL 2005 to a SQL 2000 server. I have it
>configured to use delegation. This will work fine for a while and then
>suddenly stop working. Sometimes it works for an hour, sometimes for a day
.
>I have to restart the SQL 2005 server and it will begin to work again. The
>error is:
>TCP Provider: An existing connection was forcibly closed by the remote host
.
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Any ideas?|||Is there a solution for this issue.
delegation on linked server fails in our network when we use
nt-authenticated logins. we have a sql server 2000 nodes (n1,n2) on win 2003
cluster.
Any thots,hints,links,pointers appreciated
thanks,
GA
"Sue Hoegemeier" wrote:

> A few others have reported similar issues - with no
> solutions. I worked at a place where we had delegation
> sporadically failing and then working after reboots. A
> ticket was opened with Microsoft but the issue was never
> resolved. I would guess it's a Kerberos issue, not a SQL
> issue. Make sure AD is clean and you don't have duplicate or
> bad SPNs for all machines involved. Make sure all machines
> involved have times sync working correctly, using the same
> time server.
> I'd suggest getting the Kerberos Delegation troubleshooting
> doc available at:
> http://www.microsoft.com/downloads/...&DisplayLang=en
> We also installed a tool that would do verbose logging for
> Kerberos errors - I just looked and couldn't find the tool.
> Maybe if someone else knows they will jump in and provide a
> link for that tool.
> It can be a difficult issue to troubleshoot and you may want
> to consider opening up a support ticket with Microsoft
> Product Support.
> -Sue
> On Wed, 16 Aug 2006 12:43:01 -0700, Sheriff
> <Sheriff@.discussions.microsoft.com> wrote:
>
>|||Are you having a completely different issue?
This post was about delegation working and then suddenly
failing until a reboot. Is this your issue?
-Sue
On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Is there a solution for this issue.
>delegation on linked server fails in our network when we use
>nt-authenticated logins. we have a sql server 2000 nodes (n1,n2) on win 200
3
>cluster.
>Any thots,hints,links,pointers appreciated
>thanks,
>GA
>"Sue Hoegemeier" wrote:
>|||looks like it works for few minutes when restarted the nodes...
"Sue Hoegemeier" wrote:

> Are you having a completely different issue?
> This post was about delegation working and then suddenly
> failing until a reboot. Is this your issue?
> -Sue
> On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||yes, when we restat the nodes the kerberos delegation starts to work for few
minutes and then stops with the 'login failed reason (null)' error...
"Sue Hoegemeier" wrote:

> Are you having a completely different issue?
> This post was about delegation working and then suddenly
> failing until a reboot. Is this your issue?
> -Sue
> On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||So then you followed everything in the troubleshooting
delegation doc? No one has every really posted any
resolution. I had posted a lot of steps Microsoft will have
you do when/if you open a ticket. That's about all I know
about it. It's not really a SQL issue, it's a kerberos
issue. Whether it's AD problems or issues with tickets
expiring, it's hard to say.
-Sue
On Tue, 29 Aug 2006 14:38:02 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>yes, when we restat the nodes the kerberos delegation starts to work for fe
w
>minutes and then stops with the 'login failed reason (null)' error...
>"Sue Hoegemeier" wrote:
>|||do have a ticket open with microsoft for more than a month now, but no
resolution yet. "Troubleshooting Kerberos delation" is nearly a 90 page doc.
tried a lot of things nothing seems to fix it...
"Sue Hoegemeier" wrote:

> So then you followed everything in the troubleshooting
> delegation doc? No one has every really posted any
> resolution. I had posted a lot of steps Microsoft will have
> you do when/if you open a ticket. That's about all I know
> about it. It's not really a SQL issue, it's a kerberos
> issue. Whether it's AD problems or issues with tickets
> expiring, it's hard to say.
> -Sue
> On Tue, 29 Aug 2006 14:38:02 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||Yup...a client site I was at had the issue and an open case
with PSS. At first I thought it was expiring tickets causing
the problem but then it looked more like it could be
duplicate/bad SPNs. We'd clean out AD and then find
duplicate SPNs errors after removing all dupes. Don't know
what else to tell you - I haven't seen anyone who has a
ticket open post a solution. And the place I was at never
got a resolution to the problem either.
-Sue
On Wed, 30 Aug 2006 14:50:03 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>do have a ticket open with microsoft for more than a month now, but no
>resolution yet. "Troubleshooting Kerberos delation" is nearly a 90 page doc
.
>tried a lot of things nothing seems to fix it...
>"Sue Hoegemeier" wrote:
>|||Things started working with the double hop after forcing the kerberos to use
TCP instead of UDP as in the article 244474 at
support.microsoft.com/kb/244474/
"Sue Hoegemeier" wrote:

> Yup...a client site I was at had the issue and an open case
> with PSS. At first I thought it was expiring tickets causing
> the problem but then it looked more like it could be
> duplicate/bad SPNs. We'd clean out AD and then find
> duplicate SPNs errors after removing all dupes. Don't know
> what else to tell you - I haven't seen anyone who has a
> ticket open post a solution. And the place I was at never
> got a resolution to the problem either.
> -Sue
> On Wed, 30 Aug 2006 14:50:03 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>

Delegation stops working after a while

I have a linked server set up between two servers (A & B).
Server A is running 2005 and Server B is running 2000.
Both servers SQL services are running using a domain user account and have
their SPN's registered in the AD.
The client connects to Server A using integrated security (TCP/IP and
Kerberos not NTLM) and runs disributed queries using the linked server to
server B.
Delegation is set up in the AD and is working, at least for some time.
After a while (sometimes a couple of minutes, sometimes a couple of hours)
the delegation seems to stop working and the client recieves the error
"Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection." The client is still connected and authenticated using TCP/IP
and
Kerberos.
After a restart of SQL Server on server A the delegation starts working
again.
I cannt find anything in the eventlogs on either one of the servers or the
client, and nothing in the sql server logs.
Does anybody have any idea of what could be wrong, or give me a though on
where to start looking.
Thanks
/MattiasWe are encountering this problem also. I have contacted MS but they are
still gathering information.
There is at least one other unanswered post on this here as well, see
subject = "SQL2005 Linked server authentication drops".
Are you running sql server under under a domain account that is not in the
local admins group by any chance?
--
-b
"Mattias" wrote:
> I have a linked server set up between two servers (A & B).
> Server A is running 2005 and Server B is running 2000.
> Both servers SQL services are running using a domain user account and have
> their SPN's registered in the AD.
> The client connects to Server A using integrated security (TCP/IP and
> Kerberos not NTLM) and runs disributed queries using the linked server to
> server B.
> Delegation is set up in the AD and is working, at least for some time.
> After a while (sometimes a couple of minutes, sometimes a couple of hours)
> the delegation seems to stop working and the client recieves the error
> "Login
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection." The client is still connected and authenticated using TCP/IP
> and
> Kerberos.
> After a restart of SQL Server on server A the delegation starts working
> again.
> I cannt find anything in the eventlogs on either one of the servers or the
> client, and nothing in the sql server logs.
> Does anybody have any idea of what could be wrong, or give me a though on
> where to start looking.
> Thanks
> /Mattias
>
>|||I am the other unanswered posting!
It fails intermittently when we run under 'sa' or a domain account that is
in the local admin group.
I have raised this through the Microsoft concierge service and they said
there are others with the same problem but no resolutions as yet!
Wendy
"BBogart" wrote:
> We are encountering this problem also. I have contacted MS but they are
> still gathering information.
> There is at least one other unanswered post on this here as well, see
> subject = "SQL2005 Linked server authentication drops".
> Are you running sql server under under a domain account that is not in the
> local admins group by any chance?
> --
> -b
>
> "Mattias" wrote:
> > I have a linked server set up between two servers (A & B).
> > Server A is running 2005 and Server B is running 2000.
> > Both servers SQL services are running using a domain user account and have
> > their SPN's registered in the AD.
> >
> > The client connects to Server A using integrated security (TCP/IP and
> > Kerberos not NTLM) and runs disributed queries using the linked server to
> > server B.
> > Delegation is set up in the AD and is working, at least for some time.
> >
> > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > the delegation seems to stop working and the client recieves the error
> > "Login
> > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > connection." The client is still connected and authenticated using TCP/IP
> > and
> > Kerberos.
> >
> > After a restart of SQL Server on server A the delegation starts working
> > again.
> >
> > I cannt find anything in the eventlogs on either one of the servers or the
> > client, and nothing in the sql server logs.
> >
> > Does anybody have any idea of what could be wrong, or give me a though on
> > where to start looking.
> >
> > Thanks
> > /Mattias
> >
> >
> >|||We have seen this in our environment too. One moment a linked server query
will work just fine with delegated Windows credentials, the next moment you
receive errors like the following:
OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I am still working on a reproducable way of generating the message, but I
seem to have problems a lot when I initially generate a linked server within
SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
I suspect that something in the Kerberos token is expiring. After I logout
and login, I can usually start a new session that works (just not today).
"Woo" wrote:
> I am the other unanswered posting!
> It fails intermittently when we run under 'sa' or a domain account that is
> in the local admin group.
> I have raised this through the Microsoft concierge service and they said
> there are others with the same problem but no resolutions as yet!
> Wendy
>
>
> "BBogart" wrote:
> > We are encountering this problem also. I have contacted MS but they are
> > still gathering information.
> >
> > There is at least one other unanswered post on this here as well, see
> > subject = "SQL2005 Linked server authentication drops".
> >
> > Are you running sql server under under a domain account that is not in the
> > local admins group by any chance?
> > --
> > -b
> >
> >
> > "Mattias" wrote:
> >
> > > I have a linked server set up between two servers (A & B).
> > > Server A is running 2005 and Server B is running 2000.
> > > Both servers SQL services are running using a domain user account and have
> > > their SPN's registered in the AD.
> > >
> > > The client connects to Server A using integrated security (TCP/IP and
> > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > server B.
> > > Delegation is set up in the AD and is working, at least for some time.
> > >
> > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > the delegation seems to stop working and the client recieves the error
> > > "Login
> > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > connection." The client is still connected and authenticated using TCP/IP
> > > and
> > > Kerberos.
> > >
> > > After a restart of SQL Server on server A the delegation starts working
> > > again.
> > >
> > > I cannt find anything in the eventlogs on either one of the servers or the
> > > client, and nothing in the sql server logs.
> > >
> > > Does anybody have any idea of what could be wrong, or give me a though on
> > > where to start looking.
> > >
> > > Thanks
> > > /Mattias
> > >
> > >
> > >|||I also suspect a ticket is expiring.
I am still working with MS on this with no resolution yet.
Once we see a failure, failures continue regardless of logging off and back
on until sql server is restarted. A reboot is not necessary as I previously
thought.
We have seen it take as little as a few hours or up to a week or more for
the failures to start again.
--
-b
"JD Qixcle" wrote:
> We have seen this in our environment too. One moment a linked server query
> will work just fine with delegated Windows credentials, the next moment you
> receive errors like the following:
> OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I am still working on a reproducable way of generating the message, but I
> seem to have problems a lot when I initially generate a linked server within
> SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> I suspect that something in the Kerberos token is expiring. After I logout
> and login, I can usually start a new session that works (just not today).
>
>
> "Woo" wrote:
> > I am the other unanswered posting!
> >
> > It fails intermittently when we run under 'sa' or a domain account that is
> > in the local admin group.
> >
> > I have raised this through the Microsoft concierge service and they said
> > there are others with the same problem but no resolutions as yet!
> >
> > Wendy
> >
> >
> >
> >
> > "BBogart" wrote:
> >
> > > We are encountering this problem also. I have contacted MS but they are
> > > still gathering information.
> > >
> > > There is at least one other unanswered post on this here as well, see
> > > subject = "SQL2005 Linked server authentication drops".
> > >
> > > Are you running sql server under under a domain account that is not in the
> > > local admins group by any chance?
> > > --
> > > -b
> > >
> > >
> > > "Mattias" wrote:
> > >
> > > > I have a linked server set up between two servers (A & B).
> > > > Server A is running 2005 and Server B is running 2000.
> > > > Both servers SQL services are running using a domain user account and have
> > > > their SPN's registered in the AD.
> > > >
> > > > The client connects to Server A using integrated security (TCP/IP and
> > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > server B.
> > > > Delegation is set up in the AD and is working, at least for some time.
> > > >
> > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > the delegation seems to stop working and the client recieves the error
> > > > "Login
> > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > connection." The client is still connected and authenticated using TCP/IP
> > > > and
> > > > Kerberos.
> > > >
> > > > After a restart of SQL Server on server A the delegation starts working
> > > > again.
> > > >
> > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > client, and nothing in the sql server logs.
> > > >
> > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > where to start looking.
> > > >
> > > > Thanks
> > > > /Mattias
> > > >
> > > >
> > > >|||Did you have this issue resolved ?
Any hints / links /pointers/ thots will be appreciated
Thanks,
GA
"BBogart" wrote:
> I also suspect a ticket is expiring.
> I am still working with MS on this with no resolution yet.
> Once we see a failure, failures continue regardless of logging off and back
> on until sql server is restarted. A reboot is not necessary as I previously
> thought.
> We have seen it take as little as a few hours or up to a week or more for
> the failures to start again.
> --
> -b
> "JD Qixcle" wrote:
> > We have seen this in our environment too. One moment a linked server query
> > will work just fine with delegated Windows credentials, the next moment you
> > receive errors like the following:
> >
> > OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> > "Communication link failure".
> > Msg 10054, Level 16, State 1, Line 0
> > TCP Provider: An existing connection was forcibly closed by the remote host.
> > Msg 18452, Level 14, State 1, Line 0
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I am still working on a reproducable way of generating the message, but I
> > seem to have problems a lot when I initially generate a linked server within
> > SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> > I suspect that something in the Kerberos token is expiring. After I logout
> > and login, I can usually start a new session that works (just not today).
> >
> >
> >
> >
> > "Woo" wrote:
> >
> > > I am the other unanswered posting!
> > >
> > > It fails intermittently when we run under 'sa' or a domain account that is
> > > in the local admin group.
> > >
> > > I have raised this through the Microsoft concierge service and they said
> > > there are others with the same problem but no resolutions as yet!
> > >
> > > Wendy
> > >
> > >
> > >
> > >
> > > "BBogart" wrote:
> > >
> > > > We are encountering this problem also. I have contacted MS but they are
> > > > still gathering information.
> > > >
> > > > There is at least one other unanswered post on this here as well, see
> > > > subject = "SQL2005 Linked server authentication drops".
> > > >
> > > > Are you running sql server under under a domain account that is not in the
> > > > local admins group by any chance?
> > > > --
> > > > -b
> > > >
> > > >
> > > > "Mattias" wrote:
> > > >
> > > > > I have a linked server set up between two servers (A & B).
> > > > > Server A is running 2005 and Server B is running 2000.
> > > > > Both servers SQL services are running using a domain user account and have
> > > > > their SPN's registered in the AD.
> > > > >
> > > > > The client connects to Server A using integrated security (TCP/IP and
> > > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > > server B.
> > > > > Delegation is set up in the AD and is working, at least for some time.
> > > > >
> > > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > > the delegation seems to stop working and the client recieves the error
> > > > > "Login
> > > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > > connection." The client is still connected and authenticated using TCP/IP
> > > > > and
> > > > > Kerberos.
> > > > >
> > > > > After a restart of SQL Server on server A the delegation starts working
> > > > > again.
> > > > >
> > > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > > client, and nothing in the sql server logs.
> > > > >
> > > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > > where to start looking.
> > > > >
> > > > > Thanks
> > > > > /Mattias
> > > > >
> > > > >
> > > > >|||--
-b
"DallasBlue" wrote:
> Did you have this issue resolved ?
> Any hints / links /pointers/ thots will be appreciated
> Thanks,
> GA
> "BBogart" wrote:
> > I also suspect a ticket is expiring.
> >
> > I am still working with MS on this with no resolution yet.
> >
> > Once we see a failure, failures continue regardless of logging off and back
> > on until sql server is restarted. A reboot is not necessary as I previously
> > thought.
> >
> > We have seen it take as little as a few hours or up to a week or more for
> > the failures to start again.
> > --
> > -b
> >
> > "JD Qixcle" wrote:
> >
> > > We have seen this in our environment too. One moment a linked server query
> > > will work just fine with delegated Windows credentials, the next moment you
> > > receive errors like the following:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> > > "Communication link failure".
> > > Msg 10054, Level 16, State 1, Line 0
> > > TCP Provider: An existing connection was forcibly closed by the remote host.
> > > Msg 18452, Level 14, State 1, Line 0
> > > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > > Server connection.
> > >
> > > I am still working on a reproducable way of generating the message, but I
> > > seem to have problems a lot when I initially generate a linked server within
> > > SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> > > I suspect that something in the Kerberos token is expiring. After I logout
> > > and login, I can usually start a new session that works (just not today).
> > >
> > >
> > >
> > >
> > > "Woo" wrote:
> > >
> > > > I am the other unanswered posting!
> > > >
> > > > It fails intermittently when we run under 'sa' or a domain account that is
> > > > in the local admin group.
> > > >
> > > > I have raised this through the Microsoft concierge service and they said
> > > > there are others with the same problem but no resolutions as yet!
> > > >
> > > > Wendy
> > > >
> > > >
> > > >
> > > >
> > > > "BBogart" wrote:
> > > >
> > > > > We are encountering this problem also. I have contacted MS but they are
> > > > > still gathering information.
> > > > >
> > > > > There is at least one other unanswered post on this here as well, see
> > > > > subject = "SQL2005 Linked server authentication drops".
> > > > >
> > > > > Are you running sql server under under a domain account that is not in the
> > > > > local admins group by any chance?
> > > > > --
> > > > > -b
> > > > >
> > > > >
> > > > > "Mattias" wrote:
> > > > >
> > > > > > I have a linked server set up between two servers (A & B).
> > > > > > Server A is running 2005 and Server B is running 2000.
> > > > > > Both servers SQL services are running using a domain user account and have
> > > > > > their SPN's registered in the AD.
> > > > > >
> > > > > > The client connects to Server A using integrated security (TCP/IP and
> > > > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > > > server B.
> > > > > > Delegation is set up in the AD and is working, at least for some time.
> > > > > >
> > > > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > > > the delegation seems to stop working and the client recieves the error
> > > > > > "Login
> > > > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > > > connection." The client is still connected and authenticated using TCP/IP
> > > > > > and
> > > > > > Kerberos.
> > > > > >
> > > > > > After a restart of SQL Server on server A the delegation starts working
> > > > > > again.
> > > > > >
> > > > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > > > client, and nothing in the sql server logs.
> > > > > >
> > > > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > > > where to start looking.
> > > > > >
> > > > > > Thanks
> > > > > > /Mattias
> > > > > >
> > > > > >
> > > > > >

Delegation stops working after a while

delbkups not working for SQL 2000

Hello,

In our maintenance plan for SQL Server 2000, we are performing full backups which in turn has their retention setting to 2 days. Unfortunately, it appears that this option is not working. Has anybody else seen this? We are running on Windows 2000 Advanced Server.

-LIONSPARCHave you changes the path...have you looked in the job history, what about the error logs...

maybe try and disable it, and create a new one...

Never had a problem with it...|||We are thinking long the lines of...

Microsoft Knowledge Base Article - 278667
BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or ME Computers

But I will first try re-creating the maintenance plan.

-lionsparc|||WIN2k Advanced server?

I would highly doubt it...MS might as well fold there tent, hide the snake oil and close up the 3 card montee game now...

oooppps didn't mean that they don't make a decent product...|||We're LOL on that one. I will let you know how we make out. One thing that is interesting is that we "changed" the maintenance plan to remove files 2 days+ old. So the original didn't have it. However, we did check the job history and appears the -delbckups 2DAYS isn't showing anything in the log. We see "delete old text files" but that's it. Do you know how it should show in the history? Thanks!

-LIONSPARC|||I can't find any messaging indicating the delets...just the maint logs..nothing there...

As an alternative...you can create your own sprocs to take care of this...

And create any logging you want...very customizable...

good lucksql

Wednesday, March 21, 2012

Delay in connection

I'm working with Sql 2005 developer edition

It works well but some times I get long delay in connection and read data.is it any way to solve the problem?

for more information whene is working well I can connect to database

and get all information I need in .1 sec. when is going to be late this

action may takes 20 sec

What client provider/driver and what version are you using - MDAC, SQL Native Client, .Net SqlCLient 1.1, 2.0, etc.?

Is the client ans SQL Server on the same machine or not?

Is there are a firewall protecting the SQL Server machine, specifically its SQL Server TCP port (by default 1433)?

Most of the clients attempt TCP connection first. If the SQL Server is protected by a firewall without exception for SQL Server port this usually fails in about 21 seconds. Then they try other protocols, usuallu Named Pipes. This would succeed fast if File and Printer Sharing is enabled on the server. The protocol information gets cached for certain time, which could be an explanation why it sometimes takes short time and other times ~20 seconds.

|||Hi

Glad to hear u for my problem

I run my program on the same machin as sql server is on

and I use Sql server developer edition|||

Do you know what step takes the long time - connection etsablishment, a query, etc.?

Also, are you using C# (SqlCLient), C++ (OLEDB?, ODBC? - MDAC or SQL Native Client)?

Delay in connection

I'm working with Sql 2005 developer edition
It works well but some times I get long delay in connection and read data.is it any way to solve the problem?
for more information whene is working well I can connect to database and get all information I need in .1 sec. when is going to be late this action may takes 20 sec

What client provider/driver and what version are you using - MDAC, SQL Native Client, .Net SqlCLient 1.1, 2.0, etc.?

Is the client ans SQL Server on the same machine or not?

Is there are a firewall protecting the SQL Server machine, specifically its SQL Server TCP port (by default 1433)?

|||Hi
Glad to hear u for my problem
I run my program on the same machin as sql server is on
and I use Sql server developer edition
|||

Do you know what step takes the long time - connection etsablishment, a query, etc.?

Also, are you using C# (SqlCLient), C++ (OLEDB?, ODBC? - MDAC or SQL Native Client)?

Wednesday, March 7, 2012

define/set parameter values in Management Studio?

If you have a query w/ a parameter in it (copied from reporting services, but now working with management studio), how do you declare the parameter and set its value in sql server management studio? I've had to unfortunately find all occurrences of the parameters and replace their values each time I want to change them when executing the mdx.

Unfortunately, I don't believe their is an easy and straightforward way to do this. About the only option I've been able to find is wrapping the MDX query in an XMLA query, which allows you to have parameters and define their values. The problem with this approach is that the result of the XMLA query is an XML response which contains a lot of metadata as well as the data (but it is not in any type of format that would allow you to easily look at just the query results).

Here's a link to a topic in BOL that shows an example of this:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/a4754d16-d9c4-49f6-9be0-392180b912e4.htm

If your query is a relatively simple one that returns a relatively simple result, this approach might work...

HTH,

Dave Fackler

Saturday, February 25, 2012

Default Values properties (table level) not working.

I am using SQL Server Management Studio Express (SSMSE) with SQL Server Express as my database tools/database to assign the ‘Default Value’ for a column at the table level.

Going over the basics… using the database tools (SSMSE) and when inserting a new row; all rows by default have a 'Null' value. Ok.
If a default value is assigned to a column (table level) using the database tools, the default value is inserted correctly if that column has a null value upon the creation of a new row. Ok.

This works fine when I am working with SSMSE on tables (inserting, deleting editing rows etc.) within my database…

But this doesn’t apply or work for adding new rows with datasets (example: using the default insert, update, delete statements provided by the wizard and using a DataGridView). My table level default values are not inserted into the new row, instead my column that had a default value assigned; now has a 'Null' value in the new row that was created by the dataset.

Isn’t a Null value is still a Null value for a new row?

Shouldn’t the database engine supply that ‘default value’ for a field that had a ‘Null’ value upon row creation?

I have always thought of a table level column ‘Default Value property’ as a trigger that tests for nulls and inserts the default value if that column has a null value when the new row is created. So I am expecting the database engine to insert the default value for that column, not the dataset when the value inserted into that column is null for a new row. I really don't need a (table level) column default property that only works with database tools for inserting new rows, that doesn't help me... totally baffled here...
Thanks

Hey Rick.

Default values will be applied to a column when NO explicit value is specified for the column in the corresponding insert (this includes a <NULL> explicit value)...so, for example, assume I have a table with 2 columns, colA and colB, and on colB I have a default value of 'colBDefault' specified...the following statement will end up with a record that includes a row with 'colAvalue' for colA, and null for colB, because I am explicitly saying to use a null value for colB:

insert table (colA, colB) select 'colAvalue', null

However, the following statement will end up with a value of 'colAvalue' for colA, and the default value of 'colBDefault' for colB, because no explicit value is specified for colB:

insert table (colA) select 'colAvalue'

I'd bet that the DataGridView is specifying all columns with a null value for anything you don't specify. To prove this, you could run a trace on the Sql server to see what the actual insert command being executed is...

HTH,

|||Hello Chad,

Thanks for the reply. That did help.

Unfortunately I couldn't get the ADO.Net trace logging to work... my tracing abilities are pretty much nil...

Another way to look at this is I am only pulling certain text fields that I want (no default value assigned) from the adapter / dataset for that table, not all of the fields from that particlular table.
The fields that I have designated a default value for are not included in the dataset, so they do not have an insert command etc. (or value assigned) for those fields for that table.
But... those fields that are *not* included in the insert statement etc. for that table do in fact show a value of 'Null' for the new row even though they have a default value assigned for them at the table level...

Example:
Fields: (ID), (LastName), (FirstName), ((Age) - default value set to 0), ((DeptNo) - default set to 100)

The adapter is only pulling fields: (ID), (LastName) and (FirstName); the insert etc. commands only pertain to those fields.
When a new row is inserted fields: (Age) and (DeptNo) do show a 'Null' value, not their assigned default value.

Thanks,
Rick

|||Whooooops...

My apologies!

It does work as you suggested!
I literally had six different forms to test things and simply got them mixed up, of what worked and what didn't!!!

Thanks,
Rick

Tuesday, February 14, 2012

default schema not working?

I am confused.

I added my NT account to the sql server logins for my sql server (2005), then I added a corresponding user account to my database. I then set my default schema. I connect to the database, and the default schema seems to be set to dbo.

Can anyone thing of a reason why this might be happening? Is there some sort of override if I have additional privledges on the server?

I appreciate any thoughts...

-Mike Graham

AH HA !!!

In the BOL, it says "you cannot change the default schema for a user that is mapped a windows group" - my account was in the administrators group which had been added to the logins for the sql server.

I remove the group and it started working without even closing the query window.

YES !!!!!!!!!!!!

|||

Ok - little more info:

I also noticed that if i am in the server role: sysadmin, the default schema assignment doesn't work, but if I remove myself, then it works.

|||

If you are a sysadmin, you are also a member of db_owner, so your default schema will be dbo.

The BOL quote refers to the fact that you cannot set a default schema for a database principal that is mapped to a Windows group, NOT that you cannot set it for a database principal who is mapped to a Windows account that belongs to some group.

So, what prevented the default_schema setting from working was the fact that you were a sysadmin.

Thanks
Laurentiu

Default Report Server Web Page

I've only just started working on Sql Server Reporting Services 2005, My role is mainly that of administrator (some other unfortunate sole will be doing the development)

However, I'm having some problems configuring the "Welcome" screen on the server. Currently it looks like a default web directory, I can click through to Data Sources and a directory containing the current test reports:

Looks like:

Wednesday, January 10, 2007 4:14 PM <dir> Data Sources

Wednesday, January 10, 2007 4:14 PM <dir> Test Reports

Clicking on Test Reports

gives:

[To Parent Directory]
Wednesday, January 10, 2007 4:14 PM 15193 Basic Test Report

Question is:
Is there an XML (Or XSL) file somewhere where I can define how the website displays the report directories and names.
Or
Do i need to build a custom web app?

A link in the right direction would be most appreciated!

Thanks

Sam

to access report manager go to http://servername/reports

I hope thats what you're looking for.

|||Hi,

Thanks for the reply, unfortunately thats not what I meant.
What I did need was just some initial guidance on formatting the user access on to the reporting system.

Thanks

Sam|||Forgive me if I dont know what you're looking for. Are you looking for a page that authenticates the user? Maybe if you give us more details we can help you.|||its the http://servername/reportserver page

(I'm guessing now after a little more digging that it is the same format URL for all installations)

Anyway, I just want to be able to customise that to suit the company's needs. I'm not sure if there is a way to edit an XSL file somewhere or if I need to start building a custom web application to act as a portal on to the report server.

Thanks again

Sam|||Here's an example of the page i mean:
http://www.ondotnet.com/dotnet/2004/11/01/graphics/image013.gif

As you can see it looks very basic, I was hoping to be able to make it look a bit prettier :)

Thanks
Sam|||

Sam, like I said in my first post, the portal to the web server is found at

http://servername/reports

Please try that and tell me if thats not what you're looking for.

|||

Ralph,

I think Sam is wanting to know how to modify the page that comes up when you go to http://ServerName/Reports or http://ServerName/ReportServer.

Jarret

|||

Sam Vella wrote:
its the http://servername/reportserver page
(I'm guessing now after a little more digging that it is the same format URL for all installations)
Anyway, I just want to be able to customise that to suit the company's needs. I'm not sure if there is a way to edit an XSL file somewhere or if I need to start building a custom web application to act as a portal on to the report server.
Thanks again
Sam

Sam,

The only way that I know of to accomplish what you are looking for is to create a web application using Visual Studio 2005. Basically you could create a Login page (optionally) along with your own Index page (formatted however you like) with links to the reports.

However, if you're not a developer I must warn you ahead of time that it can get rather involved.

|||Cheers mate,

Just as I feared

No probs about the time just yet either

Sam|||If you're going to go that route, let me know and I'll provide some further direction.|||Just a link to a suitable web resource would be nice
Thanks!|||

I have the same issue, but I was also using reporting services in sql 2000 previously and had a great looking page by default from

http://<servername>/reports/pages/folder.aspx

this took me to a fully formatted page with the ability to set subscriptions. (see below - I've taken out the report links but they were all there)

the equivalent in 2005 found at

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Pages

is just what Sam's found a does look very basic.

AC Yule - SQL Reporting Services

Home

Home | My Subscriptions | Site Settings | Help

Help

Search for:

Please enter one or more search terms in the search box

Contents

Properties

Would like to know if anyone's aware that i'm maybe not picking up the right page / link / location in my 2005 installation as i don't see any reason why they would 'downgrade' it.

Thanks

Steven

|||

Sam Vella wrote:
Just a link to a suitable web resource would be nice
Thanks!

I don't exactly have one link that will explain it all.

This link will give you the concepts to use in Visual Studio and SQL (Even though it isn't VS 2005): http://www.codeproject.com/aspnet/AHCreatRepsAspNet.asp

If you want your customized page to be viewable in a web browser, I have found the easiest way is to install Visual Studio Web Application Projects from here: http://msdn2.microsoft.com/en-us/asp.net/aa336618.aspx

You will also need to enable Internet Information Services. (Control Panel -> Add/Remove Programs -> Add/Remove Windows Components)

Once you have created your Visual Studio Web Application -- create a basic index page with link buttons to your reports -- you will need to publish it to c:\Inetpub\wwwroot using IIS.

If you get stuck somewhere, drop me an e-mail (gmoore@.genevasoftware.com).

|||

Hello Steven,

This page is also in RS 2005. Type this into your browser and you should get the same page as you pasted in your above post. http://<servername>/reports

Jarret

Default Report Server Web Page

I've only just started working on Sql Server Reporting Services 2005, My role is mainly that of administrator (some other unfortunate sole will be doing the development)

Looks like:

Wednesday, January 10, 2007 4:14 PM <dir> Data Sources

Wednesday, January 10, 2007 4:14 PM <dir> Test Reports

Clicking on Test Reports

gives:

[To Parent Directory]
Wednesday, January 10, 2007 4:14 PM 15193 Basic Test Report

Question is:
Is there an XML (Or XSL) file somewhere where I can define how the website displays the report directories and names.
Or
Do i need to build a custom web app?

A link in the right direction would be most appreciated!

Thanks

Sam

to access report manager go to http://servername/reports

I hope thats what you're looking for.

|||Hi,

Thanks for the reply, unfortunately thats not what I meant.
What I did need was just some initial guidance on formatting the user access on to the reporting system.

Thanks

(I'm guessing now after a little more digging that it is the same format URL for all installations)

Thanks again

Sam|||Here's an example of the page i mean:
http://www.ondotnet.com/dotnet/2004/11/01/graphics/image013.gif

As you can see it looks very basic, I was hoping to be able to make it look a bit prettier :)

Thanks
Sam|||

Sam, like I said in my first post, the portal to the web server is found at

http://servername/reports

Please try that and tell me if thats not what you're looking for.

|||

Ralph,

I think Sam is wanting to know how to modify the page that comes up when you go to http://ServerName/Reports or http://ServerName/ReportServer.

Jarret

|||

Sam Vella wrote:
its the http://servername/reportserver page
(I'm guessing now after a little more digging that it is the same format URL for all installations)
Anyway, I just want to be able to customise that to suit the company's needs. I'm not sure if there is a way to edit an XSL file somewhere or if I need to start building a custom web application to act as a portal on to the report server.
Thanks again
Sam

Sam,

However, if you're not a developer I must warn you ahead of time that it can get rather involved.

|||Cheers mate,

Just as I feared

No probs about the time just yet either

Sam|||If you're going to go that route, let me know and I'll provide some further direction.|||Just a link to a suitable web resource would be nice
Thanks!|||

I have the same issue, but I was also using reporting services in sql 2000 previously and had a great looking page by default from

http://<servername>/reports/pages/folder.aspx

this took me to a fully formatted page with the ability to set subscriptions. (see below - I've taken out the report links but they were all there)

the equivalent in 2005 found at

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Pages

is just what Sam's found a does look very basic.

AC Yule - SQL Reporting Services

Home

Home | My Subscriptions | Site Settings | Help

Help

Search for:

Please enter one or more search terms in the search box

Contents

Properties

Would like to know if anyone's aware that i'm maybe not picking up the right page / link / location in my 2005 installation as i don't see any reason why they would 'downgrade' it.

Thanks

Steven

|||

Sam Vella wrote:
Just a link to a suitable web resource would be nice
Thanks!

I don't exactly have one link that will explain it all.

This link will give you the concepts to use in Visual Studio and SQL (Even though it isn't VS 2005): http://www.codeproject.com/aspnet/AHCreatRepsAspNet.asp

You will also need to enable Internet Information Services. (Control Panel -> Add/Remove Programs -> Add/Remove Windows Components)

Once you have created your Visual Studio Web Application -- create a basic index page with link buttons to your reports -- you will need to publish it to c:\Inetpub\wwwroot using IIS.

If you get stuck somewhere, drop me an e-mail (gmoore@.genevasoftware.com).

|||

Hello Steven,

This page is also in RS 2005. Type this into your browser and you should get the same page as you pasted in your above post. http://<servername>/reports

Jarret

Thursday, March 29, 2012

Delete data from GridView and ObjectDataSource

Tuesday, March 27, 2012

Delete connection file

Thursday, March 22, 2012

Delete & create a partition

Delete & create a partition

Delete & create a partition

Delet function not working on Gridview

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.

Server Error in '/dave' Application.

Configuration Error

Delegation through Linked Server Stops working

Delegation stops working after a while

Delegation stops working after a while

delbkups not working for SQL 2000

Wednesday, March 21, 2012

Delay in connection

Delay in connection

Wednesday, March 7, 2012

define/set parameter values in Management Studio?

Saturday, February 25, 2012

Default Values properties (table level) not working.

Tuesday, February 14, 2012

default schema not working?

Default Report Server Web Page

Default Report Server Web Page

sql delete multiple tables

Blog Archive

About Me

Thursday, March 29, 2012

Tuesday, March 27, 2012

Thursday, March 22, 2012

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.The statement has been terminated.

Server Error in '/dave' Application.

Configuration Error

Wednesday, March 21, 2012

Wednesday, March 7, 2012

Saturday, February 25, 2012

Tuesday, February 14, 2012

Blog Archive

About Me

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.