Hi all,
Do I still need to write the .sql query statements (in SSMS) if I use the ADO.NET connected classes like GetSchema method or the DbDataAdaptor Object?
I'm very new to Visual Studio 2005 & SQL Server 2005..
Thanks!
As you all know, in Framework 2.0 on the desktop platform you have the convenient "abstract" Db* classes that can be used to write more generic code. For instance System.Data.SqlClient.SqlConnection inherits from System.Data.Common.DbConnection, and so forth. Now the docs clearly say that this applies to the corresponding classes in the Compact Framework. And i have seen this implied by other web searchs I've done. However this does not seem to be the case -- or, I'm missing something somewhere.
The following simple code on the desktop platform compiles OK:
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
DbConnection conn = new SqlConnection();
However it doesn't compile in a compact framework project: "Cannot implicitly convert type 'System.Data.SqlClient.SqlConnection' to 'System.Data.Common.DbConnection'".
Reflector also indicates that the Compact Framework class doesn't inherit from DbConnection.
OK, so am I missing something here, or what was the reason that Microsoft did it differently??
Anyone have a recommendation on SSIS classes (providers and instructors)? It looks like Brian Knight's classes must be at the top of the list. His classes are always full!
I have a table that I am trying to query to display all data connected to a id in one row. What is the best way to achieve this?
Something like this:
ID| CRS1 | CRS2 | CRS3| CRS4 ....
1 | A101 | A102 | A103 | A104 ....
CREATE TABLE Class
(
id int,
yr int,
trm varchar(2),
crs varchar(30)
[Code] .....
Hello specialists.
Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.
Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E
How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within junction-table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.
The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo
Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison
Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C
4 Group_E
Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4
I hope you can help me cracking this nut.
Thx in advance. Greetings Fred
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
I'm still trying to transition from a DB-centric view to an OO view,
and I could use some help from any of you OO-design wizards out there. A very common db representation is Table A, Table B, and Table A_B, which maps the two in a one-to-many relation. E.g., create table Companies ( companyId int, name varchar ); create table Products ( productId int, name varchar ); create table Companies_Products ( companyId int, productId int ); So how does this translate into object-land? I started with: public class Product public id as integer public name as string end class public clas Company public id as integer public name as string public products as generic.list(of Product) end class OK,
so far, so good -- I've represented most of my data. But I've lost some
important information: given a Product object, I have no easy way to
find out which company sells it. SQL: scan the Companies_Products table OO: iterate through companies, nested iteration through company.products. Do I just add a function to my data access layer that does the lookup? Is that allowed? On
a side note: can anyone recommend a good intro to OO design
specifically for database programmers? It seems like most of my
confusion comes from issues of translating tables into classes. Thanks!
I am using SQLXML to pass data back and forth, well I am trying to. Itry to follow a tutorial but it didn't work completely. Here is myproblem. After everything executes with NO errors, my class has nodata. Here were my steps.1. In Query Analyzer, I ran my Select statement....Select * From Notifications Where UserID = '4192' and ParentID ISNULL for XML AUTOIt returned...<Notifications ID="1" UserID="4192"CreatedWhen="2004-08-31T00:00:00" ModifiedWhen="2004-08-31T00:00:00"ModifiedBy="1" Class="1" Name="CONSILIUMSOFT" Enabled="1"/>2. I saved the result in Test.xml.3. Ran XSD on it. C:Test>XSD TEST.XMLThis MS utility wrote out the schema file, TEST.XSD4. Ran XSD on it to create the class. C:Test>XSD TEST.XSD /classes/language:csThis MS utility wrote out the CS class file, TEST.cs5. Added the CS file to my project, create a class around it and thecode is below.My problem is that when I put a break point where is says BR (in thecode below), pNotifications has no data in it. All the values areNULL.ANY ideas? I getting desperate....I should mention that I am doingthis in ASP.NET and SQL 2000 sp3Ralph Kraussewww.consiliumsoft.comUse the START button? Then you need CSFastRunII...A new kind of application launcher integrated in the taskbar!ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg************CODE*******************public class CNotifications{SqlXmlCommandobjXMLCommand = newSqlXmlCommand(ConfigurationSettings.AppSettings["XMLConnectionString"]);NotificationspNotifications = new Notifications();public CNotifications(int iID){try{objXMLCommand.RootTag = "Notifications";objXMLCommand.CommandText = @"Select * From Notifications WhereUserID = '" + iID + "' and ParentID IS NULL for XML AUTO";objXMLCommand.CommandType = SqlXmlCommandType.Sql;objXMLCommand.ClientSideXml = true;XmlReader pXMLReader = objXMLCommand.ExecuteXmlReader();XmlSerializer pSerializer = newXmlSerializer(pNotifications.GetType());pNotifications = (Notifications)pSerializer.Deserialize(pXMLReader) ;pXMLReader.Close();}catch (SqlXmlException objEx){objEx.ErrorStream.Position = 0;string strErr = (new StreamReader(objEx.ErrorStream).ReadToEnd());throw new Exception (strErr);}BP}}
View 1 Replies View Relatedahi all
iam new to VS.net 2005 and iam trying to convert ready made application from vb.net 2003 to vb.net 2005
and i cant find the sqlcedataadapter
its a windows CE application uses SQL Server CE
and i imported the system.data.sqlsrverce
and i added the sqlserverce refrence
may be its a new classes?
I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense.
I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine.
What gives with only having Microsoft.SqlServer.Server available?
thanks,
Scott
I show business objects in the ReportViewer as it is described in the MSDN article [1]. In the article they only use intrinsic types like int and string as properties of the business object.
My business objects contains also structs and classes (e.g. Point) as Properties. I expected the Reporting Services to call the ToString() method if it is not an intrinsic type. But it only shows the error string "#Error" in the TextBox.
How can I show a Struct property of my business object as a report value?
[1] MSDN article: "Walkthrough: Using a Business Object Data Source with the ReportViewer Windows Forms Control in Local Processing Mode"
I work on the Clinical Surgery Department Data Application.
The patient gets through the Admission, Surgery, PostSurgery and Discharge stages during his staying in the department. The Doctors just insert their medical data in to the different tables. I have three main classes: User, Patient and Portfolio ( patients portfolio ) and all the business bases on these classes.
The problem is with the Classes per Medical Data Tables. The Medical tables should exist cause it includes the medical data about the patient however what is not clear: Whether I should create Business and Entity classes per each medical data table ( and this is something like 50 tables ) or I should insert all the functions in the Portfolio class and then there will be a mess of functions in one class? What is the accepted way to perform it ?
All --
Please help.
I need to know how to refresh Linq-To-SQL classes after schema changes without dropping and adding them.
Now, when I make a schema change (such as add or delete a column to a table), I want to "refresh" the Context.dbml file.
The only way that seems to work is to drop the table from the Context.dbml designer and then add it back in.
Is there a better way to do this?
How are others doing this?
Please advise.
Thank you.
-- Mark Kamoski
Hi,
Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.
From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?
And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.
Thanks!
Hi,
I'm wondering if it is possible to use IF statements in a query, for example if this was my query:
SELECT Asset, Source, Val1, Val2, Val3
FROM tableA
Say the sign of the Vals is always positive, but based on if the Source field is null i want to make the Vals negative.
Could I do something like this:
SELECT Asset, Source, (IIF Source = null, Val1*-1, Val1), (IIF Source = null, Val2*-1, Val2), (IIF Source = null, Val3*-1, Val3)
FROM tableA
When I try something like this it doesn't work, is there a way to do this in a query?
Thanks.
Using the TOAD application for Oracle, I am able to number the SQL query lines for readability. Can the same be done for SQL Server 2005?
View 4 Replies View RelatedSELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
Wondering if there is a physical or realistic limitation to the numberof UNION statements I can create in a query? I have a client withapprox 250 tables - the data needs to be kept in seperate tables, but Ineed to be filtering them to create single results sets. Each tableholds between 35,000 - 150,000 rows. Should I shoot myself now?lq
View 15 Replies View RelatedI have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn€™t not pass in the parameter.
I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.
One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it€™s run! Not a good solution, but maybe it€™s the best I can do . . .
I have tried many different approaches using different functions, etc. but I€™ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:
1. To add the parameter to the WHERE clause and test for NULL I€™ve used the COALESCE function such as €œWHERE table.fieldname = COALESCE(@Param, table.fieldname)€?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.
2. To handle multiple items, for example, if @Param = €˜3,7,98€™ (essentially, a csv separated list of keys)
Code SnippetWHERE table.fieldname IN(COALESCE(@Param, table.fieldname))
doesn€™t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I€™m using
Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
which works brilliantly in my WHERE statement AS LONG AS @Param ISN€™T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?
I€™ve tried
Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was
Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,€™,€™)))
This fails with €œSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression€? due to the multiple values in the parameter. (I can€™t understand why the line without the CASE statement works, but the CASE line doesn€™t!)
Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!
Hi
I have a stored procedure and i am trying to add case statements to them.. but i am getting an Error. which is
Msg 125, Level 15, State 3, Procedure udf_EndDate, Line 34
Case expressions may only be nested to level 10.
And This is my sproc-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:<Description, ,>
-- =============================================
Create FUNCTION [dbo].[udf_EndDate] (@PeriodId int)
RETURNS datetime
AS
BEGIN
DECLARE
@Month int,
@Year char(4)
SELECT
@Month = [Month],
@Year = Cast([Year] as char(4))
FROM
Period
WHERE
PeriodId = @PeriodId
RETURN
CASE @Month WHEN 1 THEN '1/31/' + @Year ELSE
CASE @Month WHEN 2 THEN '2/28/' + @Year ELSE
CASE @Month WHEN 3 THEN '3/31/' + @Year ELSE
CASE @Month WHEN 4 THEN '4/30/' + @Year ELSE
CASE @Month When 5 Then '5/31/' + @Year ELSE
CASE @Month When 6 Then '6/30/' + @Year ELSE
CASE @Month When 7 Then '7/31/' + @Year ELSE
CASE @Month When 8 Then '8/31/' + @Year ELSE
CASE @Month When 9 Then '9/30/' + @Year ELSE
CASE @Month When 10 Then '10/31/' + @Year ELSE
CASE @Month When 11 Then '11/30/' + @Year ELSE
CASE @Month When 12 Then '12/31/' + @Year ELSE null END
END
END
END
END
END
END
END
END
END
END
END
END
Any help will be appreciated.
Regards
Karen
I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP:
Select Case tCOTSSoftware("Type")
Case 1
execute an SQL Update Command
Case 2
execute a different SQL Update Command
End Select
What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
what is the logic in multiple "FROM" statements inside a single query?
View 1 Replies View RelatedIs it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email
FROM #temp_pregovb, [Survey Return]
INNER JOIN SelectedInvited ON
[Survey Return].SelectedID = SelectedInvited.SelectedID
--*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact]
ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID
WHERE
[panelist contact].email=#temp_pregovb.email
AND SelectedInvited.CellRef IN (
SELECT surveycell
FROm [Survey Cells]
WHERe SurveyRef='5')
Hi, everyone.
I have read a lot of topics about execution plan for query, but I got little.
Please give me some help with examples for comparing different select statements to find the best efficient select statement.
Thank you very much.
These separate COUNT queries are very fast:
SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
But I want to add the COUNT statements, so this is what I did:
select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)
Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?
I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition,
(2) SQL Server Standard Edition, or
(3) by an external tool.
Dim delconn As New SqlConnection _
("data source=15-46SQLEXPRESS;AttachDbFilename =C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataProjectDB.mdf; Integrated Security=True;")
this is my coding,
anyone can spot an error from there??
the database can't be located somehow....
help me ,for god sake
if not Page.IsPostBack then Dim strConn As String = "server=(local);database=NORTHWND" Dim sql as string = "Select EmployeeID, FirstName, LastName from Employees" Dim conn as New SQLConnection(strConn) Dim Cmd As New SqlCommand(sql, conn) Dim objDR As SqlDataReader conn.Open()'This is where it has an error and says-- An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. The NORTHWND database is set up in the web.config fileThanks! Jon
View 3 Replies View RelatedBeginning this morning, when I look in Current Activity in our SQL 6.5 Server, all trusted user connections to my server are showing up under "SA".
We are running mixed security. Most of the connections are through ODBC. We have applied SP5a to SQL.
As far as I know, nothing in SQL has changed. From what others are telling me, nothing in NT or the network has changed.
Something is up. What am I missing? Where can I look?
Please help.
When I try to connect to my SQL server I get the following error.
Microsoft SQL Enterprise Manager
A connection could not be established to MLM1-[SQL Server] Cant allocate space for object 'Syslogs' in database 'tempdb' because the 'logsegment' segment is full. If you ran space in syslogs dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
Ok, I am not up on SQL so I'm not sure how to dump my tempdb database. I have a coworker that has only done this sort of thing in the Enterprise Manager and we can't get there. So how do I do this and make sure that I don't blow away any data? Also if I do dump my tempdb will this cure my problems and how do I avoid having this happen again?
Any help would be greatly appreciated,
Jeb
Hello all,I have a scripts which needs the information about the database inwhich it is running.How can I find out this information.For example if I want to know the User who has satrted this report, Iuse the SYSTEM_USER procedure.Does an equal procedure exist to find out, on which database the useris connected to.RegradsFranz-Josef
View 1 Replies View RelatedHi
I spent the whole week trying to find a solution with a problem of authorization/access on a new website (aspnet2 + slqserver 2005)
The site was running well in the test environnement but once deployed the production site behavior was quite erractic. some clients run ok, others not and that changes for the same client with each session.
it appeared that the problem came from the access to stored procedures. sometime, theirs accesses were authorized sometime denied.
finally, tests showed that the client's userid was either aspnet or iusr_
by putting the same permissions on these 2 accounts, the problem is solved and all the clients are running fine.
now the question : for which reason the client connect randomly to one or the other of these 2 id ? is this normal ?
best regards
using vs2005 to build web pages of asp.net 2.0, The database is SQL Server 2000,.But I always fail to connect.
using the following configuration in web.config:
<appSettings> <add key="DSN_student" value="server=(local);uid=admin;pwd=123456;database=network_course"/> </appSettings>
I also failed to connect using the following configuration in web.config:
<add name="network_courseConnectionString1" connectionString="Data Source=(local);Initial Catalog=network_course;User ID=admin;Password=123456;" providerName="System.Data.SqlClient" />
I am a beginner from China and eager to get answers! Thank you!