sql-server

[ADO] Why can't I get the recordset directly from the stored procedure?

I have a SP as follows. When I execute it with ADO, the first returned recordset is closed. I have to call the NextRecordset method to get the recordset I want. Why is that? How can I avoid the trouble? Thank you. create proc GetTeamCurrentPlan @ReleaseID varchar(30) as set nocount on declare @CurrentSp smallint set @CurrentSp=...

unixODBC Freetds PHP Problem

I am using Debian. I have unixODBC installed as well as FreeTDS. I am using PHP I have read several How-Tos and am stuck on a problem. I tested FreeTDS by using tsql and it works. I tested unixODBC by using isql and it works. When I created a script in PHP and tried to access a database I get the following errors. Fatal error: Call...

profile name is not valid

Hi, I have a windows account with users group and trying to exec the sp_send_dbmail but getting an error: profile name is not valid. However, when I logged in as administrator and execute the sp_send_dbmail, it managed to send the email so obviously the profile name does exist on the server.. can anyone pleas help? ...

Enabling broker after Restoring Sql Server DataBase

Hello, I have DataBase with enabled Service Broker. Then I want to restore my database in program from backup of other database, but after restoring(I restore on existing database name), my method, whitch enables Service Broker, puts this error: Msg 9772, Level 16, State 1, Line 1 The Service Broker in database "ServeDB2" cannot be ...

SQL Server SQL Select: How do I select rows where sum of a column is within a specified multiple?

I have a process that needs to select rows from a Table (queued items) each row has a quantity column and I need to select rows where the quantities add to a specific multiple. The mulitple is the order of between around 4, 8, 10 (but could in theory be any multiple. (odd or even) Any suggestions on how to select rows where the sum of a...

date ranges in where clause of index view

I have an index view which I run a query against with WHERE GETDATE() BETWEEN start_date AND end_date in the WHERE clause. I suspect that the answer to my question is probably going to be no, but, is it possible to push this into the index view or do something clever to achieve the same effect? My concern is that what the index vi...

INSERT from SAS 9.2 to SQL Server 2005 fails when table has a trigger

I connect to an SQL Server 2005 DB from SAS 9.2 via SAS/Access ODBC Driver. In the DB, I have a table my_table, which has an INSERT-trigger that copies all the inserted rows into another table in the database. In the SQL Server environment, the trigger works normally. However, SAS Proc SQL doesn't seem to get on well with a table with t...

Newly Created Function Is Not Available?

I am working on a scalar value function in MSSQL 2008. I created it and It is shown in object Explorer But when I use this function in MS Query Analyzer It gave me error **Msg 208, Level 16, State 3, Line 1 Invalid object name 'calculatecptcodeprice'.** For Me this type of error after creating Function is new. What went wrong. Please ...

date format conversion

I have date of Birth of the format 04/08/2010(DD/MM/YYYY) and sql server store of the format 7/14/2010 12:00:00 AM (MM/DD/YYYY) and i wan to check my current date from the stored database dates for the all dated greater than equal to the current date so how can i first change the formate of date and then can get appropriate result from ...

consitent vs peformance database design for db polymorphism with versioning

I need to create a database for 2 types of farmers (organizations and landlords) which have different columns that need to be stored and I also need to version their info. One db design that I came up with is this: (the consistent one) Farmers Id FType check(FType in ('organization', 'landlord')) Unique(Id,Ftype) Organizations Id ...

Foreign characters lose their diacritics

I'm trying to internationalize the questions in our survey-tool, but when I insert some translated strings, SQL-server seems to strip of some, but not all, diacritics... Example: (Lithuanian) Ar jūsų darbas reikalauja, kad jūs įgytumėte naujų žinių ir įgūdžių? Becomes Ar jusu darbas reikalauja, kad jus igytumete nauju žiniu ir igudž...

SQL Server and Oracle read / write roles

Hi, I need to create some roles (I don't know how to call it correctly, so I'll name it 'role :)) on SQL Server and Oracle, and I really don't know much about both DB servers, so I don't know what I should look for. I need to create 2 roles: users who can only filter data from tables (SELECT). users who can modify data (i.e. DELETE st...

SQL 2000 - Enterprise Manager & Triggers

We use SQL 2000 Enterprise Edition. We have noticed that the triggers are not executed when we make data changes using the Enterprise Manager. That is, we execute a "Select" query, and we edit the results directly in the grid results table. The trigger is valid since it is executed as expected if we run an update query. I am just wonde...

SQL Server 2005/2008 - Why is the sys.sysobjects view available to users without the schema name?

I've noticed some strange behavior in SQL Server Express 2008, and I've been trying to track down the cause. When you create a new database, you are able to access the sys.sysobjects view without specifying the schema identifier, as follows: SELECT * FROM [sysobjects] You are able to do this even if your default schema is something ot...

How do I do a full text search in Sql Server 2008 where the column contains multiple languages?

I have a database table in Sql Server 2008 R2 which contains data stored in multiple languages including English, Swedish, Hungarian and German. The table uses the Latin1_General_CI_AS collation. The full text catalog has the table assigned to it with an index on the multi-language column. I have two problems: In the catalog properti...

How to do Encryption and Decryption in Sql Server 2005

I want to encrypt and decrypt data with sql server using RSA or SHA algorithm rahter than doing it at UI. Is it possible to do this. Some Code Snippet will be a great help ...

sql query takes more time when run in a view

HI all, I have a huge sql query. When i put that query in a stored Proc it takes 5 seconds to execute which i run it just as a query it takes 4-5 seconds but when i run it in a view it takes 5 mins. Please advise why its running that slow in a view sql query is below: CREATE VIEW dbo.Client_Billing_RS AS SELECT DISTINCT...

How insert a NOT NULL column to an existing table

How to add a column with NOT NULL to an existing table? I have tried like: ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL; But it gives the error message like: "ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified" So, can anyone help me? ...

Difference between Sql Server 2000, 2005 and 2008

HI All Can any one have tabular info regarding the difference between Sql Server 2000, 2005 and 2008 Thanks Kishh ...

T-SQL CTE Question (SQL Server 2008)

Hi, Why does the following query work, when there is no column named Agentid in the "NewAccounts"-CTE? WITH NewAccounts AS ( SELECT 3 as Newaccountid ), MovedAUM AS ( SELECT 1 AS Agentid, 2 as Col2 ) SELECT * FROM MovedAUM WHERE agentid IN (SELECT Agentid FROM NewAccounts) The following, modified query returns the error mess...