hi, i'm working with LINQ to SQL and i have a table called Account, in that is Type, which is an int in the database but i map it to a AccountType enum, this works fine, but if i go into the dbml designer and change something later on, it gets errors, so i have to change all int->enum mappings back to int, recompile and then set them again!
has anyone else had this?
kindest regards
Hey guys, I bet you didn't guess it but I'm posting here because i need help :P
What I'm trying to do is create a procdure that will run a task on the first Working day of the week.
Normally monday but then theres public holidays etc etc.
on a normal week the task runs monday, on public holiday weeks it is tuesday, and christmas even pushs it back to wednesday.
so far im using the dts designer to do this
What I have is:
a table with the last run date(not sure I even need this now)
a table with a list of public holidays(date) along with a year column
a sql statment getting the last ran date and storing it into a global variable glastrundate
a activex vbscript getting the current year, which is then stored into a globalvariable(gcurrentyear)
then gcurrentyear is then used in a select statement to get the public holidays that are in that year, which are then stored to a rowset (gpubdays)
(select pubdays from tbl_pubdays where pubyear = ?)
Now the problem, I am trying to use another axtivex script to check the current date against each row in the global gpubdays. well really I just can't get the for each loop to work, was trying to use it like:
for each row(or pubday) in gpubdays
if day = today then
task fails and will not run the rest of the procedure
end if
next
sorry if it isnt very clear, still kinda new to SQL server
thanks if you help ^_^
ps. please dont go writing all the code for me, just a nudge in the general right direction
I know I shouldn't rely on sysdepends because it's got all sorts of problems with broken dependency chains, etc. But it's better than nothing for finding dependencies, short of rolling your own t-sql parser. So I use sysdepends anyway, with all its faults. I just don't rely on it. anyway, to the point, just an fyi really:
Here's something about sysdepends discovered today that annoys me. If you introduce a dependency of a proc on a table by selecting from the table in the proc, the sql parser discovers this and dutifully adds a row to sysdepends. Very well. But if you do the same select into a temp table, sysdepends doesn't pick up the dependency! Here's what I mean:
use master
go
drop database test_sysdepends
go
create database test_sysdepends
go
use test_sysdepends
go
create table MyTable01 (id int)
go
create proc MyProc01
as
select id from MyTable01
go
create proc MyProc02
as
create table #t (id int)
-- comment this next line out and the dependency is picked up.
-- but as long as we insert into the temp table, we don't pick it up... :(
insert into #t
select id from MyTable01
select id from #t
go
select
so2.name as parent
,so1.name as dependent
from sysdepends d
join sysobjects so1 on d.id=so1.id
join sysobjects so2 on d.depid=so2.id
go
results:
parent dependent
--------- ---------
MyTable01 MyProc01
i am seeing this on 2005 sp1, also on 2000 (msde)
grrr...
www.elsasoft.org
Well all bugs are annoying, but this one is particularly annoying because it kills the BIDS session with consequent loss of all changes made since the last save.
Click on a control flow task. Position the cursor so that it changes to the 4-way pointer. Drag the task a little bit. The UI locks up, and then BIDS disappears.
I can't repro this on demand, but it has happened about 6 times in the past month. Anyone else seen this one?
Can someone please help me.
I am running a 'simple' stored procedure from the Query Analyser (command - FindCustomer L18239, [ ]) which should return a name from a customer table from the input id. It does return the name but I keep getting the message
'Server: Msg 245, Level 16, State 1, Procedure FindCustomer, Line 9
Syntax error converting the varchar value 'Kevin ' to a column of data type int.' - Any ideas what I've done wrong?, thanks.
Kevin.
stored-proc (FindCustomer)-
CREATE PROCEDURE FindCustomer
(@CustID [char](30),
@CustName [char](15) OUTPUT)
AS
BEGIN
SELECT @CustName = (SELECT FirstName from Customer where CustId = @CustID)
RETURN @CustName
END
I'm recieving the following error:
The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine 'machine information' Error: 2147749896 (0x80041008).
I've researched the error and found out some people were running batch scripts to reinstall wmi, but none of them worked for me. One of them I gave up on after thirty minutes and it wasn't doing anything.
Please give some suggestions on how I can resolve this issue.
Greetings SSIS friends,
I have the following problem in SSIS and it's driving me nuts!!!!!
My situation is as follows :
Data Source 1 & Data Source 2are joined using a merge join. This bit works fine.
The output of the above join is then joined to a third data source but this time, I only get 63 rows coming through instead of 77097 even though the join key in the second merge join component is the same as the first one!!!
I thought I was going mad so I decided to see if the same problem occurs if I was to do this with T-SQL. I created 3 temporary tables for each of my data sources.
I did an inner join between tmpTable_Source1 and tmpTable_Source2, I then stored the result in tempTable4
The final inner join was performed between tempTable4 and tempTable3 and the result produced 77097 and not 63 rows.
What the hell is SSIS playing at?! The merge keys I used in T-SQL is the same one I used in my SSIS package!!!!
Hello,
I recently migrated from 2000 to 2005 database. On the new server, I kept getting this type of error message when there is some sort of join involved and they are not complex join either.
Error - "A severe error occurred on the current command. The results, if any, should be discarded."
ie
select A.Cookie1, A.Cookie2
into BookersLookers_Cookie
from BookersLookers_DataSet A
LEFT JOIN ActionSetup B on A.ActionID = B.ActionID
group by A.Cookie1, A.Cookie2
Is there something in the code where I could prevent this error from happening?
Any insight is appreciated,
-Lawrence
Hi,
Pbbly most of you know this:
Intra-query parallelism caused your server command (process ID #57) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
I've seen MS KnowledgeBase for that (http://support.microsoft.com/default.aspx?scid=kb;EN-US;837983)
But I have some probs with it:
1. I don't have permissions for this kind of queries
"USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO"
2. I don't know what is an "intra-query parallelism"...
I'm attaching the query I use. The query runs OK for some parameters but gets this error msg on other parameters.
I'm using a single CPU on 2003 STD Edition.
Query:
create table #mytemp_table
(client_id int not null, commission_value int null)
insert into #mytemp_table (client_id, commission_value)
select client_id, sum(transfer_value) from users_transfers where transfer_type in (1,3) and is_paid = 1 group by client_id
select
u.name [Vendor],
u.email,
sum(cost) as Payment,
cmpd.vendor_id,
ua.name [Agent],
vd.join_date,
commission_value [paid],
allow_sign
from
reports ra
left outer join codes ctd on ra.affiliation_code_show = ctd.affiliation_code_show
left outer join traffic cmp on cmp.campaign_id = ctd.campaign_id
left outer join traffic_details cmpd on cmp.campaign_id = cmpd.campaign_id
left outer join userssb u on cmpd.vendor_id = u.client_id
left outer join userssb ua on cmpd.agent_id = ua.client_id
left outer join users_details vd on u.client_id = vd.client_id
left outer join #mytemp_table com_paid on com_paid.client_id = u.client_id
left outer join allow_db asd on asd.client_id = u.client_id
where
[conditions]
group by
[all the group fields]
having sum(cost) > 2999
order by u.name
drop table #mytemp_table
I have SQL Server Manager 8.00.2039 installed because I have one program(app) installed that requires it be installed. For the first littlewhile when I had SQL server installed it would only show up in thetaskmanager tray, now MOST of the time, it shows up as a popup on mydesktop and also in my task tray when I first start XP (SP2). It staysthere until I close it. Is there a way to prevent the SQL from poppingup?Thanks
View 3 Replies View RelatedI have a report that has rolled-up groups with the "expand row" + sign on each row. Everything works great except when you click the + the report scrolls everything above the + off the screen which completely re-shifts the users context... which nobody likes... how can we turn this "feature" off please?
thank you!
I am having a problem connecting to my SQL Server 2005 database at the same time with SQL Management Studio Express and from the website at the same time.
Everytime I want to view any pages in my site that access the database, I have to close management studio and restart the server for some reason or i get a failed login error message.
Is it NOT possible to work on the database at the same time as viewing pages in the website that access the database?
This freaking error is realy starting to bug me.
I sure hope that there is a work around or something for this.
Hello, I am having problems inserting information into my DB.
First is the code for the insert
Sub AddCollector(Sender As Object, E As EventArgs)
Message.InnerHtml = ""
If (Page.IsValid)
Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database='MyCollection'"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As SqlCommand
Dim InsertCmd As String = "insert into Collectors (CollectorID, Name, EmailAddress, Password, Information) values (@CollectorID, @Name, @Email, @Password, @Information)"
myCommand = New SqlCommand(InsertCmd, myConnection)
myCommand.Connection.Open()
myCommand.Parameters.Add(New SqlParameter("@CollectorID", SqlDbType.NVarChar, 50))
myCommand.Parameters("@CollectorID").Value = CollectorID.Text
myCommand.Parameters.Add(New SqlParameter("@Name", SqlDbType.NVarChar, 50))
myCommand.Parameters("@Name").Value = Name.Text
myCommand.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar, 50))
myCommand.Parameters("@Email").Value = EmailAddress.Text
myCommand.Parameters.Add(New SqlParameter("@Password", SqlDbType.NVarChar, 50))
myCommand.Parameters("@Password").Value = Password.Text
myCommand.Parameters.Add(New SqlParameter("@Information", SqlDbType.NVarChar, 3000))
myCommand.Parameters("@Information").Value = Information.Text
Try
myCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>"
Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record"
End If
Message.Style("color") = "red"
End Try
myCommand.Connection.Close()
End If
End Sub
No matter what I get a "Could not add record" message
Even substituting the insert command string with my stored procedure I would get the same thing
Stored Procedure:
CREATE Procedure CollectorAdd
(
@Name nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50),
@Information nvarchar(3000),
@CustomerID int OUTPUT
)
AS
INSERT Collectors
(
Name,
EMailAddress,
Password,
Information
)
VALUES
(
@Name,
@Email,
@Password,
@Information
)
GO
Can anyone see any problems with this code? It looks good to me but I get the same message always.
Thanks
Originally I started using SQL Server Management Studio on the server directly. When I would right click on a view->edit I would essentially get a Alter Script of the view. Clicking on view-> design would open the query editor.
However I decided to connect to the server from my desktop instead using a local installation of SQL Server Management Studio. Now when I right click on a view I only get the modify option which opens the Query Editor and not the actual code that can be scripted. I know I can just right click->script view as->alter to to get similar results. I just can't seem to find any options to change this functionality. Does anybody know how to change this or is this a limitation of a desktop installaion?
Thanks
So on my local server I have a username CWI. I have my main DB: CW.
CWI is the owner of 5 schemas on CW, and everything works great.
---
I now go and create a new DataBase called CWTest. I want to now add the user CWI to the security section of CWTest (The same way I did it in 2000).
However, now I get the error message:
"The login already has an account under a different user name."
When I created my DataBase, IT had the default user, but now I want to add another user so I can create my schemas.
---
On our live servers, we will have 100-300 Databases all using the same useraccount as the "God Mode" user.
Any advice?
I have a very confusing/complicated query that I am trying to run and getting not the results that i want.
Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables.
When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.
Select t1.*, t3.field2, t3.field3
FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6
I suspect there is plenty wrong with this query - where should I start correcting it?
Hello, does anyone hate the new interface where you
manage the Table Relationships, Indexes, and etc?
I hate it a lot for these reasons.
1) Dialog window cannot be resized (really really annoying)
2) The Table-Relationship configuration dialog window is not as convenient to use as SQL Server 2000 Enterprise Manager.
I hope this is the correct place to provide feedback and
I hope this will get modified a bit in the next service pack (or) update....
Thanks
Hi
I have one SSIS pkg when I executed this pkg I got following error message
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "column58" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "column58" (250)" failed because truncation occurred, and the truncation row disposition on "output column "column58" (250)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "D:ok_filesmusercm.txt" on data row 1.
(SQL Server Import and Export Wizard)
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
I am using Flat file as a source & SQL 2005 as Destination.my flat file have "Tab" as a column delimiter and "{CR}-{LF}" as row delimiter and TEXT Qualifier is ["].
Advanced setting options for flatfiles were tried, but didn't work.
For solving this problem i am also using Script Component for column 58.
I am also geting more then 60 warnings of truncation
Thanks in Advance
Sachin
Hello!
I try to get a list of ConditionsVersion where Version is MAX for each ConditionsVersion.
I tried something like this (as seen on http://msdn2.microsoft.com/en-us/vcsharp/aa336747.aspx#maxGrouped):
1 List<ConditionsVersion> list = (from cv in ConditionsVersions2 group cv by cv.FKConditions into cv3 select new { 4 PKConditions = cv.PKConditions,5 FKConditions = cv.FKConditions,6 MaxVersion = cv.GroupBy.Max(cv => cv.Version)7 CTimestamp = cv.Timestamp8 }).ToList();
But it doesn't work. It would be great if someone knows why.
Thank you!
Well, just played a little bit with that new thing from Microsoft. Genius! Microsoft presented that step backwards as a step forward.
Say good bye to the 3-tier architecture, now any programmer, after 1 week training, will be able to put SELECT * into the source code. No more stored procedures and logic on a server. No more ugly WHERE clauses. Just SELECT * and pass all records in a loop :)
When I looked at the queries, generated by LINQ in SQL profiler, I noticed that they are generated automatically using the same pattern. It is obvious, of course, but now it would be really difficult to trace a problematic query back to the C# code. All updates to table X will look like as identical twins!
On the other side, it is not so bad. We will have soon a lot of projects, failing when they go to the production and face the real volumes of data. And a long queue of companies, crying and asking to save them. Perfect “job security�. Please, use LINQ! Port all your code to LINQ immediately (Laughing demonically like Dr. Evil)
Hm… a second thought, but what could we suggest to these companies, having performance problems with LINQ 3rd party applications, when there is no source code? Now we could at least modify some stored procedures, and with LINQ looks like the only recommendation could be “contact a developer of that application or buy more a powerful server�.
Is it possible to use LINQ to SQL with Report Services? If so, are there any examples, tutorials, etc.?
View 1 Replies View Related Is Linq a feature of sql server 2008 ?
Or is it a feature of DOt net. Visual Studio 2008 ?
Some say linq to sql leads to the death of stored procedures is it correct ?
View 4 Replies View RelatedWill Linq be compatible with Sql Server Everywhere without having to add additional plugins?
View 7 Replies View RelatedHullo am using Asp.Net 3.5, I want to create a usercontrol that is supported to all projects to upload file into sqlserver,
the user just give the database connection string,
tablename, column name depending upon their need, here I develop the
code using LINQ technology.
I Write a class with simple format like below,
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Linq;
/// <summary>
/// Summary description for FILE_MASTER_INSERT
/// </summary>
//namespace Linq2Sql_demo_doc
//{
[System.Data.Linq.Mapping.Table(Name = "FILE_MASTER")]
public partial class FILE_MASTER
{
string Tablename;
public FILE_MASTER()
{
//
// TODO: Add constructor logic here
//
}
[System.Data.Linq.Mapping.Column(Name="filename")]
public string FileName
{
get;
set;
}
[System.Data.Linq.Mapping.Column(Name = "file_content")]
public byte[] file_content
{
get;
set;
}
[System.Data.Linq.Mapping.Column(Name = "file_id", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
public int file_id
{
get;
set;
}
}
public class TestDB : DataContext
{
public Table<FILE_MASTER> FILE_MASTERs;
//Initializing base class constructor
public TestDB(string s) : base(s) { }
}
This
is working but, this is suitable only for single table, I expect
depending upon the user input automaticaly the tablename, column name
will change in the yellow block codes .
Is there any way to update the tablename , columnname from any other class?
Thank you. Jeyaseelan
I'm new to ASP/VS/Linq and I'm having a small problem.
I have one table setup in SQL Server Express 2005 through Visual Studio 2008. The table name is "Users" and has three columns (accountID, userName, email). AccountID is the primary key and set to auto incriment. I've added a couple of records by hand and it works.
I have a single form with a button, a label, and two text boxes. The button code is below. After entering some fake data that does not already exist in the database and clicking the button I get this.
Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.
I understand that it is trying to insert something into the accountID field but I don't understand why since I'm only providing a username and e-mail address to insert.
Your help is greatly appreciated.protected void Button1_Click(object sender, EventArgs e)
{
MyDatabaseDataContext db = new MyDatabaseDataContext();
var query = from u in db.Users
where u.email == txtEmail.Text
select u;
var count = query.Count();
if (count == 0)
{
//Create a new user object.
User newUser = new User();
newUser.username = txtUsername.Text;
newUser.email = txtEmail.Text;
//Add the user to the User table.
db.Users.InsertOnSubmit(newUser);
db.SubmitChanges();
}
else
{
Label1.Text = txtEmail.Text + " already exists in the database.";
}
I have a supplier table with all my suppliers in it. I list them in a gridview. In this gridview, there is a link next to each record to an edit page. Below is the code for the edit page. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim db As New orderLinqDataContext
Dim q = db.selectSupplierByID(Request.QueryString("id"))
Dim r As New System.Data.DataTable
txtFriend.Text = q(0).friendlyName
txtAdd1.Text = q(0).address1
txtAdd2.Text = q(0).address2
txtAdd3.Text = q(0).address3
txtAdd4.Text = q(0).address4
txtName.Text = q(0).supplierName
txtPhone.Text = q(0).phone
txtFax.Text = q(0).fax
txtPostCode.Text = q(0).postCode
End Sub
This causes a runtime error. The error is: "The query results cannot be enumerated more than once." and the txtAdd1.text line is highlighted.
How am I supposed to get at the data so I can fill my text boxes this way?
Thanks,
var query = from cloc in context.t_companylocs where (cloc.ref_company == companyid) && (cloc.street == attributes["Street"]) && (cloc.postalcode == attributes["Postalcode"]) && (cloc.country in countrylist) && (cloc.city in citylist) select cloc; attributes is a Dictionary<string, string> object.countrylist and citylist are List<string> objects.Of course the syntax above doesn't work. It's basically what I'm trying to achieve :-)A quick and dirty solution would be to just drop the two where constraints containing the "where in" statement and handle that part in the following foreach() loop.Can anyone please explain how you would do it properly?regards
View 1 Replies View RelatedDoes anyone have a good example of how to insert data using System.Data.Linq? All the examples I've seen do something likeNorthwindDataContext db = new NorthwindDataContext();var x = new Product() {...}; db.Products.Add(x);db.SubmitChanges(); However I'm not seeing an Add method on System.Data.Linq.Table<T>. Has this changed? Could I somehow not be generating my model correctly?
View 2 Replies View RelatedHello,
How can I get autogenerated value of inserted item with linq to sql?
Thanks
I would like to use LINQ to generate a sql statement that does not use LIKE, but rather uses CONTAINS. Is this possible? If not, my second question is whether or not I can parameterize a SqlCommand that uses CONTAINS. For example the following statement works just fine when I pass in the parameter via SqlCommand.Parameters.AddWithValue()SELECT * FROM [event] WHERE CONTAINS(comments, @searchTerm1)However, the following results in a variable not defined error.SELECT * FROM [event] WHERE (comments LIKE @searchTerm1)Any ideas? Thanks for your help.
View 1 Replies View RelatedHi there,
In 2005.NET, if I used an asp:repeater, I would load the data from source and then manipulate it like so://load the data from source using a predefined connection and SQL Statement.private void LoadMyDataFromSource(){ try { connection.Open(); OleDbDataReader myReader = comm.ExecuteReader(); rptrCatList.DataSource = myReader; rptrCatList.DataBind(); myReader.Close(); } finally { connection.Close(); }} //catch the ItemDataBound event and manipulate the data how I wish before it's loaded into the Repeater.protected void rptrCatList_ItemDataBound(object sender, RepeaterItemEventArgs e) { if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { int title = 4; //this is the index of the column I wish to manipulate((Label)e.Item.FindControl("lblTitle")).Text = ((IDataRecord)e.Item.DataItem).GetString(title) + ":" } }This would give me all the flexbility I ever needed. But now I wish to use linq and since the IDataRecord relies on dataReader sources, I cannot obtain the same flexbility. Can anyone point me in the right direction for obtaining the same kind of flexbility but using a linq-to-SQL dataContext? I wish to manipulate mostData that comes through from my source, so: //loadng the data in via my linq-to-sql context.private void LoadProducts() { MyDataContext db = new MyDataContext (); var all = from p in db.Products_IncontinenceStandards where p.Product_Cat == "Category1" select p; rpterCat.DataSource = all; rpterCat.DataBind(); }This is where I get stuck because, even though I can still capture the ItemDataBound event since I have databound the Repeater to the linqSource, I can't access the individual fields (or this is what I think I can't do!), Does anyone know how to relate to this so I can create the same effect? Many Thanks,Nathan Channon
Hi,
I am beginner in SQL Server So excuse me if my question is stange for you.
I have to work on an sql server 2005. I need to extract data from this server in XML file. I have look at a lots of thing and I find two solutions:
- Use SSIS
- Create a home made solution with LINQ in c#
Clearly I do not have enouth experience to see with solution are the most available? Which are the things required to use those solutions?
Thanks