sql-server

update columns when value is numeric in tsql

i want to normalize date fields from an old badly designed db dump. i now need to update every row, where the datefield only contains the year. update table set date = '01.01.' + date where date like '____' and isnumeric(date) = 1 and date >= 1950 but this will not work, because sql does not do short circuit evaluation of boolean expr...

SQL Server default values: why with one or two parentheses?

Hi, if you run this script to retrieve all default value definations in a database: select c.name as columnname, t.name as tablename, d.definition as value, d.name as constraintname from sys.default_constraints d join sys.columns c on d.parent_column_id = c.column_id and d.parent_object_id = c.object_id...

SQL Server: get top xx blog record from Umbraco by parameter...

Following SQL get what I need: SELECT TOP (50) [nodeId] FROM [dbo].[cmsContentXml] WHERE [xml] like '%creatorID="29"%' AND [xml] like '%nodeType="1086"%' ORDER BY [nodeId] DESC I need to pass in the numbers as parameters, so I have follows: exec sp_executesql N'SELECT TOP (@max) [nodeId] FROM [dbo].[cmsContentXml] WHERE...

Classic ASP SQL Server Database Connection

I am aiming to create a very basic web based DBMS for Microsoft SQL Server. However, in order to connect to a SQL Server database you seem to require a ODBC connection on the server. Is there any possible way to overcome this? ...

I want a insert query for a temp table

Hi..I am using C#.Net and Sql Server ( Windows Application ). I had created a temporary table. When a button is clicked, temporary table (#tmp_emp_answer) is created. I am having another button called "insert Values" and also 5 textboxes. The values that are entered in the textbox are used and whenever com.ExecuteNonQuery(); line comes, ...

Creating an SQL query to list all clients, who spent over 1000$

So, I have three tables with the following rows: Customers (Customer_id and Name), Orders (Customer_id, Product_id, Quantity) and Products (Price). How do I write a query which shows all customers who spent more than 1000$? Do I have to join the tables? ...

Changing Database Names and Cross Database Queries In Stored Procedures

Hi, I have a number of related databases that are version-ed. Instance of different versions may run side by side, identified by their different versions, i.e. [NorhwindV1.1] and [NorhwindV1.2] may be on the same server, along with [SouthwindV1.1] and [SouthwindV1.2]. There are a number of stored procedures that make cross database q...

Stored Procedure Permissions Problem

I have migrated a set of SQL 2000 databases to SQL 2008. Most is working well, however I have some stored procedures that scheduled and run by SQL Server Agent jobs that are giving me troubles. Many of the scheduled stored procedures work, but the stored procs that access a database other than the default databases are failing with the ...

Sql Server Replication: Snapshot vs Merge

Background information Let's say I have two database servers, both SQL Server 2008. One is in my LAN (ServerLocal), the other one is on a remote hosting environment (ServerRemote). I have created a database on ServerLocal and have an exact copy of that database on ServerRemote. The database on ServerRemote is part of a web application a...

SSIS PrimeOutput Error?

We have an SSIS job that has been running for over a year with no issue. The job takes a data set from a select statement in an oracle db and transfers the result to a table on a SQL Server 2005 instance. As of this morning, we receive the following error message: Error: 2010-05-26 05:06:47.71 Code: 0xC02090F5 Source: [job_name...

How to get 2 data sources to merge in SSIS?

Hi, I am fairly new to SSIS. I came across a situation where i have to use a data flow task. The data source is MS- SQL server 2008 and destination is Sharepoint list. I gave an SQl query for Data source object as SELECT Customer_ID, Project_ID, Project_Name, Project_Manager_ID, Project_Manager_Name, DeliveryManager1_ID, DM1NAME F...

Moving webshop storage to NoSQL solution

If you had a webshop solution based on SQL Server relational DB what would be the reasons, if any, to move to NoSQL storage ? Does it even make sense to migrate datastores that rely on relations heavily to NoSQL? If starting from scratch, would you choose NoSQL solution over relational one for a webshop project, which will, after a while...

Subsonic SELECT FROM msdb

Hi, I want to execute the following query using Subsonic: SELECT MAX([restore_date]) FROM [msdb].[dbo].[restorehistory] While the aggregate part is easy for me, the problem is with the name of the table. How should I force Subsonic to select from different database than default one. More details: This is the way I do it in my proce...

Microsoft OLE DB Provider for SQL Server error '80040e14' Could not find stored procedure

I am migrating a classic ASP web app to new servers. The database back end is migrating from SQL Server 2000 to SQL Server 2008, and the app is moving from Win2000 x86 to Win2003R2 x64. I am getting the above error on every single stored procedure call within the application. I have verified: Yes, the SQL user is set up, using corre...

Why is this function returning 0 rows when the data types are correct?

When the function is defined like this it will return the correct rows. ALTER FUNCTION [dbo].[exampleFunction]( @GUID VARCHAR(20)--NUMERIC(16,0) ,@BrowseName VARCHAR(200) ) But when I use Numeric(16,0), which is the correct data type for the data that's being passed, I receive 0 rows. ALTER FUNCTION [dbo].[exampleFunction]( @GUID N...

How to update Sharepoint list using SSIS?

Hi, I am using SSIS to transfer data from MS SQl server as data source and Sharepoint list as data destination. Now, I need to fire an update command on the sharepoint list. Please guide me to implement this. ...

do functions in sql server have different permissions rules?

Here's the situation. I'm writing an automated test that walks the list of dependencies for a proc and determines if an acct has rights for all of the dependent objects. My code looks like this: exec sp_depends 'the_proc_name' -- run this query on the results of sp_depends: select case when exists ( ...

Flattening a table that contains rows that reference other rows in SQL Server 2005

Hello, I am facing a problem that occasionally comes up when you deal with not fully normalized table. Here is the problem. Imagine a table with 4 columns, and let's call this table dbo.Hierarchical. Here is the definition of the table: if OBJECT_ID('dbo.Hierarchical') is not null drop table dbo.Hierarchical create table dbo.Hierarch...

how to save html to a database field

i have an tiny editor web page where my users can use this editor and i am saving the html into my database. i am having issues saving this html to my database. for example if there is a name with a "'" or if there are other html character "<,",">" etc, my code seems to blow up on the insert. Is there any best practices about taking an...

Composite keys as Foreign Key?

I have the following table... TABLE: Accounts ID (int, PK, Identity) AccountType (int, PK) Username (varchar) Password (varchar) I have created a composite key out of ID and AccountType columns so that people can have the same username/password but different AccountTypes. Does this mean that for each foreign table that I try ...