sql-server

generate table DDL via query mysql mssql

Hi, Is there an easy way to extract table DDL information, via a query, using either Ms or My SQL server? (preferably both?) For example, using MySQL Administrator / Navicat for MySql, there is a "DDL" function, which generates the "create table foo (....)" script. Is there any way to get this information from a query itself, such as...

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 127.0.0.1]

got the below error while trying to connect to sql server 2005 using JDBC connection. Error: 17806, Severity: 20, State: 2. Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 127.0.0.1] Logon ...

SQL Server VARCHAR(10)

When desiging a table in SQL Server Management Studio 2005, if I specify VARCHAR(10) for a column, when I tab away from the data type, SQL Server changes it to VARCHAR(50). I have to then tab back, change it back to VARCHAR(10), and it will then accept it. I understand that there are storage and processing considerations that may make V...

SQL Server, views and datetime conversions...

So I have a base table that looks something like this. SELECT [BILL_MONTH] ,[BILL_YEAR] ,[BILLED] FROM bill_Detail Everything is varchar. I created a view based on that table that converts the bill year and bill month to a datetime field. Of course, the bill_month and bill_year fields have some crap data that doesn't co...

DataReader Behaviour With SQL Server Locking

We are having some issues with our data layer when large datasets are returned from a SQL server query via a DataReader. As we use the DataReader to populate business objects and serialize them back to the client, the fetch can take several minutes (we are showing progress to the user :-)), but we've found that there's some pretty hard-c...

How to save a single result set to a temp table from a SQL stored procedure returning multiple sets?

I need to store the result set of a stored procedure in a temporary table (using SQL Server 2000). From what I've read, this (poorly constructed example) should work: create table #tempTable (TempId int primary key, Column1 varchar(100), Column2 varchar(100), DateCreated datetime) insert into #tempTable (TempId, Column1, Column2, Dat...

Asp.net MVC database connection setting in Visual studio 2008

I am trying to learn Asp.net MVC framework. I was looking at the video tutorial at the link below http://www.asp.net/learn/mvc-videos/video-395.aspx In this video the very first step is to add a new database to the example application. I have visual studio installed on my development machine but the SqlServer Express is running on a diff...

Bulk Insert In SQLCE

Hi, i am new at SQLCE and using Visual Studio 2008,SQLCE 3,5.I want to know that what the best way of inserting bulk records into sqlce database?Does BULK INSERT statement work for SQLCE?I have near 45 tables and some of these tables should have large amount of data.Using command.Prepare is enough or is there more efficent and fast way?...

Transactions within loop within stored procedure

I'm working on a procedure that will update a large number of items on a remote server, using records from a local database. Here's the pseudocode. CREATE PROCEDURE UpdateRemoteServer pre-processing get cursor with ID's of records to be updated while on cursor process the item No matter how much we optimize it, th...

mssql_bind empty string converting to NULL

I am currently using SQL Server 2000 Stored Procedures with PHP. Following the PHP doc, I use mssql_bind to assign value of parameters and then execute the Stored Procedure. The problem is that I got this bug which prevents me to bind empty strings to parameters (they are converted to NULL when the Stored Proc gets called) I dont't wan...

Saving the State of a System

A very flowery title indeed. I have a PHP web application that is in the form of a web based wizard. A user can run through the wizard and select options, run process (DB queries) etc. They can go backwards and forwards and run process again and again. I am trying to work out how to best save the state of what users do/did, what proces...

What happens to an uncommitted transaction when the connection is closed?

Are they rolled back immediately? Are they rolled back after some period of time? Are they left in an uncommitted state? Is the behavior the same if connection pooling is used and the connections are simply reset? ...

T-SQL Where Clause Case Statement Optimization (optional parameters to StoredProc)

I've been battling this one for a while now. I have a stored proc that takes in 3 parameters that are used to filter. If a specific value is passed in, I want to filter on that. If -1 is passed in, give me all. I've tried it the following two ways: First way: SELECT field1, field2...etc FROM my_view WHERE parm1 = CASE WHEN @PARM...

Searching for table/index scans

Does anyone have a query which searches through SQL2005/2008's plan cache identifying queries or stored procedures which have table/index scans within their execution plans? ...

SQL server table population source

Hi, I have an Audit database(created by someone else). Something is polulating it, with table sizes data (which makes sense as it is Audit database). The SQL server has too many jobs. I want to know what is populating the audit tables. Is there anything like sys.comments etc? which can tell me what is populating tables or do I have ...

SQL 2008 Reporting Services: I have no rights

I've just got to trying out Reporting Services and, although I'm a member of the local Administrators group in Windows, I can't do anything in Report Manager or by connecting to SSRS in SQL Management Studio. In Report Manager (it prompted me for my Windows credentials) I can see the Home page but I have no links to administer security e...

Ensuring unique numbers from a sql server database

I have an application that uses incident numbers (amongst other types of numbers). These numbers are stored in a table called "Number_Setup", which contains the current value of the counter. When the app generates a new incident, it number_setup table and gets the required number counter row (counters can be reset daily, weekly, etc ...

sql 2005 force table rename that has dependencies

How do you force a rename??? Rename failed for Table 'dbo.x. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+%28%28Katmai%5FPCU%5FMain%29.090329-1045+%29&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&Evt...

2005 SQL Reporting Services Dataset filter with OR not AND

Under Dataset Filters Tab, I want to use OR not AND but when I add a second Filter the AND appears in the And/Or column with no way to change it. Am I missing something? ...

SQL server- DTS package: minimum rights

I created a DTS package. Someone else wants to be able to save it after modifications but he is getting error: only sysadmin and DTS creator can save versions of DTS. I have given him dtsadmin rights in msdb and he has db_owner rights in all the Databases which link to this DTS package. I dont want to give him sysadmin rights. What i...