Does any one know how I would have to change the following to work with ms sql?
WHERE registrationDate between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');
What I have read implies I would have to construct it using DATEPART() which could become very long winded. Especially when the goal would be to compare on dates which I receive in the following format "2003-12-30 10:07:42". It would be nice to pass them off to the database as is.
Any pointers appreciated.
I'm trying to use RMO to programmatically perform merge synchronization. I've basically copied the SQL Server example code, as follows:
// Create a connection to the Subscriber.
ServerConnection conn = new ServerConnection(subscriberName);
MergePullSubscription subscription;
try
{
// Connect to the Subscriber.
conn.Connect();
// Define the pull subscription.
subscription = new MergePullSubscription(subscriptionDbName, publisherName, publicationDbName,
publicationName, conn, false);
// If the pull subscription exists, then start the synchronization.
if (subscription.LoadProperties())
{
// Check that we have enough metadata to start the agent.
if (subscription.PublisherSecurity != null || subscription.DistributorSecurity != null)
{
subscription.SynchronizationAgent.Synchronize();
}
else
{
throw new ApplicationException("There is insufficent metadata to " +
"synchronize the subscription. Recreate the subscription with " +
"the agent job or supply the required agent properties at run time.");
}
}
else
{
// Do something here if the pull subscription does not exist.
throw new ApplicationException(String.Format(
"A subscription to '{0}' does not exist on {1}",
publicationName, subscriberName));
}
}
catch (Exception ex)
{
// Implement appropriate error handling here.
throw new ApplicationException("The subscription could not be " +
"synchronized. Verify that the subscription has " +
"been defined correctly.", ex);
}
finally
{
conn.Disconnect();
}
I've got the server merge publication defined correctly, but when I run the above code, I get a null reference exception on the call to:
subscription.SynchronizationAgent.Synchronize();
The stack trace is as follows:
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.StatusEventSinkMethod(String message, Int32 percent, Int32* returnValue)
at Test.ConsoleTest.Program.SynchronizePullSubscription() in F:\Visual Studio Projects\Test\source\Test.ConsoleTest\Program.cs:line 124
It seems, from the stack trace, like something to do with the Status event, but I don't have a handler defined, and defining one makes no difference.
Hi,
I want to change the build system path for building my VC++ project in VS2005.
When I try to build the project, I'm getting an error that a specified header file cannot be opened. I have that header file in "Microsoft SDKs\Windows\v7.0\Include".
But the path present in 'Build system path' is "Microsoft SDKs\Windows\v7.0\Include" which i found in the Error dialog box when i tried to open that header file through the code.
So I want to change the path in build system path to "Microsoft SDKs\Windows\v7.0\Include".
How can i do this.? How to open Build system paths in VS2005.?
Hi there guys.
I have this ASP.NET/SQLServer2005 website running on a production server (Win2003, QuadCore, 4GB).
The site runs smoothly normally, but after 2-3 weeks I notice a slow performance on the site (especifically in one particular page). Also I notice that the SQL Server process is using like 2GBs of RAM. So I restart the service, the site runs fast again and the process 300-400MBs.
I'm looking for an explanation of why is this happening? What is SQL Server storing in RAM that takes too much space and degrades the performance?
What can I do to avoid this? I'm trying to avoid restarting the SQLServer everytime this happens.
Thank you!
Is there a way to navigate between tabs in VS similar to the emacs iswtichb-mode or Firefox ubiquity switch tab mode (https://wiki.mozilla.org/Labs/Ubiquity/Ubiquity_0.1_Author_Tutorial#Switching_Tabs) ?
In these apps, you just type a substring of the title of the tab (or buffer for emacs) to switch to this tab. For instance, to switch to a tab named "App_Code/Data/MyProgram.cs", you just invoke the tab switching command and then type "Progr" for instance to list all the tab whose name matches this string, including "MyProgram.cs".
It's much easier to switch between tabs this way when you have a lot of documents open and when you now where to go (which is the case in most situation)
Thank you !
I am using SQL SERVER MANAGEMENT STUDIO.
I want to know the shortcuts for easily aligning the T-SQL Queries and codes in a beautiful format for easy readability and understandablility.
Please Let me know how to align the code without using any SQL SERVER FORMATTING / ALIGNING Tools.
It is Urgent, Waiting for your kind reply at the earliest possible.
Please do give your valuable tips to align the same, right now to align the code i mean to make the code right and left aligned i m using the TAB and SPACE BAR key which becomes very difficult when the length of code is increasing.
Hi,
I have a table, and it is returning the data as -
Column1 Column2 Column3 Column4 Column5 Column6
-------------------------------------------------------------------
6 Joy Mycity NZ 123456 [email protected]
I need to disply it as -
SingleColumn
-----------------------
6
joy
mycity
NZ
123456
[email protected]
How do I do it?
Hi,
I have a table in which there are two columns : 1. import type, 2. Excel import template.
The second column - "Excel import template" should store the whole excel file.
How would I save excel file in databse...can I use binary datatype column, convert excel file to bytes and save the same ?
Thanks in advance !
Hi all
I've searched up and down Google for others with a similar problem, and while I can find the error I don't think that other people have the same base problem that I do.
Basically, I had to create a project for a unit-testing environment in order to run this test suite.
First, I add my original C file, compile, and then a test file (C++) is generated. I then exclude my original source from the project, include this test script (which includes the original source at the top), and then run.
I can debug the test file fine, but when it jumps to the original C file I get the dreaded 'no source code available for the current location' error.
Both files are located within the same location, and I compiled the original file without any issue.
Anybody have any thoughts about this? Its driving me crazy!
I have a table in which there are two columns : 1. import type, 2. Excel import template.
The second column - "Excel import template" should store the whole excel file.
How would I save excel file in databse...can I use binary datatype column, convert excel file to bytes and save the same ?
Thanks in advance !
Context
I am fairly new to database design (=know the basics) and am grappling with how best to design my database for a project I am currently working on.
In short, my database will keep a log of which employees have attended certain health and safety courses throughout the year. There are multiple types of course e.g. moving objects, fire safety, hygiene etc.
In terms of my database design I need to accommodate the following:
Each location can have multiple
divisions
Each division can have multiple
departments
Each department can have multiple
functions
Each function can have multiple job
roles
Each job role can have different
course requirements
Also note that the structure at each location may not be the same e.g. the departments within divisions are not the same across locations and the functions within departments may also differ.
Edit - updated to better articulate problem
Let's assume I am just looking at Location, Division and Department and I have my database as follows:
LocationTable DivisionTable DepartmentTable
LocationID(PK) DivisionID(PK) DepartmentID(PK)
LocationName DivisionName DepartmentName
There is a many-to-many relationship between Locations and Divisions and also between Departments and Divisions.
Suppose I set up a 'Junction Table' as follows:
Location_Division
LocationID(FK)
DivisionID(FK)
Using Location_Division I could easily pull back the Divisions for any Location.
However, suppose I want to pull back all departments for a given Division in a given Location.
If I set up another 'Junction Table' for Division and Department then I can't see how I would differentiate Division by Location?
Division_Department
DivisionID(FK)
DepartmentID(FK)
Location_Division Division_Department
LocationID DivisionID DivisionID DepartmentID
1 1 1 1
1 2 1 2
2 1 2 1
2 2 2 2
Do I need to expand the number of columns in my 'Junction Table' e.g.
Location_Division_Department
LocationID(FK)
DivisionID(FK)
DepartmentID(FK)
Location_Division_Department
LocationID DivisionID DepartmentID
1 1 1
1 1 2
1 1 3
2 1 1
2 1 2
2 1 3
Note
I have completely re-written my original post to better explain the issue I am trying to understand. I have tried to generalise the problem as much as possible.
Also, my thanks to the original people who responded. Hopefully this post makes things a little clearer.
Context
In short, I am struggling to understand the best way to design a small scale database to handle (what I perceive to be) multiple many-to-many relationships.
Imagine the following scenario for a company organisational structure:
Textile Division Marketing Division
| |
---------------------- ----------------------
| | | |
HR Dept Finance Dept HR Dept Finance Dept
| | | |
---------- ---------- ---------- ---------
| | | | | | | |
Payroll Hiring Audit Tax Payroll Hiring Audit Accounts
| | | | | | | |
Emps Emps Emps Emps Emps Emps Emps Emps
NB: Emps denotes a list of employess that work in that area
When I first started with this issue I made four separate tables:
Divisions - Textile, Marketing (PK = DivisionID)
Departments - HR, Finance (PK = DeptID)
Functions - Payroll, Hiring, Audit, Tax, Accounts (PK = FunctionID)
Employees - List of all Employees (PK = EmployeeID)
The problem as I see it is that there are multiple many-to-many relationships i.e. many departments have many divisions and many functions have many departments.
Question
Giving the database structure above, suppose I wanted to do the following:
Get all employees who work in the Payroll function of the Marketing Division
To do this I need to be able to differentiate between the two Payroll departments but I am not sure how this can be done?
I understand that I could build a 'Link / Junction' table between Departments and Functions so that I can retrieve which Functions are in which Departments. However, I would still need to differentiate the Division they belong to.
Research Effort
As you can see I am an abecedarian when it comes to database deisgn. I have spent the last two days resaerching this issue, traversing nested set models, adjacency models, reading that this issue is known not to be NP complete etc. I am sure there is a simple solution?
Adding a seemingly perfectly index is having an unexpectedly adverse affect on a query performance...
-- [Data] has a predictable structure and a simple clustered index of the primary key:
ALTER TABLE [dbo].[Data] ADD PRIMARY KEY CLUSTERED ( [ID] )
-- My query, joins on itself looking for a certain kind of "overlapping" records
SELECT DISTINCT
[Data].ID AS [ID]
FROM
dbo.[Data] AS [Data]
JOIN
dbo.[Data] AS [Compared] ON
[Data].[A] = [Compared].[A] AND
[Data].[B] = [Compared].[B] AND
[Data].[C] = [Compared].[C] AND
([Data].[D] = [Compared].[D] OR [Data].[E] = [Compared].[E]) AND
[Data].[F] <> [Compared].[F]
WHERE 1=1
AND [Data].[A] = @A
AND @CS <= [Data].[C] AND [Data].[C] < @CE -- Between a range
[Data] has about a quarter-million records so far, 10% to 50% of the data satisfies the where clause depending on @A, @CS, and @CE. As is, the query takes 1 second to return about 300 rows when querying 10%, and 30 seconds to return 3000 rows when querying 50% of the data.
Curiously, the estimated/actual execution plan indicates two parallel Clustered Index Scans, but the clustered index is only of the ID, which isn't part of the conditions of the query, only the output. ??
If I add this hand-crafted [IDX_A_B_C_D_E_F] index which I fully expected to improve performance, the query slows down by a factor of 8 (8 seconds for 10% & 4 minutes for 50%). The estimated/actual execution plans show an Index Seek, which seems like the right thing to be doing, but why so slow??
CREATE UNIQUE INDEX [IDX_A_B_C_D_E_F]
ON [dbo].[Data] ([A], [B], [C], [D], [E], [F])
INCLUDE ([ID], [X], [Y], [Z]);
The Data Engine Tuning wizard suggests a similar index with no noticeable difference in performance from this one. Moving AND [Data].[F] <> [Compared].[F] from the join condition to the where clause makes no difference in performance.
I need these and other indexes for other queries. I'm sure I could hint that the query should refer to the Clustered Index, since that's currently winning - but we all know it is not as optimized as it could be, and without a proper index, I can expect the performance will get much worse with additional data.
What gives?
Hi ,
I have a dataset with some 30 records in it. I want to update it to the database tables. which is the best method to update the table.
I am unable to use dataadapter.update() since i am using a procedure to populate the dataset.
is there any efficient way to update other than iterating through EACH record and updating it
Please help.
Thanks.
Hi,
I have a problem with group expressions in a list. I want to use two expressions:
'=Ceiling(RowNumber(Nothing)/3)' and '=Cint(Fields!kpilevel.Value)'
They work both individually, but when I insert them together only 1 works. I inserted them like this: http://img718.imageshack.us/img718/736/problemxq.png
Does anyone know how to solve this?
Thanks in advance, Kris
I have a form with an OleDbConnection object on it. This form fails to load in the Form Designer with the message:
One or more errors encountered while loading the designer.
The errors are listed below. Some errors can be fixed by rebuilding your project,
while others may require code changes.
Invalid authorization specification
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
... (stack trace continues into user code)
I've tracked this down to the OleDbConnection string. If I hardcode in the server IP, username/password/dbinstance into the constructor of the GUI form then the form will load in the designer. At run-time it is not an issue because we require the user to provide the login details.
The question:
Is it possible to use the OleDbConnection and the Form designer without supplying the database credentials in the source code of the form? For example, is there a property of the OleDbConnection or Form that I can set so that it doesn't need to access the database during Form design?
My concern is that if we ever move the database server or change the login that the code will stop working in the designer.
Is there a way to query a full text index to help determine additional noise words? I would like to add some custom noise words and wondered if theres a way to analyse the index to help determine suggestions.
i am getting below excetion on opening a form on visual studio work bench
Its not always but sometime it opens without any problem
Does anyone has a solution for this?
The path is not of a legal form.
Hide
at System.IO.Path.NormalizePathFast(String path, Boolean fullCheck)
at System.IO.Path.NormalizePath(String path, Boolean fullCheck)
at System.IO.Path.GetFullPathInternal(String path)
at System.Reflection.AssemblyName.GetAssemblyName(String assemblyFile)
at Microsoft.VisualStudio.Design.VSTypeResolutionService.AddProjectDependencies(Project project)
at Microsoft.VisualStudio.Design.VSTypeResolutionService.AssemblyEntry.get_Assembly()
at Microsoft.VisualStudio.Design.VSTypeResolutionService.AssemblyEntry.Search(String fullName, String typeName, Boolean ignoreTypeCase, Assembly& assembly, String description)
at Microsoft.VisualStudio.Design.VSTypeResolutionService.SearchProjectEntries(AssemblyName assemblyName, String typeName, Boolean ignoreTypeCase, Assembly& assembly)
at Microsoft.VisualStudio.Design.VSTypeResolutionService.GetType(String typeName, Boolean throwOnError, Boolean ignoreCase, ReferenceType refType)
at Microsoft.VisualStudio.Design.Serialization.CodeDom.AggregateTypeResolutionService.GetType(String name, Boolean throwOnError, Boolean ignoreCase)
at Microsoft.VisualStudio.Design.Serialization.CodeDom.AggregateTypeResolutionService.GetType(String name, Boolean throwOnError)
at System.ComponentModel.Design.Serialization.CodeDomSerializerBase.GetType(ITypeResolutionService trs, String name, Dictionary2 names)
at System.ComponentModel.Design.Serialization.CodeDomSerializerBase.FillStatementTable(IDesignerSerializationManager manager, IDictionary table, Dictionary2 names, CodeStatementCollection statements, String className)
at System.ComponentModel.Design.Serialization.TypeCodeDomSerializer.Deserialize(IDesignerSerializationManager manager, CodeTypeDeclaration declaration)
at System.ComponentModel.Design.Serialization.CodeDomDesignerLoader.PerformLoad(IDesignerSerializationManager manager)
at Microsoft.VisualStudio.Design.Serialization.CodeDom.VSCodeDomDesignerLoader.PerformLoad(IDesignerSerializationManager serializationManager)
at Microsoft.VisualStudio.Design.Serialization.CodeDom.VSCodeDomDesignerLoader.DeferredLoadHandler.Microsoft.VisualStudio.TextManager.Interop.IVsTextBufferDataEvents.OnLoadCompleted(Int32 fReload)
hi all,
I am a newbie in working on VS IDE and have not much experience in how the different libraries and files are linked in it. I have to build a OpenCV project which was made in VS2005 by one of my colleagues into VS2008. The project is for blob detection.
Following is what he has to say in readme :
Steps to use the library (using MSVC++ sp 5):
1 - open the project of the library and build it
2 - in the project where the library should be used,
add:
2.1 In "Project/Settings/C++/Preprocessor/Additional Include
directories" add the directory where the blob library is stored
2.2 In "Project/Settings/Link/Input/Additional library path" add
the directory where the blob library is stored and in "Object/Library
modules" add the cvblobslib.lib file
3- Include the file "BlobResult.h" where you want to use blob variables.
4- To see an example on using the blob library, see the file
example.txt inside the zip file.
NOTE: Verify that in the project where the cvblobslib.lib is used, the MFC Runtime Libraries are not mixed:
Check in "Project-Settings-C/C++-Code Generation-Use run-time library" of your project and set it to
Debug Multithreaded DLL (debug version ) or to Multithreaded DLL ( release version ).
2 Check in "Project-Settings-General" how it uses the MFC. It should be "Use MFC in a shared DLL".
NOTE: The library can be compiled and used in .NET using this steps, but the menu options may differ a little
NOTE2: In the .NET version, the character sets must be equal in the .lib and in the project. [OpenCV yahoo group: Msg 35500]
Can anyone explain me , how to go about in doing this in VS2008. I would also appreciate if someone can explain me how the different libraries are linked , what is Debug, What is Release and all in a Visual Studio project folder we have.\
Thanks in advance
Aditya
I have a question about how MS SQL evaluates functions inside CTEs. A couple of searches didn't turn up any results related to this issue, but I apologize if this is common knowledge and I'm just behind the curve. It wouldn't be the first time :-)
This query is a simplified (and obviously less dynamic) version of what I'm actually doing, but it does exhibit the problem I'm experiencing. It looks like this:
CREATE TABLE #EmployeePool(EmployeeID int, EmployeeRank int);
INSERT INTO #EmployeePool(EmployeeID, EmployeeRank)
SELECT 42, 1
UNION ALL
SELECT 43, 2;
DECLARE @NumEmployees int;
SELECT @NumEmployees = COUNT(*) FROM #EmployeePool;
WITH RandomizedCustomers AS (
SELECT CAST(c.Criteria AS int) AS CustomerID,
dbo.fnUtil_Random(@NumEmployees) AS RandomRank
FROM dbo.fnUtil_ParseCriteria(@CustomerIDs, 'int') c)
SELECT rc.CustomerID,
ep.EmployeeID
FROM RandomizedCustomers rc
JOIN #EmployeePool ep ON ep.EmployeeRank = rc.RandomRank;
DROP TABLE #EmployeePool;
The following can be assumed about all executions of the above:
The result of dbo.fnUtil_Random() is always an int value greater than zero and less than or equal to the argument passed in. Since it's being called above with @NumEmployees which has the value 2, this function always evaluates to 1 or 2.
The result of dbo.fnUtil_ParseCriteria(@CustomerIDs, 'int') produces a one-column, one-row table that contains a sql_variant with a base type of 'int' that has the value 219935.
Given the above assumptions, it makes sense (to me, anyway) that the result of the expression above should always produce a two-column table containing one record - CustomerID and an EmployeeID. The CustomerID should always be the int value 219935, and the EmployeeID should be either 42 or 43.
However, this is not always the case. Sometimes I get the expected single record. Other times I get two records (one for each EmployeeID), and still others I get no records. However, if I replace the RandomizedCustomers CTE with a true temp table, the problem vanishes completely.
Every time I think I have an explanation for this behavior, it turns out to not make sense or be impossible, so I literally cannot explain why this would happen. Since the problem does not happen when I replace the CTE with a temp table, I can only assume it has something to do with the functions inside CTEs are evaluated during joins to that CTE. Do any of you have any theories?
Hi. Does SQL Server maintains any history to track table alterations like column add, delete, rename, type/ length change etc? I found many suggest to use stored procedures to do this manually. But I'm curious if SQL Server keeps such history in any system tables? Thanks.
How can I insert Values from the comma separated input paramater to the Store prodcedure ?
Example is
exec StopreProcedure Name 17,'127,204,110,198',7,'162,170,163,170'
you can see that I have two Comma Separated Values in the parameter list , both will have same number of values if first have 5 comma seperated value then second one also has 5 comma separated values you can says
127 and 162 are related
204 and 170 are related and same for other
how can I insert these two values in ?
One comma Sepated value is inserted but how to insert two ?
I have the following query:
select .............
from
//one table and about 20 left joins//
where
(
(
this_.driverName like 'blah*'
or this_.renterName like 'blah*'
)
or exists (
select
this0__.id as y0_
from
ThirdParty this0__
where
this0__.name like 'blah*'
and this0__.claim_id=this_.id
)
)
order by
this_.id asc
And I have two environment:
One with 175 000 records in table "this_" and
second with 25 000 records in table "this_".
This query works right on 175k database and it works smth about 2 seconds, but on base with 25k this query freezes.
and if drop one the folloing item from where clause:
(
this_.driverName like 'blah*'
or this_.renterName like 'blah*'
)
or
exists (
select
this0__.id as y0_
from
ThirdParty this0__
where
this0__.name like 'blah*'
and this0__.claim_id=this_.id
)
query runs normally.
How can I to increase performance of this query?