Do T-SQL stored procedure transactions which include statements such as CREATE table and DROP table roll back effectively if an error occurs? i.e. will a transaction roll back even if a table has been deleted/dropped within that transaction? ...more >>

I'm attempting to use a Select statement to loop through a temporary table instead of using a cursor, but am experiencing difficulties with the Case statement that processes each record. The error is 'Incorrect syntax near the keyword CASE'. I've eliminated all of the unnecessary code for brevi...more >>

Is it possible to change the 'sa' password? I looked in the "Users and Groups' area and don't see it so I assume it's internal to SQL Server? I am running SQL 2000. Thanks! Mark ...more >>

Example 1 works fine ------------------------ In SQLServer-2005 created and tested successfully: STORED PROCEDURE theProcedure ENDPOINT theEndPoint pointing to theProcedure GRANT CONNECT ON ENDPOINT:: theEndPoint TO theUser In VB.NET 2005: Added WEBREFERENC...more >>

I have a table that has ten columns. The user would like to select row based on 1 to 5 of the columns. For example, the user would like to enter 'State' = 'FL' and 'City' = 'Miami' and 'Class' = '05' and not enter the other two parameters. If I pass a null, the query does not work. How can...more >>

This query was generate by Microsoft SQL Server Management Studio by right clicking a table and selecting script to. When I run the query against a different database (2k), I get the following error message: Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near '('. Below ...more >>

I guess I don't understand how to use the "if" statement. Consider the following which does not work as expected. The select statement always executes even if the current login does not have permission. What am I doing wrong? if (Has_DBAccess('msdb') = 1) begin select max(backup_fini...more >>

Hi, I have a question on sql injection attacks. I am building a tool that will be used exclusively by our other developers and will generate stored procs for them dynamically based off input from them. I wanted to add a "parser" functionality where based off the table and where clause they ...more >>

Recently created a database, in which one table has 28million rows, a query ran on this database takes about 3 mins to run, is there anyway/tips to increase performance ? I ran the query through the tuning advisor and there was no recommendations, currently running a trace on it at the mo. S...more >>

Hi Group, I have tried in rendering report from MS reporting services through generated stub class using Axis2-nightly build. And the Axis2 generated class not parsed the rendered result( in soap xml response). I had checked the soap response using tcpmon and it seems response has rendered re...more >>

I want to create a tool that traces sql statements like profiler does, but is used within a web app to troubleshoot issues. I have too many users to just start a profile and hope I find my own spid. I am curious to know where Profiler gets its data from. Is it Sysprocess table only? are ther...more >>

I want to create a tool that traces sql statements like profiler does, but is used within a web app to troubleshoot issues. I have too many users to just start a profile and hope I find my own spid. I am curious to know where Profiler gets its data from. Is it Sysprocess table only? are ther...more >>

Hi I have 1 app that always make SELECT count(DISTINCT cedula) FROM(matrix_odontograma) This is very slow in 1 MB of record, exist any way to make this more efficient ? ...more >>

Hi all, I have one table with many rows, and now I need change data type of one = column (nosso_numero INT), the type is INT and I need change it to = BIGINT, but I can't lost old rows. Have any way to do this? HOW? CREATE TABLE documentos_clientes_banco ( .... nosso_numero INT...more >>

In SQL Server 2005, I have created a stored procedure and run it in master so that it can be used universally from any database. The stored procedure displays database roles, objects, and permissions. The problem is that when I run it in master SSMS automaticall adds the line "USE MASTER" a...more >>

Hi, Using this string I get the error Run-time error ';214217900 line1: incorrect syntax new '01' Set rdset = CONN.Execute("Select Name From TimeTable Where City=0 AND RDate=#" & Format$(scheduledtime_date.Text, "mm/dd/yyyy") & " " & Format$(scheduledtime_time, "HH:MM") & "#") I was us...more >>

Hello All - I have written a process that allows a client to setup (in a queue) some emails which need to get blasted out to their users. They can use the interface to create these "blasts" at any time, however, using SQL Scheduled Jobs, a process runs every 15 minutes that checks for data in...more >>

I need to derive address information from the zip code. I have a zip code file that I need to put into a SQL table. What's the best way to do this?...more >>

I need to derive address information from the zip code. I have a zip code file that I need to put into a SQL table. What's the best way to do this?...more >>

I need to derive address information from the zip code. I have a zip code file that I need to put into a SQL table. What's the best way? ...more >>

I try to write a SQL query to return top sales companies in two years. The subquery returns that the top 10 companies in each year. But I get the error message as follows. Server: Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is ...more >>

Hi folks, What is the main reasons to create the web services from 2005 SQL server, instead from IIS ? Except the following two reason: 1. no need IIS 2. the content of developing work Please advise. Thanks. Peter...more >>

I want to run a bat file from xp_cmdshell. The problem is the bat file executes many file commands using relitive paths (and changing that is not an option). xp_cmdshell executes from "C:\WINDOWS\system32", so when I run the bat file from xp_cmdshell, it cant find any of the files the ba...more >>

Hello, I'm trying to something that just works in Oracle, but does not in Sql Server. How can I get the percentage of two counts to be returned on each row of the query? select count(sid), /* all not the not null */ count(*), (count(sid) / count(*) ) as percent_not_null, 4 as four...more >>

Does anyone know how to measure or how to investigate (specifically when a large query is generated) how much data is transferred along the network for a small office network. Clients use Access front end to bring back reports. thanks for any help Sammy...more >>

Does any one have any good references or books that would direct me as to how to set up an employee scheduling databse? Or example diagrams, tables, etc... I am in charge of creating such a databae and dont know where to begin. I have tried creating multiple tables and getting something ...more >>

Hi, I have a function that is declared as CREATE FUNCTION Get_Items ( @ItemList VarChar(400) ) RETURNS TABLE AS RETURN ( SELECT * FROM Items WHERE (ID IN (@ItemList)) ) my @itemlist is '1','2','3' so when I say Select * from Get_Items('1','2','3') it does not execute I do no...more >>

A poster in another forum has asked what are the ramifications of just adding 1 to a date (e.g., getdate() + 1) vs. using adddate(). While I clearly advise using adddate(), I realize that I don't have a good response to the question of "what's the diff?" Suggestions? -- Arnie Rowland,...more >>

I used to use this string in VB6 while connecting to MS Access 2000 1) Dim conn As ADODB.Connection Dim rdset As ADODB.Recordset Set conn = New ADODB.Connection FilePath = App.Path & "\data.mdb" With conn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionStri...more >>

That was very helpful. This is my first attempts at this kind of stuff so I appreciate the help. Your example does seem do address the problem in a much more efficient way. I have been banging my head against a wall trying to figure this out. It seems simple enough, but I just dont think...more >>

So far I have downloaded and installed: SqlServer2K5_BOL_Jul2006.msi SQL2005RPSPS.msi which I probably did not need SQL2005RPFIN.msi SQL2000SampleDb.msi SQLEXPR_ADV.EXE Stopped. It said everything was already installed AdventureWorksDBCI.msi SqlServerSamples.msi ...more >>

I am don't speake english but I to try write my problem... I want comparing the objects (tables, views, etc) from databases, but don't obtain the list of objects of all databases and not obtain the correctly way of execute the macro Example: Set @ExecSQL = 'Select * from sysObjects Whe...more >>

In Transact SQL, how can I make variable a table name ( or possibly a column name ...... ) ? For instance, in 'Select * from TableName', the name of the table is variable and known only when running. I wish to do that "inside" Transact SQL. I searched, and did not found ... Thanks for help....more >>

In Transact SQL, how can I make variable a table name (or possibly a column name ......) ? For instance, in : 'Select * from TableName', the name of the table is variable and known only when running. I wish to do that "inside" Transact SQL. I searched, and did not found ... Thanks for help....more >>

Hi, This is a question that occasionally pops up but that I haven't come up with a good answer to. Table 1 is linked to Table2 in a many to many relationship through Table12. Table1 and Table 2 both have FKs to Table 4. They should only be linked if this FK is the same in both tables. So wh...more >>

Hi all. I have some sql like SELECT * FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\aaa.xls;', 'SELECT * FROM [Sheet1$]') Which works perfectly on our development machines (Windows 2003, SQL Server 2005 SP1). When I take it across to our production machines a...more >>

I have a SQL Agent job scheduled to run once per day, but it takes more time to complete than one day . So, what happens is that after onday it used to hang the DB . It does not do anything. Is there any way to get out of it ? Is it happening because of the Schedule or is there anything like ...more >>

Can anyone advise how to select the names of columns of a table? Thanks Soc. ...more >>

Hi All, I'm pretty new to using these technologies so bear with me. I have created a c# app which communicates with sql-server express 2005. When I run an insert on a table via the SqlCommand class nothing seems to happen! No errors are apparent either. If I check the table contents in the ...more >>

I'm trying to construct a query to pull the results (itemcount) from a dynamically-populated table (acheck_54_1) based on the values of a specific field - checkid. However, if there isn't a specific checkid in the table, the query will return nothing, including nothing for the checkids that are ...more >>